MySQL Server Optimization-(1)

Parameter Optimization: it is actually a solution officially provided by MySQL for our server.

This document introduces a command:

Show status; list the running status values of the MySQL server

Show variables: List MySQL server configuration information/

I. Slow Query

Log_slow_queries = slow_queries is basically the same as the previous function, and must be restarted to take effect (on: off: Off)

Slow_launch_time is recorded if the execution time exceeds 2 seconds.
Slow_query_log this is a global variable that can be changed during runtime. It indicates whether to enable the slow query log. In this field, it indicates on.

Representative: The system displays 19054 slow queries. After discovery. You can view the logs directly.

Ii. Maximum number of connections

Sometimes MySQL will encounter this error 1040: Too connector connections,

One is that the access traffic is indeed high, and the MySQL server cannot resist it. In this case, we need to consider increasing the pressure on the server to distribute the read traffic.

The value of max_connections In the MySQL configuration file is too small:

See:

The maximum number of connections is 500.

The actual number of connections is 257.

Pretty good.

In fact, there is a formula to determine whether a person is good or bad. The actual maximum number of connections/Maximum number of connections = about 85%. Very good.

For example, if the number is less than 10%. Probably because your maximum connections configuration is high.

3. key_buffer_size

The key_buffer_size of the 256m is allocated. What does it mainly do?

The balance parameter of the index hit rate.

Key_read_requests INDEX request

Key_reads does not use indexes. Scan the disk directly.

So how can we balance that. Here is another formula.

Key_reads/key_read_requests is less than 0.1%. 1000 requests to scan the hard disk in one zone. This is a small note.

Iv. key_blocks_u *

Key_blocks_unused indicates the number of unused cache clusters (blocks)

Key_blocks_used indicates the maximum number of blocks used in the past.

In the current environment, all the caches are used, either by adding key_buffer_size, or by using a transitional index, the cache is full. Key_blocks_used/(key_blocks_unused + key_blocks_used) = 80% or so is not bad.

V. Open Table

Table cache table_cache.

Open_tables

Opened_tables: Number of opened tables

Query table_cache.

The following formula is good. About this situation is good.

Open_tables/opened_tables * 100%> = 85%
Open_tables/table_cache * 100% <= 95%

Today, we will first introduce the experience accumulated by our predecessors. This is our fortune.

In the next article, I will continue to introduce

OK.