Sunday, 7 November 2010

New global buffer constraints in Drizzle

Both MySQL and Drizzle have buffers such as sort and join buffers which have size parameters to tune them.  Unfortunately these settings are per-client, so if you have thousands of clients all using these buffers you could easily exhaust the RAM of a server.  Especially if they are set quite high.

So in the latest Drizzle trunk (and the release due in the next few days) I have introduced global buffer constraints.  These constraints make sure that between all the clients there is a cap on how much RAM can be allocated to a buffer.

This was a task the Rackspace Cloud team requested be implemented and I have also seen (and subsequently lost the link to) a blog which discusses the possibility of something similar in MySQL.  If anyone has seen the blog post I am talking about please let me know :)

How this is implemented


For each buffer type there is a new atomic counter which is keeping account of the allocations for each buffer type.  If one of theses counters hits a cap then the query executing when the cap is hit will return an error.  So far I have added this to 4 buffers which are sort buffer, join buffer, read buffer and read rnd buffer.  The new options for these are:
--join-heap-threshold

--read-buffer-threshold

--read-rnd-threshold

--sort-heap-threshold

By default all are set to 0 which means unlimited, and cannot be set lower than their associative settings (eg. sort-heap-threshold cannot be lower than sort-buffer-size).

I would typically expect this would be used most in cloud scenarios where the virtual servers could have a limited amount of RAM and swap available.

9 comments:

  1. Are these global buffers pre-allocated, or do we still have the per-query malloc() occurring for them?

    ReplyDelete
  2. Currently still per-query, they are just implemented using global atomic counters for now. We are looking to do some nifty things with allocation later.

    ReplyDelete
  3. Good for "Pay as you use" kind of environments.
    Cloud specific requirement :)

    ReplyDelete
  4. Ur mah hero!!!!

    ReplyDelete
  5. Wow!
    Great idea!!!!

    ReplyDelete
  6. [...] Added adjustable global upper cap for sort buffers added. This is to limit the sum of all sort buffers across all running threads.  You can read more about this here and here [...]

    ReplyDelete
  7. [...] Added adjustable global upper cap for sort buffers. This is to limit the sum of all sort buffers across all running threads.  You can read more about this here and here [...]

    ReplyDelete