1
lhy360121 2018-07-23 15:16:03 +08:00
经常挂的报错是什么?
要看日志看日志看日志 |
2
nvhanzhi OP @lhy360121 日志里啥都没有啊,我把操作日志文件删除了,把 localhost 改成 127.0.0.1 了 也没有作用
|
3
lhy360121 2018-07-23 15:22:25 +08:00
怎么可能没有,系统日志,mysql 日志
|
4
nvhanzhi OP @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) |
5
torment5524 2018-07-23 15:24:00 +08:00
其实你要是自己数据库不会排查的话,建议你直接上阿里云的 rds 数据库。。。价格也差不多。
根据你目前的情况的话,看日志找找慢 sql,阿里云的监控里面看看内存缓存和 cpu 负载等指标 |
6
nvhanzhi OP 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 |
7
nvhanzhi OP @torment5524 我看不懂日志,日志比较大。。。 我的网站是 Wordpress
|
8
torment5524 2018-07-23 15:27:14 +08:00
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 这不是很清楚了么。。。 |
9
torment5524 2018-07-23 15:28:07 +08:00
服务器上 free 看下内存
|
10
chinvo 2018-07-23 15:31:38 +08:00 via iPhone
用 InnoDB 要开 swap
|
11
openbsd 2018-07-23 15:37:10 +08:00
改配置到 1 核 2G
省下来的钱买个内网数据库 要省心很多 |
12
nvhanzhi OP @torment5524 # free
total used free shared buff/cache available Mem: 3881936 2122264 859316 57784 900356 1465352 Swap: 0 0 0 @chinvo 用 InnoDB 要开 swap 我不知道 InnoDB 在哪里,这是个什么东西 |
13
jasonyang9 2018-07-23 15:40:12 +08:00
@nvhanzhi #12 骚年,系统性的学一下会比较好
|
14
Mazexal 2018-07-23 15:41:23 +08:00
@nvhanzhi 你用 mysql 不知道 InnoDB 我也是服的.....InnoDB 就是 mysql 数据库引擎的一种
|
15
s609926202 2018-07-23 15:41:34 +08:00 via iPhone 1
如果没有专业的运维,强烈建议直接购买云数据库,自己安装的都是坑
|
16
lhy360121 2018-07-23 15:43:13 +08:00 1
把 innodb_buffer_pool_size 这个值改小, 你内存不够。
|
17
torment5524 2018-07-23 15:44:48 +08:00
@nvhanzhi
看你发的图 总内存 3.88g ,已经使用了 2g,缓存用了 900m 可用的就 1.4g ,你设置的 innodb_buffer_pool_size 是 2g,分配内存不足了,要么你改小这个 innodb_buffer_pool_size,要么设置 swap。 网上搜下 InnoDB: mmap,有具体操作办法。 再就是赞同#11 的 openbsd 的方案,比较省心 |
18
likuku 2018-07-23 15:47:25 +08:00 1
两核 4G 内存... 这哪够啊.
直接用 mysql 给的 small 配置模版就好了呗。 非得用 mysql ? sqlite 也不错,小规模使用,很多时候 sqlite 足够了。 |
19
likuku 2018-07-23 15:48:08 +08:00
swap 还是不要折腾了,系统开始频繁用到 swap,那么性能也就恶化到几乎没法用了。
|
20
nvhanzhi OP @torment5524 我前几天 innodb_buffer_pool_size 是 1g 也挂啊。。。
|
21
likuku 2018-07-23 15:53:53 +08:00
@nvhanzhi Wordpress 啊,那只有 mysql 了。
“前几天 innodb_buffer_pool_size 是 1g 也挂” 那就继续减小,要知道当年 mysql 给它默认值只有 128M。 你查查你数据表类型都是 Innodb 么? |
22
nvhanzhi OP @likuku 改成了 512 再不行我就按这个设置 swap https://www.k2zone.cn/?p=1182
其实我不懂 innodb_buffer_pool_size 的大小具体有啥用,为什么要设置的小而不是大? |
23
opengps 2018-07-23 16:11:34 +08:00
阿里云数据库正在搞拼团,要不要找我发你地址?
|
25
opengps 2018-07-23 16:20:55 +08:00
迁移可以找迁移工具或者直接导出脚本(注意是:架构+数据)
按理说,你应该没必要单独用数据库服务,因为看你的描述,还不是业务量上来之后的样子,所以完全可以先自己继续用 ecs 折腾着玩。 出现这个问题肯定有某个地方是可以解决的,最笨的办法可以选择备份数据后重装,liunx 我不懂所以帮不了你,win 的话可以先考虑虚拟内存 |
26
agdhole 2018-07-23 16:26:54 +08:00
买数据库,省心
|
28
likuku 2018-07-23 17:27:50 +08:00
@nvhanzhi innodb_buffer_pool_size 你可以当作它是 innodb 自己的内存(把 innodb 想像成一个独立的系统,没错它是 mysql 内的一个数据引擎 /数据插件),你服务器本身内存太小了,你给 innodb 分了太多内存,结果繁忙时 mysql 整体没有足够内存了。还不行就改 256M 吧,你 wordpress 访问量不大,innodb_buffer_pool_size 那就不需要开那么高。
|
29
likuku 2018-07-23 17:30:06 +08:00 1
@nvhanzhi 不建议搞 swap,那玩意只是延迟系统僵死(近乎饮鸩止渴),若系统频繁用到 swap,那么说明当前物理内存不够
|
30
falcon05 2018-07-23 17:47:23 +08:00 via iPhone 1
只跑 wordpress 的话,禁用 innodb 引擎,直接用 myisam,64M 内存也能跑得很好
|
31
torment5524 2018-07-23 17:49:57 +08:00
直接切到 rds 的 mysql 服务器吧,不用操这些心,应用服务器降下配置
|
32
my3157 2018-07-23 17:54:10 +08:00
data dir 在 Aliyun 自带的系统盘?
|
33
jeffsun 2018-07-23 18:23:47 +08:00
innodb 缓存不够了,缓存开多少看你数据量多大,一般数据量和 bufferpool 比例在 5:1 差不多,做好索引优化就行,ecs 做数据库还是比较省钱,因为阿里云 rds 价格一般是相同配置的 2 倍+,因为它是双机高可用的,或者你可以买一个单机版,比相同配置 ecs 贵一点。
|
34
nvhanzhi OP |
35
likuku 2018-07-23 21:35:26 +08:00
|
39
nvhanzhi OP 又挂了。。。
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. |
40
my3157 2018-07-23 23:00:59 +08:00
@nvhanzhi #34 建议, 换个稍微大点的 SSD 盘, 把数据库迁进去 , 500G 左右的, 具体看下参数, 阿里云小磁盘, iops 灰常感人, 写日志都能把磁盘 io 写满
|
41
my3157 2018-07-23 23:02:18 +08:00
@nvhanzhi #34 另外, 加下 swap, 一般云服务器都是禁用 swap, 虽然说性能会下降, 但不至于死, 内存大小的 2 倍就 OK
|
42
nvhanzhi OP @my3157 好 ,我把 innodb_buffer_pool_size 改成 256 了,要是还经常挂我就搞 8G 的 swap 试试。谢谢你!
|
43
nvhanzhi OP @my3157 我的云盘是 100G 的,不知道现在用了多少,应该不超过 50G,我的图片什么的都是阿里云 OSS 上,服务器上就网站备份比较占空间
|
44
Mac 2018-07-23 23:08:26 +08:00 via Android
小内存就不要用 innodb 了,反正是云服务器,死艹 myisam,用 wordpress 不会有什么区别。
|
46
my3157 2018-07-24 12:18:53 +08:00
@nvhanzhi #43 阿里的磁盘 IO 是和大小有关系的, 以前遇到过 N 次 磁盘 IO 莫名飙升导致的整个 server 假死, ping 都 ping 不通的情况
|
47
stevenkang 2018-07-24 14:19:21 +08:00
为什么不直接用云虚拟主机或者弹性 Web 托管,数据库、PHP 环境都搭好了,wordpress 直接跑就行了
|
48
salamanderMH 2018-07-24 15:12:22 +08:00
加下 swap
|
49
nvhanzhi OP 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 搞不懂我的硬盘还有多少。。。 |
50
nvhanzhi OP 已开 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 |