Directory
One, get the total number of processes under MySQL user
Second, host performance status
Third, CPU utilization
Four, disk IO volume
Five, swap volume [memory]
VI, Database performance status
Seven, QueryLog
Viii. extended-status instructions for mysqladmin view MySQL status values
Nine, open source monitoring software
One, get the total number of processes under MySQL user ps -ef | awk ' {print $1} ' | grep ' MySQL ' | grep -v "grep" | wc-1 two, host performance status # uptime[[email protected] ~]# 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 four, disk IO volume # vmstat or # iostat Five, swap ingress and egress [memory]# free Six, database performance status (1) QPS (query 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) / secondsmysql > 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) 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 status (temporary table condition) MySQL > show status like ' create_tmp% ';(one) Binlog Cache usage status mysql > show status like ' binlog_cache% ';() Innodb_log_waits volume mysql > show status like ' innodb_log_waits '; VII, QUERYLOGMYSQL has a function that can log down to run a slower SQL statement , the default is no this log, in order to enable this function, to modify the my.cnf or when MySQL started to add some parameters. If you modify it in my.cnf, you need to add the following lines of reference Long_query_time = 1 log-slow-queries = /var/youpath/slow.log log-queries-not-using-indexes[this was introduced after mysql4.10]long_query_time is the execution of more than how long the SQL will be log down, here is 1 seconds. log-slow-queries settings to write the log there, can be empty, the system will give a default file Host_ Name-slow.log, I generated the log in the MySQL data directory log-queries-not-using-indexes is literally, log down does not use the indexed query. Open the above parameters, run for a period of time, you can switch off, save the production environment. Next is the analysis, my file name is called Host-slow.log. First mysqldumpslow –help The following, my main use is quote-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 stmts that include this string -s, is the order of orders, stating that the writing is not detailed enough, I use it down, Including read the code, mainly have c,t,l,r and Ac,at,al,ar, respectively, according to the number of query, time, lock time and return records to sort, the front plus a when the flashback -t, is the meaning of top n, That is, to return the first number of data -g, behind can write a regular matching pattern, case insensitive mysqldumpslow -s c -t 20 host-slow.logmysqldumpslow -s r -t 20 host-slow.log The above command shows the 20 most visited SQL statements and 20 SQL that returns the maximum number of recordsets. mysqldumpslow -t 10 -s t -g "Left join" Host-slow.log This is the SQL statement containing the left link in the first 10 of the time. Eight, Mysqladmin's extended-status instruction view MySQL each status value/usr/local/mysql/bin/mysqladmin -u user --host=ip --password=Passwd extended-status|grep $VARIABLE |awk ' {print $4} ' The MySQL status value meaning that the user summarizes aborted_clients because the guestThe connection is dead and the number of connections that have been discarded is not properly closed. aborted_connects the number of times the connection to the MySQL server has failed. connections the number of attempts to connect to the MySQL server. created_tmp_tables the number of suppressed temporary tables that have been created when the statement is executed. delayed_insert_threads the number of deferred plug-in processor threads being used. delayed_writes the number of rows written with insert delayed. delayed_errors the number of rows written with insert delayed for some errors (possibly repeating key values). flush_commands the number of times the flush command was executed. handler_delete the number of times a row was requested to be deleted from a table. The number of times the handler_read_first request reads the first row in the table. The handler_read_key requests a number based on the key read line. The number of times a handler_read_next request reads into a row based on one key. The number of times a handler_read_rnd request reads into a row based on a fixed position. handler_update the number of times a row in the table was requested to be updated. The number of times the handler_write request inserts a row into the table. key_blocks_used the number of blocks used for the keyword cache. key_read_requests the number of times a key value was requested to be read from the cache. key_reads the number of times a key value has been physically read from disk. key_write_requests requests a keyword block to be written to the cache count. key_writes the number of times a key-value block is physically written to disk. max_used_connections the maximum number of connections that are used at the same time. not_flushed_key_blocks a key block that has been changed in the key cache but has not been emptied to disk. not_flushed_delayed_rows the number of rows waiting to be written in the Insert delay queue. open_tables the number of open tables. open_files the number of open files. open_streams the number of open streams (primarily for log records) OPENED_TABLes the number of tables that have been opened. questions the number of queries destined for the server. slow_queries the number of queries that will take more than long_query_time time. threads_connected the number of connections currently open. threads_running the number of threads that are not sleeping. uptime How many seconds the server has worked. Nine, open source monitoring software 1,rrdtool 2,nagios 3,mrtg 4,cacti 1. Number of processes for the system MySQL Ps -ef | grep "MySQL" | grep -v "grep" | wc –l2. slave_runningmysql > show status like ' slave_running '; If the system has a slave This value indicates the health level from the server 3. threads_connectedmysql > show status like ' threads_connected '; the number of currently connected clients. 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_runningmysql > 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, to monitor some other values at the same time. 5.aborted_clientsmysql > 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 it is not affected, but for other applications you may want to track this value because an abnormally broken connection might be in the tableUnderstand some of the applications are problematic. 6.questionsmysql> show status like ' Questions '; the number of queries per second can also be the number of all 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_tablesmysql> show status like ' opened_tables '; 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_joinmysql> 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 the system by 10. select_scanmysql> show status like ' Select_scan '; executes the number of full-table search queries. 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_queriesmysql> show status like ' slow_queries '; the number of queries over this value (--long-query-time), Or not using the index query number. There is a small conflict for all queries. If this value grows, it indicates that the system has a performance problem. 12.threads_createdmysql> show status like&nbSP; ' 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 processlistmysql> 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 engine innodb status\g; The statement generates a lot of information from which you can get your interest. The first thing you need to check is "average load per second computed from the nearest XX second". (1) pending normal aio reads: The value is the size of the Innodb io request query. 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 hit rate is very dependent on your application. When you feel a problem, please check your hit rate (4) INSERTS/S, UPDATES/S, DELETES/S, READS/S: There are some innodb of the underlying operation. You can use these values to check your load condition to see if the range of values is expected. 15. Host performance status Shell> uptime16. CPU usage shell> topshell> vmstat17. Disk Ioshell> vmstatshell> iostat18.swap (memory) Shell> free19.MySQL error Logs nothing will be written to the error log after the server has been initialized properly, so any information in the log should be taken care of by the administrator. 20.InnoDB tablespace 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 on the InnoDB table. 21.QPS Query volume per second qps = questions (or queries) / secondsmysql > show /* global */ status like ' Question '; 22.TPS (transaction volume per second) tps = (com_commit + com_rollback) / secondsmysql > 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 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 ratio 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. Copy the delay amount mysql > show slave status30. 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 volume mysql > show status like ' innodb_log_waits '; http://www.open-open.com/lib/view/open1328776066546.html
MySQL Performance monitoring related