Author: Dann Corbit
Date: 14:53:12 03/08/06
10.1 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 7.19 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 15.3 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).
This page took 0 seconds to execute
Last modified: Thu, 15 Apr 21 08:11:13 -0700
Current Computer Chess Club Forums at Talkchess. This site by Sean Mintz.