Mysql Performance Monitoring _ MySQL

Mysql Performance Monitoring related bitsCN.com

Mysql Performance Monitoring

1. obtain the total number of processes under the mysql User

II. host performance status

III. CPU usage

IV. disk I/O volume

5. swap inbound/outbound [memory]

VI. database performance status

VII. querylog

8. run the extended-status command of mysqladmin to view the status values of mysql.

9. open-source monitoring software

1. obtain the total number of processes under the mysql User

Ps-ef | awk '{print $1}' | grep "mysql" | grep-v "grep" | WC-1

II. host performance status

# Uptime

[Root @ ~] # Uptime

13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00

III. CPU usage

# Top

Or

# Vmstat

IV. disk I/O volume

# Vmstat or # iostat

5. swap inbound/outbound [memory]

# Free

VI. database performance status

(1) QPS (queries 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 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 % ';

(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) number of Table Cache statuses

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 latency

Mysql> show slave status

(10) Tmp Table status (temporary Table status)

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

(11) Binlog Cache usage

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

(12) Innodb_log_waits volume

Mysql> show status like 'innodb _ log_waits ';

VII. querylog

Mysql has a function to log down and run slow SQL statements. This log is not available by default. to enable this function, modify my. cnf or add some parameters when mysql is started. If you modify it in my. cnf, add the following lines:

Reference

Long_query_time = 1

Log-slow-queries =/var/youpath/slow. log

Log-queries-not-using-indexes [this is introduced after mysql4.10]

Long_query_time indicates how long the SQL statement will be logged after execution, which is 1 second.

The log-slow-queries setting writes the log there, which can be left blank, the system will give a default file host_name-slow.log, and the log I generated is in the mysql data directory

Log-queries-not-using-indexes is the literal meaning, and no index query is used in log.

Turn on the above parameters and shut down after a period of operation, saving the trouble of affecting the production environment.

Next is the analysis, my file here is called host-slow.log.

First, mysqldumpslow-help:

Reference

-S ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default

-T NUM just show the top n queries

-G PATTERN grep: only consider into Ts that include this string

-S is the order, which indicates that the write is not detailed enough. I used it, including reading the code, mainly including

C, t, l, r, and ac, at, al, ar are sorted by the number of queries, time, lock time, and number of returned Records, respectively, flashback with a added above

-T indicates the top n, that is, the number of data records returned.

-G. you can write a regular expression matching later. it is case insensitive.

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.

Mysqldumpslow-t 10-s t-g left join host-slow.log

Return the first 10 SQL statements containing the left join according to the time.

8. run the extended-status command of mysqladmin to view the status values of mysql.

/Usr/local/mysql/bin/mysqladmin-u User -- host = IP -- password = Passwd extended-status | grep $ VARIABLE | awk '{print $4 }'

Meanings of mysql status values summarized by netizens

Aborted_clients indicates the number of dropped connections because the client fails to properly close the connection.

The number of connection times of the MySQL server that failed the Aborted_connects attempt.

The number of times that Connections attempted to connect to the MySQL server.

Created_tmp_tables: Number of implicit temporary tables created when the statement is executed.

The number of threads that Delayed_insert_threads is using to insert a delayed processor.

The number of rows written by Delayed_writes using insert delayed.

The number of rows in which Delayed_errors writes data using insert delayed (which may duplicate key values.

The number of times Flush_commands executes the FLUSH command.

The number of rows that Handler_delete requests to delete from a table.

The number of times the Handler_read_first request reads the first row of the table.

The Handler_read_key request number is based on the key-read row.

The number of times the Handler_read_next request reads a row based on a key.

The number of times the Handler_read_rnd request reads a row based on a fixed position.

The number of times Handler_update requests to update a row in the table.

The number of times a Handler_write request inserts a row into a table.

The number of Key_blocks_used blocks used for keyword cache.

The number of times Key_read_requests requests read a key value from the cache.

The number of times that Key_reads reads a key value from the disk physically.

Number of times that Key_write_requests requests write a key block to the cache.

The number of times that Key_writes physically writes a key-value block to a disk.

The maximum number of connections simultaneously used by Max_used_connections.

Not_flushed_key_blocks has been changed in the key cache but has not been cleared to the disk.

The number of rows that Not_flushed_delayed_rows is waiting to write in the insert delay queue.

The number of open tables in Open_tables.

The number of open files in Open_files.

Number of open streams in Open_streams (mainly used for log recording)

The number of open tables in Opened_tables.

The number of queries sent by Questions to the server.

Slow_queries takes more than long_query_time.

The number of connections currently opened by Threads_connected.

The number of threads whose Threads_running is not sleeping.

How many seconds does the Uptime server work.

9. open-source monitoring software

1, RRDTool

2. Nagios

3, MRTG

4, Cacti

BitsCN.com