+-
MySQL似乎希望在对其执行任何大型插入或select语句之后将整个表保留在缓存中(表大小= ~20GB).现在我的innodb缓冲池是20GB.总RAM为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％
然后经过更多插入(3密耳记录)：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 Architecture(由Percona CTP Vadim Tkachenko提供)
InnoDB的
您的缓冲池状态如下所示
缓冲池大小1310719
这是你的页面缓冲区大小. Each page is 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.该空间用于Insert Buffer Section of the InnoDB Buffer Pool (a.k.a. Change Buffer).这用于缓解对系统表空间文件(所有人都知道为ibdata1)的非唯一索引的更改.
InnoDB存储引擎正在管理缓冲池的内部.因此,InnoDB永远不会超过62.5％的RAM.更重要的是,缓冲池的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分配方式
>您设置InnoDB缓冲池的20G
> MyISAM密钥缓存有2G
>剩余的0.6G来自151(max_connections)次(每个DB连接或线程2.7M). 2.7M来自(join_buffer_size sort_buffer_size read_buffer_size)
对于InnoDB,DB Connections中的任何小峰值都会使RAM超过62.5％的阈值.
MyISAM(旁注)
引起我注意的是
Key buffer size / total MyISAM indexes: 2.0G/58.7M
由于MyISAM的索引很少.您可以将key_buffer_size设置为64M.
您不需要为此重新启动mysql.赶紧跑
SET GLOBAL ket_buffer_size = 1024 * 1024 * 64;
然后,在my.cnf中修改它
[mysqld]
key_Buffer_size = 64M
这将为OS提供2GB的RAM.你的VM只会爱你！
试试看 ！！！
警告
在InnoDB表上运行FLUSH TABLES只是根据.ibd文件关闭文件.这不会直接推动变革.这些变化必须通过InnoDB的管道进行迁移.这就是您在修改的数据库页面中看到峰值的原因.当InnoDB无需刷新时,4262个已更改的页面(66.59 MB)将被刷新.
点击查看更多相关文章
转载注明原文：MySQL没有释放内存 - 乐贴网
