Innodb Performance, Lets make this fast.... - Brian "Krow" Aker's Idle Thoughts

The range of this variable is 0 to 1000. A value of 20 or higher is interpreted as infinite concurrency. Infinite means that concurrency checking is disabled and the possibly considerable overhead of acquiring and releasing a mutex is avoided.

WTF? Why not say the range is "0 to 20" then?!

With quad cores coming out now this argues that the default should be increased.

I'm going to have to benchmark this on our end.

I use screen -xR in my .bashrc to start or attach screen automatically whenever I login or start a new terminal. If you have this in your .screenrc it's actually not so annoying to use screen:

startup_message off
caption always "%{= kw}%-w%{= BW}%n %t{-}%+w %-= %1` %y.%m.%d %C:%s%a"

BTW, why not enable openID comments? I had to create a fake livejounral account just to leave you this silly little message, and I already have eleventy billion openID accounts.

Try innodb_thread_concurrency=0, the latest and greatest infinite and skips concurrency checking value. You may still be getting concurrency checking overhead.

I doubt that there's any fixed value that will both be fast on high performance I/O limited boxes and not cause InnoDB concurrency trouble on CPU-bound load.

There was one approach I discussed with Heikki that he seems to have liked. Adding an automatic back-off for semaphore checks as the number of times a thread has to wait increases, so the pile-up of waiting threads is somewhat self-healing and self-regulating.

Alternatively, we could add a variable for the effective number drives available to the database and use that as a multiplier, guessing that lots of drives are there because it's an I/O limited workload. Something like default drives of 2 and thread concurrency of drives * 6. This one could also help with the problem of the InnoDB insert and purge threads being set up for a single drive workload and not being adequate for keeping up with high performance disk systems: multiply the idle thresholds and work to do by effective drives.

Also worth remembering the discussion with burtonator about solid state drives and how we don't (I think) currently have any scaling for optimiser cost values to account for the very fast seek times. Would be nice for you (before 5.1) and one or two of Jim's team to have one to play with and the low capacity ones are cheap enough now. Free wouldn't surprise me, if we asked.

Add the file I/O threads change and an option to never make the replication SQL thread wait and we'd have quite a nice improvement package for people with powerful boxes and high loads.

Then Jim can grumble at us raising the bar on him. :)

No experience. You might try experimenting with a Flash drive, though worth noting that they may cache writes and that can break InnoDB crash recovery if a power loss happens. SSDs are potentially very exciting for even quite large databases in the few hundred gigabytes range, particularly where enough RAM for good performance is too expensive or impractical.

innodb_thread_concurrency=0 indeed may be best after the patches or may be not depending on workload you have. It is really workload specific rather than depending only on number of hard drives and CPUs :)

The other thing few people know about is innodb_thread_sleep delay which can be limiting performance in many cases because a lot of threads will be sleeping before even entering the queue.

Also look at innodb_commit_concurrency which can help with workload when contension is on commit stage of the query which is not protected by normal innodb_thread_concurrency.

It is indeed rarely needed. Some long running queries may easily use all 500 tickets.

Generally higher numbers would make things more efficient but increase the chance of starvation by small queries while large ones process.

I'm a little familiar with mysqlslap, and was wondering how you would recommend testing 32-bit performance vs. 64-bit performance. I have been trying to state the case for 64-bit machines, and there are some stats out there, but I think my own stats would go a lot further.

What sort of test would you recommend, possibly using mysqlslap for this?

The manual at

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_thread_concurrency

says:

--- cut ---
The range of this variable is 0 to 1000. A value of 20 or higher is interpreted as infinite concurrency before MySQL 5.1.12. From 5.1.12 on, you can disable thread concurrency checking by setting the value to 0. Disabling thread concurrency checking allows InnoDB to create as many threads as it needs.

The default value is 20 before MySQL 5.1.11, and 8 from 5.1.11 on.
--- cut ---

This is _not_ outdated and is in sync with the latest 5.1 source.