学生兼职网站开发/济南疫情最新消息
mysql内存监控大体归为以下3步
1.os角度查看mysql进程内存使用
2.mysql服务角度查看mysql内存池使用
3.开启performance_schema及内存监控
1.从OS进程使用情况出发，查看mysql进程的内存使用情况
因为mysql是单进程的，可以通过ps -aux或者top -H -p查看进程的内存使用情况
#ps -aux|grep 3306
root 40761 0.0 0.0 9696 1632 ? S Mar22 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my3306.cnf
mysql 47657 45.4 23.6 36487720284 125051340 ? Sl Mar22 24943:51 /u01/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --basedir=/u01/mysql --datadir=/home/mysql/data3306/dbs3306 --plugin-dir=/u01/mysql/lib/plugin --user=mysql --log-error=/home/mysql/data3306/mysql/master-error.log --open-files-limit=65535 --pid-file=/home/mysql/data3306/dbs3306/lzl.pid --socket=/home/mysql/data3306/tmp/mysql.sock --port=3306RSS物理内存使用为125051340k=119G
#top -H -p 47657
top - 14:07:25 up 347 days, 20:33, 2 users, load average: 0.38, 0.25, 0.23
Threads: 112 total, 0 running, 112 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.1 us, 0.6 sy, 0.0 ni, 98.2 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 52782332+total, 25450158+free, 15152912+used, 12179262+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 33546016+avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 47989 mysql 20 0 33.982t 0.116t 11340 S 0.3 23.7 139:41.84 mysqld 47657 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 2:03.18 mysqld 47658 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:00.00 mysqld 47963 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:00.00 mysqld 47964 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:52.90 mysqld 47965 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:09.30 mysqld 47966 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:09.17 mysqld 47967 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:08.97 mysqld 47968 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:09.12 mysqld 47969 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:07.04 mysqld 47970 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:08.41 mysqld 47971 mysql 20 0 33.982t 0.116t 11340 S 0.0 23.7 0:07.69 mysqld RES为0.116t约119G
2.从mysql server的角度出发查看内存池配置和内存使用
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 | --buffer pool 96G
| 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> show variables like '%tmp_table%';
+----------------+---------+
| Variable_name | Value |
+----------------+---------+
| max_tmp_tables | 32 |
| tmp_table_size | 2097152 | --tmp总大小不会过64m
+----------------+---------+
2 rows in set (0.01 sec)mysql> show variables like '%heap_table%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 67108864 | --内存中的mem表不会超过64m
+---------------------+----------+mysql内存分为共享内存和私有内存
共享内存：buffer pool、log buffer、max_heap_table_size（mem表）、tmp table size 、query cache（一般没有）
私有内存：binlog cache size、sort buffer size、read buffer size、read rnd buffer size、join buffer size、thread stack
从mysql server的角度看，mysql使用内存=共享内存+（私有内存*当前连接数）
mysql在启动后内存会逐渐增大，比如buffer pool最大会增大到它的上限，私有内存同理，不是每个连接连进来就分配完私有内存。但私有内存如果不断开是不会释放的
实际上，私有内存很难计算，它的理论最大值为：最大私有内存*max connections，这个值是非常大的
即时用当前连接数去计算，这个值也不小。实际上还是很难计算每个线程使用了多少私有内存
从mysql server的角度出发，除了看配置外，可以查看show engine innodb status\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 105545465856 --98G
Dictionary memory allocated 984430
Buffer pool size 6290688
Free buffers 1229
Database pages 6283987
Old database pages 2319656
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 23098255, not young 126063559
0.08 youngs/s, 0.03 non-youngs/s
Pages read 4571517, created 161404225, written 506625151
0.14 reads/s, 0.11 creates/s, 4.72 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 2 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6283987, unzip_LRU len: 0
I/O sum[243]:cur[0], unzip sum[0]:cur[0]Total large memory allocated的官方解释为The total memory allocated for the buffer pool in bytes.
它不是mysql总内存使用，而是buffer pool的实际使用
这个案例就无法从os和数据库层面分析出为什么mysql进程占用119G而，buffer pool仅占用98G
3.performance_schema和mem级别监控
3.1开启performance_schema需要设置只读参数performance_schema：
[mysqld]
performance_schema=ON3.2开启memory监控（默认仅开启了一小部分内存相关监控）（不需要重启）
update performance_schema.setup_instruments set enabled='YES' where name like 'memory/%';开启了以后，需要让mysql运行（采集memory数据）一段时间
然后就可以看到是哪些事件占用了内存
mysql> select * from sys.memory_global_by_current_bytes where current_count>0 limit 5;
+--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+
| memory/sql/sp_head::main_mem_root | 684 | 132.40 GiB | 198.21 MiB | 707 | 132.40 GiB | 191.77 MiB |
| memory/performance_schema/events_statements_history_long | 1 | 13.66 MiB | 13.66 MiB | 1 | 13.66 MiB | 13.66 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 9.84 MiB | 9.84 MiB | 1 | 9.84 MiB | 9.84 MiB |
| memory/performance_schema/events_statements_history_long.sqltext | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |这里的sp_head::main_mem_root就是有问题的，sp就是store procedure存储过程
select substring_index(substring_index(event_name,'/',2),'/',-1) as event_type,round(sum(current_number_of_bytes_used)/1024/1024,2) as mb_current_used from performance_schema.memory_summary_global_by_event_name group by event_type having mb_current_used>0;
+--------------------+-----------------+
| event_type | mb_current_used |
+--------------------+-----------------+
| innodb | 0.02 |
| memory | 0.22 |
| mysys | 0.06 |
| performance_schema | 147.79 |
| pps | 0.01 |
| sql | 142128.70 |
+--------------------+-----------------+mysql> select * from memory_summary_by_thread_by_event_name where COUNT_ALLOC<>0 order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;
-----------+-----------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| THREAD_ID | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+-----------+-----------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| 4714282 | memory/sql/sp_head::main_mem_root | 285 | 0 | 64331465280 | 0 | 0 | 285 | 285 | 0 | 64331465280 | 64331465280 |
| 4604342 | memory/sql/sp_head::main_mem_root | 238 | 0 | 45586109280 | 0 | 0 | 238 | 238 | 0 | 45586109280 | 45586109280 |
| 4713052 | memory/sql/sp_head::main_mem_root | 86 | 0 | 16148509440 | 0 | 0 | 86 | 86 | 0 | 16148509440 | 16148509440 |
| 4603976 | memory/sql/sp_head::main_mem_root | 43 | 0 | 14151373920 | 0 | 0 | 43 | 43 | 0 | 14151373920 | 14151373920 |
| 109 | memory/memory/HP_PTRS | 12090 | 12090 | 2542472192 | 2542472192 | 0 | 0 | 9 | 0 | 0 | 1892864 |
| 4714282 | memory/memory/HP_PTRS | 597 | 597 | 166925976 | 166925976 | 0 | 0 | 2 | 0 | 0 | 599160 |
| 4714282 | memory/sql/JSON | 3693488 | 3693488 | 148292992 | 148292992 | 0 | 0 | 478 | 0 | 0 | 18880 |
| 4604342 | memory/sql/JSON | 2960336 | 2960336 | 118767744 | 118767744 | 0 | 0 | 478 | 0 | 0 | 18880 |
| 4604342 | memory/memory/HP_PTRS | 393 | 393 | 109885944 | 109885944 | 0 | 0 | 2 | 0 | 0 | 599160 |
| 35 | memory/innodb/mem0mem | 119832 | 119832 | 80694364 | 80694364 | 0 | 0 | 8 | 0 | 0 | 5387 |
+-----------+-----------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
10 rows in set (0.01 sec)找到thread id后就可以找到会话信息
mysql> select * from threads where thread_id in (4714282,4604342,4713052,4603976)-> ;
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| 4603976 | thread/sql/one_connection | FOREGROUND | 4603936 | dbadmin | 10.21.1.93 | lzl_db | Sleep | 5 | NULL | NULL | NULL | NULL | YES | YES | TCP/IP | 38030 |
| 4713052 | thread/sql/one_connection | FOREGROUND | 4713012 | dbadmin | 10.21.1.93 | lzl_db | Sleep | 1 | NULL | NULL | NULL | NULL | YES | YES | TCP/IP | 72433 |
| 4604342 | thread/sql/one_connection | FOREGROUND | 4604302 | dbadmin | 10.20.2.194 | lzl_db | Sleep | 1 | NULL | NULL | NULL | NULL | YES | YES | TCP/IP | 38035 |
| 4714282 | thread/sql/one_connection | FOREGROUND | 4714242 | dbadmin | 10.20.2.194 | lzl_db | Sleep | 77 | NULL | NULL | NULL | NULL | YES | YES | TCP/IP | 38034 |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
mysql> select * from information_schema.processlist where id in (4603936,4713012,4604302,4714242);
+---------+---------+-------------------+------------+---------+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+---------+---------+-------------------+------------+---------+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 4713012 | dbadmin | x:x | lzl_db | Query | 0 | init | INSERT INTO table111
SELECT id,xxx, REPLACE(JSON_EXTRACT(var_points,'$[1].binId'),'\"','') ,REPLACE(JSON_EXTRACT(var_points,'$[1].variable'),'\"','') xxx,REPLACE(JSON_EXTRACT(var_points,'$[1].value'),'\"','') ,REPLACE(JSON_EXTRACT(var_points,'$[1].point'),'\"','') FROM xxx
WHERE id = new.id |
| 4604302 | dbadmin | x:x | lzl_db | Sleep | 0 | | NULL |
| 4714242 | dbadmin | x:x | lzl_db | Sleep | 85 | | NULL |
| 4603936 | dbadmin | x:x | lzl_db | Sleep | 56 | | NULL |
+---------+---------+-------------------+------------+---------+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)