欢迎光临
我们一直在努力

MySQL优化纪录

优化纪录

20200304第一次

20200312第二次

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

未经允许不得转载:侯建方的个人网站 » MySQL优化纪录

点击此处获取更多金融后续培训参考答案

登录

找回密码

注册