InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
14 views

Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table. The table in question is an InnoDB table and the ...
0
votes
0answers
6 views

How to diagnose MariaDB crash when changing InnoDB column to AUTO_INCREMENT?

I have this table, and I'm trying to change the first column to AUTO_INCREMENT: +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
0answers
16 views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
0
votes
0answers
15 views

MySQL on Server side PRIMARY KEY taking automatically random Id

I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values. inserted id's like 1, 2, 35, 67, ...
0
votes
1answer
25 views

In which situations is TokuDB higher performance? [on hold]

I noticed that WordPress runs quite a bit faster when I use XtraDB (MariaDB's InnoDB replacement) instead of TokuDB. This is strange, because TokuDB is advertised as being 20x faster. However, I ...
0
votes
1answer
22 views

InnoDB error: exceeded log capacity…but only during import

I see this error in mysql.log: InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc.. I think I understand it, but the fact is that it's happening only during a ...
0
votes
1answer
19 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
1
vote
0answers
38 views

innodb slower much slower than expected

Trying to figure this one out and I apologize for not providing a lot of information from the start (if you need further let me know and I can try to provide it). Query is performing very poorly - it ...
1
vote
2answers
67 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
0
votes
3answers
23 views

Mysql Create table shows ERROR 1005 errno 150

I am trying to make a junction table, but I getting a ERROR 1005 errno 150 CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage ...
2
votes
1answer
52 views

Super slow (day long) count(*) after upgrade to 5.6

After upgrading to Percona 5.6.20 from 5.5.34 I noticed counts on larger tables take a massively long time to execute. The table is about 27G and 300M rows. It is not compressed and was innodb ...
0
votes
1answer
18 views

How to combine duplicate parent rows in a relational MySQL table

I have my DB structured like this with people.person_id as the foreign key for each row in people_meta. people table ╔═════╦════════╦═══════╗ ║ id ║ fname ║ lname ║ ╠═════╬════════╬═══════╣ ║ ...
1
vote
0answers
50 views

MySQL keeps inserting new rows automatically [closed]

I have no clue of what is going on. My script goes this way: $i = 0; $c = $somecount; while($i < $c){ $en = comment_version($comments, $i, 'en'); $ru = comment_version($comments, $i, 'ru'); ...
0
votes
2answers
64 views

Very high load avg for MySQL database [closed]

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate. In ...
1
vote
1answer
29 views

I broke replication. Could this have been fixed?

I ran mysql_upgrade on both my master and slave after upgrading to a later version of mysql. Somewhere during this process replication broke for the mysql table. I was able to fix it manually, but ...
1
vote
1answer
32 views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
0
votes
1answer
19 views

Identifying root cause of slow query outliers from pt-query-digest?

I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of ...
0
votes
1answer
30 views

Sudden periods of performance degradation

For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version ...
1
vote
1answer
40 views

Shut Down MySQL during InnoDB rollback after crash

I am puzzled once again. We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is ...
0
votes
0answers
30 views

innodb fulltext Table handler out of memory

I am running mariadb 10 and have a large innodb table with a fulltext index. When searching I am regularly getting this error ERROR 128 (HY000): Table handler out of memory Is there some way I ...
0
votes
2answers
51 views

MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem ...
0
votes
1answer
33 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; ...
0
votes
0answers
14 views

Does storing page views in my page table slow performance?

I am storing page views in a view_countcolumn on my page table. So every time the page gets loaded I increment the view count and update the entry. What affect can this have on the performance of my ...
0
votes
1answer
54 views

MySQL keeps crashing

I've restored my vserver from a backup which was created by my host. After that my MySQL server is not starting anymore. I've tried several ways to recovery my tables, but it's still not working. ...
0
votes
1answer
23 views

Fastest way to alter column type in a 2-billion+ records innodb table

I have a MySQL table with 4 columns. The 1st column record_id is of type int, primary key, and auto-incremented. Now that I have added more than 2 billion records to this table I am finding that I ...
0
votes
1answer
25 views

innodb fulltext ibdata1 size

I am running mariadb 10 and have a large innodb table with a full text index. I have innodb_file_per_table=ON I've noticed though that the ibdata1 file grows at the same rate as my table file. I ...
1
vote
1answer
35 views

Innodb issue is preventing MySQL from starting

For some reason mysqld refused to start after I rebooted my server. Now when I try to start it I will get the following error: [root@vps1 ~]# service mysqld restart ERROR! MySQL server PID file ...
2
votes
2answers
134 views

High MySQL “InnoDB Writes Per Second” - Can't see the queries?

On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc. I noticed today that on the "Server Status" tab there's ...
0
votes
1answer
20 views

Automatic slave reconfiguration after updating master

I have development mysql master and slave servers and a prod server. I need to set up regular (once a week) syncronization of dev and prod databases. It would be easy for me to use prod# mysqldump > ...
0
votes
1answer
68 views

Very slow response even with a high number of available connections

I'm running this server for data mining purposes. It runs several compute-intensive data mining applications in parallel and does simultaneous access to the MySQL server. Here are the ...
0
votes
1answer
55 views

Appropriate use of mysql Optimize Table

I want to come up with some best practice for maintaining our mysql database. versions 5.5/6 and using InnoDB. i came across this artice - ...
0
votes
0answers
7 views

Why pages not made young is zero?

In results of Show INNODB Status showed me very weird result: Pages made young 81821279, not young 0 The official statement says: Pages made young, not young: The number of old pages that were ...
0
votes
1answer
31 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
0
votes
0answers
39 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
101 views

mysql - Recovering databases from ibdata1 , .frm and .idb

I have a server running a mysql instance which crashed today. Now there are a massive amounts of tables in multiple database exceeding over 200GB stored on disk. After the server crash mysql is ...
2
votes
1answer
60 views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (suprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
1
vote
1answer
48 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
0answers
15 views

Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

I'm currently converting large tables to the Barracuda file format. We are using file_per_table. I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this. ...
2
votes
1answer
55 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
0
votes
1answer
41 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
1
vote
0answers
34 views

mysqldump with --single-transaction seems to be “fixing” InnoDB tables with page corruption - why?

I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest ...
2
votes
2answers
58 views

Should we enable innodb_file_per_table for single tenant SaaS app (thousands of databases)

We have a mySQL backed SaaS application where each customer has their own mySQL database. So think of it like a wordpress/magento etc. database for each client. Each database has ~200 table, and we ...
0
votes
0answers
46 views

Why is MySQL database is having irregular performance issues?

I first thought it was because of some bad queries, but it wasnt. It took 90 secs to execute a select statement on a table with two rows one column while during other times it executed within ms. ...
1
vote
1answer
41 views

MySQL FullText search on string shorter than 3 chars returns no rows

I have a MySQL table with some addresses in it. Let's say I have the following 2 rows: "10 Fake Street" "101 Fake Street" I'm trying to use fulltext search with MATCH() AGAINST(). I have set ...
0
votes
1answer
22 views

Importing Data with Single .ibd file

I have one ".ibd" file with me. I have configured my local Database server with FILE_PER_TABLE option. i have two question here. When i was trying to restore/import the IBD file, the engine expect ...
0
votes
0answers
46 views

InnoDB says I'm in Deadlock when it should not be

I'll do my best to explain the situation. I'm at a complete loss. Preliminaries I'm running a Java Web Application in Tomcat 7.0 using a JDBC Connector to communicate with my MySQL Database. The ...
0
votes
0answers
17 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
0
votes
1answer
36 views

MySQL my.cnf: innodb_log_file_size is missing

How to set innodb_log_file_size to MySQL database if there is no innodb_log_file_size in my.cnf configuration file? I'd like to backup via Percona Xtrabackup and the backup crashes at this point ...
0
votes
0answers
27 views

innodb_buffer_pool & innodb_buffer_pool_instances

I have a database dedicated server with one quad-core cpu and 48GB Memory. Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system. At First, to ...
1
vote
2answers
28 views

Is it safe to delete the innodb schema on a new Amazon RDS instance?

As per the title, I'm struggling to find out if it's safe to delete the innodb schema that's created on a fresh Amazon RDS instance. mysql> SHOW DATABASES; +--------------------+ | Database ...