Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
You are starting with the default my.cnf for production? Man, you gotta optimize it more. Will detail you more in my answer. :-) –  Ashwin kumar Oct 8 '12 at 12:18

3 Answers 3

up vote 6 down vote accepted

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 INNODB STATUS at the MySQL prompt, or with mysql -uroot -p... -e "SHOW 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.

share|improve this answer
    
Good to know! I do see in the show status command the information about the lock and related query and table. Given that this is in my code, how can I use this information from the status command to begin to debug the issue? The PHP PDO query is pretty straight forward - connect, prepare, execute, repeat. I'd be happy to post any code or the status message if that would help. –  user658182 Oct 6 '12 at 1:16
    
@user658182 check this post on stackoverflow on how to deal with deadlocks: how to avoid mysql deadlock found when trying to get lock –  Valor Oct 7 '12 at 20:35
1  
@max-vernon thanks for the syntax edit, clearly english it's not my native language :-). –  Valor Oct 7 '12 at 20:37
    
-e "SHOW ENGINE INNODB STATUS" –  glyph Feb 7 at 22:10

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.]

share|improve this answer

I completely agree with Valor answer above.

Your question on "What starting configuration settings and approach would you recommend to begin solving this problem?"

Checkout to know basics to secure your database.

In detail, I have few suggestions for you:

  1. Default MySQl instance have root user without password. Add a password

  2. Default installation leaves a test database in your server. This is to be removed as it can be accessed by any user. Also you should not have any database that contains the name 'test.' It is not recommended

  3. Do not have more than one root user. Also it is recommend to rename root to something more obscure

  4. All users in your servers should not be given access to all databases. Only required database should be authorized for users to use.

The post mentioned above will detail you more on configuring your server.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.