mysql中的監控與優化過程是怎樣的

mysql中的監控與優化過程是怎樣的，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

1. 監控與優化

1.1 監控指標

1.1.1 QPS

mysql> show global status like 'Com%';
mysql> show global status like 'Queries';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Queries       | 1983766 |
+---------------+---------+
1 row in set (0.00 sec)

QPS = ( Queries 2- Queries 1 ) / 間隔時間

mysql> show global status where variable_name in ('Queries','uptime');
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Queries       | 1983768 |
| Uptime        | 1364443 |
+---------------+---------+
2 rows in set (0.00 sec)

1.1.2 TPS

mysql> show global status where variable_name in ('com_insert','com_update','com_delete','uptime');
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_delete    | 23676   |
| Com_insert    | 793072  |
| Com_update    | 259586  |
| Uptime        | 1364651 |
+---------------+---------+
4 rows in set (0.00 sec)

Transaction_sum= Com_delete+ Com_insert+ Com_update

TPS = (Transaction_sum 2 - Transaction_sum 1 )  / (time 2 - time 1)

1.1.3 并發數

mysql> show global status like 'Threads_running';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 2     |
+-----------------+-------+
1 row in set (0.01 sec)

1.1.4 連接數

# 最大連接數
mysql> show global status like 'max_used_connections%';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Max_used_connections      | 22                  |
| Max_used_connections_time | 2019-09-04 13:49:52 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)
# 當前連接數
mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 2     |
+-------------------+-------+
1 row in set (0.01 sec)

1.1.5 緩存命中率

##從緩存中讀取的次數
mysql> show global status like 'innodb_buffer_pool_read_requests';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 16217299 |
+----------------------------------+----------+
1 row in set (0.00 sec)
##從物理磁盤讀取的次數
mysql> show global status like 'innodb_buffer_pool_reads';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 2067  |
+--------------------------+-------+
1 row in set (0.00 sec)

緩存命中率 = ( innodb_buffer_pool_read_requests -  innodb_buffer_pool_reads )  /

innodb_buffer_pool_reads * 100%

1.1.6 服務可用性

1. 周期連接，執行查詢：select @ @version; 或 select user();

2. mysqladmin -uroot -pxxx -hxxxx ping

1.1.7 阻塞

##< mysql 5.7
SELECT b.trx_mysql_thread_id as '被阻塞的線程',
       b.trx_query as '被阻塞的SQL',
       c.trx_mysql_thread_id as '阻塞線程',
       c.trx_query as '阻塞SQL',
       UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) as '阻塞時間'
FROM information_schema.INNODB_LOCK_WAITS a 
JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id=b.trx_id
JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_id
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>30
##> mysql 5.7
SELECT waiting_pid AS '被阻塞的線程',
       waiting_query AS '被阻塞的SQL',
       blocking_pid AS '阻塞線程',
       blocking_query AS '阻塞SQL',
       wait_age AS '阻塞時間',
       sql_kill_blocking_query AS '建議操作'
FROM sys.innodb_lock_waits
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

1.1.8 死鎖

##pt工具
pt-deadlock-logger u=admin, p=123456, h=127.0.0.1 \
--create-dest-table \
--dest u=admin,p=123456, h=127.0.0.1.D=dba,t=deadlock
##全局參數，日志監控
mysql> set persist innodb_print_all_deadlocks=on;

11.1.9 慢查詢

1. 監控慢查詢日志

2. 通過information_shcema.processlist表實時監控

1.1.10 主從延遲

1. show slave status\G

2. pt-heartbeat

##主庫
pt-heartbeat --user=xx --password=xxx -h master --create-table --database xxx --update --daemonize --interval=1
##從庫
pt-hearbeat --user=xx --password=xx -h slave --database xxx
--monitor --daemonize --log /tmp/slave_lag.log

1.1.11 主從狀態

1. show slave status\G

   IO/SQL 兩個線程狀態（yes or no)

1.2 負載問題

1.3 優化

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。