Basic Details
The read_rnd_buffer_size system variable sets the buffer uffer size for reads for MyISAM tables. It's also used to set the buffer size for both MyISAM and InnoDB storage, associated with Multi-Range Read optimization.
Releem automatically tunes read_rnd_buffer_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes read_rnd_buffer_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
read_rnd_buffer_size – Usage
The read_rnd_buffer_size variable is most commonly used to read rows in a sorted order after a sort, using the MyISAM storage engine. But this variable can also be used generally with different storage engines (MyISAM, InnoDB, Aria) to optimally read rows after a sort.
By reading from this buffer after a sorting operation, the system can avoid unnecessary disk seeks.
The read_rnd_buffer_size variable should not be confused with the read_buffer_size variable. The latter only applies to MyISAM and does not affect the InnoDB storage engine.
By reading from this buffer after a sorting operation, the system can avoid unnecessary disk seeks.
The read_rnd_buffer_size variable should not be confused with the read_buffer_size variable. The latter only applies to MyISAM and does not affect the InnoDB storage engine.
read_rnd_buffer_size – Configuration
read_rnd_buffer_size system variable can be configured using the command line or configuration file:
Command Line:
mysqld> set global read_rnd_buffer_size = XX
Replace XX with a value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server, and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
read_rnd_buffer_size = XX
Replace XX with value to suit your database needs. Must restart the server to see the changes applied.
Command Line:
mysqld> set global read_rnd_buffer_size = XX
Replace XX with a value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server, and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
read_rnd_buffer_size = XX
Replace XX with value to suit your database needs. Must restart the server to see the changes applied.
read_rnd_buffer_size – Considerations
The read_rnd_buffer_size variable can be increased for servers with many ORDER BY queries for a significant performance improvement. Additionally, the buffer can be used to optimize data retrieval, as long as the table fields can be converted to a fixed size. This means Blob/Text columns are not suitable for this use case.
But this buffer is allocated for each client on the server, so it's better to change the session variable from within clients running larger queries, instead of the global variable.
If you're not sure which value to choose for your servers, Releem can handle that for you.
But this buffer is allocated for each client on the server, so it's better to change the session variable from within clients running larger queries, instead of the global variable.
If you're not sure which value to choose for your servers, Releem can handle that for you.
The read_rnd_buffer_size system variable is pretty straightforward and there aren't a lot of complex considerations, but this variable can be used to a great performance benefit when set properly. Let Releem handle the calculations and figure out exactly the correct value, on a per session basis, for read_rnd_buffer_size. Releem takes the guesswork out of MySQL server tuning, to improve the overal MySQL Performance Score with proven configuration recommendations.