What exactly is Read_rnd_buffer_size

Peter Zaitsev  |  July 24, 2007 | Posted in:Insight for DBAs

Looking for documentation for read_rnd_buffer_size your would find descriptions such as "The read_rnd_buffer_size is used a fter a sort, when reading rows in sorted order. If you use many queries with ORDER by, upping this can improve performance "which are cool but it does don't really tell you How exactly read_rnd_buffer_size works as well as which layer it corresponds to–sql or storage engine.

Honestly as it had name very similar to Read_buffer_size which are currently only used by MyISAM tables I thoughtread_rnd_b Uffer_size is also MyISAM only. But talking to Monty today I learned it isn't the case.

Read_rnd_buffer can used for all storage engines not is only by MyISAM. It is used for some sorts to optimally read rows after the sort. Here's how it works:

As sort is performed it can performed have only row pointers together with key Value–which was offsets for MyISAM a ND primary key values for INNODB or storing full data which are being retrieved (good for small data lengths).

In case sort with row pointer storage was used and the fields which was being length can be converted to fixed size (basica Lly Everything but blob/text) MySQL can use Read_rnd_buffer to optimize data retrieval–as data are sorted by the key Valu E It needs to is accessed in pretty much random row pointer (typically physical) order. MySQL takes bunch of pointers from sort_buffer (just enough so all rows fit in Read_rnd_buffer as they is read) and sorts them by row pointer, when performs reading to Read_rnd_buffer in the sorted order–it can be pretty much sequential if You ' re lucky.

The Read_rnd_buffer_size is important (optimization works in following conditions):

• Row pointers is stored in the Sort_buffer, not the whole data selected
• Blob/text columns is not selected
• A lot of rows was retrieved after sort–if you had LIMIT it was unlikely to help as MySQL would stop fetching rows by
  Pointers quickly

For me this means since MySQL 4.1 This option was used in narrow range of cases–if you retrieve few fields (less thanmax_ Length_for_sort_data) data should is stored in sort buffer and the sort file so there would is no need for read_rnd_buffer, if The selected columns is long so they is longer than max_length_for_sort_data it would frequently mean there are some TE Xt/blob columns among them. It would be used however if there is large number of columns or there was long VARCHAR columns Used–it takes only couple of UTF8 VARCHAR (255) to create a row which are longer than max_length_for_sort_data in its static presentation.

We should do benchmarks sometime to see how it really impacts performance both for MyISAM and Innodb.

What exactly is Read_rnd_buffer_size