Mysql – How to configure MySQL Innodb to handle 1000s of inserts per hour

deadlockinnodbinsertMySQLperformance

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour.

This one error is crippling the site:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 
Deadlock found when trying to get lock; 
try restarting transaction: INSERT INTO {location_instance} 
(nid, vid, uid, genid, lid) VALUES (:db_insert_placeholder_0, 
:db_insert_placeholder_1, :db_insert_placeholder_2, 
:db_insert_placeholder_3, :db_insert_placeholder_4); 
Array ( [:db_insert_placeholder_0] => 1059 [:db_insert_placeholder_1] => 
1059 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] => 
cck:field_item_location:1059 [:db_insert_placeholder_4] => 1000 )

I would be very surprised if MySQL could not handle this type of load. So, my questions are then, is this a database issue and how can I configure MySQL to be able to handle this much traffic?

I have a copy of my website set up on a development server with scripts that simulate the load of content being added to the website. I am running Ubuntu, LAMP stack, with 16GB of RAM.

Admittedly, I am not very knowledgeable about databases. In fact, I am starting with the default my.cnf that comes with it after 'apt-get install' finishes. Tables are all Innodb. What starting configuration settings and approach would you recommend to begin solving this problem?

Let me know what more information you may need.

Thanks

Best Answer

You are dealing with a deadlock, not a performance bottleneck issue.

If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.

Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in some specific situations.

InnoDB can show you detailed deadlock information by running SHOW ENGINE INNODB STATUS at the MySQL prompt, or with mysql -uroot -p... -e "SHOW ENGINE INNODB STATUS".

However, this only shows the last deadlock that occurred, there is no deadlock log.

Thankfully, there's a tool pt-deadlock-logger which takes care of that problem, it takes care of polling InnoDB status and saves all the detailed deadlock information before it's refreshed with a new deadlock.

Related Question