How does MySQL's read_buffer_size parameter affect write buffering and write performance?

Each thread//Thatdoes a sequential scan  forAMyISAMtable//allocates a buffer of ThisSize (inchbytes forEach table it scans.

If You DoMany sequential scans, you might want to increase ThisValue, which defaults to131072. The value of ThisVariable should be a multiple of 4KB. If

It is Setto a value that isNot a multiple of 4KB, its value would be rounded down to the nearest multiple of 4KB. This option isAlso usedinchThe following context forAll storage engines: Forcaching the Indexes In a temporary file (not a temporary table), when sorting rows for ORDER by.
Used to cache the staging table index---Order byfor BULK INSERT into partitions.
Cache Bulk INSERT into partition for caching results of nested queries.
Cache result sets for nested queries

Read_buffer_size sets the size of the buffer to be used when scanning table data in sequential scan mode.
This buffer is generated when each thread is sequentially scanned, and if there are multiple tables in the same query with a full table scan, it will produce more than one buffer.

How read_buffer_size impacts write buffering and write performance

• June 23, 2010

In MySQL, even though the name read_buffer_size implies and the variable controls only read buffering, but it AC Tually does dual purpose by providing sequential IO buffering for both reads and writes.

In case of write buffering,; It groups the sequential writes until read_buffer_size (actually min (read_buffer_size, 8K)); And then does the physical write once the buffer is full. In the most cases; This value was the initial value ofread_buffer_size when server actually started first time; As this is a dynamic global variable; Even if the value dynamically at run time; It won't affect write buffering size (and in some cases of the read buffering as well) as this is stored one-time in my _default_record_cache_size (might be a bug?); And that variable are used in initializing IO cache buffers.

The cases where read_buffer_size is actually used for Buffering writes within MySQL:

• SELECT into ... OUTFILE 'fileName'
  • When writing to OUTFILE, the writes is buffered before writing to OUTFILE
• When Filesort was used, during merge buffers and when merged results was written to a temporary file, then writes was Buffe Red

Normally you'll see performance boost due to IO buffering on slower disks if you have IO saturation as IOs is Grou PED together to a single write; If you had good IO sub-system or when writes is almost NOOP (RAID controller caching), etc., then Buffering had NE Gative impact.

Here's some stats on how many physical writes be actually posted for a simple SELECT ... into OUTFILE (file size 384936838 bytes) for variable read_buffer_size values (server needs to be restarted in-or Der to get the new value):

TD valign= "Top" width= ">exe Time" secs

read_buffer_size	physical writes
=0 (defaults to 8200)	23495	28.39
=131072 (default)	2937	27.44
=16777216	23	26.71
=33554432	12	=536870912	1

Total writes is calculated using simple patch that I wrote around Mysys/my_write.c to get the real physical writes posted As a global status counter.

Shell

123456789

mysql> Show global status like ' Write_count '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | write_count | 23496 | +---------------+-------+ 1 row in set (0.00 sec)

As you can see, increase inread_buffer_size might save total physical writes and might help if you have the lot of OU Tfiles or heavy file sorting to some extent; But again this would actually affect overall performance due to one of the known bugs and the buffer is also allocated per Q Uery based; So is careful as allocation and initialization of big buffers is much costlier than real IO cost.

In either case; may be worth if the sequential read buffering are actually controlled by Read_buffer_size and introduce new WR  Ite_buffer_size that controls the write buffering instead of using the same for both or use a different variable like io_buffer_size.

How does MySQL's read_buffer_size parameter affect write buffering and write performance?