博主记录了在ECS上遇到MySQL频繁因内存不足挂掉的问题，通过查看日志发现错误代码11和12，判断为内存不足。首次优化尝试将`innodb_buffer_pool_size`调整为1024M，但问题依然存在。随后增加交换空间并调整参数，将`innodb_buffer_pool_size`设为128M，但问题再次发生。后续计划根据问题持续优化，考虑调整为64M或256M。
优化纪录
20200304第一次
新的ECS型号如下
2 vCPU 4 GiB (I/O优化)
ecs.n4.large 3Mbps
弄这个ECS纯属是个人的爱好，折腾一下代码，网站的访问量也不大，所以原来单独购买的mysql就没有继续付费，而是在新的ECS中安装了MySQL，最近每隔一段时间，网站就会出现连接数据库失败。不用多想指定是MySQL挂了，重启服务，网站就能正常访问了，这种问题在我最早接触LNMP环境的时候就出现过，一直没有解决，所以才购买单独的MySQL。虽然对MySQL服务不是很了解，但是这次想尝试解决一下。
以下方法作为纪录，在后续运行中观察效果
第一步查看MySQL的日志，oneinstack的MySQL日志路径 data/mysql/mysql-error.log。其他的系统可以用下面命令
#输入密码登录mysql控制状态
mysql -uroot -p
#日志文件路径
mysql> show variables like 'general_log_file';
#错误日志文件路径
mysql> show variables like 'log_error';
#慢查询日志文件路径
mysql> show variables like 'slow_query_log_file';
我查询了一下我的mysql错误日志
2020-03-03T02:43:30.049323Z 0 [ERROR] [MY-010249] [Server] Can't create thread to handle new connection(errno= 11)
2020-03-03T02:48:16.693398Z 0 [ERROR] [MY-010249] [Server] Can't create thread to handle new connection(errno= 11)
2020-03-03T03:06:51.731783Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 17940
2020-03-03T03:06:53.339663Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2020-03-03T03:06:53.352836Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-03-03T03:06:53.509660Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-03T03:06:53.704104Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
2020-03-03T03:06:53.752431Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-03-03T04:04:55.549207Z 0 [ERROR] [MY-010249] [Server] Can't create thread to handle new connection(errno= 11)
2020-03-03T04:07:25.895943Z 0 [ERROR] [MY-010249] [Server] Can't create thread to handle new connection(errno= 11)
2020-03-03T10:33:16.452243Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 18706
2020-03-03T10:33:18.289343Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2020-03-03T10:33:18.303826Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-03-03T10:33:18.482270Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-03T10:33:18.605097Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
2020-03-03T10:33:18.716052Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-03-03T17:26:47.452039Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 19333
2020-03-03T17:26:50.030603Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2020-03-03T17:26:50.044630Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-03-03T17:26:50.237681Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-03T17:26:50.350140Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
2020-03-03T17:26:50.523605Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-03-03T17:27:28.022776Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 19405
2020-03-03T17:27:28.516579Z 0 [ERROR] [MY-012681] [InnoDB] mmap(137363456 bytes) failed; errno 12
2020-03-03T17:27:28.516724Z 0 [ERROR] [MY-012681] [InnoDB] mmap(137363456 bytes) failed; errno 12
2020-03-03T17:27:28.532647Z 1 [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
2020-03-03T17:27:28.532756Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-03-03T17:27:28.532828Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-03-03T17:27:28.533067Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-03-03T17:27:28.533192Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-03T17:27:28.540822Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18) MySQL Community Server - GPL.
查看错误日志，百度一大圈，貌似主要的原因就是内存(暂且按这方面的问题去解决)
第二步修改MySQL的配置文件，文件位置etc/my.cnf。服务器内存是4GB的，用命令查询了一下，结果3995，接近4GB
#命令
free -m
#结果
total used free shared buff/cache available
Mem: 3955 863 2588 76 503 2796
Swap: 0 0 0
my.cnf配置文件
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 1024M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
这里给innodb_buffer_pool_size分配了1024，占据内存的四分之一，难道这个数值有些大了，百度一番这个值默认是128MB，可以根据自己服务器的配置调整，既然这样，暂且调到128MB，运行一段时间，看看是否还会出现数据库down掉的情况。
从网站迁移过来到现在数据库down掉大约4次了，基本上每周一次，这次调整，先看看情况，如果下周还出现问题，继续下调这个值，到64MB试试看，如果两周不出现问题上调到256MB试试看。
20200312第二次
中午打开网站的时候，又一次出现了“链接数据库错误”，重启服务器后访问正常，难道是上次的优化不管作用。继续查看日志，如下：
2020-03-11T09:09:38.109906Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 16786
2020-03-11T09:09:38.568369Z 0 [ERROR] [MY-012681] [InnoDB] mmap(137363456 bytes) failed; errno 12
2020-03-11T09:09:38.568529Z 1 [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
2020-03-11T09:09:38.568597Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-03-11T09:09:38.568661Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-03-11T09:09:38.568905Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-03-11T09:09:38.569020Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-11T09:09:38.573628Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18) MySQL Community Server - GPL.
上次基本上把日志都清理了，这次就看的比较明显了。(从日志上看貌似服务器的时间好像有点不对，稍后调整一下)。日志少了，也就好分析了。对于这方面算是个小白，把各条错误送给百度。errno12的找到的结果和上次的解决方案差不多，搜了一下其他的错误，总结了一下内存不足是主要原因。按照网上的方法重新设置了一下，具体设置步骤如下：
#检查内存 swap为0
free -h
total used free shared buff/cache available
Mem: 3.9G 357M 3.1G 65M 403M 3.2G
Swap: 0B 0B 0B
尝试 增加swap交换空间解决问题：
dd if=/dev/zero of=/swapfile bs=1M count=1024
mkswap /swapfile
swapon /swapfile
#为了保证下次系统启动后，此swap分区被自动加载，需要修改系统的fstab文件
vim /etc/fstab
#在文件/etc/fstab中加入 /swapfile swap swap defaults 0 0
增加完成后重新启动MYSQL，顺便查一下swap是否添加成功
free -h
total used free shared buff/cache available
Mem: 3.9G 676M 1.7G 77M 1.5G 2.9G
Swap: 1.0G 0B 1.0G
本次操作my.cnf暂时不做调整，下次出现问题时同时调整以下参数
innodb_buffer_pool_size =64M #目前为128M
key_buffer_size =120M #目前为256M
118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
