Inside 君最近把金庸先生的笑傲江湖重看了三遍,感慨良多。很多工作、管理、生活、學習上的問題都能在其中一窺究竟,而那是年輕時所不能體會的一種感悟。比如下面風清揚的這段話:
風清揚又道:“單以武學而論,這些魔教長老們也不能說真正已窺上乘武學之門。他們不懂得,招數是死的,發招之人卻是活的。死招數破得再妙,遇上了活招數,免不了縛手縛腳,只有任人屠戮。這個‘活’字,你要牢牢記住了。學招時要活學,使招時要活使。倘若拘泥不化,便練熟了幾千萬手絕招,遇上了真正高手,終究還是給人家破得干干淨淨。”
今天,來談談 MySQL 的 OOM ( out of memory )問題診斷。之前,這類問題的定位對於普通用戶來說並不怎麼簡單。但是在 MySQL 5.7 中, OOM 問題的定位變得極其容易。還沒掌握的小伙伴趕快來看下吧。通常來說, 發生 OOM 時可在系統日志找到類似的日志提示:
MySQL 5.7 的庫 performance_schema 新增了以下這幾張表,用於從各維度查看內存的消耗:
memory_summary_by_account_by_event_name
memory_summary_by_host_by_event_name
memory_summary_by_thread_by_event_name
memory_summary_by_user_by_event_name
memory_summary_global_by_event_name
簡單來說,就是可以根據用戶、主機、線程、賬號、全局的維度對內存進行監控。同時庫 sys 也就這些表做了進一步的格式化,可以使得用戶非常容易的觀察到每個對象的內存開銷:
mysql> select event_name,current_alloc
-> from memory_global_by_current_bytes limit 10;
+------------------------------------------------------------------------------+---------------+
| event_name | current_alloc |
+------------------------------------------------------------------------------+---------------+
| memory/performance_schema/events_statements_history_long | 13.66 MiB |
| memory/performance_schema/events_statements_history_long.sqltext | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.tokens | 9.77 MiB |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB |
| memory/performance_schema/table_handles | 9.00 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.80 MiB |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 5.62 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 4.88 MiB |
| memory/performance_schema/events_statements_summary_by_user_by_event_name | 4.40 MiB |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | 4.40 MiB |
+------------------------------------------------------------------------------+---------------+
10 rows in set (0.00 sec)
細心的同學可能會發現,默認情況下 performance_schema 只對 performance_schema 進行了內存開銷的統計。但是在對 OOM 進行診斷時,需要對所有可能的對象進行內存監控。因此,還需要做下面的設置:
mysql> update performance_schema.setup_instruments
-> set enabled = 'yes' where name like 'memory%';
Query OK, 310 rows affected (0.00 sec)
Rows matched: 380 Changed: 310 Warnings: 0
mysql> select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 5;
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index | YES | NO |
| memory/innodb/buf_buf_pool | YES | NO |
| memory/innodb/dict_stats_bg_recalc_pool_t | YES | NO |
| memory/innodb/dict_stats_index_map_t | YES | NO |
| memory/innodb/dict_stats_n_diff_on_level | YES | NO |
+-------------------------------------------+---------+-------+
5 rows in set (0.00 sec)
但是這種在線打開內存統計的方法僅對之後新增的內存對象有效:
mysql> select event_name,current_alloc from memory_global_by_current_bytes
-> where event_name like '%innodb%';
+------------------------+---------------+
| event_name | current_alloc |
+------------------------+---------------+
| memory/innodb/mem0mem | 36.52 KiB |
| memory/innodb/trx0undo | 704 bytes |
| memory/innodb/btr0pcur | 271 bytes |
+------------------------+---------------+
3 rows in set (0.01 sec)
如想要對全局生命周期中的對象進行內存統計,必須在配置文件中進行設置,然後重啟:
[mysqld]
performance-schema-instrument='memory/%=COUNTED'
mysql> select event_name,current_alloc from memory_global_by_current_bytes limit 5;
+----------------------------+---------------+
| event_name | current_alloc |
+----------------------------+---------------+
| memory/innodb/os0file | 1.42 GiB |
| memory/innodb/buf_buf_pool | 1.05 GiB |
| memory/innodb/os0event | 51.15 MiB |
| memory/innodb/hash0hash | 41.44 MiB |
| memory/innodb/log0log | 32.01 MiB |
+----------------------------+---------------+
5 rows in set (0.00 sec)
通過上面的結果,有小伙伴是不是已經發現 可疑的內存使用 了呢?memory/innodb/os0file這個對象使用了1.42G內存,而整個數據庫實例的Buffer Pool只有1.05G。那麼這時就可以去bugs.mysql.com上去搜索下。果不其然,是一個官方bug,並已在5.7.14修復。而通過類似方法Inside君已經定位了5起OOM問題。當然,這裡Inside君只是拋出了一個思路,活學活用,才能達到無招勝有招的至臻境界。