?

Log in

No account? Create an account

Innodb Concurrency, No Kidding...

« previous entry | next entry »
May. 13th, 2009 | 02:22 pm

Back in 2007 I, and then several others, pointed out the Innodb Concurrency should be set to zero (or any large number) if you are planning on scaling.

http://krow.livejournal.com/542306.html

I don't get why anyone is congratulating themselves on this self discovery, since we have all known about this for over two years. It is great to see that the default setting for 5.4 is finally being fixed but this shouldn't be a revelation to anyone who has been working with the server for years (and the Innodb team fixed this in their own plugin a while ago, which should be no surprise since they author the code). This was one of the first, and frankly simple, changes made to Drizzle.

Here are some more obvious ones:
  • Kill the query cache (and disable Stored procedures and their cache if you can).
  • Remove the locks around show process list.
  • Fix the binlog (or just ditch it).
  • Fix all of the locking in the access controls.
  • Increase the default table cache.
  • Depending on your filesystem/etc move to multi file Innodb (for that matter, take the Innodb plugin for a spin for compression).
  • ...

    Want the best bang for the buck for performance?

    Buy SSD for your databases, and go figure out how to use a caching layer so as to not need to manage a bunch of replication servers.

    If you are worried about managing your caching server I would look at Gear6 or Virident. I am especially impressed with the Gear6 replication technology.
  • Link | Leave a comment | Share

    Comments {10}

    concurrency 0, 8, or 20?

    from: anonymous
    date: May. 13th, 2009 09:42 pm (UTC)
    Link

    Hi Brian; in your referenced LJ post, you seem to show that higher concurrency are better -- and a move from 20 to 8 is bad -- but you also comment "0 for scaling". So what's the difference in cases where you want concurrency of 200, or of 0?

    Reply | Thread

    Brian "Krow" Aker

    Re: concurrency 0, 8, or 20?

    from: krow
    date: May. 13th, 2009 09:47 pm (UTC)
    Link

    The value "0" just disables the concurrency limits (which is what you are doing by upping the value).

    I need to check but I believe at this point as soon as you go above a certain number Innodb does this anyway (I do need to double check that).

    Reply | Parent | Thread

    Egor Egorov

    (no subject)

    from: egorfine
    date: May. 13th, 2009 10:00 pm (UTC)
    Link

    Why do you hate the Query Cache? It looks like it is delivering it's value for web apps where lots of same reads happen. Or am I missing something?

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: May. 13th, 2009 10:54 pm (UTC)
    Link

    It slaughters performance on multi-core. Big giant lock which synchronizes everything. One a single core this is no big deal... but increase cores and it will kill you.

    If you need the query cache, the rule of thumb is to figure out how to use Memcached.

    Toru has looked at fixing this issue BTW, but until someone does... it just needs to go away.

    Reply | Parent | Thread

    Egor Egorov

    (no subject)

    from: egorfine
    date: May. 13th, 2009 10:58 pm (UTC)
    Link

    ahhhh now i c. It changes the whole story. Thanks!

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: May. 14th, 2009 12:58 pm (UTC)
    Link

    If you're using 5.1.33 or later then SELECT SQL_NO_CACHE ... will skip almost all of the query cache work, including taking the mutex that Brian is referring to. This doesn't work if SQL_NO_CACHE is inside a comment.

    Brian's right about memcached though. It's really a better solution if it's convenient to maintain cache consistency within the application.

    Reply | Parent | Thread

    Egor Egorov

    (no subject)

    from: egorfine
    date: May. 14th, 2009 02:28 pm (UTC)
    Link

    No doubt memcached is a better solution.

    Reply | Parent | Thread

    awfief

    (no subject)

    from: awfief
    date: May. 13th, 2009 10:21 pm (UTC)
    Link

    I highly doubt new DBA's read every single Planet MySQL post from post #1 when they join in.

    Be nice to newbies. :) They should congratulate themselves on the self-discovery, if they discovered it themselves.

    Personally, I won't rest until ALL sensible default values are in. innodb_file_per_table anyone? (it's not ALWAYS better, but in 95% of the cases, it is....)

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: May. 13th, 2009 10:54 pm (UTC)
    Link

    This is not about MySQL newbie DBA's.

    Reply | Parent | Thread

    (no subject)

    from: jamesd
    date: May. 14th, 2009 12:45 pm (UTC)
    Link

    Brian, the optimal value depends on the system and application. 0 typically works well for trivially simple benchmarks with little variability in query run time. For more complex workloads it's likely to fall over badly. It's rarely the optimal value for cases encountered by the MySQL support team.

    For 5.4 the latest and greatest general recommendation for maximum throughput with transaction processing benchmarks is 16, on a 16 core server.

    It's worthwhile for everyone to try 0 but not be surprised if a value between 4 and 32 is optimal for mixed workloads.

    Reply | Thread