Go to the first, previous, next, last section, table of contents.

How does one change the size of MySQL buffers?

You can get the current buffer sizes with:

> ./mysqld --help

This should result in a list of all mysqld options and configurable variables like the following. 

Possibly variables to option --set-variable (-O) are: 
back_log              current value: 5
join_buffer           current value: 131072
key_buffer            current value: 1048568
max_allowed_packet    current value: 65536
max_connections       current value: 90
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
net_buffer_length     current value: 8192
record_buffer         current value: 131072
sort_buffer           current value: 2097144
table_cache           current value: 64
tmp_table_size        current value: 1048576
thread_stack          current value: 65536
back_log How many outstanding connection requests may MySQL have. This comes into play when the main MySQL thread gets VERY many connection requests in a very short time. It then takes some time (but very short) for the main thread to check the connection and start a new thread. The back_log is how many connects can be stacked during this short time before MySQL momentarily stops answering new requests. You only need to increase this if you expect a large number of connections in a short period of time. In other words, the size of the listen queue for incoming tcp/ip connections. The manual page for the unix system call listen(2) should have more details. Check your OS documentation for the maximum value for this variable.
join_buffer This buffer is used for full joins (without indexes). It is allocated one time for each full join between two tables. Increase this to get a faster full join when adding indexes is not possible. Normally the best way to get fast joins is by adding indexes.
key_buffer Buffers index blocks and are shared by all threads. You might want to increase this when doing many delete/inserts on a table with lots of indexes. To get even more speed use LOCK TABLES. See section LOCK TABLES syntax.
max_allowed_packet Max size of one packet. This allows the message buffer to grow up to this limit when needed (it is initiated to net_buffer_length). May be set very big because this is mainly to find erroneous packets. You must increase this if you are using big BLOBS. It should be as big as the biggest BLOB you want to use.
max_connections How many simultaneous clients are allowed. If you increase this you probably has to increase the number of file descriptors mysqld has. This is Operating system depended so look at you OS documentation.
max_join_size Joins that touch more records than max_join_size return an error. Set this if you have users to tend to make joins without a WHERE that take a long time and return millions of rows.
max_sort_length The number of bytes to use when sorting on BLOB or TEXT columns.
net_buffer_length The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory you can set it to the expected size of a query.
record_buffer Each thread that is doing a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans you may want to increase this.
sort_buffer Each thread that needs to do a sort allocates a buffer of this size. Increase this for faster ORDER BY or GROUP BY. See section Where MySQL stores temporary files.
table_cache Number of open tables for all threads. If this is increased you must see to that the number of open file descriptor is also increased. MySQL needs two file descriptors for each unique table.
tmp_table_size If a temporary table gets bigger than this a The table ### is full error will be generated. Increase this if you do many advanced GROUP BY queries.
thread_stack How big will each threads C stack be. A lot of the limits detected by crash-me are dependent on this. The default is normally enough.
MySQL uses algorithms that are very scalable so one can usually run with very little memory or give MySQL more memory to get better performance. If you have much memory and many tables and want maximum performance with a moderate number of clients you should use something like: 
> safe_mysqld -O key_buffer=16M -O table_cache=128 \
        -O sort_buffer=4M -O record_buffer=1M &
If you have little memory with lots of connections, use something like: 
> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &
or even 
> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 \
        -O record_buffer=8k -O net_buffer=1K &
Note that if you change an option to mysqld it is only for that instance of the server. To see the effects of a parameter change, do something like this mysqld -O key_buffer=32m --help. You can check the parameters in effect with mysqladmin variables. If there are very many connections, 'swapping problems' may occur if mysqld has not been configured to use very little memory for each connection. It also works better if you have a enough memory for all connections of course. For example, for 200 open connections one should have a table cache of at least 200 * (max_number of tables in join).

Go to the first, previous, next, last section, table of contents.