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.

MySQL read_buffer_size

What do we generally hear about read_buffer_size tuning? If you want fast full table scans for a 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. Let’s see if it is really best strategy:

To check things out I’ve created a table with a simple structure:

Populated it with 75M of rows to reach 4G in size so the 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.

Here are the results I’ve got:

read_buffer_size impace on scan performance
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 a few percent as you increase the buffer to 128K but after that instead of improving any further, it drops down sharply being 50% slower at the 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 a good explanation. It could be OS has to split large requests into multiple ones submitting them to the 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 impace on in memory table
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 the best number still remains the same – 128K and 32M is again the worst value. This means it can’t 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 parallelism is another important variable which was not considered – small buffers with high concurrency may mean more seeks and so worse performance, or maybe not – something to test as well.

In general, it just reconfirms one basic thing – do not just grab someone else’s “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.

Share this post

Comments (28)

  • Peter Zaitsev

    Thanks Douglas,

    Indeed this way MySQL uses one variable for multiple purpose is quite unfortunate.

    September 17, 2007 at 12:00 am
  • Douglas Manley

    An interesting evolutionary consequence of this setting is that it is used by the MEMORY table engine as the allocation increment size (minus a few bytes). This means that a MEMORY table with *one row* will take up, essentially, “read_buffer_size” bytes. For each on the table, add on another “read_buffer_size” bytes to the table. The table will not change in size again until all of that allocation is used by new rows; then it will grow in increments again.

    This is not documented anywhere as far as I can tell, and I only found it after banging my head against a wall looking through the MySQL source code.

    September 17, 2007 at 12:00 am
  • Jay Pipes

    Like Monty wrote, the performance degradation is caused by the flip between regular malloc and mmap() at 256K (default):


    September 17, 2007 at 10:24 am
  • peter


    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.

    September 17, 2007 at 10:29 am
  • Jay Pipes


    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…


    September 17, 2007 at 10:37 am
  • Sinisa Milivojevic


    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.

    September 17, 2007 at 10:57 am
  • peter


    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.

    September 17, 2007 at 11:58 am
  • peter


    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 🙂

    September 17, 2007 at 12:02 pm
  • Roland Volkmann

    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,

    September 17, 2007 at 5:08 pm
  • peter

    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.

    September 18, 2007 at 4:22 am
  • Sinisa Milivojevic


    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)

    September 18, 2007 at 9:42 am
  • peter


    As far as I remember Innodb tables are not read using read_buffer 🙂

    September 18, 2007 at 9:44 am
  • Roland Volkmann

    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 …

    September 18, 2007 at 11:27 am
  • peter


    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.

    September 18, 2007 at 1:39 pm
  • Apachez

    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 🙂

    September 20, 2007 at 10:34 pm
  • Son Nguyen

    Wow, I’m glad I found this benchmark and not wasting investing into more memory (I’m just begin to see swap with one of our servers)

    September 10, 2008 at 2:40 pm
  • Apachez

    Is it just me or how do you properly set the buffers to 128 kbyte?

    I put this in my my.cnf:

    key_buffer_size = 256M
    sort_buffer_size = 128K
    read_buffer_size = 128K
    read_rnd_buffer_size = 128K
    join_buffer_size = 128K
    myisam_sort_buffer_size = 128K

    and restarted the mysql process, the result when running “SHOW VARIABLES LIKE ‘%buffer%’;” is the following (only including the rows regarding to the my.cnf):

    key_buffer_size 268435456
    sort_buffer_size 131064
    read_buffer_size 126976
    read_rnd_buffer_size 126976
    join_buffer_size 126976
    myisam_sort_buffer_size 131072

    The key_buff shows the proper “268435456” which is 256*1024*1024, but what about the rest? Shouldnt they all show 131072 which is 128*1024 or am I missing something here?

    September 21, 2008 at 4:18 am
  • Apachez

    I have now filed the above as bug

    September 24, 2008 at 12:51 pm
  • peter

    Thanks Apachez,

    I guess this is how rounding works.

    September 24, 2008 at 1:04 pm
  • Junseok, Bae

    Thank You!

    And could I ask a question?

    I’m novice and have no idea for my situation..

    I use windows + wampserver(x64 APM are all self updated) and innodb only.

    I set the key_buffer_size value 32MB(in [mysqld]) but lightly slower than when I use it with 512MB.

    When there’s no use of MyISAM, the key_buffer_size is also effect?

    Anyway, Thank you for great articles!

    October 10, 2013 at 8:26 pm
    • Harish Naik

      Apart from MyISAM, read_buffer_size is used by

      Queries using order by statement
      Nested queries
      Bulk inserts in partitions

      But not specifically by innodb as said in the above coments.

      October 26, 2017 at 5:22 pm
  • Monte

    Thanks for posting this. The disk is the lowest common denominator, so 128K makes sense for typical hardware.

    June 5, 2019 at 12:25 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.