You can get the default buffer sizes used by the mysqld server with this command:
shell> mysqld --help
This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
join_buffer current value: 131072
key_buffer current value: 1048540
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 90
max_connect_errors current value: 10
max_join_size current value: 4294967295
max_sort_length current value: 1024
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:
shell> mysqladmin variables
Each option is described below. Values for buffer sizes, lengths and stack sizes are given in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. Case of suffix letters does not matter; 16M and 16m are equivalent.
back_log :
The number of outstanding connection requests MySQL can 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 value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. 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. Attempting to set back_log higher than this maximum will be ineffective.
connect_timeout :
The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.
join_buffer :
The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.)
key_buffer :
Index blocks are buffered and are shared by all threads. key_buffer is the size of the buffer used for index blocks. You might want to increase this value when doing many DELETE or INSERT operations on a table with lots of indexes. To get even more speed, use LOCK TABLES.
max_allowed_packet :
The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use.
max_connections :
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. See below for comments on file descriptor limits.
max_connect_errors :
If there is more than this number of interrupted connections from a host this host will be blocked for further connections. You can unblock a host with the command FLUSH HOSTS.
max_join_size :
Joins that are probably going to read more than max_join_size records return an error. Set this value if your users tend to perform joins without a WHERE clause that take a long time and return millions of rows.
max_sort_length :
The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).
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. (That is, the expected length of SQL statements sent by clients. If statements exceed this length, the buffer is automatically enlarged, up to max_allowed_packet bytes.)
record_buffer :
Each thread that does 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 value.
sort_buffer :
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.
table_cache :
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. MySQL needs two file descriptors for each unique open table. See below for comments on file descriptor limits.
tmp_table_size :
If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. Increase the value of tmp_table_size if you do many advanced GROUP BY queries.
thread_stack :
The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value. The default is normally large enough.
wait_timeout :
The number of seconds the server waits for activity on a connection before closing it.
table_cache and max_connections affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to do this, because the method for changing the limit varies widely from system to system.
table_cache is related to max_connections. For example, for 200 open connections, you should have a table cache of at least 200 * n, where n is the maximum number of tables in a join.
MySQL uses algorithms that are very scalable, so you 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 this:
shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M -O record_buffer=1M &
If you have little memory and lots of connections, use something like this:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O record_buffer=100k &
or even:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &
If there are very many connections, "swapping problems" may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course.
Note that if you change an option to mysqld, it remains in effect only for that instance of the server.
To see the effects of a parameter change, do something like this:
shell> mysqld -O key_buffer=32m --help
Make sure that the --help option is last; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.
MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers
shell> mysqld --help
This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
join_buffer current value: 131072
key_buffer current value: 1048540
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 90
max_connect_errors current value: 10
max_join_size current value: 4294967295
max_sort_length current value: 1024
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:
shell> mysqladmin variables
Each option is described below. Values for buffer sizes, lengths and stack sizes are given in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. Case of suffix letters does not matter; 16M and 16m are equivalent.
back_log :
The number of outstanding connection requests MySQL can 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 value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. 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. Attempting to set back_log higher than this maximum will be ineffective.
connect_timeout :
The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.
join_buffer :
The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.)
key_buffer :
Index blocks are buffered and are shared by all threads. key_buffer is the size of the buffer used for index blocks. You might want to increase this value when doing many DELETE or INSERT operations on a table with lots of indexes. To get even more speed, use LOCK TABLES.
max_allowed_packet :
The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use.
max_connections :
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. See below for comments on file descriptor limits.
max_connect_errors :
If there is more than this number of interrupted connections from a host this host will be blocked for further connections. You can unblock a host with the command FLUSH HOSTS.
max_join_size :
Joins that are probably going to read more than max_join_size records return an error. Set this value if your users tend to perform joins without a WHERE clause that take a long time and return millions of rows.
max_sort_length :
The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).
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. (That is, the expected length of SQL statements sent by clients. If statements exceed this length, the buffer is automatically enlarged, up to max_allowed_packet bytes.)
record_buffer :
Each thread that does 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 value.
sort_buffer :
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.
table_cache :
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. MySQL needs two file descriptors for each unique open table. See below for comments on file descriptor limits.
tmp_table_size :
If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. Increase the value of tmp_table_size if you do many advanced GROUP BY queries.
thread_stack :
The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value. The default is normally large enough.
wait_timeout :
The number of seconds the server waits for activity on a connection before closing it.
table_cache and max_connections affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to do this, because the method for changing the limit varies widely from system to system.
table_cache is related to max_connections. For example, for 200 open connections, you should have a table cache of at least 200 * n, where n is the maximum number of tables in a join.
MySQL uses algorithms that are very scalable, so you 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 this:
shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M -O record_buffer=1M &
If you have little memory and lots of connections, use something like this:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O record_buffer=100k &
or even:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &
If there are very many connections, "swapping problems" may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course.
Note that if you change an option to mysqld, it remains in effect only for that instance of the server.
To see the effects of a parameter change, do something like this:
shell> mysqld -O key_buffer=32m --help
Make sure that the --help option is last; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.
MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers
No comments:
Post a Comment