Mysql optimization and troubleshooting

Mysql optimization and troubleshooting

First, we will introduce several keywords.

1 show status indicates the current status data of the database

Show [session | global] status session is the statistical result of the current connection. global is all the statistical results since the Database Service was started.

Show status like 'com _ % '; among them, Com_XXX indicates the number of times the XXX statement is executed. We mainly focus on the parameters Com_select, Com_insert, Com_update, and Com_delete, which respectively indicate the number of queries and the number of additions, update count, delete count

 

In addition, several parameters are worth noting.

Connections: number of attempts to connect to the MySQL server

Uptime: the server's working time (unit: seconds)

Slow_queries: Number of slow queries

Then go to the key point for slow query.

When is the slow query time, that is, the query time exceeds the set time of the slow query, that is, the slow query time is set to 10 seconds, and a query exceeds 10 seconds. This query is called a slow query. you can set the time for querying slow queries.

First, check whether the slow query is enabled.

Use log_slow_queries to determine whether to enable long_query_time slow query.

Enable slow Query

Long_query_time = 2
Slow-query-log = On
Slow_query_log_file = "C:/Program Files/MySQL Server 5.6/mysql_slow_query.log"

Put these three parameters under [mysqlld] and restart the database service. Then, you can see that the slow query is enabled.