MySQL Per Client Parameters

Be extra cautious when increasing the value of a per-client variable. For each per-client buffer, the potential amount of server memory required is the size of the buffer times max_connections. Parameters for these buffers normally are set to 1MB or 2MB at most.

MySQL uses a record buffer to perform sequential table scans. Its size is controlled by read_buffer_size system variable. Increasing the size of this buffer allows larger chunks of the table to be read at one time, which can speed up scanning by reducing the number of disk seeks required. A second record buffer is also allocated for use in reading records after an intermediate sort (such as might be required by an ORDER BY clause) or for non-sequential table reads. Its size is controlled by the read_rnd_buffer_size variable, which default to the value of read_buffer_size. This means changing read_buffer_size potentially can actually result in double the effective memory increase.

The sort buffer is used for operation such as ORDER BY and GROUP BY. Its size is controlled by the sort_buffer_size system variable. If clients execute many queries that sort large record sets, increasing the sort buffer size can speed up sorting operations.

The join buffer is used to process joins. Its size is controlled by the join_buffer_size system variable. Increase its value if clients tend to perform complex joins.

The server allocates a communication buffer for exchanging information with the client. If clients tend to issue very long queries, the queries will fail if the communication buffer is not large enough. The buffer size is controlled by max_allowed_packet parameter. Unlike parameter such as read_buffer_size, it is generally safe to set the value of max_allowed_packet quite high. The server does not actually allocate a communication buffer that large as soon as a client connects. It begin with the buffer of size net_buffer_length bytes and increase it as necessary, up to a maximum of max_allowed_packet bytes.

Although these buffers are client specific, it isn't necessarily the case that the server actually allocates each one for every client. No sort buffer or join buffer is allocated for a client unless it performs sorts or joins.

One scenario in which very long queries can occur is when you dump tables with mysqldump and reload them with mysql. If you run mysqldump with the —opt option (which is enabled by default) to create a dump file containing log multiple-row INSERT statements, those statements might be too long for the server to handle when you use mysql later to send the contents of the file back to the server to be reloaded. Note that it might be necessary to set the client-side value of max_allowed_packet in both cases as well. mysqldump and mysql both support a —max_allowed_packet option for setting client-size value.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License