Monitor running MySQL

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.