MySQL监控
F6打开console，
Mysql> show status ---显示状态信息（扩展show status like 'XXX'）
View Code
mysql> show status; +-----------------------------------+------------+ | Variable_name | Value | +-----------------------------------+------------+ | Aborted_clients | 98 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 294 | | Bytes_sent | 14907 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 1 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_create_user | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 0 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 0 | | Com_set_option | 1 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 0 | | Com_show_databases | 0 | | Com_show_errors | 0 | | Com_show_fields | 0 | | Com_show_grants | 0 | | Com_show_innodb_status | 2 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_ndb_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 1 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 2 | | Com_show_storage_engines | 0 | | Com_show_tables | 0 | | Com_show_triggers | 0 | | Com_show_variables | 3 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 0 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 350504 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 13 | | Created_tmp_tables | 5 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 273 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 400 | | Innodb_buffer_pool_pages_data | 15534 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 2013 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 3794 | | Innodb_buffer_pool_pages_total | 19328 | | Innodb_buffer_pool_read_ahead_rnd | 5376 | | Innodb_buffer_pool_read_ahead_seq | 460646 | | Innodb_buffer_pool_read_requests | 2518283966 | | Innodb_buffer_pool_reads | 134387 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 81045 | | Innodb_data_fsyncs | 6615 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2213449728 | | Innodb_data_reads | 29757322 | | Innodb_data_writes | 6990 | | Innodb_data_written | 73122816 | | Innodb_dblwr_pages_written | 2013 | | Innodb_dblwr_writes | 1664 | | Innodb_log_waits | 1 | | Innodb_log_write_requests | 11946 | | Innodb_log_writes | 1665 | | Innodb_os_log_fsyncs | 3310 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 6318592 | | Innodb_page_size | 16384 | | Innodb_pages_created | 114 | | Innodb_pages_read | 29757317 | | Innodb_pages_written | 2013 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 2 | | Innodb_rows_inserted | 76 | | Innodb_rows_read | 794994909 | | Innodb_rows_updated | 21000 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 140094 | | Key_blocks_used | 2 | | Key_read_requests | 62 | | Key_reads | 8 | | Key_write_requests | 0 | | Key_writes | 0 | | Last_query_cost | 10.499000 | | Max_used_connections | 54 | | Not_flushed_delayed_rows | 0 | | Open_files | 0 | | Open_streams | 0 | | Open_tables | 0 | | Opened_tables | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 5 | | Qcache_free_memory | 89396160 | | Qcache_hits | 45541824 | | Qcache_inserts | 2067 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1088940 | | Qcache_queries_in_cache | 2027 | | Qcache_total_blocks | 4204 | | Questions | 49595103 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 5 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 95275 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 1 | | Threads_connected | 23 | | Threads_created | 77167 | | Threads_running | 1 | | Uptime | 694308 | | Uptime_since_flush_status | 694308 | +-----------------------------------+------------+ 248 rows in set mysql>
Mysql>show processlist---显示正在运行中的线程数 （root权限，不然显示的当前用户的）
Mysql> show variables ---显示系统变量（扩展show variables like 'XXX'）
Mysql> show innodb status ---显示InnoDB存储引擎的状态
Shell> mysqladmin variables -u username -p password---显示系统变量
Shell> mysqladmin extended-status -u username -p password---显示状态信息
常在sitescope里面添加的的计数器值有：
* Slave_running:如果系统有一个从复制服务器，这个值指明了从服务器的健康度
* Aborted_clients:客户端被异常中断的数值（因为连接到mysql服务器的客户端没有正常地断开或关闭）。对于一些应用程序是没有影响的，但对于另一些应用程序可能你要跟踪该值，因为异常中断连接可能表明了一些应用程序有问题。
* Questions:每秒钟获得的查询数量。也可以是全部查询的数量（注：可以根据你输入不同的命令会得到你想要的不同的值）。
* Handler_*:如果你想监视底层（low-level）数据库负载，这些值是值得去跟踪的。如果Handler_read_rnd_next值相对 于你认为是正常值相差悬殊，可能会告诉你需要优化或索引出问题了。Handler_rollback表明事务被回滚的查询数量。你可能想调查一下原因。
* Opened_tables:表缓存没有命中的数量。如果该值很大，你可能需要增加静态变量缓存数table_cache的数值。典型地，你可能想要这个值每秒打开的表数量少于1或2.
* Select_full_join: 没有主键（key）联合（Join）的执行。该值可能是零。这是捕获开发错误的好方法，因为一些这样的查询可能降低系统的性能。
* Select_scan:执行全表搜索查询的数量。在某些情况下是没问题的，但占总查询数量该比值应该是常量（注：Select_scan除以总查询数量商应该是常数）。如果你发现该值持续增长，说明需要优化，缺乏必要的索引或其他问题。
* Slow_queries:超过该值（--long-query-time）的查询数量，或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长，表明系统有性能问题。
Threads_cached：线程缓存内的线程数
Threads_running:如果数据库超负荷了，你将会得到一个正在（查询的语句持续）增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时， 要同时监视其他的一些值。Threads_connected: 当前客户端已连接的线程数量。
Threads_created: 创建过的线程数。调优静态变量线程缓存数thread_cache：如果该值增加很快，当前thread_cache_size的值可能太小。缓存访问率是Threads_created/Connections。服务器应缓存多少线程以便重新使用。当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程被放入缓存，一般配置8。
3）mysqladmin processlist or "SHOW FULL PROCESSLIST"命令
你 可以通过使用其他的统计信息得到已连接线程数量和正在运行线程的数量，检查正在运行的查询花了多长时间是一个好主意。如果有一些长时间的查询（由于很差的 构思或公式），管理员可以被通知。你可能也想了解多少个查询是在"Locked"的状态—---该值作为正在运行的查询不被计算在内而是作为非活跃的。一 个用户正在等待一个数据库响应。
4) "SHOW INNODB STATUS"命令
View Code
mysql> show innodb status; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Status | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================== 130321 14:05:01 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 33 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8828115, signal count 8643799 Mutex spin waits 0, rounds 15785784, OS waits 138148 RW-shared spins 17614779, OS waits 8621423; RW-excl spins 3449, OS waits 294 ------------ TRANSACTIONS ------------ Trx id counter 0 18013120 Purge done for trx's n:o < 0 18013117 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 20540 MySQL thread id 343180, query id 49592463 jyc-zhangrenying.super.com 192.168.1.95 root show innodb status ---TRANSACTION 0 0, not started, OS thread id 25120 MySQL thread id 349302, query id 49588293 jyc-zhangrenying.super.com 192.168.1.95 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 29757322 OS file reads, 6990 OS file writes, 6615 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 1253513, used cells 1196566, node heap has 3794 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 521213682 Log flushed up to 1 521213682 Last checkpoint at 1 521213682 0 pending log writes, 0 pending chkp writes 3315 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 349044422; in additional pool allocated 1984384 Buffer pool size 19328 Free buffers 0 Database pages 15534 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 29757317, created 114, written 2013 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 24116, state: waiting for server activity Number of rows inserted 76, updated 21000, deleted 2, read 794994909 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
该语句产生很多信息，从中你可以得到你感兴趣的。首先你要检查的就是“从最近的XX秒计算出来的每秒的平均负载”。
* Pending normal aio reads: 该值是innodb io请求查询的大小（size）。如果该值大到超过了10—20，你可能有一些瓶颈。
* reads/s, avg bytes/read, writes/s, fsyncs/s:这些值是io统计。对于reads/writes大值意味着io子系统正在被装载。适当的值取决于你系统的配置。
* Buffer pool hit rate:这个命中率非常依赖于你的应用程序。当你觉得有问题时请检查你的命中率
* inserts/s, updates/s, deletes/s, reads/s:有一些Innodb的底层操作。你可以用这些值检查你的负载情况查看是否是期待的数值范围。
4）OS数据。查看系统状态好的工具是vmstat/iostat/mpstat，这些可以看man手册
5）MySQL错误日志-----在服务器正常完成初始化后，什么都不会写到错误日志中，因此任何在该日志中的信息都要引起管理员的注意。
mysql> show variables like 'log_%';---查看是否启用了日志
6）InnoDB表空间信息。InnoDB仅有的危险情况就是表空间填满----日志不会填满。
检查的最好方式就是：show table status;你可以用任何InnoDB表来监视InnoDB表的剩余空间。
