The methods and concepts of performance tuning

SHOW STATUS;
FLUSH STATUS;

View current number of connections show STATUS like ' thread_% ';
Thread_cached: The number of threads being cached
Thread_running: The number of threads in the active state
Thread_connected: The number of threads currently connected
thread_created: Number of threads created in total

Thread Cache Hits
thread_connected = SHOW GLOBAL STATUS like thread_created;
Connections = SHOW GLOBAL STATUS like ' Connections ';
Tch= (1-(threads_created/connections)) * 100

View Active Connected Content
SHOW processlist;

If the number of tch is less than 90%, creating a connection takes time and increases the number of thread_cached

QPS (query throughput per second) MyISAM engine

Questions = SHOW GLOBAL STATUS like ' Questions ';
Uptime = SHOW GLOBAL STATUS like ' Uptime ';
Qps=questions/uptime

TPS (number of things transferred per second), that is, the number of transactions processed per second by the server, and if InnoDB is displayed, no InnoDB will be displayed.

Com_commit = SHOW GLOBAL STATUS like ' com_commit ';
Com_rollback = SHOW GLOBAL STATUS like ' com_rollback ';
Uptime = SHOW GLOBAL STATUS like ' Uptime ';
tps= (Com_commit + com_rollback)/uptime

The QPS and TPS values must be monitored in real time, and if you are close to the test peaks of the architecture, may God be with you

Read/writes Ratio
Qcache_hits = SHOW GLOBAL STATUS like ' qcache_hits ';
Com_select = SHOW GLOBAL STATUS like ' com_select ';
Com_insert = SHOW GLOBAL STATUS like ' Com_insert ';
Com_update = SHOW GLOBAL STATUS like ' com_update ';
Com_delete = SHOW GLOBAL STATUS like ' com_delete ';
Com_replace = SHOW GLOBAL STATUS like ' com_replace ';
r/w= (Com_select + qcache_hits)/(Com_insert + com_update + com_delete + com_replace) * 100

Read-write ratio, the important basis for optimizing the database, read more to optimize the reading, write more to optimize the write

Slow queries per minute
Slow_queries = SHOW GLOBAL STATUS like ' slow_queries ';
Uptime = SHOW GLOBAL STATUS like ' Uptime ';
Sqpm=slow_queries/(UPTIME/60)

Slow queries/questions Ratio
Slow_queries = SHOW GLOBAL STATUS like ' slow_queries ';
Questions = SHOW GLOBAL STATUS like ' Questions ';
S/q=slow_queries/questions

The new version on the line to focus on slow query, let the test to kick the developer's butt

Full_join per minute
Select_full_join = SHOW GLOBAL STATUS like ' select_full_join ';
Uptime = SHOW GLOBAL STATUS like ' Uptime ';
Fjpm=select_full_join/(UPTIME/60)

Do not use Index to Full_join, optimize index go on.

Innodb buffer Read Hits
Innodb_buffer_pool_reads = SHOW GLOBAL STATUS like ' innodb_buffer_pool_reads ';
innodb_buffer_pool_read_requests = SHOW GLOBAL STATUS like ' innodb_buffer_pool_read_requests ';
Ifrh= (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100

InnoDB buffer hit target 95%-99%;

Table Cache
open_tables= SHOW GLOBAL STATUS like ' open_tables ';
opened_tables= SHOW GLOBAL STATUS like ' opened_tables ';
table_cache= SHOW GLOBAL STATUS like ' Table_cache ';

Table_cache should be greater than open_tables less than opened_tables

Temp tables to Disk ratio

Created_tmp_tables = Show global status like ' Created_tmp_tables ';
Created_tmp_disk_tables = Show global status like ' Created_tmp_disk_tables ';

Tdr= (created_tmp_disk_tables/created_tmp_tables) *100

SHOW GLOBAL STATUS like ' innodb_row_lock_% ';

• Innodb_row_lock_current_waits

  The number of row locks currently being waited for. Added in MySQL 5.0.3.

• Innodb_row_lock_time

  The total time is spent in acquiring row locks, in milliseconds. Added in MySQL 5.0.3.

• Innodb_row_lock_time_avg

  The average time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.

• Innodb_row_lock_time_max

  The maximum time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.

• Innodb_row_lock_waits

  The number of times a row lock had to is waited for. Added in MySQL 5.0.3.

——————————————————————————————————————————————————

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.

13. 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.

15. Host Performance Status

Shell> Uptime

16.CPU usage Rate

Shell> Top

Shell> Vmstat

17. Disk IO

Shell> Vmstat

Shell> Iostat

18.swap Incoming and outgoing (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 Query volume 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 Ratio

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 Ratio

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

28. Lock Status

Mysql> Show status like '%lock% ';

29. Replication Delay Amount

MySQL > Show slave status

30.TMP table status (temporary table condition)

MySQL > Show status like ' create_tmp% ';

31.Binlog Cache Usage Status

MySQL > Show status like ' binlog_cache% ';

32.innodb_log_waits Quantity

MySQL > Show status like ' Innodb_log_waits ';

The methods and concepts of performance tuning