MySQL Performance monitoring related

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