Simple configuration optimization for MySQL servers

Source: Internet
Author: User

The following article describes how to optimize the configuration of the MySQL server and how to optimize the configuration of the MySQL server, if you are interested in its practical application, you can click the following article to learn about it.

You can use this command to obtain the default buffer size of the MySQL server:

Shell> MySQL (the best combination with PHP) d -- help

This command generates a table of all MySQL (the best combination with PHP) options d and configurable variables. The output includes the default value and looks like this:

Possible variables for option -- set-variable (-O) are:

  1. back_log current value: 5   
  2. connect_timeout current value: 5   
  3. delayed_insert_timeout current value: 300   
  4. delayed_insert_limit current value: 100   
  5. delayed_queue_size current value: 1000   
  6. flush_time current value: 0   
  7. interactive_timeout current value: 28800   
  8. join_buffer_size current value: 131072   
  9. key_buffer_size current value: 1048540   
  10. lower_case_table_names current value: 0   
  11. long_query_time current value: 10   
  12. max_allowed_packet current value: 1048576   
  13. max_connections current value: 100   
  14. max_connect_errors current value: 10   
  15. max_delayed_threads current value: 20   
  16. max_heap_table_size current value: 16777216   
  17. max_join_size current value: 4294967295   
  18. max_sort_length current value: 1024   
  19. max_tmp_tables current value: 32   
  20. max_write_lock_count current value: 4294967295   
  21. net_buffer_length current value: 16384   
  22. query_buffer_size current value: 0   
  23. record_buffer current value: 131072   
  24. sort_buffer current value: 2097116   
  25. table_cache current value: 64   
  26. thread_concurrency current value: 10   
  27. tmp_table_size current value: 1048576   
  28. thread_stack current value: 131072   
  29. wait_timeout current value: 28800  

If a MySQL server is running, run this command to view the variable value it actually uses:

  1. Shell> MySQL (the best combination with PHP) admin variables

Each option is described below. The buffer size, length, and stack size values are given in bytes. You can use the suffix "K" or "M" to indicate that the values are displayed in K bytes or MB. For example, 16 MB indicates 16 Mb. It does not matter if the suffix is uppercase or lowercase. 16M and 16m are the same.

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


The number of connections required for MySQL (the best combination with PHP. When the main MySQL (best combination with PHP) threads get a lot of connection requests in a very short time, this works, and then the main thread takes some time (although very short) check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short period of time before MySQL (the best combination with PHP) temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. The Unix listen (2) System Call manual page should have more details. Check your OS document to find the maximum value of this variable. Trying to set back_log to be higher than your operating system limit will be invalid.


MySQL (the best combination with PHP) d server is waiting for a connection packet in seconds before responding with Bad handshake.


The time for an insert delayed thread to wait for the INSERT statement before termination.


After the delayed_insert_limit row is inserted, the insert delayed processor checks whether any SELECT statement is not executed. In this case, execute the allow statements before continuing.


An extra large Queue (by number of rows) should be allocated for processing insert delayed ). If the queue is full, any insert delayed client will wait until the queue has space.


If this value is set to a non-zero value, all tables are closed every flush_time second (to release resources and sync to disk ).


The number of seconds that the MySQL server waits for action on an interactive connection before closing it. An interactive customer is defined as the customer who uses the CLIENT_INTERACTIVE option for MySQL (the best combination with PHP) _ real_connect. Wait_timeout is also visible.


The buffer size used for join Operations (not the join operation using indexes ). The buffer zone allocates a buffer for each join between two tables. When an index is increased, increasing this value will allow you to get a faster join. The best way to get a quick join is to increase the index .)


The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. Remember that since MySQL (the best combination with PHP) does not cache read data, you must leave some space for the OS file system cache. To get more speed when writing multiple rows, use lock tables. See the 7.24 lock tables/unlock tables syntax.


If a query takes more time than it (in seconds), The Slow_queries counter will be added.


The maximum size of a package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. By default, if this value is too small, a large (possibly incorrect) package will be captured. If you are using a large BLOB column, you must add this value. It should be as big as the largest BLOB you want to use.


Number of customers allowed simultaneously. Increase the number of file descriptors required by MySQL (the best combination with PHP) d. See the comments for file descriptor restrictions. See 18.2.4 Too connector connections error.


If there are more than this number of connections interrupted from a host, this host blocks further connections. You can use the flush hosts command to clear a host.


Do not start threads with more than this number to process the insert delayed statement. If you try to INSERT data to a new table after all the insert delayed threads are used, the row will be inserted, as if the DELAYED attribute was not specified.


An error may be returned if more than max_join_size records are to be read. If your user wants to execute a join that does not have a WHERE clause and takes a long time and returns millions of rows, set it.


The number of bytes used for sorting BLOB or TEXT values (each value is used only in the header of max_sort_length; others are ignored ).


This option does not work at the moment ). The maximum number of temporary tables that a customer can open at the same time.


The communication buffer is reset to this size between queries. Normally this should not be changed, but if you have a small amount of memory, you can set it to the desired size for the query. That is, the expected length of the SQL statement sent by the customer. If the statement exceeds this length, the buffer is automatically expanded until max_allowed_packet bytes .)


Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value.


Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. See where MySQL 18.5 (the best combination with PHP) Stores temporary files.


Number of tables opened for all threads. Add this value to increase the number of file descriptors required by MySQL (the best combination with PHP) d. MySQL (the best combination with PHP) requires two file descriptors for each unique Open table. For more information, see the comments on file descriptor restrictions. For information on how the table cache works, see 10.2.4 MySQL (the best combination with PHP) how to open and close the table.


If a temporary table exceeds this size, MySQL (The best combination with PHP) generates an error in The table tbl_name is full format. If you do a lot of advanced group by queries, increase the tmp_table_size value.


The stack size of each thread. Many of the limits detected by the crash-me test depend on this value. By default, the team's general operations are large enough. See section 10.8 use your own benchmark.


The number of seconds before the MySQL server closes a connection. You can also see interactive_timeout.

MySQL (the best combination with PHP) is a very scalable algorithm, so you can usually run with a small amount of memory or give MySQL (the best combination with PHP) more memory for better performance.

If you have a lot of memory and many tables and have a medium number of customers and want the maximum performance, you should have something like this:

  1. Shell> safe_MySQL (the best combination with PHP) d-O key_buffer = 16 M-O table_cache = 128 \
  2. -O sort_buffer = 4 M-O record_buffer = 1 M &

If you have less memory and a large number of connections, use the following:

  1. Shell> safe_MySQL (the best combination with PHP) d-O key_buffer = 512 k-O sort_buffer = 100 k \
  2. -O record_buffer = 100 k &

Or even:

  1. Shell> safe_MySQL (the best combination with PHP) d-O key_buffer = 512 k-O sort_buffer = 16 k \
  2. -O table_cache = 32-O record_buffer = 8 k-O net_buffer = 1 K &

If there are many connections, a "switching problem" may occur, unless MySQL (the best combination with PHP) d has been configured to use very little memory for each connection. Of course, if you have enough memory for all the connections, MySQL (the best combination with PHP) d performs better.

Note: If you change an option of MySQL (the best combination of PHP and MySQL) d, it will only maintain the example of the MySQL server.

To understand the effect of a parameter change, do the following:

  1. Shell> MySQL (the best combination with PHP) d-O key_buffer = 32 m -- help

Ensure that the -- help option is the last one; otherwise, the effects of any options listed after it on the command line will not be reflected in the output.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.