Mysql performance monitoring indexes and Analysis

1. Number of mysql PROCESSES IN THE SYSTEM

Ps-ef | grep "mysql" | grep-v "grep" | wc-l

2. Slave_running

Mysql> show status like 'slave _ running ';

If the system has a slave server, this value indicates the slave server health.

3. Threads_connected

Mysql> show status like 'threads _ connected ';

Number of connections to the current client. This value will be less than the preset value, but you can also monitor this value, which ensures that the client is active.

4. Threads_running

Mysql> show status like 'threads _ running ';

If the database is overloaded, you will get a number that is (the query statement continues) increasing. This value can also be less than the preset value. It is okay if the value exceeds the limit within a short period of time. When the value of Threads_running exceeds the preset value and does not fall back within 5 seconds, you need to monitor 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 Connected to the mysql server is not normally disconnected or closed. There is no impact on some applications, but for some other applications, you may want to track this value, because an exception to interrupt the connection may indicate that some applications have problems.

6. Questions

Mysql> show status like 'questions ';

The number of queries obtained per second can also be the number of all queries. You can enter different commands to obtain different values you want.

7. Handler _*

Mysql> show status like 'handler _ % ';

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

If the value of Handler_read_rnd_next differs greatly from the normal value you think, it may tell you that the optimization or index has a problem. Handler_rollback indicates the number of queries that the transaction is rolled back. You may want to investigate the cause.

8. Opened_tables

Mysql> show status like 'opened _ tables ';

The number of table cache hits. If the value is large, you may need to increase the value of table_cache. Typically, you may want this value to open less than 1 or 2 tables per second.

9. Select_full_join

Mysql> show status like 'select _ full_join ';

No Join operation is performed. This value may be zero. This is a good way to capture development errors, because some such queries may reduce system performance.

10. Select_scan

Mysql> show status like 'select _ scan ';

The number of queries executed for the entire table. In some cases, there is no problem, but the ratio to the total number of queries should be a constant (that is, Select_scan/The total number of queries should be a constant ). If you find that this value continues to grow, it indicates that optimization is required and there is no necessary index or other problems.

11. Slow_queries

Mysql> show status like 'slow _ queries ';

The number of queries with this value (-- long-query-time) exceeded or the number of queries with no index is used. There will be small conflicts for all queries. If this value increases, the system has performance problems.

12. Threads_created

Mysql> show status like 'threads _ created ';

This value should be low. A high value may mean that you need to increase the value of thread_cache, or you have encountered a continuously increasing connection, indicating a potential problem.

13. Number of client connection Processes

Shell> mysqladmin processlist

Mysql> show processlist;

You can use other statistical information to get the number of connected threads and the number of running threads. It is a good idea to check how long the running query takes. The administrator can be notified of some long-time queries. You may also want to know how many queries are in the "Locked" status-this value is not counted as a running query, but as inactive. A user is waiting for a database response.

14. innodb status

Mysql> show engine innodb status \ G;

This statement generates a lot of information, from which you can get what you are interested in. First, you need to check the "average load per second calculated from the last XX seconds ".

(1) Pending normal aio reads: the size of the innodb io request query ). If the value is greater 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 subsystems, the io subsystem is being loaded. The proper value depends on your system configuration.

(3) Buffer pool hit rate: the hit rate is very dependent on your application. Check your hit rate when you think there is a problem.

(4) inserts/s, updates/s, deletes/s, reads/s: some underlying Innodb operations. You can use these values to check your load status to see if it is the expected value range.

15. Host performance status

Shell> uptime

16. CPU usage

Shell> top

Shell> vmstat

17. Disk IO

Shell> vmstat

Shell> iostat

18. swap inbound/outbound (memory)

Shell> free

19. MySQL Error Log

After the server completes initialization normally, nothing will be written to the error log. Therefore, any information in this log should be noticed by the Administrator.
20. InnoDB tablespace Information

The only danger of InnoDB is that the tablespace is filled-logs are not filled. 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 queries per second

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. Number of Table Cache statuses

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 ';

28. Lock status

Mysql> show status like '% lock % ';

29. Replication latency

Mysql> show slave status

30. Tmp Table Status (temporary Table status)

Mysql> show status like 'create _ tmp % ';

31. Binlog Cache Usage

Mysql> show status like 'binlog _ cache % ';

32. Innodb_log_waits volume

Mysql> show status like 'innodb _ log_waits ';