监控告警某mysql实例内存使用率85%,而且在一点点的往上长
实例分配内存130G
登陆数据库show engine innodb status\G
buffer pool使用约98G,占总内存的73%作业,还有10几%未知
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 105545465856 --98G
Dictionary memory allocated 756731
Buffer pool size 6290688
Free buffers 1128
Database pages 6287620
Old database pages 2320995
Modified db pages 48
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 105066270, not young 178748226
0.06 youngs/s, 0.00 non-youngs/s
Pages read 12290542, created 429773422, written 1146860087
0.00 reads/s, 0.33 creates/s, 15.05 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: 6287620, unzip_LRU len: 0
I/O sum[1143]:cur[8], unzip sum[0]:cur[0]
查看buffer的配置
mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 4194304 |
| 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 | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 103079215104 | --96
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 442368 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 262144 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 868352 |
| read_rnd_buffer_size | 442368 |
| sort_buffer_size | 868352 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
mysql内存分为2大块:共享内存和私有内存。共享内存是全局使用(比如oracle的SGA就是共享内存),私有内存为每个会话分配
这个网页可以大概计算一下mysql最大内存占用,但是不是很准确,因为少了几个内存池
http://www.mysqlcalculator.com
1.这种计算是计算的最大值,不是mysql实际占用内存,connections调整到足够大的话,内存理论的最大值就会很大
2.mysql内存使用=共享内存+(私有内存*连接数)
3.mysql在启动后内存会逐渐增大,比如buffer pool最大会增大到它的上限,但私有内存如果不断开是不会释放的
查看mysql进程的线程使用情况,res已经使用到了约120G
#top -p 75160
top - 15:52:42 up 289 days, 22:18, 1 user, load average: 0.32, 0.23, 0.22
Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.2 us, 0.1 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 52782332+total, 25626828+free, 15125904+used, 12029598+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 33605420+avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
75160 mysql 20 0 27.791t 0.117t 11316 S 0.7 23.7 36606:47 mysqld
ps aux查看rss,rss为实际占用内存,125178552为实际内存,约119G
mysql 75160 24.9 23.7 30052095684 125178552 ? Sl 2020 36614:41 /u01/mysql57_20190815/bin/mysqld --defaults-file=/etc/my3005.cnf --basedir=/u01/mysql57_20190815 --datadir=/home/mysql/data3005/dbs3005 --plugin-dir=/u01/mysql57_20190815/lib/plugin --user=mysql --log-error=/home/mysql/data3005/mysql/master-error.log --open-files-limit=65535 --pid-file=/home/mysql/data3005/dbs3005/07fe10702.cloud.e18.qm499.pid --socket=/home/mysql/data3005/tmp/mysql.sock --port=3005
rss 使用125178552,119G。
mysql进程使用119G,buffer pool用96G,其他pool较小,占用不会超5G,还有20G的内存不知道是谁占用了
网上搜到可能跟并行slave同步有关,是个bug,但调整后也没效果
mysql> show variables like '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.00 sec)
mysql> show variables like '%slave-para%';
Empty set (0.00 sec)
mysql> show variables like '%worker%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 8 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
mysql> stop slave;
mysql> set global slave_parallel_workers=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
最后performance也没有打开,没有记录mem的使用情况,没有信息可以继续分析了
最后还是主从切换后,重启以前的主库,内存释放了