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.

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:

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.

2 Answers
2

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:

How is performance writing the log files? Any issues there?

I there something else going on? Any deadlocks being reported?

What does top show regarding CPU time? Sleeping, idle, or wait?

What sort of write concurrency are we talking about? How many concurrent writers?