Maybe I should make the sub-tag line, make your MySQL database run up to 300% faster by using Waffle Grid… Ahhh its all about marketing.
Had an interesting holiday, snuck away several times to benchmark and test out the new Waffle Grid release… nothing says turkey day like Waffles and a really fast database ( Hey! Yes my wife knew she married a complete and utter nerd, she just might not have understood the depths of my nerdom ). As I mentioned before with the release of MySQL 5.1.30 I switched over all my testing over to this version, and in my tests this combination is running really well. Additionally I had a hardware issue which compelled me to retest some of the tests I did last week. So who loves benchmarks! I DO I DO!!! I like to go fast, so lets get right down to it.
I ran a series of 20W DBT2 benchmarks on MySQL 5.1.30 with and without waffle grid ( and with a new patched memcached ), take a look:
|DBT2 – Memcached Patch||TPM|
|768M Local/768 Remote No MC patch||6575.8|
|768M Local/768 Remote MC patch||9371.54|
As you see the baseline test used a innodb_buffer_pool of 768M ( all these tests ran 16 threads hitting 20Wby the way ) with which we achieved a baseline New Order TPM of 3228. By Enabling Waffle Grid and allocating 768M for a remote buffer pool (basically doubling the total memory for the innodb buffer pool ) we were able to achieve 6575 TPM, a 200% increase over the baseline. While this is good, when we introduced the patched memcached we discussed previously we raised the TPM to 9371, nearly 300% over the baseline, which is just awesome. While this still is less then the 16508 TPM ( 500% ) a 1.5 GB all local buffer pool can achieve, Waffle grid is really a solution for those who have run out of local memory space. What I want to see next is large database tests, i.e. 128GB tests, with 64GB local, and 64GB remote. While the smaller sets of data should replicate larger datasets with similar %’s ( i.e. the local buffer pool was sized to about 25% of the total data size, with another 25% allocated remotely using Waffle Grid, which should yeild similar #’s to 100GB system with 25GB local and 25GB remote), I still want to test this.
How did the patched Memcached get an extra 3K TPM? Its all about the hit ratio. Take a look at the difference:
|Memcached Hit Ratio||Hit Ratio|
|Memcached 5.1.25 Unpatched||68.80%|
|Memcached 5.1.25 Patched||94.90%|
The new patched memcached enabled us to improve our hit ratio by just over 26%. More cache hits equates to less disk activity, which is the hole goal of Waffle Grid.
I know their may still be some neigh sayers out their… after all the only tests I have done up to now are DBT2 test. Well here is a world exclusive! Sysbench numbers, yes we can no longer be accused of being a one trick pony we do well on both synthetic benchmarks!
What’s interesting about these numbers are we almost equaled the performance of a completely local buffer with a Waffle remote buffer!
And finally, I talked about the performance benefit of having the fastest interconnect when you deploy a Waffle grid, I decided to put together a little test comparing localhost memcached to memcached connected over 1Gbe to memcached over a 100Mb connection. Let the grudge match begin:
What was really surprising here is that the 1Gb connection almost matched memcached running on the localhost, I expected more of a drop off in performance. Some idea’s? Because memcached and the database were on the same server for the localhost test its possible that they were fighting for CPU resources… or thats the best guess I can come up with. What’s telling though is the 100Mb connection test. Using a 100Mb interconnect you might as well not bother deploying Waffle Grid, the extra few transactions hardly seems worth the complexity.
I would love to test this with faster interconnects and see what happens.