MYSQL 为什么数据库层面看到的内存远大于系统的内存?
空LINUX 系统<p># free -m</p><p> total used free sharedbuff/cache available</p><p>Mem: 7821 96 7577 8 147 7304</p><p>Swap: 8063 0 8063</p>
MYSQL 参数文件
<div>#Global Memon Set######innodb buf 2048MB, binlog_cache=16MB;logbuf=16MB
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size= 16M
key_buffer_size = 8M
max_heap_table_size =16M
###innodb buffer subordinate
innodb_sort_buffer_size = 4M#插入排序 1M
innodb_ddl_buffer_size = 4M
innodb_adaptive_hash_index = OFF #来禁用
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup= ON
#### Thread Memon Set8+8+8+8+16+16+8=72+1=73MB
join_buffer_size=8388608#8M
sort_buffer_size=8388608 #8M
read_buffer_size=8388608 #8M
read_rnd_buffer_size=8388608#8M
net_buffer_length = 16384 #16K max 1M
tmp_table_size =16777216 #16M
binlog_cache_size =16777216 #16M
bulk_insert_buffer_size=8388608#8M
thread_cache_size = 32
thread_stack = 256K
#limit
max_allowed_packet = 16M
interactive_timeout = 600
lock_wait_timeout = 300
max_execution_time = 100000
max_connections=500</div>
启动MYSQL
<div>root@localhost ~]# free -m
total used free sharedbuff/cache available
Mem: 7821 1590 5988 8 242 5762
Swap: 8063 0 8063
#ps -o rss,command -C mysqld
RSS COMMAND
1523928 /usr/sbin/mysqld
# top -p 4322
PID USER PRNI VIRT RES SHR S%CPU %MEM TIME+ COMMAND
4322 root 20 0 5522936 1.5g17748 S 0.0 19.0 0:09.15 mysqld</div>
物理内存使用了 1590对比MYSQL使用1494MB
2.1 从MYSQL层统计内存使用
<div><font color="#000000">mysql>SELECT * FROM sys.memory_global_total;</font>
<font color="#000000">+-----------------+</font>
<font color="#000000">| total_allocated |</font>
<font color="#000000">+-----------------+</font>
<font color="#000000">| 2.79 GiB |</font>
<font color="#000000">+-----------------+</font>
<font color="#000000">1 row in set (0.02 sec)</font></div>
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 | 2.15 GiB |
| memory/performance_schema | 606.30 MiB |
| memory/mysys | 40.88 MiB |
| memory/sql | 12.20 MiB |
| memory/temptable | 1.00 MiB |
| memory/mysqld_openssl | 134.76 KiB |
| memory/mysqlx | 2.67 KiB |
| memory/vio | 2.12 KiB |
| memory/myisam |696 bytes |
| memory/csv | 88 bytes |
| memory/blackhole | 88 bytes |
+---------------------------+---------------+
11 rows in set (0.00 sec)
<div><b><font color="#000000">mysql> show engine innodb status;</font></b>
<b><font color="#000000"> ----------------------
BUFFER POOL AND MEMORY
---------------------- </font></b>
<b><font color="#000000">Total large memory allocated 0 </font></b>
<b><font color="#000000">Dictionary memory allocated 489415</font></b>
<b><font color="#000000">Buffer pool size 131056 </font></b>
<b><font color="#000000">Buffer pool size, bytes 2147221504 </font></b>
<b><font color="#000000">Free buffers 99486 </font></b>
<b><font color="#000000">Database pages 31570 </font></b>
<b><font color="#000000">Old database pages 11810 </font></b>
<b><font color="#000000">Modified db pages0 </font></b>
<b><font color="#000000">Pending reads 0 </font></b></div>show engine innodb status; 显示属于真实分配的内存吗?
我没有仔细阅读你的提问，但我想说我不太相信mysql的sys视图统计内存使用情况这个功能，我认为他的统计是不准确或者有缺陷的。之前我有发现过这方面的bug，是一张sys库里的统计内存使用相关的视图，和你的案例不是完全一样的视图，官方验证确认了是bug，但没有打算修复的意思，所以我很失望，链接如下:
https://bugs.mysql.com/bug.php?id=105969 show engine innodb status; 显示属于真实分配的内存吗?
==
这个只能看到部分innodb引擎使用的内存，像redo log buffer之类的就看不到了。
即便是查看sys视图，也没办法统计到全部内存消耗情况，有些没纳入统计。
mysqld进程消耗更重要的是关注有无内存泄漏发生，有无swap产生。
页:
[1]