Zabbix的Mysql数据库内存占用高问题分析与处理

问题：1、Zabbix内存使用率高；2、MariaDB没有开启独享表空间

• 监控发现zabbix内存占用高，且每隔一周左右的时间内存就会用满。

• 登录服务器通过top命令分析内存使用情况

• 通过ps aux|grep mysql查询Mysql服务状态和Mysql安装目录为/var/lib/mysql

ps aux|grep mysql

[root@instance-1p55pphp ~]# ps aux|grep mysql
mysql      936  0.0  0.0 113416  1608 ?        Ss   11:14   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     1183  2.5  3.4 1637644 279076 ?      Sl   11:14   0:59 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      5820  0.0  0.0 112816   972 pts/0    S+   11:54   0:00 grep --color=auto mysql

• 查看Mysql的配置文件

cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

• 查看Mysql的数据库文件，发现ibdata1文件很大，有6.8G

cd /var/lib/mysql
[root@instance-1p55pphp mysql]# du -sh *
16K	aria_log.00000001
4.0K	aria_log_control
6.8G	ibdata1
5.0M	ib_logfile0
5.0M	ib_logfile1
1004K	mysql
0	mysql.sock
212K	performance_schema
2.3M	zabbix

• ibdata1 是什么？

ibdata1是InnoDB的共有表空间，默认情况下会把表空间存放在一个文件ibdata1中，会造成这个文件越来越大.

• ibdata1文件增大的原因有哪些？

原因1：使用InnoDB共享表空间存储数据

参数innodb_file_per_table，控制innodb引擎采用共享表空间存储还是独立表空间存储。

参数innodb_file_per_table为OFF时，innodb采用共享表空间存储。除了存储上面的内容外，新创建表的数据和索引也存放在ibdata1文件中。所以，随着表数目和数据量的增长，ibdata1文件也会逐渐增长。

参数innodb_file_per_table为ON时，innodb采用独立表空间存储。新创建表的数据和索引存放在各自的ibd文件中。ibdata1文件只存储上述内容，一般情况下，文件并不会有明显增长。

这种情况，ibdata1文件在主从间会同步增长。

原因2：UNDO LOG堆积

InnoDB为了实现MVCC多版本并发控制，通过记录更新操作的undo log和redo log来实现。而innoDB的undo log是存放在ibdata1共享表空间的。undo log堆积，是导致ibdata1文件增大的主要原因。这种ibdata1增长的情况一般发生在主库，从库采用单线程同步一般不会有明显问题。

导致undo log堆积的常见场景：

事务长期间不提交或回滚，导致undo log堆积

当事务A对一行数据进行修改时，InnoDB会把对事务A可见的旧版本数据存储一份在undo log中，如果此时又有事务B要修改这行数据，InnoDB又会把对事务B可见的旧版本数据再存储一份在undo log中，以此类推。 如果这条数据当前有N个事务要对其进行修改，那么innoDB就会存储N份历史版本的undo信息。而这些undo信息就存放在ibdata1文件中，这是导致ibdata1增大的主要原因之一。常见的情况：如load data操作；大量并发事务；旧事务长时间不提交或回滚。

为保证大事务中读请求MVCC一致性导致undo log堆积

事务中读取大量数据的SQL长时间不能提交或回滚。在事务开始到事务提交或回滚前的这段时间内，为保证MVCC读版本一致性，对事务要读取表的数据的所有修改操作产生的undo log就一直无法被purge掉造成堆积，从而导致ibdata1增大。这也是导致ibdata1增大的主要原因之一。 常见的情况，如mysqldump逻辑备份时加了–single-transaction，同时其他事务对备份的表有大量的更新操作。

解决方案

MariaDB开启独享表空间

• 使用独享表空间，将表空间分别单独存放。MySQL开启独享表空间的参数是Innodb_file_per_table，会为每个Innodb表创建一个.ibd的文件。

• 导出数据库中所有数据

mysqldump -u root -p --all-database > /root/all-database.dump

• 删除数据库中数据

mysql -u root -p
MariaDB [(none)]> drop database zabbix;

• 停止MySQL/MariaDB

systemctl stop mysqld.service     #(用于Mysql)
systemctl stop mariadb.service     #(用于MariaDB)

• 删除ibdata1文件（移动到/tmp下）

mv /var/lib/mysql/ibdata1 /tmp
mv /var/lib/mysql/ib_logfile0 /tmp
mv /var/lib/mysql/ib_logfile1 /tmp

• 修改my.cnf配置，增加innodb_file_per_table参数，innodb_file_per_table=1

vi /etc/my.cnf
[mysqld]
innodb_file_per_table=1

• 启动MySQL/MariaDB

systemctl start mysqld.service     #(用于Mysql)
systemctl start mariadb.service     #(用于MariaDB)

优化MariaDB内存占用

• 修改table_open_cache的值

vi /etc/my.cnf
在mysqld下面添加table_open_cache = 512,我这里预设的值是512，已经可以满足我服务的快速缓存访问了。如果你的表很多，或者并发很多的话，估计这个值需要调大的。

• 修改table_open_cache的值后，mariadb所占用的内存有所下降，从优化前的4.5G，下降到3G。