这个月初，通过zabbix监控发现有1台mysql数据库的从库内存剩余空间不断降低。检查以往的监控历史图表，发现由于内存占用不断增大，每隔一个多月，就会因为内存严重不足，导致这台服务器的1个mysql实例（端口：3316）重启。数据库实例重启之后，内存被大量释放，但经过一个多月，又会因为不断占用的内存再次重启mysql实例。

　　上图是这台服务器的zabbix监控图形，近半年来3次内存枯竭的时间分别是去年10月27日、12月25日、今年2月8日。

　　执行“top”命令，可以快速找到是那个mysql实例的进程PID：

[root@DB3data1]#toptop-14:06:03up829days,5min,1user,loadaverage:0.00,0.02,0.05Tasks:167total,1running,166sleeping,0stopped,0zombie%Cpu(s):0.2us,0.1sy,0.0ni,99.5id,0.2wa,0.0hi,0.0si,0.0stKiBMem:32520424total,14101408used,18419016free,144168buffersKiBSwap:0total,0used,0free.1240256cachedMemPIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND3348mysql20060480084.340g7436S1.014.048:20.58mysqld1039mysql20032.919g5.942g0S0.319.2923:58.09mysqld18301root20012369216721176R0.30.00:00.02top1root2004110823841232S0.00.08:53.72systemd2root200000S0.00.00:05.82kthreadd3root200000S0.00.01:30.15ksoftirqd/05root0-20000S0.00.00:00.00kworker/0:0H7rootrt0000S0.00.00:21.02migration/08root200000S0.00.00:00.00rcu_bh9root200000S0.00.00:00.00rcuob/010root200000S0.00.00:00.00rcuob/111root200000S0.00.00:00.00rcuob/212root200000S0.00.00:00.00rcuob/313root200000S0.00.00:00.00rcuob/414root200000S0.00.00:00.00rcuob/515root200000S0.00.00:00.00rcuob/616root200000S0.00.00:00.00rcuob/717root200000S0.00.00:00.00rcuob/818root200000S0.00.00:00.00rcuob/919root200000S0.00.00:00.00rcuob/1020root200000S0.00.00:00.00rcuob/1121root200000S0.00.00:00.00rcuob/1222root200000S0.00.00:00.00rcuob/1323root200000S0.00.00:00.00rcuob/1424root200000S0.00.0373:05.02rcu_sched

　　执行“ps -ef|grep mysql”命令，就可以根据上面的PID，找到是3316端口的mysql实例：

[root@DB3data1]#ps-ef|grepmysqlmysql10392609002017?15:23:58/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/mysql/data/--plugin-dir=/usr/local/mysql/lib/mysql/plugin--user=mysql--log-error=/mysql/data/mysql-error.log--open-files-limit=65535--pid-file=/mysql/data/mysql.pid--socket=/mysql/data/mysql.sockmysql334852310Mar15?00:48:20/usr/local/mysql/bin/mysqld--defaults-extra-file=/mysql/data1/my.cnf--basedir=/usr/local/mysql--datadir=/mysql/data1/--plugin-dir=/usr/local/mysql/lib/mysql/plugin--user=mysql--log-error=/mysql/data1/mysql-error.log--open-files-limit=65535--pid-file=/mysql/data1/mysql.pid--socket=/mysql/data1/mysql.sock--port=3316root5231102016?00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--defaults-extra-file=/mysql/data1/my.cnf--datadir=/mysql/data1/--user=mysqlroot1832717987014:06pts/200:00:00grep--color=automysqlroot26090102016?00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/mysql/data/--pid-file=/mysql/data/mysql.pid

　　3316这个实例是rabbitMQ持久化存储数据用的，最初我怀疑可能与持久化出问题有关。

　　但是，运维同事根据数据库的错误日志，认为是mysql的配置参数设置不当有关。

[root@DB3~]#cd/mysql/data1/[root@DB3data1]#ll-trtotal1607352drwx------2mysqlmysql6Nov282016testdrwx------2mysqlmysql4096Nov282016performance_schemadrwx------2mysqlmysql4096Nov282016mysql-rwxr-xr-x1rootroot127Nov282016start_mysql_3316.sh-rw-rw----1mysqlmysql56Nov282016auto.cnfdrwx------2mysqlmysql4096Nov282016activemq-rw-rw----1mysqlmysql536870912Mar713:09ib_logfile1-rw-r--r--1rootroot2455Mar1509:57my.cnf-rw-rw----1mysqlmysql266186542Mar1518:05mysql-bin.000012-rw-rw----1mysqlmysql60Mar1518:05mysql-bin.indexsrwxrwxrwx1mysqlmysql0Mar1518:05mysql.sock-rw-rw----1mysqlmysql5Mar1518:05mysql.pid-rw-r-----1mysqlroot24166Mar1518:05mysql-error.log-rw-rw----1mysqlmysql17395510Mar1913:49mysql-bin.000013-rw-rw----1mysqlmysql79691776Mar1913:49ibdata1-rw-rw----1mysqlmysql536870912Mar1913:49ib_logfile0-rw-rw----1mysqlmysql204840084Mar1913:49mysql-slow.log[root@DB3data1]#tail-90mysql-error.log2018-02-0719:59:2812413[Note]InnoDB:Compressedtablesusezlib1.2.32018-02-0719:59:2812413[Note]InnoDB:UsingLinuxnativeAIO2018-02-0719:59:2812413[Note]InnoDB:UsingCPUcrc32instructions2018-02-0719:59:2812413[Note]InnoDB:Initializingbufferpool,size=26.0G2018-02-0719:59:3012413[Note]InnoDB:Completedinitializationofbufferpool2018-02-0719:59:3012413[Note]InnoDB:HighestsupportedfileformatisBarracuda.2018-02-0719:59:3012413[Note]InnoDB:Thelogsequencenumbers1625987and1625987inibdatafilesdonotmatchthelogsequencenumber12657781043intheib_logfiles!2018-02-0719:59:3012413[Note]InnoDB:Databasewasnotshutdownnormally!2018-02-0719:59:3012413[Note]InnoDB:Startingcrashrecovery.2018-02-0719:59:3012413[Note]InnoDB:Readingtablespaceinformationfromthe.ibdfiles...2018-02-0719:59:3012413[Note]InnoDB:Restoringpossiblehalf-writtendatapages2018-02-0719:59:3012413[Note]InnoDB:fromthedoublewritebuffer...InnoDB:1transaction(s)whichmustberolledbackorcleanedupInnoDB:intotal127441rowoperationstoundoInnoDB:Trxidcounteris10797056InnoDB:LastMySQLbinlogfileposition0793055160,filenamemysql-bin.0000112018-02-0719:59:3212413[Note]InnoDB:128rollbacksegment(s)areactive.InnoDB:Startinginbackgroundtherollbackofuncommittedtransactions2018-02-0719:59:327fd34afa8700InnoDB:Rollingbacktrxwithid10003975,127441rowstoundoInnoDB:Progressinpercents:12018-02-0719:59:3212413[Note]InnoDB:Waitingforpurgetostart2018-02-0719:59:3212413[Note]InnoDB:PerconaXtraDB(http://www.percona.com)5.6.27-76.0started;logsequencenumber1265778104322018-02-0719:59:3312413[Note]Recoveringafteracrashusing/mysql/data1/mysql-bin34567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991002018-02-0719:59:3312413[Note]InnoDB:Rollbackoftrxwithid10003975completed2018-02-0719:59:337fd34afa8700InnoDB:Rollbackofnon-preparedtransactionscompleted2018-02-0719:59:5112413[Note]Startingcrashrecovery...2018-02-0719:59:5112413[Note]Crashrecoveryfinished.2018-02-0719:59:5112413[Note]Serverhostname(bind-address):'*';port:33162018-02-0719:59:5112413[Note]IPv6isavailable.2018-02-0719:59:5112413[Note]-'::'resolvesto'::';2018-02-0719:59:5112413[Note]ServersocketcreatedonIP:'::'.2018-02-0719:59:5112413[Warning]'user'entry'root@db3'ignoredin--skip-name-resolvemode.2018-02-0719:59:5112413[Warning]'user'entry'@db3'ignoredin--skip-name-resolvemode.2018-02-0719:59:5112413[Warning]'proxies_priv'entry'@root@db3'ignoredin--skip-name-resolvemode.2018-02-0719:59:5112413[Note]EventScheduler:Loaded0events2018-02-0719:59:5112413[Note]/usr/local/mysql/bin/mysqld:readyforconnections.Version:'5.6.27-76.0-log'socket:'/mysql/data1/mysql.sock'port:3316Sourcedistribution18031518:04:56mysqld_safeNumberofprocessesrunningnow:018031518:04:56mysqld_safemysqldrestarted2018-03-1518:04:570[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).2018-03-1518:04:570[Warning]'ERROR_FOR_DIVISION_BY_ZERO'isdeprecatedandwillberemovedinafuturerelease.2018-03-1518:04:570[Warning]'NO_ZERO_DATE'isdeprecatedandwillberemovedinafuturerelease.2018-03-1518:04:570[Warning]'NO_ZERO_IN_DATE'isdeprecatedandwillberemovedinafuturerelease.2018-03-1518:04:570[Note]/usr/local/mysql/bin/mysqld(mysqld5.6.27-76.0-log)startingasprocess3348...2018-03-1518:04:573348[Warning]Usinguniqueoptionprefixmyisam-recoverinsteadofmyisam-recover-optionsisdeprecatedandwillberemovedinafuturerelease.Pleaseusethefullnameinstead.2018-03-1518:04:573348[Warning]Usinguniqueoptionprefixmyisam-recoverinsteadofmyisam-recover-optionsisdeprecatedandwillberemovedinafuturerelease.Pleaseusethefullnameinstead.2018-03-1518:04:573348[Note]Plugin'FEDERATED'isdisabled.2018-03-1518:04:573348[Warning]Theoptioninnodb(skip-innodb)isdeprecatedandwillberemovedinafuturerelease2018-03-1518:04:573348[Warning]Theoptioninnodb(skip-innodb)isdeprecatedandwillberemovedinafuturerelease2018-03-1518:04:573348[Note]InnoDB:Usingatomicstorefcountbufferpoolpages2018-03-1518:04:573348[Note]InnoDB:TheInnoDBmemoryheapisdisabled2018-03-1518:04:573348[Note]InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins2018-03-1518:04:573348[Note]InnoDB:Memorybarrierisnotused2018-03-1518:04:573348[Note]InnoDB:Compressedtablesusezlib1.2.32018-03-1518:04:573348[Note]InnoDB:UsingLinuxnativeAIO2018-03-1518:04:573348[Note]InnoDB:UsingCPUcrc32instructions2018-03-1518:04:573348[Note]InnoDB:Initializingbufferpool,size=2.0G2018-03-1518:04:573348[Note]InnoDB:Completedinitializationofbufferpool2018-03-1518:04:573348[Note]InnoDB:HighestsupportedfileformatisBarracuda.2018-03-1518:04:573348[Note]InnoDB:Thelogsequencenumbers1625987and1625987inibdatafilesdonotmatchthelogsequencenumber12972128167intheib_logfiles!2018-03-1518:04:573348[Note]InnoDB:Databasewasnotshutdownnormally!2018-03-1518:04:573348[Note]InnoDB:Startingcrashrecovery.2018-03-1518:04:573348[Note]InnoDB:Readingtablespaceinformationfromthe.ibdfiles...2018-03-1518:04:583348[Note]InnoDB:Restoringpossiblehalf-writtendatapages2018-03-1518:04:583348[Note]InnoDB:fromthedoublewritebuffer...InnoDB:1transaction(s)whichmustberolledbackorcleanedupInnoDB:intotal103450rowoperationstoundoInnoDB:Trxidcounteris11109120InnoDB:LastMySQLbinlogfileposition0266186542,filenamemysql-bin.0000122018-03-1518:04:593348[Note]InnoDB:128rollbacksegment(s)areactive.InnoDB:Startinginbackgroundtherollbackofuncommittedtransactions2018-03-1518:04:597eff9bd1d700InnoDB:Rollingbacktrxwithid10797063,103450rowstoundoInnoDB:Progressinpercents:12018-03-1518:04:593348[Note]InnoDB:Waitingforpurgetostart2342018-03-1518:04:593348[Note]InnoDB:PerconaXtraDB(http://www.percona.com)5.6.27-76.0started;logsequencenumber129721281675678910111213141516172018-03-1518:04:593348[Note]Recoveringafteracrashusing/mysql/data1/mysql-bin181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991002018-03-1518:05:003348[Note]InnoDB:Rollbackoftrxwithid10797063completed2018-03-1518:05:007eff9bd1d700InnoDB:Rollbackofnon-preparedtransactionscompleted2018-03-1518:05:033348[Note]Startingcrashrecovery...2018-03-1518:05:033348[Note]Crashrecoveryfinished.2018-03-1518:05:033348[Note]Serverhostname(bind-address):'*';port:33162018-03-1518:05:033348[Note]IPv6isavailable.2018-03-1518:05:033348[Note]-'::'resolvesto'::';2018-03-1518:05:033348[Note]ServersocketcreatedonIP:'::'.2018-03-1518:05:033348[Warning]'user'entry'root@db3'ignoredin--skip-name-resolvemode.2018-03-1518:05:033348[Warning]'user'entry'@db3'ignoredin--skip-name-resolvemode.2018-03-1518:05:033348[Warning]'proxies_priv'entry'@root@db3'ignoredin--skip-name-resolvemode.2018-03-1518:05:043348[Note]EventScheduler:Loaded0events2018-03-1518:05:043348[Note]/usr/local/mysql/bin/mysqld:readyforconnections.Version:'5.6.27-76.0-log'socket:'/mysql/data1/mysql.sock'port:3316Sourcedistribution

　　在上面mysql错误日志里，第4行“Initializing buffer pool, size = 26.0G”引起了他的注意，并据此对mysql配置文件做了修改。

[root@DB3data1]#tail-16my.cnf#INNODB#innodb-flush-method=O_DIRECTinnodb-log-files-in-group=2innodb-log-file-size=512Minnodb-flush-log-at-trx-commit=1innodb-file-per-table=1innodb-buffer-pool-size=2G#LOGGING#log-error=/mysql/data1/mysql-error.loglog-queries-not-using-indexes=1slow-query-log=1slow-query-log-file=/mysql/data1/mysql-slow.log#slaveslave_skip_errors=1062

　　第7行的参数“innodb-buffer-pool-size”当时配置的有几十G，他把这个参数修改为“2G”。

　　这个数据库实例用于rabbitMQ持久化，而rabbitMQ是提供给电销使用的。

　　在电销部门下午6点下班之后，他通过“ps -ef|grep mysql”找到这个实例，再“kill -9”杀掉这个实例，没想到，这个实例就立即自己启动了。

　　电销相关的开发同事检查电销业务没有问题后，我们就下班了。

　　第二天检查zabbix监控，不仅内存被大量释放，而且，也没有再不断被占用。