Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/).
After the migration, everything went fine for the first two weeks, but for a couple days I've been experiencing some problems.
At first, all my queries would lock under an update or insert at certain tables. With a SHOW PROCESSLIST; I could see that the locking queries had the state of "freeing items". I started to research and found this:
Why do MySQL threads often show "freeing items" status when the query cache is disabled?
I've changed the innodb_thread_concurrency to 32 as suggested, and that really did help in order to keep the website running well, which means that I'm not experiencing application locks any more.
The problem is that inserts and updates are still slow, some of them taking more than 60 seconds to happen and that is terrible. I'll try to put my database under maintenance and optimize or repair it, but I don't know if that is going to work really well.
Does anyone knows what I could do about it? Thanks!
Best Answer
My knowledge of MySQL is somewhat limited so I may be entirely wrong here, but this is me thinking through your issue given my experience with MySQL w/innodb and other MVCC db's.
If you are seeing processes or threads waiting on eachother it has to be a locking issue. That's the only explanation. Given MySQL's structure, it has to be a thread locking issue. There are no other options.
This leads to a few specific questions: