4

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!

Improve this question
3
  • This seems relevant: dba.stackexchange.com/questions/905/…
    – Purefan
    Commented Mar 19, 2012 at 21:54
  • Percona's xtradb (completely compatible with InnoDB) may have resolved this issue.
    – Rick James
    Commented Apr 6, 2012 at 0:12
  • Have you tried to set innodb_thread_concurrency to 0, as suggested in an answer of the above linked question?
    – ypercubeᵀᴹ
    Commented Jan 31, 2013 at 6:42

2 Answers 2

Reset to default
1

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:

  1. How is performance writing the log files? Any issues there?
  2. I there something else going on? Any deadlocks being reported?
  3. What does top show regarding CPU time? Sleeping, idle, or wait?
  4. What sort of write concurrency are we talking about? How many concurrent writers?
Improve this answer
1

I had similar problem with MySQL 5.1, you can see in here Innodb Slow queries since convert from MyISAM

before start, have a look on This post, it will help you to understand how to configure your innodb Log checkpoint and dirty Buffer pool pages

I suspect that you are running MySQL 5.1 on innoDB buld-in version, if possible, try to install the innodb plugin

Then you will be able to play with innodb_write_io_threads and innodb_read_io_threads

even if you are not able to update the innodb version, try to lower the value for innodb_max_dirty_pages_pct that almost solved my problem.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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