MYSQL environment variables_PHP tutorial

You can get the mysqld server default buffer size with this command:

shell> mysqld --help

This command generates a table of all mysqld options and configurable variables. The output includes 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
delayed_insert_timeout?current value: 300
delayed_insert_limit?current value: 100 : 0
interactive_timeout? current value: 28800
join_buffer_size???current value: 131072
key_buffer_size??? current value: 1048540
lower_case_table_names?current value: 0
long_query_time??? current value: 10
max_allowed_packet??current value: 1048576
max_connections??? current value: 100
max_connect_errors??current value: 10
max_delayed_threads? current value: 20
max_heap_table_size? current value : 16777216
max_join_size????current value: 4294967295
max_sort_length???current value: 1024
max_tmp_tables????current value: 32 net_buffer_length ?? current value: 16384
query_buffer_size?? current value: 0
record_buffer???? current value: 131072
sort_buffer????? current value: 2097116
table_cache???? current value: 64
thread_concurrency??current value: 10
tmp_table_size????current value: 1048576
thread_stack?????current value: 131072
wait_timeout?????current value : 28800

If there is a mysqld server running, you can see the values ​​of the variables it actually uses by executing this command:

shell> mysqladmin variables

Each option is described below. For buffer size, length, and stack size values ​​given in bytes, you can use the suffix "K" or "M" to indicate that the value is displayed in K bytes or megabytes. For example, 16M indicates 16 megabytes. The case of the suffix letters does not matter; 16M and 16m are the same.

You can also use the command SHOW STATUS to see some statistics from a running server. See 7.21 SHOW syntax (get table and column information).

back_log
The number of connections required for MySQL. This works when the main MySQL thread gets a lot of connection requests in a short period of time, and then the main thread takes some time (albeit briefly) to check for connections and start a new thread. The back_log value indicates how many requests can be stored in the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect many connections in a short period of time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limit on this queue size. The man page for the Unix listen(2) system call should have more details. Check your OS documentation to find out the maximum value of this variable. Attempting to set back_log higher than your operating system's limit will have no effect.
connect_timeout
The number of seconds the mysqld server is waiting for a connection message before responding with a Bad handshake.
delayed_insert_timeout
The amount of time an Insert DELAYED thread should wait for an Insert statement before terminating.
delayed_insert_limit
After inserting delayed_insert_limit rows, the Insert DELAYED processor will check if any Select statements have not been executed. If so, execute the allow statements before continuing.
delayed_queue_size
How large a queue (in number of rows) should be allocated for processing Insert DELAYED. If the queue is full, any client that performs an Insert DELAYED will wait until the queue becomes free again.
flush_time
If this is set to a non-zero value, then every flush_time seconds all tables will be closed (to free up resources and sync to disk).
interactive_timeout
The number of seconds the server waits for action on an interactive connection before closing it. An interactive client is defined as a client using the CLIENT_INTERACTIVE option to mysql_real_connect(). Also visible wait_timeout.
join_buffer_size
The buffer size used for all joins (not indexed joins). Buffer allocates a buffer for each full join between two tables. When adding an index is not possible, increasing this value can result in a faster full join. (Often the best way to get fast joins is to increase the index.)
key_buffer_size
The index block is buffered and shared by all threads. key_buffer_size is the buffer size used for index blocks, increase it to get better handling of the index (for all reads and multiple writes), to as much as you can afford. If you make it too big, the system will start paging and really slow down. Remember that since MySQL does not cache read data, you will have to leave some space for the OS file system cache. To get more speed when writing multiple rows, use LOCK TABLES. See 7.24 LOCK TABLES/UNLOCK TABLES syntax.
long_query_time
If a query takes longer than this (in seconds), the Slow_queries counter will be incremented.
max_allowed_packet
The maximum size of a packet. The message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes if needed. By default, this value is too small to capture large (possibly erroneous) packets. If you are using large BLOB columns, you must increase this value. It should be as large as the largest BLOB you want to use.
max_connections
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors required by mysqld. See note below on file descriptor limitations. See 18.2.4 Too many connections error.
max_connect_errors
If there are more than this number of disconnected connections from a host, this host blocks further connections. You can use the FLUSH HOSTS command to unblock a host.
max_delayed_threads
Do not start more than this number of threads to process the Insert DELAYED statement. If you try to insert data into a new table after all Insert DELAYED threads have been used, rows will be inserted as if the DELAYED attribute was not specified.
max_join_size
Joins that may read more than max_join_size records will return an error. Set this if your users want to perform joins that don't have a Where clause, take a long time, and return millions of rows.
max_sort_length
The number of bytes used when sorting BLOB or TEXT values ​​(only the first max_sort_length bytes of each value are used; the rest are ignored).
max_tmp_tables
(This option does nothing yet). The maximum number of temporary tables that a client can keep open at the same time.
net_buffer_length
The communication buffer is reset to this size between queries. Normally this should not be changed, but if you have little memory you can set it to the size expected by the query. (That is, the expected length of the SQL statement issued by the client. If the statement exceeds this length, the buffer is automatically expanded until max_allowed_packet bytes.)
record_buffer
Each thread performing a sequential scan scans it Each table is allocated a buffer of this size. If you do a lot of sequential scans, you may want to increase this value.
sort_buffer
Each thread that needs to be sorted allocates a buffer of this size. Increasing this value speeds up ORDER BY or GROUP BY operations. See 18.5 Where MySQL stores temporary files.
table_cache
The number of tables open for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires 2 file descriptors per unique open table, see comments on file descriptor limitations below. For information on how table caching works, see 10.2.4 How MySQL Opens and Closes Tables.
tmp_table_size
If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. If you do a lot of advanced GROUP BY queries, increase the tmp_table_size value.
thread_stack
The stack size of each thread. Many limitations detected by crash-me tests rely on this value. The default team is large enough for general operations. See 10.8 Using Your Own Benchmarks.
wait_timeout
The number of seconds the server waits for action on a connection before closing it. Also visible interactive_timeout.
MySQL uses a very scalable algorithm, so you can usually run with less memory or give MySQL more memory to get better performance.

If you have a lot of memory and a lot of tables and a moderate number of clients, and want maximum performance, you should have 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 less memory and a lot 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 many connections, "swapping problems" may occur, unless mysqld has been configured per connections use very little memory.Of course if you have enough memory for all connections, mysqld performs better.

Note that if you change an option on mysqld, it actually only persists for that instance of the server.

In order to understand the effect of a parameter change, do this:

shell> mysqld -O key_buffer=32m --help

Make sure the --help option is the last one; otherwise , the effects of any options listed after it on the command line will no longer be reflected in the output.