溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
記錄一下線上業務數據庫每天需要監控的參數和變量情況，聲明下：每家公司需要監控的狀態變量均不完全相同，本文僅是針對作者自己負責的業務數據庫而言，另外，其中很多的參數均是從MySQL官網摘取的英文譯著，對其中的錯誤之處，望請指正！
total（顯示了緩沖池總共有多少個頁，即Buffer pool size，頁數x頁大小）
data（Database page，顯示了分配用來存儲數據庫頁的頁數，即，表示LRU列表中頁的數量,包含young sublist和old sublist）
dirty（Modified db pages，顯示臟數據庫頁數）
flushed（Innodb_buffer_pool_pages_flushed）
free（Free buffers，顯示了緩沖池空閑頁數）
mysql> show status like 'innodb_buffer_pool_read%';
| Innodb_buffer_pool_read_requests | 1085462370751 |
| Innodb_buffer_pool_reads | 31655 |
+---------------------------------------+---------------+
Innodb_buffer_pool_read_requests
The number of logical read requests
Innodb_buffer_pool_reads
The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk
Innodb_buffer_read_hits=
(1-innodb_buffer_pool_reads/ innodb_buffer_pool_read_requests)*100%
mysql> show status like 'innodb_buffer_pool_pages_flushed';
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_flushed | 16896625 |
+----------------------------------+----------+
The number of requests to flush pages from the InnoDB buffer pool.
mysql> show global status like 'innodb_rows_%';
+----------------------+---------------+
| Variable_name | Value |
+----------------------+---------------+
| Innodb_rows_deleted | 4377501 |
| Innodb_rows_inserted | 13328425 |
| Innodb_rows_read | 1592292250994 |
| Innodb_rows_updated | 8069672 |
+----------------------+---------------+
Innodb_rows_deleted
The number of rows deleted from InnoDB tables.
Innodb_rows_inserted
The number of rows inserted into InnoDB tables.
Innodb_rows_read
The number of rows read from InnoDB tables.
Innodb_rows_updated
The number of rows updated in InnoDB tables.
Key_blocks_used命中率：
Key_blocks_used/( Key_blocks_used+ Key_blocks_unused)x100%
mysql> show status like 'key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_unused | 25210 |
| Key_blocks_used | 7907 |
Key_blocks_unused
The number of unused blocks in the MyISAM key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size
Key_blocks_used
The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
Key_buffer_read命中率：
1-key_reads/key_read_requestsx100%
mysql> show status like 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_read_requests | 572800837 |
| Key_reads | 474655 |
Key_read_requests
The number of requests to read a key block from the MyISAM key cache
Key_reads
The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests
Key_buffer_write命中率：
1-key_writes/key_write_requestsx100%
mysql> show status like 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_write_requests | 135684306 |
| Key_writes | 3233824 |
+------------------------+-----------+
Key_write_requests
The number of requests to write a key block to the MyISAM key cache
Key_writes
The number of physical writes of a key block from the MyISAM key cache to disk
mysql> show global status like 'thread%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 96 |
| Threads_connected | 4 |
| Threads_created | 225461 |
| Threads_running | 1 |
+-------------------+--------+
Threads_cached
The number of threads in the thread cache.
Threads_connected
The number of currently open connections.
Threads_created
The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.
Threads_running
The number of threads that are not sleeping.
1 - Threads_created / connections x 100%
mysql> show global status like 'thread%';
| Threads_created | 225461 |
The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections
mysql> show global status like 'connections';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Connections | 86073503 |
+---------------+----------+
1 row in set (0.00 sec)
The number of connection attempts (successful or not) to the MySQL server
QPS計算方式：
QPS= Questions/Uptime
mysql> show global status like 'questions';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Questions | 15674599428 |
+---------------+------------+
The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.
The discussion at the beginning of this section indicates how to relate this statement-counting status variable to other such variables
mysql> show global status like 'Uptime';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 13609186 |
+---------------+----------+
The number of seconds that the server has been up
TPS計算方式：
TPS=(com_commit+com_rollback) /Uptime
mysql> show status like 'com_commit';（不會記錄隱式提交的事務）
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit | 0 |
+---------------+-------+
mysql> show status like 'com_rollback' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback | 0 |
+---------------+-------+
The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements
記錄每一次的語句，只記錄隱式提交的數據，如 autocommit=1
mysql> SHOW GLOBAL STATUS WHERE variable_name IN
-> ('Com_insert','Com_delete','Com_select','Com_update');
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_delete | 59246 |
| Com_insert | 7880676 |
| Com_select | 1334273891 |
| Com_update | 8651697 |
+---------------+------------+
The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements
mysql> show status like 'bytes%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| Bytes_received | 576 |
| Bytes_sent | 2648 |
+----------------+-------+
Bytes_received
The number of bytes received from all clients.
Bytes_sent
The number of bytes sent to all clients.
mysql> show status like 'handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
Handler_read_rnd_next
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have
Handler_read_rnd
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly
Handler_commit
The number of internal COMMIT statements.
Handler_rollback
The number of requests for a storage engine to perform a rollback operation
mysql> show status like 'select_full_join';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Select_full_join | 0 |
+------------------+-------+
The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
mysql> show status like 'select_scan';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Select_scan | 0 |
+---------------+-------+
The number of joins that did a full scan of the first table
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
The number of queries that have taken more than long_query_time seconds. This counter increments regardless of whether the slow query log is enabled.
mysql> show global status like 'open%_tables';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Open_tables | 1360 |
| Opened_tables | 530013 |
+---------------+--------+
Open_tables
The number of tables that are open
Opened_tables
The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small
mysql> show status like '%lock%';
+------------------------------------------+------------+
| Variable_name | Value |
+------------------------------------------+------------+
| Com_lock_tables | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 0 |
| Innodb_row_lock_current_waits | 18 |
| Innodb_row_lock_time | 1472088440 |
| Innodb_row_lock_time_avg | 8968 |
| Innodb_row_lock_time_max | 31991 |
| Innodb_row_lock_waits | 164133 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Table_locks_immediate | 1702805509 |
| Table_locks_waited | 373537 |
+------------------------------------------+------------+
Com_lock_tables
The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed
Com_unlock_tables
Handler_external_lock
The server increments this variable for each call to its external_lock() function, which generally occurs at the beginning and end of access to a table instance. There might be differences among storage engines. This variable can be used, for example, to discover for a statement that accesses a partitioned table how many partitions were pruned before locking occurred: Check how much the counter increased for the statement, subtract 2 (2 calls for the table itself), then divide by 2 to get the number of partitions locked.
Innodb_row_lock_current_waits
The number of row locks currently being waited for by operations on InnoDB tables.
Innodb_row_lock_time
The total time spent in acquiring row locks for InnoDB tables, in milliseconds.
Innodb_row_lock_time_avg
The average time to acquire a row lock for InnoDB tables, in milliseconds.
Innodb_row_lock_time_max
The maximum time to acquire a row lock for InnoDB tables, in milliseconds.
Innodb_row_lock_waits
The number of times operations on InnoDB tables had to wait for a row lock.
Performance_schema_locker_lost
Performance_schema_rwlock_classes_lost
Performance_schema_rwlock_instances_lost
Performance Schema status variables are listed in Section 22.16, “Performance Schema Status Variables”. These variables provide information about instrumentation that could not be loaded or created due to memory constraints.
Table_locks_immediate
The number of times that a request for a table lock could be granted immediately.
Table_locks_waited
The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
mysql> show status like 'binlog_cache%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Binlog_cache_disk_use | 37 |
| Binlog_cache_use | 13354593 |
+-----------------------+----------+
Binlog_cache_disk_use
The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction
Binlog_cache_use
The number of transactions that used the binary log cache.
mysql> show status like 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
+------------------------------+-------+
Normally, writes to the InnoDB buffer pool happen in the background. When InnoDB needs to read or create a page and no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits. If innodb_buffer_pool_size has been set properly, this value should be small.
mysql> show status like '%Innodb_log_waits%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_log_waits | 0 |
+------------------+-------+
The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing
查看文件資源使用情況：
最大打開文件數 open_files_limit
The number of files that the operating system permits mysqld to open. The value of this variable at runtime is the real value permitted by the system and might be different from the value you specify at server startup. The value is 0 on systems where MySQL cannot change the number of open files.
已打開文件數 Open_files
The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.
查看表資源使用情況：
表緩存數 table_open_cache
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See Section 5.1.9, “Server Status Variables”. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
已打開表 Open_tables
The number of tables that are open.
表空間監控
慢查詢監控
查詢時間（Query_time_pct_95）
鎖等待時間（Lock_time_pct_95）
掃描行數（Rows_examined_pct_95）
出現次數
AWR報告
檢查錯誤日志(每個數據庫實例不一定相同，根據系統變量確定)
mysql> select @@global.log_error;
+----------------------------+
| @@global.log_error |
+----------------------------+
| /home/data/mysql/error.log |
+----------------------------+
檢查本地備份完整性
檢查異地容災備份完整性(可使用ftp、expect等方式)
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
