MySQL: what read_buffer_size value is optimal ?
The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to less believe in common sense or common sense of documentation writers and do more benchmarks and performance research. I just recently wrote about rather surprising results with sort performance and today I've discovered even read_buffer_size selection may be less than obvious.
What do we generally hear about read_buffer_size tuning ? If you want fast full table scans for large table you should set this variable to some high value. Sample my.cnf values on large memory sizes recommend 1M settings and MySQL built-in default is 128K. Some people having a lot of memory and few concurrent connections set it as high as 32M in hopes for better performance. Lets see if it is really best strategy:
To check things out I've created table with simple structure:
-
mysql> SHOW CREATE TABLE dt2 \G
-
*************************** 1. row ***************************
-
TABLE: dt2
-
CREATE TABLE: CREATE TABLE `dt2` (
-
`grp` int(10) UNSIGNED NOT NULL,
-
`slack` varchar(50) DEFAULT NULL
-
) ENGINE=MyISAM DEFAULT CHARSET=latin1
-
1 row IN SET (0.00 sec)
Populated it with 75M of rows to reach 4G in size so workload will be IO bound on the box with 2GB of memory.
The was running Fedora Core i686 had 2 Xeon CPUs and 2 drives in RAID0.
I've used the following query to perform full table scans, with 3 runs and averaged results. MySQL 5.1.21-beta was used for tests.
-
mysql> SELECT count(*) FROM dt2 WHERE slack LIKE "a%";
-
+----------+
-
| count(*) |
-
+----------+
-
| 4705992 |
-
+----------+
-
1 row IN SET (51.77 sec)
Here are the results I've got:
| read_buffer_size | Time (sec) |
|---|---|
| 8200 | 45.2 |
| 16K | 44.8 |
| 32K | 45.6 |
| 64K | 43.4 |
| 128K | 43.0 |
| 256K | 51.9 |
| 512K | 60.8 |
| 2M | 65.2 |
| 8M | 66.8 |
| 32M | 67.2 |
8200 bytes is the minimum size for read_buffer_size, this is why we start from this value.
As you can see results look really strange. Performance indeed grows by few percent as you increase buffer to 128K but after that instead of improving any further it drops down sharply being 50% slower at 2MB size. After this value it continues to drop slowly all the way to 32M.
Why this is happening ? I have not spent enough time to come up with good explanation. It could be OS has to split large requests into multiple ones submitting them to device which slows things down or it could be something else. But the fact remains - on some platforms for some workloads large read_buffer_sizes may hurt you even on large full table scans. (I wrote about some other cases when it hurts a while ago)
Let us do one more test - what if we test out smaller table (which fits in OS cache):
| read_buffer_size | Time (sec) |
|---|---|
| 8200 | 4.15 |
| 16K | 4.15 |
| 32K | 4.12 |
| 64K | 4.11 |
| 128K | 4.11 |
| 256K | 4.12 |
| 512K | 4.25 |
| 2M | 4.49 |
| 8M | 4.54 |
| 32M | 4.58 |
As you see the difference in percents is smaller with only 10% difference between best and worst numbers but best number still remains the same - 128K and 32M is again the worst value. This means it can't be request split issues, at least not just that.
Note: In this case I'm really curious how much values change on different platforms (OS and Hardware) as well as different file systems as these could all be involved here. Different table structures (ie longer rows) also may affect results, not to mention tables with fragmented rows when IO pattern can be a lot different.
The degree of parallelizm is another important variable which was not considered - small buffers with high concurrency may mean more seeks and so
worse performance, or may be not - something to test as well.
In general it just reconfirms one basic thing - do not just grab someone elses "best configuration" from the web and apply for your application if you're interested in best performance - experiment with realistic load and realistic data (including fragmentation) to find what works best for you.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
17 Comments
Like Monty wrote, the performance degradation is caused by the flip between regular malloc and mmap() at 256K (default):
http://bonglonglong.com/2007/09/06/read-buffer-performance-hit/
-j
Comment :: September 17, 2007 @ 10:24 am
Jay,
This is entirely different. The table which is scanned is a huge one and read_buffer should be allocated only once so either if mmap is slow it should not affect things.
Comment :: September 17, 2007 @ 10:29 am
Peter,
I understand you. That makes sense. Couple things:
a) A couple typos. default value for read_buffer_size is 128K, not 128M… and there is another place you say 128M when I think you mean 128K…
b) What is the CPU cache for this machine? I agree that since this is a large table and the read_buffer must be used repeatedly, that values of read_buffer_size which best fit the CPU L1/2 cache would likely mean better performance? Would be nice to have other folks with different processor caches do a similar test…
-j
Comment :: September 17, 2007 @ 10:37 am
Peter,
What you discovered looks definitely like unnecessary slowdown. However, in order to make measurement more scientific, I would propose that you make a table InnoDB and to have O_DIRECT method of accessing files, or to mount filesystem on Solaris (or HP-UX) appropriately. Would be nice to see if this is may be caused to some extent by OS cacheing. Another thing to try is to use CHAR instead of VARCHAR.
Comment :: September 17, 2007 @ 10:57 am
Jay,
Thanks Corrected.
Regarding Cache - this box has CPUs with 1024K caches.
Cache may explain second case (which also starts to slow down most on 512K-2M jump) but not the Io Bound one - I’ve checked with large buffers we have Lower read throughput from the disk, lower CPU usage with higher iowait.
Comment :: September 17, 2007 @ 11:58 am
Sinisa,
O_DIRECT uses very different IO path and well may cause different behavior. Not to mention with Innodb you can’t really control buffers to see IO performance with different buffers. Though you can use SysBench to do the test.
There are a lot of ways this can be researched and probably fixed, as you may guess MySQL does not pay me for performance research and more and I only have little spare time left
Comment :: September 17, 2007 @ 12:02 pm
Hi Peter,
your box has RAID0, and I guess it has stripe size of 128 KB.
When I was testing file IO performance on RAID0-Systems using Windows XP some time ago, I found great correlation of stripe size / buffer size and performance. So with MyISAM file IO it might me the same thing.
With best regards,
Roland.
Comment :: September 17, 2007 @ 5:08 pm
This may sounds silly but I can’t find the sripe size for the box I have experimented with. It well may be 128K
The interesting thing however if you’re doing reads larger than stripe size you should get better performance, not worse - aligned
128K read will have to be read from single hard drive while 256K could use both hard drives for parallel IO.
As we go to higher block sizes in theory RAID should be able to do even more optimization - say you do 2MB aligned read with 128M size - RAID could perform 1M read for each of underlying devices and then reorder 128K blocks to give requested 2M result set giving close to twice of sequential read speed from single device (assuming there is enough bus bandwidth for it)
When I have some time I’ll try to rerun this test on my Dell PowerEdge 2950 box which has more hard drives and so in theory must love IO in large blocks.
Comment :: September 18, 2007 @ 4:22 am
Peter,
O_DIRECT does use different I/O path, but that is irrelevant. What is relevant that even InnoDB tables are read by using read buffer and that such combo would dodge out the consequences of OS cache-ing … I think I will try to do myself what I suggested, as soon as I find some time ……… ;o)
Comment :: September 18, 2007 @ 9:42 am
Sinisa,
As far as I remember Innodb tables are not read using read_buffer
Comment :: September 18, 2007 @ 9:44 am
Hi Peter,
your theory using larger buffers is valid for sequential IO only. Having random IO large buffers will result in lot of unneccessary read ahead data. And if file system hadn’t written file in consecutive physical segments (fragmentation), then theory becomes much more complicated …
Comment :: September 18, 2007 @ 11:27 am
Ronald,
Indeed. However read_buffer_size is used for sequential scans - it is not used if table is read via index lookup for example.
In this case I performed test on virtually empty file system so I would be surprised if file is significantly fragmented.
Also we’re speaking about logical IO to file anyway so if file is fragmented one large read to the file will be split to several smaller physical reads but there would not be more of these for larger buffer.
Comment :: September 18, 2007 @ 1:39 pm
11. Also the filesystems own buffers will be used which will lower the impact using random access (depending on size of the tables etc).
Regarding the 128k optimization it sounds very much like a sync is needed for how the data travels from the harddrive into the cpu. Pretty much similar why a cpu:ram ratio of 1:1 regarding fsb is often better than 1:2 ratio or some other variant.
Jay: Any progress of a tool which can find “optimal values” for a given system?
Peter: I disagree with you regarding example configurations. By using example configuration from a given system you can save much time in order to if not get a perfect configuration at least get a way better configuration than the outdated examples which are included with the mysql itself. It sounds more that you want to protect your consulting business to help others to find their optimal values with such statement as you gave in the end of this article.
Most people involving in security should by now know that security by obscurity doesnt work, I think the same applies in order to find optimal values for different setup. Optimization by obscurity doesnt work either - so lets share our findings and configuration examples
Comment :: September 20, 2007 @ 10:34 pm
Log Buffer #63: a Carnival of the Vanities for DBAs…
Welcome to the 63rd edition of Log Buffer, the weekly review of database blogs. For those of you reading…
Trackback :: September 21, 2007 @ 9:01 am
[...] http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/ [...]
Pingback :: March 6, 2008 @ 9:19 am
[...] http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/ http://www.mysqlperformanceblog.com/2006/06/06/are-larger-buffers-always-better/ [...]
Pingback :: March 6, 2008 @ 6:33 pm
[...] looks like the guys over at the MySQL Performance Blog came to the same conclusion I did: Leave read_buffer_size to its default value. Changing it [...]
Pingback :: May 19, 2008 @ 5:40 pm