You can use this command to get the MYSQLD server default buffer size:
Shell> mysqld --help
This command generates a table for all MySQLD options and configurable variables. The output includes default and looks like this:
Possible Variables for option --set-variable (-o) area:
Back_log current value: 5
Connect_timeout Current Value: 5
Delayed_Insert_timeout Current Value: 300
Delayed_Insert_limit Current Value: 100
Delayed_Queue_size Current Value: 1000
Flush_time Current Value: 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
Max_write_lock_count capital value: 4294967295
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 being running, by performing this command, you can see the value of the variable it actually use:
Shell> MySQLADMIN VARIABLES
Each option is described below. For buffers, lengths, and stack size are given by bytes, you can use the suffix "k" or "m" to indicate the display value in the K byte or megabytes. For example, 16m points to 16 megabytes. The suffix letter has no relationship; 16m and 16m are the same.
You can also see some statistics from a running server with a command show status. See 7.21 Show syntax (get information on the table, column).
Back_log
Requires the number of connections that mysql can have. When the primary mysql thread gets a lot of connection requests in a short period of time, this works, then the main thread takes some time (despite short) check the connection and start a new thread. The back_log value indicates how many requests can be present in the stack in the short time before mySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short period of connection, you need to add it, in other words, this value is the size of the listening queue that comes to the TCP / IP connection. Your operating system has its own restrictions on this queue. Unix Listen (2) The man page for system call should have more details. Check your OS document to find the maximum value of this variable. Trying to set the restriction of Back_LOG higher than your operating system will be invalid. Connect_timeout
MySQLD Server is waiting for a second number of seconds to connect packets before answering with Bad Handshake.
Delayed_INSERT_TIMEOUT
A INSERT Delayed thread should wait for the INSERT statement before terminating.
Delayed_Insert_limit
After inserting the delayed_insert_limit, the Insert Delayed processor will check if any SELECT statement is not executed. If so, perform these statements before proceeding.
Delayed_Queue_size
It should be allocated for the INSERT Delayed allocation to all a queue (in line). If the queue is full, any customer who performs INSERT Delayed will wait until the queue has space.
FLUSH_TIME
If this is set to a non-zero value, then every FLUSH_TIME seconds all tables will be turned off (to release resources and SYNC to disk).
Interactive_timeout
The server is waiting for a second number of seconds to act on an interactive connection before closing it. A interactive customer is defined as a customer who uses the client_interactive option for mysql_real_connect (). You can also see WAIT_Timeout.
JOIN_BUFFER_SIZE
The size of the buffer used for all the coupling (not using the index). The buffer allocates a buffer to each all-in-one in 2 tables. When the index is impossible, add this value to obtain a faster all-in-one. (The best way to get a quick connection is to increase the index.)
Key_Buffer_Size
Index blocks are buffered and shared by all threads. Key_buffer_size is a buffer size for index blocks, increasing it to get a better index (written to all readings and more), and you can afford to afford. If you make it too big, the system will start switching and really slowing down. Remember that since MySQL does not caches read data, you will have to cache some space for the OS file system. To get more speeds when writing multiple rows, use Lock Tables. See 7.24Lock Tables / UNLOCK TABLES syntax.
Long_query_time
If a query is used exceeds it (in seconds), the Slow_Queries deciple will be increased.
MAX_ALLOWED_PACKET
A maximum size of a package. The message buffer is initialized to NET_BUFFER_LENGTH bytes, but can be added to the max_allowed_packet by the MAX_ALLOWED_PACKET. By default, this value will be too small to capture large (possibly wrong) packages. If you are using a large BLOB column, you must add this value. It should be as big as the biggest blob you want to use.
Max_Connections
Allowed customers' quantity. Increase this value increases the number of file descriptors required by mysqld. See the annotations for file descriptors. See 18.2.4 Too Many Connections.
MAX_CONNECT_ERRORS
If there is more than the number of connections from one host interrupt, this host will prevent further connections. You can use the flush hosts command to clear a host.
MAX_DELAYED_THREADS
Don't start more than the number of threads to handle the INSERT Delayed statement. If you try to insert data in all INSERT DELAYED threads, the row will be inserted, just like the delayed property is not specified. MAX_JOIN_SIZE
A link to read more than MAX_JOIN_SIZE will return an error will be returned. If your user wants to perform without a WHERE clause, it takes a long time and returns to a million line link, set it.
Max_sort_length
The number of bytes used in sorting the BLOB or Text value (each value is used only the max_sort_length byte; the rest is ignored).
MAX_TMP_TABLES
(This choice is not currently doing anything). A customer can maintain the maximum number of temporary tables that open.
NET_BUFFER_LENGTH
The communication buffer is reset between the query to the size. Usually this should not be changed, but if you have little memory, you can set it to query the size of the expectations. (Ie, the length of the SQL statement is expected. If the statement exceeds this length, the buffer is automatically expanded until the max_allowed_packet paragraph.)
RECORD_BUFFER
Each of the threads performing a sequential scan allocates a buffer of this size for each table scanned. If you do a lot of order scans, you may want to add this value.
Sort_buffer
The thread that needs to be sorted allocates a buffer of the size. Add this value to accelerate the ORDER BY or Group BY operation. See where 18.5 mysql stores temporary files.
Table_cache
Open the number of tables for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique open table, see the annotations for file descriptors. For information on how to work, see how to open and close the table 10.2.4 mysql.
TMP_TABLE_SIZE
If a temporary table exceeds the size, MySQL produces an error in the form of the Table TBL_NAME IS FULL. If you do a lot of advanced Group By queries, add the TMP_TABLE_SIZE value.
Thread_stack
The stack size of each thread. Many restrictions detected by Crash-Me test depends on this value. The general operation of the default team is big enough. See 10.8 Using your own benchmark.
Wait_timeout
The server is waiting for a second number of seconds that is connected to a connection before turning off it. INTERACTIVE_TIMEOUT can also be seen.
MySQL uses is a very scalable algorithm, so you usually use very little memory to run or give Mysql more to get better performance.
If you have a lot of memory and a lot of tables and have a medium-quantity customer, you want the biggest performance, you should be like this:
Shell> SAFE_MYSQLD -O key_buffer = 16m -o table_cache = 128 /
-O sort_buffer = 4m -o record_buffer = 1M &
If you have fewer memory and a lot of connections, use such things:
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 a lot of connections, "Switching issues" may occur unless the mysqld has been configured with very little memory. Of course, if you have enough memory for all connections, MySQLD is performed better. Note that if you change an option of MySQLD, it actually only holds the example of the server.
In order to understand the effect of a variation of parameters, do this:
Shell> mysqld -o key_buffer = 32m --help
Ensure that the --help option is the last one; otherwise, the effect of any options listed in the command line will not be reflected in the output.
