MySQL Performance tuning Five Ways

(i) connection

Connections typically come from a Web server, and the following lists some of the connection-related parameters and how to set them up.

1, Max_connections

This is the maximum number of connections allowed by the Web server, remembering that each connection uses session memory (about session memory, which is covered later in the article).

2, max_packet_allowed

The maximum packet size, usually equal to the size of the largest data set you need to return in a chunk, if you are using remote mysqldump, then its value needs to be larger.

3, Aborted_connects

Check the system Status counter to determine that it has not grown if the number of increases indicates that the client has encountered an error while connecting.

4, Thread_cache_size

An inbound connection creates a new thread in MySQL because it's cheap and fast to open and close connections in MySQL, and it doesn't have as many persistent connections as other databases, such as Oracle, but thread pre-creation doesn't save time, which is why MySQL threads are cached.

If you are growing, pay close attention to the threads that are created, and let your thread cache larger, and for 2550 or 100 of thread_cache_size, memory consumption is not much.

(ii) Query caching

Cached queries in MySQL include two resolution query plans, as well as the returned datasets, which will invalidate items in the query cache if the underlying table data or structure changes.

1, Query_cache_min_res_unit

MySQL parameters in the query_cache_min_res_unit query cache block is allocated at this size, using the following formula to calculate the average size of the query cache, based on the calculation results set this variable, MySQL will be more efficient use of query cache, cache more queries, Reduce the waste of memory.

2, Query_cache_size

This parameter sets the total size of the query cache.

3, Query_cache_limit

This parameter tells MySQL to drop a query larger than this size, generally large queries are relatively rare, such as running a batch to perform a large report statistics, so those large result sets should not be filled with the query cache.

Qcache hit ratio = Qcache_hits/(qcache_hits + com_select)

Use

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

Use

Sql> Show variables like ' query% ';

qcache_* status variables you can get with:

Sql> Show status like ' qcache% ';

Gets the value of the query_cache_size.

(iii) Temporary tables

The memory speed is pretty fast, so we want all the sort operations to be done in memory, and we can adjust the query to make the result set smaller to sort the memory, or set the variable to a larger size.

Tmp_table_size

Max_heap_table_size

Whenever you create a temporary table in MySQL, it uses the minimum value of both variables as the threshold value, and in addition to building temporary tables on disk, many sessions are created that preempt restricted resources, so it's best to tune the query instead of setting these parameters higher, and  Note that tables with a BLOB or text field type are written directly to disk. MySQL bidirectional replication technology

(iv) Session memory

Each session in MySQL has its own memory, which is the memory allocated to the SQL query, so you want to make it as large as possible to meet your needs. But you have to balance the number of consistent sessions in the database at the same time. The black art here is that MySQL is cached on demand, so you can't just add them and multiply the number of sessions, which is a much larger estimate than MySQL typically uses. The best practice is to start MySQL, connect all the sessions, and then continue to focus on the Virt column of the top-level session, the number of mysqld rows is usually relatively stable, this is the actual total memory usage, minus all the static MySQL memory area, get the actual all session memory, Then divide by the number of sessions to get an average.

1, Read_buffer_size

Cache a block of contiguous scans, which is a cross-storage engine, not just a myisam table.

2, Sort_buffer_size

The size of the sort buffer is performed, preferably set to 1m-2m, and then set in the session, setting a higher value for a particular query.

3, Join_buffer_size

Execute the buffer size allocated by the federated query, set it to 1m-2m size, and then set it individually on demand in each session.

4, Read_rnd_buffer_size

For sorting and order by operations, it is best to set it to 1M and then set it as a session variable to a larger value in the session.

(v) Slow query log

A slow query log is a useful feature of MySQL.

1, Log_slow_queries

MySQL parameter in the log_slow_queries parameter set it in the My.cnf file, set it to on, by default, MySQL will put the file into the data directory, the file is named "Hostname-slow.log", But you can also specify a name for this option when you set it.

2, Long_query_time

The default value is 10 seconds, you can set it dynamically, the value from 1 to set it to ON, if the database is started, by default, the log will be closed. As of 5.1.21 and the version of Google patch installed, this option can be set in microseconds, which is an amazing feature, because once you have eliminated all queries that have been queried for more than 1 seconds, the adjustment is very successful, which helps you to eliminate the problem SQL before the problem gets bigger.

3, Log_queries_not_using_indexes

It's a good idea to turn on this option, which really records the query that returns all rows.

MySQL Performance tuning Five Ways