How can I configure MySQL Innodb to handle 1000s of inserts per hour?

Question

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

Answer 1

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.

Answer 2

This could be as simple as one part of your code running a transaction:

insert into t1...
insert into t2...
commit;

while another part of your code modifies the same tables in a different order:

delete from t2 where...
delete from t1 where...
commit;

If both of those transactions run at the same time, a race condition can occur: the first transaction can't modify t2 because it's being locked by the second transaction; while the second transaction is similarly blocked because t1 is locked by the first transaction. MySQL chooses one transaction to be the "victim" where the INSERT/UPDATE/DELETE fails. The application needs to catch that error, and retry the statement - maybe after a pause so the other transaction has time to finish. Nothing to do with capacity limits, just unfortunate timing that can be exacerbated by the way the code is arranged. Switch around the DELETEs in transaction #2, or the INSERTs in transaction #1, and then there is no conflict - each transaction will wait for access to the table(s) it needs.

In MySQL 5.6, you can run with the innodb_print_all_deadlocks option enabled to collect info about all deadlocks (not just the most recent one) in the MySQL error log.

[Obligatory Disclaimer: I am an Oracle employee. The above is my personal view not an official statement.]