Monitor running MySQL

One way to monitor running MySQL is to check the status of MySQL running.

(1) QPS (query amount per second)

QPS = Questions (or Queries)/seconds

MySQL > Show global status Like "question%";

(2) TPS (transaction volume per second)

TPS = (com_commit + com_rollback)/seconds

MySQL > Show global status Like "Com_commit";

MySQL > Show global status Like "Com_rollback";

(3) key Buffer hit ratio

Mysql>show Global status like "key%";

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

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

(4) InnoDB Buffer Hit Rate

Mysql> Show status like "innodb_buffer_pool_read%";

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

(5) Query Cache Hit Rate

Mysql> Show status like "qcache%";

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

(6) Table Cache State Amount

Mysql> Show global status Like "open%";

Compare open_tables and Opend_tables values

(7) Thread Cache Hit Rate

Mysql> Show global status Like "thread%";

Mysql> Show global status Like "Connections";

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

(8) Lock status

Mysql> Show global status Like "%lock%";

table_locks_waited/table_locks_immediate=0.3% if the ratio is larger, it indicates that the blocking caused by the table lock is more serious.

Innodb_row_lock_waits Innodb line Lock, too large may be caused by a gap lock

(9) Replication delay Amount

MySQL > Show slave status

View delay Time

() TMP Table condition (temporary table condition)

MySQL > Show status like "create_tmp%";

Created_tmp_disk_tables/created_tmp_tables ratio is best not more than 10%, if the Created_tmp_tables value is larger,

It may be that there are too many sentences or a connection sentence is not optimized

(one) Binlog Cache usage Status

MySQL > Show status like "binlog_cache%";

If the Binlog_cache_disk_use value is not 0, you may need to increase the binlog_cache_size size

(innodb_log_waits) Quantity

MySQL > Show status like "Innodb_log_waits";

Innodb_log_waits value is not equal to 0, indicating that Innodb log buffer is waiting due to lack of space

such as command:

> #show Global status;

Although you can use:

> #show Global status Like%...%;

To filter, but for a long list, each item represents what it means, or it is necessary to figure it out.

One, get the total number of processes under MySQL user

Ps-ef | awk ' {print '} ' | grep "MySQL" | Grep-v "grep" | Wc-1

Second, host performance status

# uptime

[Email protected] ~]# uptime

13:05:52 up, Min, 1 user, Load average:0.00, 0.00, 0.00

Third, CPU utilization

# Top

Or

# Vmstat

Four, disk IO volume

# Vmstat or # Iostat

Five, swap volume [memory]

# free

VI, Database performance status

(1) QPS (query amount per second)

QPS = Questions (or Queries)/seconds

MySQL > Show/*50000 Global * * status like ' Question ';

(2) TPS (transaction volume per second)

TPS = (com_commit + com_rollback)/seconds

MySQL > Show status like ' Com_commit ';

MySQL > Show status like ' Com_rollback ';

(3) 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% ';

(4) 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% ';

(5) Query Cache Hit Rate

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

Mysql> Show status like ' qcache% ';

(6) Table Cache State Amount

Mysql> Show status like ' open% ';

(7) Thread Cache Hit Rate

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

Mysql> Show status like ' thread% ';

Mysql> Show status like ' Connections ';

(8) Lock status

Mysql> Show status like '%lock% ';

(9) Replication delay Amount

MySQL > Show slave status

() TMP Table condition (temporary table condition)

MySQL > Show status like ' create_tmp% ';

(one) Binlog Cache usage Status

MySQL > Show status like ' binlog_cache% ';

(innodb_log_waits) Quantity

MySQL > Show status like ' Innodb_log_waits ';

Open source monitoring software

One, RRDTool

Two, Nagios.

Three, MRTG

Four, Cacti

Monitor running MySQL