peter_zaitsev (peter_zaitsev) wrote,

MySQL: Innodb bottlenecks

Recently we've discovered couple of Innodb performance issues I was not aware of. Well... I knew these phenomena exists but I did not think
the impact is that dramatic.

1) Innodb auto_increment lock.
We've been seeing a lot serious performance degradations with SysBench tests with Innodb tables with large amount of connections. At 1024 connections
performance could be 10 times lower than with 10 connections. Well, it turns out the lock taken for auto_increment column for insertion is responsible for large portion of it. The sad thing is this lock is taken even if you explicitely specify value you're inserting - as soon as column has auto_increment column. We're still to investigate why regression is so serious but in the meanwhile the advise could be to avoid using auto_increment
if you do not really use it. The good part however is - it is not that typical case. Many connections I'm speaking about is not really number of connections but of concurrently running query - if you have 1000 connections but they are idle most of the time you would not see this effect.

2) The second surprise was "deleted index entries" - the fact is Index in Innodb contains pointers to all rows, even thouse which are deleted,
or which changed column value so they no more match the current key. This is done to allow using indexes for reading old versions, however this means
queries may slow down significantly if there is long transaction running which does not allow purge to happen. Just to clarify - it is _any_ long transaction - even the one which does not touch table in question - Innodb needs to maintain old version for old tables as there is information which table
going transaction will need to access next. So if you have some script which hung for hours with trasaction open you might be in trouble.

So when this hurts a lot ? When there are a lot of rows in the index which are no more where. The typical case when I've seen is for example various queues implementations. For example you have flag "needs_review" in the table which is set to 1 when object needs to be reviewed. You may
have simple query to pull up next one for review - "select * from tbl where needs_review=1 limit 1" - Logically this query will require single row read by index, but in practice it may be pretty long index scan to find first non-deleted row.

This tricky thing versioning adds are often forgotten about.
  • Post a new comment


    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    default userpic