56,677
社区成员
发帖
与我相关
我的任务
分享关于mysql内存的问题
最近数据库所在服务器内存变得不稳定
红色部分的突降突升是因为我重启了第一个实例
我这台服务器共有两个实例，innodb_buffer_pool_size都为2G
使用top查看内存第二个实例占用了4G内存，mysql版本5.7.20，数据挂载在存储上，下面贴出一些状态值
[root@bj-216 mysqld_exporter]# top -d 1
top - 15:51:23 up 485 days, 22:42, 5 users, load average: 0.41, 0.31, 0.29
Tasks: 643 total, 1 running, 638 sleeping, 0 stopped, 4 zombie
%Cpu(s): 0.2 us, 0.3 sy, 0.0 ni, 99.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 7763948 total, 224852 free, 6363688 used, 1175408 buff/cache
KiB Swap: 7995388 total, 6940664 free, 1054724 used. 1010084 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21650 mysql 20 0 6031188 4.146g 7568 S 0.0 56.0 11979:13 mysqld
45430 mysql 20 0 3981540 1.225g 12236 S 0.0 16.5 2:42.79 mysqld
[root@bj-216 mysqld_exporter]# free -mh
total used free shared buff/cache available
Mem: 7.4G 6.1G 217M 21M 1.1G 984M
Swap: 7.6G 1.0G 6.6G
mysql> show variables like "%buffer%";
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 2147483648 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 134217728 |
| myisam_sort_buffer_size | 33554432 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
mysql> show status like "%buffer%";
+---------------------------------------+----------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 190801 9:15:28 |
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 190802 10:55:07. |
| Innodb_buffer_pool_pages_data | 120475 |
| Innodb_buffer_pool_bytes_data | 1973862400 |
| Innodb_buffer_pool_pages_dirty | 1111 |
| Innodb_buffer_pool_bytes_dirty | 18202624 |
| Innodb_buffer_pool_pages_flushed | 67084448 |
| Innodb_buffer_pool_pages_free | 8206 |
| Innodb_buffer_pool_pages_misc | 2383 |
| Innodb_buffer_pool_pages_total | 131064 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 13711072 |
| Innodb_buffer_pool_read_ahead_evicted | 21129 |
| Innodb_buffer_pool_read_requests | 127399031992 |
| Innodb_buffer_pool_reads | 11563025 |
| Innodb_buffer_pool_wait_free | 446928 |
| Innodb_buffer_pool_write_requests | 5245362442 |
+---------------------------------------+----------------------------------------------------+
冒昧打扰我所邀请的两位大佬，希望大佬看到了且恰好有时间帮我解答一下
红色部分的突降突升是因为我重启了第一个实例
我这台服务器共有两个实例，innodb_buffer_pool_size都为2G
使用top查看内存第二个实例占用了4G内存，mysql版本5.7.20，数据挂载在存储上，下面贴出一些状态值
[root@bj-216 mysqld_exporter]# top -d 1
top - 15:51:23 up 485 days, 22:42, 5 users, load average: 0.41, 0.31, 0.29
Tasks: 643 total, 1 running, 638 sleeping, 0 stopped, 4 zombie
%Cpu(s): 0.2 us, 0.3 sy, 0.0 ni, 99.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 7763948 total, 224852 free, 6363688 used, 1175408 buff/cache
KiB Swap: 7995388 total, 6940664 free, 1054724 used. 1010084 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21650 mysql 20 0 6031188 4.146g 7568 S 0.0 56.0 11979:13 mysqld
45430 mysql 20 0 3981540 1.225g 12236 S 0.0 16.5 2:42.79 mysqld
[root@bj-216 mysqld_exporter]# free -mh
total used free shared buff/cache available
Mem: 7.4G 6.1G 217M 21M 1.1G 984M
Swap: 7.6G 1.0G 6.6G
mysql> show variables like "%buffer%";
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 2147483648 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 134217728 |
| myisam_sort_buffer_size | 33554432 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
mysql> show status like "%buffer%";
+---------------------------------------+----------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 190801 9:15:28 |
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 190802 10:55:07. |
| Innodb_buffer_pool_pages_data | 120475 |
| Innodb_buffer_pool_bytes_data | 1973862400 |
| Innodb_buffer_pool_pages_dirty | 1111 |
| Innodb_buffer_pool_bytes_dirty | 18202624 |
| Innodb_buffer_pool_pages_flushed | 67084448 |
| Innodb_buffer_pool_pages_free | 8206 |
| Innodb_buffer_pool_pages_misc | 2383 |
| Innodb_buffer_pool_pages_total | 131064 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 13711072 |
| Innodb_buffer_pool_read_ahead_evicted | 21129 |
| Innodb_buffer_pool_read_requests | 127399031992 |
| Innodb_buffer_pool_reads | 11563025 |
| Innodb_buffer_pool_wait_free | 446928 |
| Innodb_buffer_pool_write_requests | 5245362442 |
+---------------------------------------+----------------------------------------------------+
冒昧打扰我所邀请的两位大佬，希望大佬看到了且恰好有时间帮我解答一下
...全文
请发表友善的回复…
发表回复
lhdz_bj 2019-10-01
- 打赏
- 举报
1、内存使用不止受innodb_buffer_pool_size参数影响，还有其他参数，例如：innodb_sort_buffer_size，join_buffer_size ，key_buffer_size，read_buffer_size，read_rnd_buffer_size，sort_buffer_size ；
2、很明显，如上参数中，key_buffer_size，read_buffer_size，read_rnd_buffer_size，sort_buffer_size 都进行过调整，其中，后三个参数都是针对每个连接的，尤其是sort_buffer_size，针对每个会话的排序都会分配，如果并发高，这个还是比较客观的。
