关于mysql的监控

[size=large] (一): 监控的目标
1.快速的得到Mysql过去一段时间或者当前运行的状态
2.因硬件升级或者系统配置的改变而诊断对Mysql数据库性能影响
3.在Mysql数据库系统出现故障要能够及时收到告警
4.为日后编写运维报告提供各项数据指标供分析


(二): 监控原理
自从Mysql数据库系统启动并且提供服务，Mysql内部自身的一套“计数器”就开始工作，可以通过Show Global Status ， Show global variables ，Show full processlist得到Mysql数据库系统当前各种系统变量和状态。然后获取这些数据进行计算和统计分析。


从Mysql数据库系统启动并且提供服务，Mysql内部自身的一套“计数器”就开始工作，可以通过
Show Global Status ， Show global variables ，Show full processlist得到Mysql数据库系统当前各种系统变量和状态 ，并且后文介绍个各种监控工具几乎都是对这三个指令的输出进行数据的计算和统计分析。

(三)： 监控指标的确定
1、尝试连接MySql服务器已经失败的次数：Aborted_connects
2、mySql已经发出的网络流量：Bytes_send
3、mySql已经接受到的网络流量：Bytes_received
4、mySql当前打开连接数量：Threads_connected
5、发往mysql服务器的查询数量： Questions



(四)： MySql状态值含义（通过
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。

（五） 利用脚本去调用mySql查询其系统变量并以字符串的格式返回值

（六） 将获取到的值进行分析统计然后以图标的形式展现出来

（七） mysqlReport监控并输出MySql状态值

注意： 我开始打算采取调命令行的方式去获取监控数据， ^_^ ^_^ 但是这种做法不可取， 因为不断地查询会不断地开启进程... ...

————————————mysql监控变量——————————————————
下边是MySQL Cacti Plugin 可以监控的mysql的参数

Innodb Buffer Pool Activity

* Pages Created
* Pages Written
* Pages Read

Innodb Buffer Pool Pages

* Pool Size
* Database Pages
* Free Pages
* Modified Pages

Inoodb File I/O

* File Reads
* Files Writes
* Log Writes
* File Fsyncs

Innodb Pending I/O

* Aio Log Ios
* Aio Sync ios
* Buffer Pool Flushes
* Chkp Writes
* Ibuf Aio Reads
* Log Flushes
* Log Writes
* Normal Aio Reads
* Normal Aio Writes

Innodb Insert Buffer

* Inserts
* Merged
* Merges

Innodb Log

* Log Buffer Size
* Log Bytes Written
* Log Bytes Flushed
* Unflushed Log

Innodb Row Operations

* Rows Read
* Rows Deleted
* Rows Updated
* Rows Inserted

Innodb Semaphores

* Spin Rounds
* Spin Waits
* OS Waits

Innodb Transactions

* Innodb Transactions
* Current Transactions
* History List
* Read Views

MySQL Binary/Relay Logs

* Binlog Cache use
* Binlog Cache Disk Use
* Binary Log Space
* Relay Log Space

MySQL Command Counters

* Questions
* SELECT
* DELETE
* INSERT
* UPDATE
* REPLACE
* LOAD
* DELETE MULTI
* INSERT SELECT
* UPDATE MULTI
* REPLACE SELECT

MySQL Connections

* Max Connections
* Max Used Connections
* Aborted Clients
* Aborted Connects
* Threads Connected
* Connections

MySQL Files and Tables

* Table Cache
* Open Tables
* Open Files
* Opened Tables

MySQL Network Traffic

* Bytes Received
* Bytes Sent

MySQL Processlist

* State Closing Tables
* State Copying to Tmp Table
* State End
* State Freeing Items
* State Init
* State Locked
* State Login
* State Preparing
* State Reading From Net
* State Sending Data
* State Sorting Result
* State Statistics
* State Updating
* State Writing to Net
* State None
* State Other

MySQL Query Cache

* Queries In Cache
* Hits
* Inserts
* Not Cached
* Lowmem Prunes

MySQL Query Cache Memory

* Query Cache Size
* Free Memory
* Total Blocks
* Free Blocks

MySQL Replication

* Slave Running
* Slave Stopped
* Slave Lag
* Slave Open Temp Tables
* Slave Retried Transactions

MySQL Select Types

* Select Full Join
* Select Full Range Join
* Select Range
* Select Range Check
* Select Scan

MySQL Sorts

* Sort Rows
* Sort Range
* Sort Merge Passes
* Sort Scan

MySQL Table Locks

* Table Locks Immediate
* Table Locks Waited
* Slow Queries

MySQL Temporary Objects

* Created Tmp Tables
* Created Tmp Disk Tables
* Created Tmp Files

MySQL Threads

* Thread Cache Size
* Threads Created


————————————————需要监控的参数说明（二）——————————————
mysql中的统计数据的几种类型如下:

Incrementing ----递增的
Absolute ---绝对值的
Formula --公式推导的
General ---普通的

Questions
Incrementing — The number of queries and protocol commands that have been run.

Uptime
Absolute — The number of seconds the server has been online.

Uptime_since_flush_status
Absolute — The number of seconds since either the server was started or FLUSH STATUS has been run.

===连接相关===
Aborted_clients ----暴力的被关掉的连接(已经成功连接,但是被暴力关闭的)
Incrementing — A count of the number of clients that did not call mysql_close() or equivalent in their API, and thus the MySQL server forcibly closed their connection.

Aborted_connects----尝试连接但是没有成功的次数
Incrementing — A count of the number of clients that did not successfully connect due to handshake or network errors in the connect process.

Connections ---目前为止,一共的连接数,累加
Incrementing — The number of clients currently connected.

Max_used_connections
Absolute — The maximum number of clients that have been connected since the server started. If this is equal to max_connections configuration variable, you have reached maxed out the connections to the server.

===网络相关===

Bytes_*
Incrementing — The number of bytes sent to (Bytes_sent) clients (primarily result sets) and received from (Bytes_received) clients (primarily query text).

===DML语句===

Com_*
Incrementing — The number of commands of various types that have been run:

Com_select — SELECT
Com_insert — INSERT
Com_insert_select — INSERT ... SELECT ...
Com_update — UPDATE
Com_delete — DELETE

===事务相关===

Com_*
Incrementing — The number of various transactional commands that have been run:

Com_begin — BEGIN or START TRANSACTION
Com_commit — COMMIT
Com_rollback — ROLLBACK
These commands do not count implicit transactions that have been started for single statements using a transactional storage engine. They do count executed but useless commands, such as when using MyISAM tables.

===Prepared Statements===

Com_*
Incrementing — The number of various prepared statement commands, or their equivalent API commands, that have been run:

Com_stmt_prepare — PREPARE
Com_stmt_execute — EXECUTE
Com_stmt_close — CLOSE

Prepared_stmt_count
Absolute — The number of prepared statements that are currently allocated across all connections.


===Temporary tables===

Created_tmp_tables
Incrementing — The number of temporary tables that have been created.

Created_tmp_disk_tables
Incrementing — The number of temporary tables that have been converted from in-memory (HEAP) to on-disk (MyISAM) temporary tables. This can occur for one of a few reasons:


===Table cache usage===

Opened_tables
Incrementing — The number of tables that have been opened because there wasn't an available cached open table in the table cache. This normally implies that either:
The FLUSH TABLES command is being run often.
The size of table_cache is too small, and tables must be closed in order to open others.

Open_tables
Absolute — The number of tables currently open. If this is equal to the table_cache variable, it would imply that the table_cache setting is too low.

===Threads===

Threads_created
Incrementing — The number of threads that have been created. This should be stable as long as the thread_cache variable is large enough.

Threads_cached
Absolute — The number of threads currently in the cache and available for use.

Threads_connected --当前处于连接状态的线程
Absolute — The number of threads currently handling a connection.

Threads_running --当前正在工作的线程
Absolute — The number of threads currently in a non-idle (Sleep) state.


===InnoDB buffer pool===

Innodb_buffer_pool_pages_*
Absolute — The number of pages in various states in the buffer pool:

Innodb_buffer_pool_pages_data — Data pages; containing data or indexes from tables.
Innodb_buffer_pool_pages_dirty — Pages marked as "dirty"; having changes that have yet to be written to disk.
Innodb_buffer_pool_pages_free — Free pages; available for data to be stored.
Innodb_buffer_pool_pages_total — All pages; the total number of pages which can be allocated for InnoDB's buffer pool. This
number will be equal to innodb_buffer_pool_size / Innodb_page_size.

Innodb_buffer_pool_*
Incrementing —

Innodb_buffer_pool_read_requests — The number of requests for a page from the buffer pool. This counter is incremented whether the page exists in the buffer pool, or requires a disk read to fetch the page into the buffer pool.
Innodb_buffer_pool_reads — The number of pages that had to actually be read from disk into the buffer pool. -----在缓存池没有找到,必须从硬盘读取到缓存的数据
Innodb_buffer_pool_write_requests — The number of pages that have been requested to be written from the buffer pool to disk.

Innodb_buffer_pool_read_ahead_*
Incrementing —

Innodb_buffer_pool_read_ahead_rnd
Innodb_buffer_pool_read_ahead_seq

Buffer pool miss rate ---失效率
100 * (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

The buffer pool miss rate is the percentage of the time that a page was requested but was not present in the buffer pool and had to be read from the disk. Increasing the size of innodb_buffer_pool_size may decrease the number of buffer pool misses. The inverse of this (100 - miss rate) is the buffer pool hit rate.

Percent of dirty pages --脏页比列
100 * (Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_data)

The percent of dirty pages is a ratio of many pages are in the cache in a modified state (dirty) and shoudl be written to disk before shutdown.

Percent buffer pool used for data ---缓冲池利用率
100 * (Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total)

The percent of the buffer pool used for data is a ratio of how many pages are currently allocated for data and index pages in the buffer pool.

Percent buffer pool free --空闲率
100 * (Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total)

The percent of the buffer pool free is the ratio of how many pages are not currently allocated in the buffer pool. These pages may or may not have been allocated from the operating system by MySQL/InnoDB. That is they may really be "free" as in entirely unallocated, or they may be "free in the buffer pool", allocated but currently unused.


InnoDB_data_*
Incrementing —

Innodb_data_reads — The number of times data has been read.
Innodb_data_read — The amount of data read (in bytes) because of Innodb_data_reads.
Innodb_data_writes — The number of times data has been written.
Innodb_data_written — The amount of data written (in bytes) because of Innodb_data_writes.

InnoDB_pages_*
Incrementing —

Innodb_pages_created — The number of pages that were newly created because of new data being inserted or updated and pages being appended or split.
Innodb_pages_read — The number of pages that have been read because they were not present in the buffer pool.
Innodb_pages_written — The number of pages that have been written or rewritten.

InnoDB_rows_*
Incrementing —

Innodb_rows_deleted — The number of rows that have been deleted.
Innodb_rows_inserted — The number of rows that have been inserted.
Innodb_rows_read — The number of rows that have been read.
Innodb_rows_updated — The number of rows that have been updated.

===MyISAM===

Key buffer size
Key_blocks_used —
Key_blocks_unused —
Key_blocks_not_flushed —

Key buffer activity

Key_*
Key_reads
Key_writes

Key_*_requests
Key_read_requests
Key_write_requests

Key buffer misses----myisam索引缓存失效率
100 * (Key_reads / Key_read_requests)

——————————————————mysql监控命令——————————————————
SHOW DATABASES
SHOW STATUS LIKE ''%{0}%''
SHOW VARIABLES LIKE ''%{0}%''
SHOW TABLE STATUS FROM {0} LIKE ''{1}''[/size]