在mariadb 10.1版本中,在information_schema.processlist表中,新增了几个字段,其中有一个memory_used,其记录的是连接的内存消耗。
同时新增了一个状态变量memory_used,其记录的应该是所有连接加起来消耗的内存(官方并没有特别详细地解释https://mariadb.com/kb/en/mariadb/show-processlist/),应该类似于oracle pga的概念。
为了再检查下mysql占用内存远超过buffer pool的原因,我们特地让运维将某个线上实例从percona切换到了mariadb 10.1.21进行观察。如下:
[code][root@iZbp13xgu1d7hpg1gca4ndZ ~]# mysql -uroot -pmysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1046786
Server version: 10.1.20-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show status like ‘memory_used’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| Memory_used | 67464 |
±--------------±------+
row in set (0.00 sec)
MariaDB [(none)]> show global status like ‘memory_used’;
±--------------±----------+
| Variable_name | Value |
±--------------±----------+
| Memory_used | 568761128 |
±--------------±----------+
row in set (0.00 sec)
MariaDB [(none)]> select user,host,memory_used from information_schema.processlist;
±----------------±---------------------±------------+
| user | host | memory_used |
±----------------±---------------------±------------+
| root | localhost | 84576 |
| osm | 10.253.106.167:47847 | 95376 |
| osm | 10.253.106.167:47843 | 94616 |
| osm | 10.253.106.167:47845 | 94224 |
| osm | 10.253.106.167:47841 | 93856 |
| osm | 10.253.106.167:47835 | 94224 |
| osm | 10.253.106.167:47836 | 94616 |
| osm | 10.253.106.167:47831 | 67464 |
| osm | 10.253.106.167:47832 | 67464 |
| osm | 10.253.106.167:47815 | 71312 |
| osm | 10.253.106.167:47791 | 67464 |
| osm | 10.253.106.167:47792 | 67464 |
| osm | 10.253.106.167:47780 | 67464 |
| event_scheduler | localhost | 39784 |
±----------------±---------------------±------------+
rows in set (0.00 sec)[/code]
在其内存输出中,没有办法得出global memory_used是如何组成的?processlist汇总起来也不是这个值,这确实比较奇怪,官方和各种forum也没找到各种解释。。。
该服务器buffer pool配置的是4GB,外有一些临时表在用,当前的进程内存消耗如下: