The company's Web site access more and more large, MySQL naturally become a bottleneck, so recently I have been studying MySQL optimization, the first natural thought is the MySQL system parameters optimization, as a large number of visits to the Web site (more than 200,000 times a day) of the database system, can not expect MySQL The default system parameters allow MySQL to run very smoothly. The following article is mainly about the MySQL server optimization of the timing of the operation of the procedures, and this article also introduces the optimal configuration of the MySQL server application code, if you are interested in its practical application, you can click on the following article to understand it.
You can use this command to get the MySQL server default buffer size:
Shell> MySQL (best combination with PHP) d--help
This command generates a table of all MySQL (and PHP best mix) d options and configurable variables. The output includes default values and looks like something like this:
Possible Variables for option--set-variable (-O) are:
Copy Code code as follows:
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 Current 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 MySQL server running, by executing this command, you can see the value of the variable it actually uses:
Copy Code code as follows:
Shell> MySQL (best combination with PHP) admin variables
Each option is described below. The value of the buffer size, length, and stack size is given in bytes, and you can use the suffix "k" or "M" to indicate that the value is displayed in K-byte or megabytes. For example, 16M points out 16 megabytes. The case of the suffix letter is not related; 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, column information).
Back_log
The number of connections required for MySQL (the best combination of PHP). When the main MySQL (and the best combination of PHP) threads get very many connection requests in a very short time, this works, and then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be on the stack for a short period of time before MySQL (the best combination of PHP) has stopped answering new requests. Only if you expect to have a lot of connections in a short 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 limits on the size of this queue. Unix Listen (2) system calls to the hand album should have more details. Check your OS documentation to find the maximum value for this variable. Attempting to set Back_log above your operating system will be ineffective.
Connect_timeout
MySQL (the best combination of PHP) d server is waiting for the number of seconds for a connection message before it answers with bad handshake (poor handshake).
Delayed_insert_timeout
An insert delayed thread should wait for the INSERT statement time before terminating.
Delayed_insert_limit
After inserting the Delayed_insert_limit line, the insert delayed processor checks to see if any SELECT statements have not been executed. If so, allow these statements to be executed before continuing.
Delayed_queue_size
How many queues (in rows) should be allocated for processing insert delayed. If the queue is full, any customer who inserts delayed will wait until there is room in the queue.
Flush_time
If this is set to a value other than 0, all tables will be closed for every flush_time second (to free resources and sync to disk).
Interactive_timeout
The number of seconds the MySQL server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the client_interactive option for MySQL (the best combination of PHP) and _real_connect (). Also visible wait_timeout.
Join_buffer_size
The buffer size (not an indexed join) for the full join (join). The buffer allocates a buffer to each of the 2 tables, and when the index is added it is not possible to increase the value to get a faster full join. (Often the best way to get a quick connection is to add an index.) )
Key_buffer_size
The index block is buffered and is shared by all threads. Key_buffer_size is the size of the buffer used for the index block, adding that it can be better processed by indexing (for all read and multiple writes), to the extent that you can afford that much. If you make it too big, the system will start to change pages and it really slows down. Remember that since MySQL (the best combination of PHP) does not cache read data, you will have to leave some room for the OS file system cache. To get more speed when writing multiple rows, use lock TABLES. See 7.24LOCK tables/unlock TABLES syntax.
Long_query_time
If a query takes more time than it (in seconds), the Slow_queries register is incremented.
Max_allowed_packet
The maximum size of a package. The message buffer is initialized to net_buffer_length bytes, but can be incremented to max_allowed_packet bytes as needed. By default, this value is too small to catch large (possibly wrong) packages. If you are using a large BLOB column, you must increase the value. It should be as big as the largest blob you want to use.
Max_connections
The number of simultaneous customers allowed. Increase this value to increase the number of file descriptors required for MySQL (and the best combination of PHP) d. See below for comments with file descriptor restrictions. See 18.2.4 Too many connections error.
Max_connect_errors
If there are more than that number of disconnected connections from one host, this host blocks further connections. You can use flush hosts command to dredge a host.
Max_delayed_threads
Do not start a thread with more than this number to handle the insert delayed statement. If you attempt to insert data into a new table after all insert delayed threads are used, the row is inserted, just as the delayed attribute is not specified.
Max_join_size
A join that may be reading more than Max_join_size records will return an error. If your users want to perform a join that does not have a WHERE clause, takes a long time and returns millions of rows, set it up.
Max_sort_length
The number of bytes to use when ordering a blob or text value (each value is used only by the header max_sort_length byte; the rest is ignored).
Max_tmp_tables
(the option is not doing anything yet). The maximum number of temporary tables that a customer can keep open at the same time.
Net_buffer_length
The communication buffer is reset between queries to that size. Usually this should not be changed, but if you have very little memory, you can set it to the size of the query you want. (that is, the desired length of the SQL statement that the customer emits.) If the statement exceeds this length, the buffer is automatically enlarged until the Max_allowed_packet byte. )
Record_buffer
Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans. If you do a lot of sequential scans, you might want to add that value.
Sort_buffer
Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action. See 18.5 MySQL (the best combination of PHP) where to store temporary files.
Table_cache
The number of tables opened for all threads. Adding this value increases the number of file descriptors required for MySQL (and the best combination of PHP) d. MySQL (the best combination of PHP) requires 2 file descriptors for each unique open table, with comments on the file descriptor restrictions. For information about how table caching works, see how 10.2.4 MySQL (the best combination of PHP) opens and closes the table.
Tmp_table_size
If a temporary table exceeds that size, MySQL (the best combination of PHP) produces an error in the table tbl_name is full form, adding tmp_table_size values if you do many advanced group by queries.
Thread_stack
The stack size of each thread. Many of the limitations detected by the CRASH-ME test depend on this value. The default team's general operation is large enough. See 10.8 using your own benchmark.
Wait_timeout
The number of seconds the MySQL server waits for action on a connection before closing it. Also visible interactive_timeout.
MySQL (the best combination of PHP) uses a scalable algorithm, so you can usually run with little memory or give MySQL (the best combination of PHP) more storage for better performance.
If you have a lot of memory and a lot of tables and have a medium number of customers, want maximum performance, you should have something like this:
Copy Code code as follows:
Shell> Safe_mysql (best combination with PHP) d-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:
Copy Code code as follows:
Shell> Safe_mysql (best combination with PHP) d-o key_buffer=512k-o sort_buffer=100k \ o record_buffer=100k &
or even:
Copy Code code as follows:
Shell> Safe_mysql (the best combination with PHP) d-o key_buffer=512k-o sort_buffer=16k \ o table_cache=32-o record_buffer=8k-o FFER=1K &
If there are many connections, "swap issues" can occur unless MySQL (and the best combination of PHP) d has been configured to use little memory for each connection. Of course, if you have enough memory for all the connections, MySQL (the best combination of PHP) performs better.
Note that if you change the MySQL (and PHP best combination) d option, it is actually only maintained for that example of the MySQL server.
To understand the effect of a parameter change, do this:
Copy Code code as follows:
Shell> MySQL (the best combination of PHP) D-o key_buffer=32m--help
Ensure that the--HELP option is the last one; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.