Mysql – thesql INNODB inserts very slow

innodbMySQL

The database's schema is as follows.

 CREATE  TABLE  `items` (  
 `id` mediumint( 8  )  unsigned NOT  NULL  AUTO_INCREMENT ,
 `name` varchar( 45  )  NOT  NULL ,
 `main_type` tinyint( 4  )  NOT  NULL ,
 `rarity` tinyint( 4  )  NOT  NULL ,
 `stack_size` smallint( 6  )  NOT  NULL ,
 `sub_type` tinyint( 4  )  NOT  NULL ,
 `cost` mediumint( 8  )  unsigned NOT  NULL ,
 `ilvl` smallint( 6  )  unsigned NOT  NULL DEFAULT  '0',
 `flavor_text` varchar( 250  )  NOT  NULL ,
 `rlvl` tinyint( 3  )  unsigned NOT  NULL ,
 `final` tinyint( 4  )  NOT  NULL DEFAULT  '0',
 PRIMARY  KEY (  `id`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = ascii;

Now, doing an insert on this table takes 0.22 seconds. I don't know why it's taking so long to do a single row insert. Reads are really really fast something like 0.005 seconds. With using the example configuration from here dev mysql innodb it averages ~0.002 to ~0.005 seconds. Why it takes more than 100x more time to do a single insert makes no sense to me. My computer is as follows. OS:Debian Sid x86-x64, Mysql 5.1, RAM:4GB ddr2, cpu 2.0Ghz dual core, HDD 7200RPM 32MB cache 640GB.

Why it's taking almost 100x as much time for a SELECT * FROM items; vs INSERT INTO items …; will never make any sense to me. It's still a small table at only 70 rows, and took that long even when it had 0 rows.

Edit: Also this table has quite a few other tables linked to itself via the id. There's a few of them out there that are linked to it and do an on update=cascade; on delete=cascade;. I believe that that is the biggest issue here. If it is then, i can probably go in and change it and do individual deletes from the various little things when they are removed. The insert speed seems to be ~0.2 seconds whether i'm doing the insert on just items or i'm also doing it on another table that has a foreign key link to the main one.

Best Answer

Well, my first guess is that your InnoDB is probably broken. You can check whether there aren't any

triggers that would do slow operation on insert
processes going on that would lock the table
foreign keys/constraints pointing to this table

Best way to completely audit a database against anything that would cause such trouble is to read schemas dump from mysqldump command.

Related Solutions

Mysql – InnoDB: Error: log file ./ib_logfile0 is of different size

InnoDB is insanely picky about it's config; if something's not right, it'll just give up and go home. To make a change to the log file size without data loss:

Revert any config changes you've made to the log file size and start MySQL again.
In your running MySQL: SET GLOBAL innodb_fast_shutdown=0;
Stop MySQL
Make the configuration change to the log file size.
Delete both log files.
Start MySQL. It will complain about the lack of log files, but it'll create them and all will be well.

MySQL InnoDB – innodb_file_per_table cons

I have the complete answer for this one.

Once innodb_file_per_table is put in place, and new InnoDB tables can be shrunk using ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; This will shrink new .ibd files GUARANTEED.

If you run ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; on an InnoDB table created before you used innodb_file_per_table, it will yank the data and indexes for that table out of the ibdata1 file and store it in a .ibd file, This will leave a permanent pigeon whole in the ibdata1 that can never be reused.

The ibdata1 file normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollback Segments
- Undo Space
Table Metadata (Data Dictionary)
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary indexes)
See the Pictorial Representation of ibdata1

Here is the guaranteed way to shrink the ibdata1 file pretty much forever...

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql, information_schema and performance_schema schemas)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1 (see update below before deleting!)

At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) Restart mysql

This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1 at 1G each

STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata and intermittent MVCC data.

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE mydb.mytable ENGINE=InnoDB; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this numerous times in my career as a MySQL DBA without so much as a single problem thereafter. In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 50MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul.

UPDATE 2013-07-02 15:08 EDT

There is a caveat I have in this regard that I updated in other posts of mine but I missed this: I am updating my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

Best Answer

Related Solutions

Mysql – InnoDB: Error: log file ./ib_logfile0 is of different size

MySQL InnoDB – innodb_file_per_table cons

UPDATE 2013-07-02 15:08 EDT

Related Topic