Mysql

MySQL沒有釋放記憶體

  • September 23, 2020

MySQL 似乎希望在對其執行任何大型插入或選擇語句後將整個表保留在記憶體中（表大小 = ~20GB）。現在我的 innodb 緩衝池是 20GB。總記憶體為 32GB。我將提供一些記憶體使用情況和 innodb 狀態的輸出以及 mysqltuner 的輸出。在過去的幾天裡，它一直讓我發瘋。請幫忙！感謝您提供任何回饋，如果您需要更多資訊，請告訴我。

此外，執行“FLUSH TABLES”只會在記憶體中關閉並重新打開它們。至少我認為這就是正在發生的事情。這是我執行一堆插入之前的 innodb 目前記憶體狀態：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21978152960; in additional pool allocated 0
Dictionary memory allocated 6006471
Buffer pool size   1310719
Free buffers       347984
Database pages     936740
Old database pages 345808
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 78031, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 551887, created 384853, written 4733512
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 936740, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

mysqld 記憶體使用百分比：60.9%

mysqld 插入後的記憶體使用百分比（1 百萬條記錄）：63.3%

然後在更多插入（300 萬條記錄）之後：70.2%

它不應該限制在62.5% 左右嗎？（20/32GB）總記憶體？

從頂部排序我的 MEM 使用的輸出：

top - 14:30:56 up 23:25,  3 users,  load average: 3.63, 2.31, 1.91
Tasks: 208 total,   4 running, 204 sleeping,   0 stopped,   0 zombie
Cpu(s): 96.0%us,  3.0%sy,  0.0%ni,  0.0%id,  1.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  28821396k total, 28609868k used,   211528k free,   138696k buffers
Swap: 33554428k total,    30256k used, 33524172k free,  1208184k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
1228 mysql     20   0 25.1g  19g 5512 S   31 70.2  62:01.10 mysqld

這是執行這些插入後的 innodb 記憶體輸出：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21978152960; in additional pool allocated 0
Dictionary memory allocated 6006471
Buffer pool size   1310719
Free buffers       271419
Database pages     1011886
Old database pages 373510
Modified db pages  4262
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 82521, not young 0
7.08 youngs/s, 0.00 non-youngs/s
Pages read 585218, created 426667, written 5192189
24.08 reads/s, 53.08 creates/s, 1135.07 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1011886, unzip_LRU len: 0
I/O sum[0]:cur[266], unzip sum[0]:cur[0]

根據 innodb 狀態，分配的總記憶體是相同的——但我的作業系統（虛擬 Ubuntu 伺服器 12.04）報告的記憶體使用量比這更多。記憶體使用保持不變，在這裡我將其定義為 MySQL 服務而不是“釋放”記憶體。有什麼建議？

mysqltuner.pl 的輸出：

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 226M (Tables: 287)
[--] Data in InnoDB tables: 33G (Tables: 1000)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 959

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 14m 27s (1M q [14.603 qps], 6K conn, TX: 16B, RX: 1B)
[--] Reads / Writes: 46% / 54%
[--] Total buffers: 22.2G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 22.6G (82% of installed RAM)
[OK] Slow queries: 0% (6/1M)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Key buffer size / total MyISAM indexes: 2.0G/58.7M
[OK] Key buffer hit rate: 100.0% (216M cached / 38K reads)
[OK] Query cache efficiency: 81.2% (799K cached / 984K selects)
[!!] Query cache prunes per day: 5561
[OK] Sorts requiring temporary tables: 4% (819 temp sorts / 16K sorts)
[!!] Temporary tables created on disk: 27% (6K on disk / 22K total)
[OK] Thread cache hit rate: 99% (11 created / 6K connections)
[!!] Table cache hit rate: 0% (97 open / 10K opened)
[OK] Open file limit used: 12% (129/1K)
[OK] Table locks acquired immediately: 99% (433K immediate / 433K locks)
[!!] InnoDB  buffer pool / data size: 20.0G/33.6G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
   Run OPTIMIZE TABLE to defragment tables for better performance
   MySQL started within last 24 hours - recommendations may be inaccurate
   Enable the slow query log to troubleshoot bad queries
   When making adjustments, make tmp_table_size/max_heap_table_size equal
   Reduce your SELECT DISTINCT queries without LIMIT clauses
   Increase table_cache gradually to avoid file descriptor limits
   Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
   query_cache_size (> 128M)
   tmp_table_size (> 128M)
   max_heap_table_size (> 16M)
   table_cache (> 431)
   innodb_buffer_pool_size (>= 33G)

首先看一下 InnoDB 架構（由 Percona CTP Vadim Tkachenko 提供）

InnoDB 架構

InnoDB

你的緩衝池狀態說

緩衝池大小 1310719

那就是頁面中的緩衝區大小。每頁為 16K。結果是 20G - 16K。

請注意以下幾點： 您將數據推送到 InnoDB 緩衝池中。有什麼變化？

Buffer pool size   1310719 
Free buffers       271419 (It was 347984)
Database pages     1011886 (Is was 936740)
Old database pages 373510 (It was 345808)
Modified db pages  4262 (It was 0)

另外，請注意頁面中緩衝池大小之間的差異。

1310719（緩衝池大小）- 1011886（數據庫頁）= 298833

那是 298833 個 InnoDB 頁面。那是多少空間？？？

mysql> select FORMAT(((1310719  - 1011886) * 16384) / power(1024,3),3) SpaceUsed;
+-----------+
| SpaceUsed |
+-----------+
| 4.560     |
+-----------+

那是4.56GB。該空間用於InnoDB 緩衝池（又名更改緩衝區）的插入緩衝區部分。這用於減輕對系統表空間文件的非唯一索引的更改（所有這些都被稱為ibdata1）。

InnoDB 儲存引擎正在管理緩衝池的內部結構。因此，InnoDB 永遠不會超過 RAM 的 62.5%。更重要的是，緩衝池的 RAM 永遠不會歸還。

70.2% 的 RAM 來自哪裡？？？

回頭看看mysqltuner.pl這些行的輸出

[OK] Maximum possible memory usage: 22.6G (82% of installed RAM)
Key buffer size / total MyISAM indexes: 2.0G/58.7M
[--] Total buffers: 22.2G global + 2.7M per thread (151 max threads)

mysqld有三種分配RAM的主要方式

數據庫連接中的任何小峰值都會使 RAM 超過您在 InnoDB 中看到的 62.5% 門檻值。

MyISAM（旁注）

吸引我眼球的是

Key buffer size / total MyISAM indexes: 2.0G/58.7M

由於您的 MyISAM 索引很少。您可以將key_buffer_size設置為 64M。

您不需要為此重新啟動 mysql。趕緊跑

SET GLOBAL key_buffer_size = 1024 * 1024 * 64;

然後，在my.cnf

[mysqld]
key_Buffer_size = 64M

這將為作業系統提供 2GB 的 RAM。你的虛擬機會因為它而愛你！！！

試一試 ！！！

警告

在 InnoDB 表上執行FLUSH TABLES只是針對文件關閉.ibd文件。這不會真正直接推動更改。更改必須通過 InnoDB 的管道遷移。這就是為什麼您會看到Modified db pages. 當 InnoDB 計劃刷新時，4262 個更改的頁面（66.59 MB）被刷新。

引用自：https://dba.stackexchange.com/questions/62021

相關問答

Mysql

Azure Database for MySQL - 記憶體不斷增加然後崩潰

  • March 8, 2022
檢視問答
Mysql

Mysql 8.0.21 long living Prepared statements 有記憶體洩漏？還是我們在做一些奇怪的事情

  • March 3, 2022
檢視問答
Mysql

Mysql : Aborted connection …/… (讀取通訊包時出錯)

  • January 13, 2022
檢視問答
Mysql

具有大量數據庫和大量表的 MySQL 實例的最佳配置是什麼？

  • October 9, 2020
檢視問答
Mysql

即使在增加緩衝池大小之後，鎖的總數也超過了鎖表的大小

  • March 3, 2020
檢視問答
Mysql

MySQL導入innodb數據庫時記憶體不足

  • April 3, 2019
檢視問答