mysql readbuffersize_MySQL 的 read_buffer_size 参数是如何影响写缓冲和写性能的？

In MySQL, even though the name

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 most cases; this value is the initial value ofread_buffer_size when server actually started first time; as this is a dynamic global variable; even if you change the value dynamically at run time; it will not affect write buffering size (and in some cases of read buffering as well) as this is stored one-time in my_default_record_cache_size (might be a bug ?); and that variable is used in initializing IO cache buffers.

Two use cases where read_buffer_size is actually used for buffering writes within MySQL:

SELECT INTO … OUTFILE ‘fileName‘

When writing to OUTFILE, the writes are buffered before writing to OUTFILE

When filesort is used, during merge buffers and when merged results are written to a temporary file, then writes are buffered

Normally you will see performance boost due to IO buffering on slower disks or when you have IO saturation as IOs are grouped together to a single write; but when you have good IO sub-system or when writes are almost NOOP (RAID controller caching), etc., then buffering has negative impact.

Here is some stats on how many physical writes are actually posted for a simple SELECT … INTO OUTFILE (file size 384936838 bytes) for variable read_buffer_size values (server needs to be restarted in-order to get the new value):

read_buffer_size

physical writes

exe time in secs

=0 (defaults to 8200)

23495

28.39

=131072 (default)

2937

27.44

=16777216

23

26.71

=33554432

12

26.00

=536870912

1

26.72

Total writes are 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

1

2

3

4

5

6

7

8

9

mysql>show global status like'Write_count';

+---------------+-------+

|Variable_name|Value|

+---------------+-------+

|Write_count|23496|

+---------------+-------+

1row inset(0.00sec)

As you can see, increase inread_buffer_size might save total physical writes and might help if you have lot of OUTFILEs or heavy file sorting to some extent; but again this will actually affect overall performance due to one of the known bug and the buffer is also allocated per query based; so be careful as allocation and initialization of big buffers are much costlier than real IO cost.

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