Five configuration options affect of the MySQL performance

(A) connections

Connection is usually from the Web server, the following are some of the parameters related with the connection, and how to set them.

1, max_connections

This is the Web server allows the maximum number of connections, each connection uses memory.

2,max_packet_allowed

Maximum packet size, generally mean that you need to return a chunk the size of the largest data set, if you use a remote mysqldump, then the need for greater its value.

3,aborted_connects

Check the state of the system counter, is no growth, growth that if the client connection error.

4,thread_cache_size

Access MySQL connections will create a new thread, because the MySQL open and close connections are cheap, fast speed, it is not like other databases, such as Oracle are so many persistent connections, but the thread is created in advance and will not save time, That is why the reasons for the MySQL thread cache.

Please attention to these additional threads, so your thread cache more, for 2550 or 100 thread_cache_size, memory footprint is not much.

(B) Query cache

(C) Temp tables

Memory query speed is very fast, so we hope that all of the sorting operations are in memory, so that we can adjust the query result set in order to achieve a smaller sort of memory, or to be more variable.

tmp_table_size

max_heap_table_size

Whenever you create temporary tables in MySQL, which will use the minimum of these two variables as the critical value, in addition to building temporary tables on disk, it will also create many sessions, these sessions will be limited to seize the resources, the most Good is to adjust the query rather than the higher of these parameters, while there is a need to pay attention BLOB or TEXT field types of the table will be directly written to disk.

(D) concurrent sessions

MySQL Each concurrent sessions has its own memory, this memory is the memory allocated to the SQL query, so you want to make it as large as possible to meet the needs. But you can not balance the same time, the number of concurrent sessions within the database consistency. Here is a littleblack art is the MySQL cache is allocated as needed, so you can not just add them and multiply by the number of concurrent sessions, this estimate down to less than the typical use of MySQL is much greater.

Best practice is to start MySQL, connect all the session, and then continue to focus on top-level session of the VIRT column, mysqld number of rows usually remain relatively stable, which is the total amount of actual memory, minus all the static MySQL memory region to be the actual All session memory, and then divided by the number of sessions to be average.

1,read_buffer_size

Size of the buffer used for doing full table scans of MyISAM tables. Allocated per thread, if a full scan is needed.

2,sort_buffer_size

The size of the sorting buffer, it is best to set it to 1M-2M, and then set in the thread for a particular query to set a higher value.

3,join_buffer_size

Implementation of the joint distribution of the buffer size of the query, set the size to 1M-2M, and then again in a separate demand for each thread in the set.

4,read_rnd_buffer_size

Used to sort and order by operations, it is best to set it to 1M, then the session can be used as a thread variable is set to a larger value.

(E) slow query log

MySQL slow query log is a useful feature.

1, log_slow_queries

 Parameters log_slow_queries MySQL my.cnf file to set parameters in it, set it on, by default, MySQL will file into the data directory, files with "hostname-slow.log " in the form of name, but you When setting this option you can assign a name.

2, long_query_time

 The default value is 10 seconds, you can dynamically set it, the value from 1 to set it on, if thedatabase is started, by default, the log will be closed.

3, log_queries_not_using_indexes

 Turn this option is a good idea, it truly records the query returns all rows.

MySQL query cache in the query plan includes two analysis, and the returned data set, if the underlying table data or structural changes will make the query cache item is invalid.

1, query_cache_min_res_unit

MySQL query cache parameters query_cache_min_res_unit the size of the block is allocated, use the following formula to calculate the average size of the query cache, set this variable according to the results, MySQL will be more effective use of the query cache, query cache more reduce the memory waste.

2, query_cache_size

This parameter set the total size of the query cache.

3, query_cache_limit

This tells MySQL lost query is larger than this size, is still relatively rare for large queries, such as the implementation of a large run a batch report statistics, so that should not be filled with a large result set query cache.

qcache hit ratio = qcache_hits/(qcache_hits + com_select).

SQL> show status like 'qcache%';
SQL> show status like 'com_%';

Find these variables.

average query size = (query_cache_size – qcache_free_memory)/qcache_queries_in_cache.

SQL> show variables like 'query%';
qcache_ * status variables you can get with:
SQL> show status like 'qcache%';
Get query_cache_size value.