Innodb Performance, Lets make this fast....

« previous entry | next entry »
Aug. 15th, 2007 | 03:53 pm

So at the moment I am interested in how fast I can make Innodb on a 8-way machine while it keeping durable (aka disk write cache is disabled). This setup is tuned to run without replication.

Here is my changes for the 8-way, sata disk system:

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10000M:autoextend
innodb_log_file_size=512M
innodb_log_buffer_size=8M
innodb_log_files_in_group = 2
innodb_checksums=0
innodb_support_xa=0
innodb_doublewrite=0
innodb_thread_concurrency=36
innodb_locks_unsafe_for_binlog=1innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10000M:autoextend
innodb_log_file_size=512M
innodb_log_buffer_size=8M
innodb_log_files_in_group = 2
innodb_checksums=0
innodb_support_xa=0
innodb_doublewrite=0
innodb_thread_concurrency=36
innodb_locks_unsafe_for_binlog=1

Almost none of these made a real difference in performance except, innodb_thread_concurrency. In 5.1.11 the default was changed from 20 to 8. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

Even the though the manual says that "A value of 20 or higher is interpreted as infinite concurrency", I am finding that adjusting the number above 20 can significantly change performance (or hell just set it to Zero since in almost all cases where you are scaling that number turns out the best). Let me present to you four runs of mysqlslap generating 100 new rows per connection (the third number being smaller is better):

Columns:
  • Storage Engine
  • Built in test
  • Average of runs
  • Minimum run
  • Maximum run
  • Concurrent connections
  • Statements executed by each connection

    innodb_thread_concurrency=8->
    innodb,write,0.876,0.833,1.000,1,100
    innodb,write,5.455,5.226,6.076,25,100
    innodb,write,10.894,10.611,11.545,50,100
    innodb,write,16.183,15.926,17.046,75,100
    innodb,write,21.574,21.387,21.817,100,100

    innodb_thread_concurrency=36->
    innodb,write,0.877,0.834,0.922,1,100
    innodb,write,1.777,1.722,1.900,25,100
    innodb,write,2.506,2.456,2.591,50,100
    innodb,write,3.729,3.660,3.880,75,100
    innodb,write,4.968,4.893,5.116,100,100
    innodb,write,6.299,6.113,6.914,125,100
    innodb,write,7.464,7.321,7.672,150,100
    innodb,write,10.493,10.025,10.972,175,100
    innodb,write,16.685,16.457,17.166,200,100

    innodb_thread_concurrency=100 ->
    innodb,write,0.858,0.833,0.875,1,100
    innodb,write,1.775,1.720,1.873,25,100
    innodb,write,1.885,1.755,2.570,50,100
    innodb,write,1.842,1.786,1.920,75,100
    innodb,write,2.046,1.820,3.122,100,100
    innodb,write,2.388,2.298,2.492,125,100
    innodb,write,3.480,3.089,3.726,150,100
    innodb,write,7.652,7.524,7.768,175,100
    innodb,write,11.099,10.873,11.528,200,100

    innodb_thread_concurrency=200 ->
    innodb,write,0.861,0.833,0.875,1,100
    innodb,write,1.797,1.755,1.956,25,100
    innodb,write,1.806,1.754,1.923,50,100
    innodb,write,1.844,1.788,1.965,75,100
    innodb,write,1.952,1.833,2.590,100,100
    innodb,write,1.965,1.892,2.357,125,100
    innodb,write,2.286,1.902,3.805,150,100
    innodb,write,2.060,1.992,2.261,175,100
    innodb,write,2.312,2.199,2.557,200,100

    On at least an 8way machine adjusting innodb_thread_concurrency to 200 made a significant improvement (this brings doubt that the change in 5.1.11 from 20 to 8 as a default value was a good idea). One other thing to note, in the past I have rewritten the code around innodb_file_io_threads to allow it to be modified on UNIX systems, and that too has made a difference.

    I need to rerun the test and change innodb_flush_method. It gets adjusted a lot for tuning, and I am curious to see how this modifies the above numbers. Update I tried O_DSYNC, the sync method that is sometimes believed to be faster. At least on a 2.6 Kernel is made the run slightly slower (almost insignificant though).

    All of this makes me wonder how this would effect the Sun T1000 Sun I have.

    BTW SubEthaEdit is pretty awesome. I can copy and paste between a shared buffer between my two macs.... I wonder if there is someway to do shared editing with vi without resorting to screen....
  • Link | Leave a comment | Add to Memories | Share

    Comments {22}

    Brad Fitzpatrick

    (no subject)

    from: brad
    date: Aug. 15th, 2007 11:58 pm (UTC)
    Link

    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?!

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: Aug. 16th, 2007 02:00 am (UTC)
    Link

    I don't know. It could be that the manual is simply out of date. I only increased the number on a lark (ok... that and I don't always find the manual to be correct).

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: Aug. 16th, 2007 05:20 am (UTC)
    Link

    The manual used to say that 500 meant unlimited. Afer several changes of defaults and what meant infinite they appear to have missed the latest and greatest that 0 = infinite.

    I've let them know.

    Reply | Parent | Thread

    newz_top

    (no subject)

    from: newz_top
    date: Jan. 2nd, 2008 06:30 pm (UTC)
    Link

    because its not right!

    Reply | Parent | Thread

    Default?

    from: burtonator
    date: Aug. 16th, 2007 02:49 am (UTC)
    Link

    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.

    Reply | Thread

    Re: Default?

    from: jamesd
    date: Aug. 16th, 2007 05:47 am (UTC)
    Link

    The default was raised to 20/unlimited after tests showed that it would generally improve performance in disk-bound workloads. The default was dropped from 20 back to 8 after reports of hangs in sysbench with 2048 threads, via bug #15868. Heikki thought that bug 22868 might have fixed that problem and made higher values safe again. Latest and greatest "unlimited" value appears to be 0.

    Knowing how you get on and what the drive counts and core counts are would probably be helpful so we can avoid another change it/undo it cycle.

    If you're disk bound, higher values are quite likely to be good. CPU-bound and it'll depend on how effective the mutex contention reducing patches have been.

    Reply | Parent | Thread

    just use screen

    from: emurphy77
    date: Aug. 16th, 2007 03:57 am (UTC)
    Link

    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.

    Reply | Thread

    Brian "Krow" Aker

    Re: just use screen

    from: krow
    date: Aug. 16th, 2007 07:03 am (UTC)
    Link

    Hi!

    So you always join an active screen session when you login? Now that is an excellent trick. Though... screen has no ncurses see all sessions at once does it?

    The openID comments are not me, its LJ. This is how I think it works... I won't allow comments from "everyone", aka people who are not logged into the system. If I allow openID comments... then I allow comments from roving comment spam bots.

    What LJ needs to do is differentiate between OpenID and anonymous commenting. Without Brad running LJ though... I have my doubts on any changes occurring.

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: Aug. 16th, 2007 05:10 am (UTC)
    Link

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

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: Aug. 16th, 2007 06:52 am (UTC)
    Link

    Ok!

    I had seen one reference to that, the 0, but I haven't read about it anywhere. After looking at this though... I really don't know that we should be setting this so low by default.

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: Aug. 16th, 2007 11:38 am (UTC)
    Link

    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. :)

    Reply | Parent | Thread

    (no subject)

    from: bmurphy96
    date: Aug. 16th, 2007 02:40 pm (UTC)
    Link

    I am curious about the solid-state drives you mention. What kind of experience have you had with this? I would love to see tests of how having SSD's for your data files would speed up access.

    Using SSD's in theory should almost eliminate seek-time on your data removing a huge bottleneck.

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: Aug. 17th, 2007 02:50 am (UTC)
    Link

    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.

    Reply | Parent | Thread

    (no subject)

    from: vakfike5
    date: Jul. 11th, 2008 12:41 am (UTC)
    Link

    The manual used to say that 500 meant unlimited. Afer several changes of defaults and what meant infinite they appear to have missed the latest and greatest that 0 = infinite.

    I've let them know.





    Red Alert 2

    Reply | Parent | Thread

    peter_zaitsev

    (no subject)

    from: peter_zaitsev
    date: Aug. 16th, 2007 09:17 am (UTC)
    Link

    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.

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: Aug. 16th, 2007 03:02 pm (UTC)
    Link

    Have you tried adjusting commit tickets? That is a parameter I have never played with, and I am curious as to how it influences the process (though with a default number like 500 I don't expect many to hit it).

    Reply | Parent | Thread

    peter_zaitsev

    (no subject)

    from: peter_zaitsev
    date: Aug. 16th, 2007 08:23 pm (UTC)
    Link

    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.

    Reply | Parent | Thread

    Patrick Galbraith

    64-bit vs 32-bit

    from: capttofu
    date: Aug. 17th, 2007 11:39 am (UTC)
    Link

    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?

    Reply | Thread

    Brian "Krow" Aker

    Re: 64-bit vs 32-bit

    from: krow
    date: Aug. 17th, 2007 04:39 pm (UTC)
    Link

    I see two reasons for 64bit in your space:

    1) Fat Apache Children. If you are not fully utilizing your CPU and your Apache children are being killed too quickly for memory leaks.

    2) Databases. You need more memory, so you need 64bit. Look at your innodb status information and see what the hit rate is.

    Reply | Parent | Thread

    Brian "Krow" Aker

    Re: 64-bit vs 32-bit

    from: krow
    date: Aug. 17th, 2007 04:47 pm (UTC)
    Link

    One thought... just to show of memory usage... run a slap built in key read test on 32bit vs 64 bit. Give the 64bit machine more memory :)

    Reply | Parent | Thread

    The MySQL manual is correct

    from: anonymous
    date: Feb. 11th, 2009 07:51 am (UTC)
    Link

    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.

    Reply | Thread

    Brian "Krow" Aker

    Re: The MySQL manual is correct

    from: krow
    date: Feb. 11th, 2009 07:55 am (UTC)
    Link

    This is just old enough... that who knows what all was done when anymore.

    Reply | Parent | Thread