Mysql Performance Query command

1. Number of system mysql processes

Ps-ef | grep "MySQL" | Grep-v "grep" | Wc–l

2.slave_running

MySQL > Show status like ' slave_running ';

If the system has a slave replication server, this value indicates the health of the server from the

3.threads_connected

MySQL > Show status like ' threads_connected ';

The number of connections currently being made by the client. This value will be less than the preset value, but you can also monitor this value to be large, which guarantees that the client is in an active state.

4.threads_running

MySQL > Show status like ' threads_running ';

If the database is overloaded, you will get a value that is growing (the query statement continues). This value can also be less than the pre-set value. This value is not a problem in a short period of time to exceed the limit value. When the threads_running value exceeds the preset value and the value does not fall back within 5 seconds, you need to monitor some other values at the same time.

5.aborted_clients

MySQL > Show status like ' aborted_clients ';

The value of the client being abnormally interrupted, that is, the client that is connected to the MySQL server is not properly disconnected or shut down. For some applications there is no impact, but for other applications you may want to track this value because an abnormally interrupted connection may indicate a problem with some applications.

6.Questions

Mysql> Show status like ' Questions ';

The number of queries per second can also be the number of queries, depending on the command you enter, you will get the different values you want.

7.handler_*

Mysql> Show status like ' handler_% ';

If you want to monitor the underlying (low-level) database load, these values are worth tracking.

If the Handler_read_rnd_next value is quite different from what you think is normal, it might tell you that you need to optimize or index a problem. Handler_rollback indicates the number of queries that the transaction was rolled back. You may want to look into the reasons.

8.opened_tables

Mysql> Show status like ' Opened_tables ';

The table cache does not have a hit count. If the value is large, you may need to increase the Table_cache value. Typically, you might want this value to open fewer than 1 or 2 tables per second.

9.select_full_join

Mysql> Show status like ' Select_full_join ';

There is no execution of the primary key (key) union (Join). The value may be zero. This is a good way to catch development errors because some of these queries can degrade the performance of your system.

10.select_scan

Mysql> Show status like ' Select_scan ';

The number of full-table search queries performed. In some cases it is not a problem, but the ratio of total queries should be constant (that is, the select_scan/total query number quotient should be constant). If you find that the value continues to grow, it means you need to optimize for a lack of necessary indexes or other problems.

11.slow_queries

Mysql> Show status like ' Slow_queries ';

The number of queries that exceed this value (--long-query-time), or the number of indexed queries is not used. There is a small conflict for all queries. If this value grows, it indicates that the system has a performance problem.

12.threads_created

Mysql> Show status like ' threads_created ';

The value should be low. A higher value may mean that you need to increase the number of Thread_cache, or you are experiencing a persistent increase in the connection, indicating a potential problem.

Number of client connection processes

Shell> mysqladmin Processlist

Mysql> show Processlist;

You can get the number of connected threads and the number of running threads by using other statistics, and it's a good idea to check how long the running queries have taken. If there are some long queries, the administrator can be notified. You may also want to know how many queries are in the state of "Locked" —---the value is not counted as a running query but as inactive. A user is waiting for a database response.

14.innodb Status

Mysql> show InnoDB status;

The statement generates a lot of information from which you can get what you are interested in. The first thing you need to check is "average load per second computed from the nearest XX second".

(1) Pending normal AIO reads: This is the size of the InnoDB IO request query (size). If the value is larger than 10-20, you may have some bottlenecks.

(2) reads/s, Avg bytes/read, writes/s, fsyncs/s: These values are IO statistics. For reads/writes large values means that the IO subsystem is being loaded. The appropriate value depends on the configuration of your system.

(3) Buffer pool hit rate: This ratio is very dependent on your application. Please check your hit rate when you feel a problem.

(4) inserts/s, updates/s, deletes/s, reads/s: There are some InnoDB bottom-up operations. You can use these values to check your load condition to see if the range of values is expected.

Host performance status

Shell> Uptime

16.CPU Usage Rate

Shell> Top

Shell> Vmstat

. disk IO

Shell> Vmstat

Shell> Iostat

18.swap Amount of Access ( memory )

shell> Free

19.MySQL error Log

After the server has been initialized normally, nothing is written to the error log, so any information in the log should be taken care of by the administrator.
20.InnoDB Table Space Information

InnoDB The only danger is that the table space fills up----The log does not fill. The best way to check is: Show table status; You can use any InnoDB table to monitor the remaining space of the InnoDB table.

21.QPS per second Query Volume

QPS = Questions (or Queries)/seconds

MySQL > Show/* global * * status like ' Question ';

22.TPS ( transaction volume per second )

TPS = (com_commit + com_rollback)/seconds

MySQL > Show status like ' Com_commit ';

MySQL > Show status like ' Com_rollback ';

23.key Buffer hit rate

Key_buffer_read_hits = (1-key_reads/key_read_requests) * 100%

Key_buffer_write_hits = (1-key_writes/key_write_requests) * 100%

Mysql> Show status like ' key% ';

24.InnoDB Buffer hit rate

Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%

Mysql> Show status like ' innodb_buffer_pool_read% ';

25.Query Cache hit rate

Query_cache_hits = (Qcahce_hits/(qcache_hits + qcache_inserts)) * 100%;

Mysql> Show status like ' qcache% ';

26.Table Cache State Amount

Mysql> Show status like ' open% ';

27.Thread Cache hit rate

Thread_cache_hits = (1-threads_created/connections) * 100%

Mysql> Show status like ' thread% ';

Mysql> Show status like ' Connections ';

Lock Status

Mysql> Show status like '%lock% ';

Copy delay amount

MySQL > Show slave status

30.Tmp Table condition ( temporary table condition )

MySQL > Show status like ' create_tmp% ';

31.Binlog Cache Usage Status

MySQL > Show status like ' binlog_cache% ';

32.innodb_log_waits Volume

MySQL > Show status like ' Innodb_log_waits ';

Mysql Performance Query command