Phase one: mysql read-related variables
Ok, it is time to show some numbers. We will begin our investigation examining read-related options and how they affect mysql server performances in DBT-2 workload:
Wow, just increasing the buffer pool size from 8 MB (default) to 2 GB makes a lot of difference, while enabling / disabling a 256 MB query cache seems to not alter things in a significant manner. But why increasing the buffer pool size to 2 GB results in this big speed-up? The reason lies in current physical disk technology: when reading/writing small data blocks (eg: 4 KB blocks), disk speed is severely hampered by seek and rotation latencies. In the worst case, when dealing with random access and 4 KB blocks, even a modern, 15K RPM disk can transfer less then 1 MB/s. If you think that I am using an old, 7200 RPM laptop disk, well... things are even worse. Increasing the buffer pool size enable mysql to cache much of the needed data and indexes, decreasing disk usage time. This is one case where a specific, targeted cache do wonders in comparison to the generic, operating system level cache (which only know about disk blocks).
Will sysbench confirm this scenario? First, let see the “simple”, read-only run:
Again, wee see that increasing the buffer size to 2 GB give us a wonderful increase in query speed. This time, we see that a 256 MB, always-on query cache have a bad influence on server performance: this is the reason behind the default 0 byte-sized query cache. On the other hands, there are workloads where the query cache can speed up things noticeably so, if you are low on performance, you can give it a try.
What about sysbench “complex”, transactional test?
We begin to see a pattern, right? Increasing the buffer size continue to led to enormous gains. When considering that we got these performance gains without compromising server reliability, we can be very happy about these results. However, you should not always expect this exceptional speed up: in write-bound test, simply increase the buffer pool size can not turn a slow system in a fast one. For example, the prepare time for both DBT2 and Sysbench test database was only slightly affected by the 2 GB buffer pool. In detail, DBT2 prepare time decreased by about 18 seconds, down from 05:30 (min:sec) to 05:12. Similarly, Sysbench prepare time remain steady at ~8 seconds.