网站数据库经常挂，小白求助！

[nvhanzhi]

2 核 4G 的阿里云服务器，2M 带宽
/etc/my.cnf 的配置

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt="MySQL [\d]> "
no-auto-rehash

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

skip-name-resolve
#skip-networking
back_log = 300

max_connections = 1895
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 1024
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 128M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 256M

thread_cache_size = 64

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

ft_min_word_len = 4

#log_bin = mysql-bin #不让 mysql 的操作日志文件生成
binlog_format = mixed
expire_logs_days = 7 #日志保存 7 天

log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0
explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 2048M
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

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 500M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

谢谢大家了！

[lhy360121]

经常挂的报错是什么？

要看日志看日志看日志

[nvhanzhi]

@lhy360121 日志里啥都没有啊，我把操作日志文件删除了，把 localhost 改成 127.0.0.1 了 也没有作用

[lhy360121]

怎么可能没有，系统日志，mysql 日志

[nvhanzhi]

@lhy360121

2018-07-23 14:56:20 11266 [Note] InnoDB: Restoring possible half-written data pages
2018-07-23 14:56:20 11266 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 123228603010
2018-07-23 14:56:20 11266 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 84149106, file name mysql-bin.000004
2018-07-23 14:56:21 11266 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:56:21 11266 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:56:21 11266 [Note] InnoDB: 5.6.36 started; log sequence number 123228603010
2018-07-23 14:56:21 11266 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:56:21 11266 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:56:21 11266 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:56:21 11266 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:56:21 11266 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-23 14:57:10 11308 [Warning] You need to use --log-bin to make --binlog-format work.
2018-07-23 14:57:10 11308 [Note] Plugin 'FEDERATED' is disabled.
2018-07-23 14:57:10 11308 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-23 14:57:10 11308 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-23 14:57:10 11308 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-23 14:57:10 11308 [Note] InnoDB: Memory barrier is not used
2018-07-23 14:57:10 11308 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-07-23 14:57:10 11308 [Note] InnoDB: Using Linux native AIO
2018-07-23 14:57:10 11308 [Note] InnoDB: Using CPU crc32 instructions
2018-07-23 14:57:10 11308 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2018-07-23 14:57:10 11308 [Note] InnoDB: Completed initialization of buffer pool
2018-07-23 14:57:10 11308 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-23 14:57:10 11308 [Note] InnoDB: Log scan progressed past the checkpoint lsn 123229690053
2018-07-23 14:57:10 11308 [Note] InnoDB: Database was not shutdown normally!
2018-07-23 14:57:10 11308 [Note] InnoDB: Starting crash recovery.
2018-07-23 14:57:10 11308 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-07-23 14:57:10 11308 [Note] InnoDB: Restoring possible half-written data pages
2018-07-23 14:57:10 11308 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 123229980451
2018-07-23 14:57:11 11308 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 84149106, file name mysql-bin.000004
2018-07-23 14:57:11 11308 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:57:11 11308 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:57:11 11308 [Note] InnoDB: 5.6.36 started; log sequence number 123229980451
2018-07-23 14:57:11 11308 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:57:11 11308 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:57:11 11308 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:57:11 11308 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:57:11 11308 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)

[torment5524]

其实你要是自己数据库不会排查的话，建议你直接上阿里云的 rds 数据库。。。价格也差不多。

根据你目前的情况的话，看日志找找慢 sql，阿里云的监控里面看看内存缓存和 cpu 负载等指标

[nvhanzhi]

2018-07-23 14:48:03 9667 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:48:03 9667 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:48:03 9667 [Note] InnoDB: 5.6.36 started; log sequence number 123224907401
2018-07-23 14:48:03 9667 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:48:03 9667 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:48:03 9667 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:48:04 9667 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:48:04 9667 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-23 14:48:18 9733 [Warning] You need to use --log-bin to make --binlog-format work.
2018-07-23 14:48:18 9733 [Note] Plugin 'FEDERATED' is disabled.
2018-07-23 14:48:18 9733 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-23 14:48:18 9733 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-23 14:48:18 9733 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-23 14:48:18 9733 [Note] InnoDB: Memory barrier is not used
2018-07-23 14:48:18 9733 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-07-23 14:48:18 9733 [Note] InnoDB: Using Linux native AIO
2018-07-23 14:48:18 9733 [Note] InnoDB: Using CPU crc32 instructions
2018-07-23 14:48:18 9733 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2018-07-23 14:48:18 9733 [Note] InnoDB: Completed initialization of buffer pool
2018-07-23 14:48:18 9733 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-23 14:48:18 9733 [Note] InnoDB: Log scan progressed past the checkpoint lsn 123225057677
2018-07-23 14:48:18 9733 [Note] InnoDB: Database was not shutdown normally!
2018-07-23 14:48:18 9733 [Note] InnoDB: Starting crash recovery.
2018-07-23 14:48:18 9733 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-07-23 14:48:18 9733 [Note] InnoDB: Restoring possible half-written data pages
2018-07-23 14:48:18 9733 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 123225090259
2018-07-23 14:48:19 9733 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 84149106, file name mysql-bin.000004
2018-07-23 14:48:19 9733 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-23 14:48:19 9733 [Note] InnoDB: Waiting for purge to start
2018-07-23 14:48:19 9733 [Note] InnoDB: 5.6.36 started; log sequence number 123225090259
2018-07-23 14:48:19 9733 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-07-23 14:48:19 9733 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-07-23 14:48:19 9733 [Note] Server socket created on IP: '0.0.0.0'.
2018-07-23 14:48:20 9733 [Note] Event Scheduler: Loaded 0 events
2018-07-23 14:48:20 9733 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.36-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-23 14:48:26 9777 [Warning] You need to use --log-bin to make --binlog-format work.
2018-07-23 14:48:26 9777 [Note] Plugin 'FEDERATED' is disabled.
2018-07-23 14:48:26 9777 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-23 14:48:26 9777 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-23 14:48:26 9777 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-23 14:48:26 9777 [Note] InnoDB: Memory barrier is not used
2018-07-23 14:48:26 9777 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-07-23 14:48:26 9777 [Note] InnoDB: Using Linux native AIO
2018-07-23 14:48:26 9777 [Note] InnoDB: Using CPU crc32 instructions
2018-07-23 14:48:26 9777 [Note] InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: mmap(274726912 bytes) failed; errno 12
2018-07-23 14:48:26 9777 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-07-23 14:48:26 9777 [ERROR] Plugin 'InnoDB' init function returned error.
2018-07-23 14:48:26 9777 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-07-23 14:48:26 9777 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-07-23 14:48:26 9777 [ERROR] Aborting

[nvhanzhi]

@torment5524 我看不懂日志，日志比较大。。。 我的网站是 Wordpress

[torment5524]

2018-07-23 14:48:26 9777 [Note] InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: mmap(274726912 bytes) failed; errno 12
2018-07-23 14:48:26 9777 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-07-23 14:48:26 9777 [ERROR] Plugin 'InnoDB' init function returned error.
2018-07-23 14:48:26 9777 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-07-23 14:48:26 9777 [ERROR] Unknown/unsupported storage engine: InnoDB
这不是很清楚了么。。。

[torment5524]

服务器上 free 看下内存

[chinvo]

用 InnoDB 要开 swap

[openbsd]

改配置到 1 核 2G
省下来的钱买个内网数据库
要省心很多

[nvhanzhi]

@torment5524 # free
total used free shared buff/cache available
Mem: 3881936 2122264 859316 57784 900356 1465352
Swap: 0 0 0


@chinvo 用 InnoDB 要开 swap
我不知道 InnoDB 在哪里，这是个什么东西

[jasonyang9]

@nvhanzhi #12 骚年，系统性的学一下会比较好

[Mazexal]

@nvhanzhi 你用 mysql 不知道 InnoDB 我也是服的.....InnoDB 就是 mysql 数据库引擎的一种

[s609926202]

如果没有专业的运维，强烈建议直接购买云数据库，自己安装的都是坑

[lhy360121]

把 innodb_buffer_pool_size 这个值改小， 你内存不够。

[torment5524]

@nvhanzhi
看你发的图
总内存 3.88g ，已经使用了 2g，缓存用了 900m 可用的就 1.4g ，你设置的 innodb_buffer_pool_size 是 2g，分配内存不足了，要么你改小这个 innodb_buffer_pool_size，要么设置 swap。
网上搜下 InnoDB: mmap，有具体操作办法。

再就是赞同#11 的 openbsd 的方案，比较省心

[likuku]

两核 4G 内存... 这哪够啊.

直接用 mysql 给的 small 配置模版就好了呗。

非得用 mysql ？ sqlite 也不错，小规模使用，很多时候 sqlite 足够了。

[likuku]

swap 还是不要折腾了，系统开始频繁用到 swap，那么性能也就恶化到几乎没法用了。

[nvhanzhi]

@torment5524 我前几天 innodb_buffer_pool_size 是 1g 也挂啊。。。

[likuku]

@nvhanzhi Wordpress 啊，那只有 mysql 了。

“前几天 innodb_buffer_pool_size 是 1g 也挂” 那就继续减小，要知道当年 mysql 给它默认值只有 128M。

你查查你数据表类型都是 Innodb 么？

[nvhanzhi]

@likuku 改成了 512 再不行我就按这个设置 swap https://www.k2zone.cn/?p=1182

其实我不懂 innodb_buffer_pool_size 的大小具体有啥用，为什么要设置的小而不是大？

[opengps]

阿里云数据库正在搞拼团，要不要找我发你地址？

[nvhanzhi]

@opengps 我不懂怎样把我的 MySQL 的数据库搞到阿里云上去。。。现在是 Wordpress 直接搞的

[opengps]

迁移可以找迁移工具或者直接导出脚本（注意是：架构+数据）
按理说，你应该没必要单独用数据库服务，因为看你的描述，还不是业务量上来之后的样子，所以完全可以先自己继续用 ecs 折腾着玩。
出现这个问题肯定有某个地方是可以解决的，最笨的办法可以选择备份数据后重装，liunx 我不懂所以帮不了你，win 的话可以先考虑虚拟内存

[agdhole]

买数据库,省心

[likuku]

@nvhanzhi 直接买阿里云的 RDS 服务

[likuku]

@nvhanzhi innodb_buffer_pool_size 你可以当作它是 innodb 自己的内存(把 innodb 想像成一个独立的系统，没错它是 mysql 内的一个数据引擎 /数据插件)，你服务器本身内存太小了，你给 innodb 分了太多内存，结果繁忙时 mysql 整体没有足够内存了。还不行就改 256M 吧，你 wordpress 访问量不大，innodb_buffer_pool_size 那就不需要开那么高。

[likuku]

@nvhanzhi 不建议搞 swap，那玩意只是延迟系统僵死(近乎饮鸩止渴)，若系统频繁用到 swap，那么说明当前物理内存不够

[falcon05]

只跑 wordpress 的话，禁用 innodb 引擎，直接用 myisam，64M 内存也能跑得很好

[torment5524]

直接切到 rds 的 mysql 服务器吧，不用操这些心，应用服务器降下配置

[my3157]

data dir 在 Aliyun 自带的系统盘?

[jeffsun]

innodb 缓存不够了，缓存开多少看你数据量多大，一般数据量和 bufferpool 比例在 5:1 差不多，做好索引优化就行，ecs 做数据库还是比较省钱，因为阿里云 rds 价格一般是相同配置的 2 倍+，因为它是双机高可用的，或者你可以买一个单机版，比相同配置 ecs 贵一点。

[nvhanzhi]

@jeffsun @torment5524 @likuku @falcon05 我网站有 5 万多商品，每天流量两三千，IP1000 多。
@my3157 是的，只有一个盘
@likuku 买阿里云的 RDS 服务我 2 核 4G 的服务器一天要 24 块多，一年 8760，包年也要 5000 多，明显不划算啊。

[likuku]

@falcon05
@nvhanzhi

改换 myisam 是个好办法(phpmyadmin 可以在线直接切换的)，数据也放系统盘...你这也太省了。

aliyun 不清楚，aws 的 ec2 主机自带盘是不能拿来作数据保存的，因为系统盘都算是临时存储，物理机层面有调度时，重启 vps 后，系统盘都重置的。再怎么样，也得另外买个独立存储盘来存储数据。

都几万商品了，都用它做生意了，数据也算蛮重要了，有些钱是不可以省的。

[nvhanzhi]

@likuku 我研究研究吧 先去备份网站。。。问题是网站不赚钱啊。。。

[likuku]

@nvhanzhi 不赚钱还投入这么多时间和精力？直接淘宝上开个网店+装修 下，不就挺好吗？

[nvhanzhi]

@likuku 淘宝同行竞争不过啊，人家都做 10 年了

[nvhanzhi]

又挂了。。。
Error establishing a database connection
This either means that the username and password information in your wp-config.php file is incorrect or we can ’ t contact the database server at 127.0.0.1. This could mean your host ’ s database server is down.

Are you sure you have the correct username and password?
Are you sure that you have typed the correct hostname?
Are you sure that the database server is running?
If you ’ re unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.

[my3157]

@nvhanzhi #34 建议, 换个稍微大点的 SSD 盘, 把数据库迁进去 , 500G 左右的, 具体看下参数, 阿里云小磁盘, iops 灰常感人, 写日志都能把磁盘 io 写满

[my3157]

@nvhanzhi #34 另外, 加下 swap, 一般云服务器都是禁用 swap, 虽然说性能会下降, 但不至于死, 内存大小的 2 倍就 OK

[nvhanzhi]

@my3157 好 ，我把 innodb_buffer_pool_size 改成 256 了，要是还经常挂我就搞 8G 的 swap 试试。谢谢你！

[nvhanzhi]

@my3157 我的云盘是 100G 的，不知道现在用了多少，应该不超过 50G，我的图片什么的都是阿里云 OSS 上，服务器上就网站备份比较占空间

[Mac]

小内存就不要用 innodb 了，反正是云服务器，死艹 myisam，用 wordpress 不会有什么区别。

[nvhanzhi]

@Mac myisam 完全没了解过，今天先看看情况，实在不行就试试 myisam，多谢了！

[my3157]

@nvhanzhi #43 阿里的磁盘 IO 是和大小有关系的, 以前遇到过 N 次 磁盘 IO 莫名飙升导致的整个 server 假死, ping 都 ping 不通的情况

[stevenkang]

为什么不直接用云虚拟主机或者弹性 Web 托管，数据库、PHP 环境都搭好了，wordpress 直接跑就行了

[salamanderMH]

加下 swap

[nvhanzhi]

Filesystem Size Used Avail Capacity iused ifree %iused Mounted on
/dev/disk1s1 466Gi 433Gi 28Gi 95% 2051882 9223372036852723925 0% /
/dev/disk1s4 466Gi 4.0Gi 28Gi 13% 4 9223372036854775803 0% /private/var/vm

搞不懂我的硬盘还有多少。。。

[nvhanzhi]

已开 Swap，谢谢所有大神！@all @Mac @Mazexal @agdhole @chinvo @falcon05 @jasonyang9 @jeffsun @lhy360121 @likuku @my3157 @stevenkang @salamanderMH

[root@i~]# free -h
total used free shared buff/cache available
Mem: 3.7G 1.4G 867M 49M 1.4G 2.0G
Swap: 7.8G 10M 7.8G