詳解分析MySQL8.0的內存消耗

  在MySQL8.0在啟動的時候,會配置各種各樣的buffer和cache來提高數據庫的性能。如果我們在一臺服務器上配置瞭MySQL8.0的服務,那麼這臺服務器的內存會同時被操作系統、MySQL8.0服務、以及其他應用程序所共享。

   生產環境中,經常會遇到內存的報警,在處理這些報警之前,你需要知道MySQL本身消耗內存最多的點在哪裡,這樣才能比較直觀的判斷出來你的MySQL服務占用的內存有多少,以及如何降低MySQL本身的內存消耗。

   在MySQL配置文件中,最常用的兩個內存相關的參數是innodb_buffer_pool_size、innodb_log_buffer_size,我們來看這兩個參數。

1、innodb_buffer_pool_size

這個參數定義瞭buffer pool的大小,大傢可能都比較熟悉,buffer pool中的內容包含innodb 表、索引、以及其他的輔助buffer,buffer pool的大小對MySQL系統性能影響比較大,默認情況下,MySQL8.0配置的buffer pool大小是128MB,通常情況下,如果是單機單實例,沒有其他業務,那麼MySQL官方建議配置的大小為系統內存的50%到75%之間。當然,如果你的服務器上還部署瞭其他的應用程序,那麼你需要酌情減小這個比例,從而騰出內存。

如果你的操作系統的內存很充裕,你可以設置多個innodb buffer pool實例,可以使用下面的參數來調整這個實例的個數:

mysql> show variables like '%innodb_buffer_pool_instances%';
+------------------------------+-------+
| Variable_name    | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1  |
+------------------------------+-------+
1 row in set (0.00 sec)

2、innodb_log_buffer_size

這個參數定義瞭innodb存儲引擎向磁盤上寫redo log之前,最多在內存中緩存數據的大小,默認是16MB。這個值增加之後,大的事務可以不用在事務提交之前將redo log落盤。如果你的update、delete和insert操作影響行數比較多,那麼你需要考慮增大這個值。

重點來瞭:

在操作系統裡面,MySQL占用的內存不僅僅是上述兩個內存配置參數有關,通常情況下,我們計算MySQL占用的內存的時候,會使用下面4個值相加的方式:

1、innodb_buffer_pool_size

2、key_buffer_size  (這個參數通常是myisam表占用內存的關鍵參數)

3、max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) (這三個是連接級別的buffer)

4、max_connections*2MB

所以當你使用top命令看到你的MySQL占用的內存遠遠超過innodb_buffer_pool_size的時候,你需要考慮的另外一個關鍵因素是連接數是否超標瞭,一旦連接數過高,那麼上述3、4這兩部分消耗的內存將會非常多。

當然,上面列舉的,是MySQL最主要占用內存的幾個因素,除此之外,其他的內存消耗的地方,可以查看官方文檔:

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

上述文檔中,還有介紹我們如何使用performance_schema來監控MySQL的內存使用,這裡我提一下整個流程,詳細的細節以及參數介紹請參看官方文檔。

1、查看

performance_schema.setup_instruments

這張表,找到你關註的內存變量的名稱(直接搜索,結果有490多條,分為好幾個大類,一定記得過濾自己關註的參數)。舉個例子,我們搜索memory/innodb相關參數,代表innodb存儲引擎占用的內存,結果如下:

mysql> SELECT * FROM performance_schema.setup_instruments  WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| NAME          | ENABLED | TIMED | PROPERTIES  | VOLATILITY | DOCUMENTATION |
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| memory/innodb/adaptive hash index   | YES  | NULL |     |   0 | NULL   |
| memory/innodb/log and page archiver  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/buf_buf_pool    | YES  | NULL | global_statistics |   0 | NULL   |
| memory/innodb/buf_stat_per_index_t  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/clone      | YES  | NULL |     |   0 | NULL   |
| memory/innodb/dict_stats_bg_recalc_pool_t | YES  | NULL |     |   0 | NULL   |
| memory/innodb/dict_stats_index_map_t  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/dict_stats_n_diff_on_level | YES  | NULL |     |   0 | NULL   |
| memory/innodb/other      | YES  | NULL |     |   0 | NULL   |
| memory/innodb/partitioning    | YES  | NULL |     |   0 | NULL   |
| memory/innodb/row_log_buf     | YES  | NULL |     |   0 | NULL   |
| memory/innodb/row_merge_sort    | YES  | NULL |     |   0 | NULL   |
| memory/innodb/std       | YES  | NULL |     |   0 | NULL   |
| memory/innodb/trx_sys_t::rw_trx_ids  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/undo::Tablespaces   | YES  | NULL |     |   0 | NULL   |
| memory/innodb/ut_lock_free_hash_t   | YES  | NULL |     |   0 | NULL   |
| memory/innodb/api0api      | YES  | NULL |     |   0 | NULL   |
| memory/innodb/api0misc     | YES  | NULL |     |   0 | NULL   |
| memory/innodb/btr0btr      | YES  | NULL |     |   0 | NULL   |

2、在配置文件中寫上相關的參數,開啟統計,以memory/innodb/row_log_buf為例,配置文件修改的如下:

performance-schema-instrument='memory/innodb/row_log_buf=COUNTED'

3、啟動實例,並在performance_schema數據庫的memory_summary_global_by_event_name表中查看內存統計結果。

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/row_log_buf'\G

當然,你還可以根據sys表中的結果,查看每個大類的聚合結果,如下:

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
  code_area, FORMAT_BYTES(SUM(current_alloc))
  AS current_alloc
  FROM sys.x$memory_global_by_current_bytes
  GROUP BY SUBSTRING_INDEX(event_name,'/',2)
  ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area     | current_alloc |
+---------------------------+---------------+
| memory/innodb    | 843.24 MiB |
| memory/performance_schema | 81.29 MiB  |
| memory/mysys    | 8.20 MiB  |
| memory/sql    | 2.47 MiB  |
| memory/memory    | 174.01 KiB |
| memory/myisam    | 46.53 KiB  |
| memory/blackhole   | 512 bytes  |
| memory/federated   | 512 bytes  |
| memory/csv    | 512 bytes  |
| memory/vio    | 496 bytes  |
+---------------------------+---------------+

更詳細的信息,請參見官方文檔。

以上就是詳解分析MySQL8.0的內存消耗的詳細內容,更多關於MySQL8.0 內存消耗的資料請關註WalkonNet其它相關文章!