I have wordpress with mysql installed on a Amazon EC2 linux AMI with 1 CPU and 1 GB memory.
Recently, mysql has been crashing at least once or twice a week and I can't find out what is wrong.
when I run the command
sudo service mysqld status
I get this
mysqld dead but subsys locked
I have enabled logging by editing the /etc/my.cnf as follows:-
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
log_error=/var/log/mysqld.log
[mysqld_safe] log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
But everytime when it crashed, I can't really identify the errors in the log file. Recent crash shows the following in the log file /var/log/mysqld.log but not sure if they are the logs when I have restarted mysql.
Version: '5.6.37' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2017-12-06 12:29:53 30473 [Note] Plugin 'FEDERATED' is disabled.
2017-12-06 12:29:53 30473 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-12-06 12:29:53 30473 [Note] InnoDB: The InnoDB memory heap is disabled
2017-12-06 12:29:53 30473 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-12-06 12:29:53 30473 [Note] InnoDB: Memory barrier is not used
2017-12-06 12:29:53 30473 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-12-06 12:29:53 30473 [Note] InnoDB: Using Linux native AIO
2017-12-06 12:29:53 30473 [Note] InnoDB: Using CPU crc32 instructions
2017-12-06 12:29:53 30473 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-12-06 12:29:53 30473 [Note] InnoDB: Completed initialization of buffer pool
2017-12-06 12:29:53 30473 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-06 12:29:53 30473 [Note] InnoDB: The log sequence numbers 189532097 and 189532097 in ibdata files do not match the log sequence number 189715563 in the ib_logfiles!
2017-12-06 12:29:53 30473 [Note] InnoDB: Database was not shutdown normally!
2017-12-06 12:29:53 30473 [Note] InnoDB: Starting crash recovery.
2017-12-06 12:29:53 30473 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-12-06 12:29:53 30473 [Note] InnoDB: Restoring possible half-written data pages
2017-12-06 12:29:53 30473 [Note] InnoDB: from the doublewrite buffer...
2017-12-06 12:29:53 30473 [Note] InnoDB: 128 rollback segment(s) are active.
2017-12-06 12:29:53 30473 [Note] InnoDB: Waiting for purge to start
2017-12-06 12:29:53 30473 [Note] InnoDB: 5.6.37 started; log sequence number 189715563
2017-12-06 12:29:53 30473 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2017-12-06 12:29:53 30473 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2017-12-06 12:29:53 30473 [Note] Server hostname (bind-address): '*'; port: 3306
2017-12-06 12:29:53 30473 [Note] IPv6 is available.
2017-12-06 12:29:53 30473 [Note] - '::' resolves to '::';
2017-12-06 12:29:53 30473 [Note] Server socket created on IP: '::'.
2017-12-06 12:29:53 30473 [Note] Event Scheduler: Loaded 0 events
2017-12-06 12:29:53 30473 [Note] /usr/libexec/mysql56/mysqld: ready for connections. Version: '5.6.37' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
I have read that some insisted that 1GB of memory is not enough for wordpress. But I also have read that some says 1GB is sufficient and in fact they only uses less than 800MB for their memory, some even have less than 600MB and their site is running fine. So I am not too convince I need more than 1GB of memory.
I have created swap file for the memory and when I do
free -m
, it shows the following:
total used free shared buffers cached
Mem: 993 856 137 0 8 51
-/+ buffers/cache: 796 196
Swap: 1023 338 685
Just for your information, my website is a really really low traffic ones with average of less than 10 clicks per day, in fact, maximum would be not more than 20 clicks per day or minimum 0 clicks per day. No transactions, just purely browsing sort of website and a simple (5 fields) enquiry form.
Can anyone help to pinpoint why there is no specific error in the log file?
Is there a way to find out what is wrong with mysql?
Any help would be much appreciated. Thank you.
New findings in /var/log/message, I saw this in the file. Can I confirm if this means I need more memory to my ec2 instance? But why? as mentioned earlier, it is a very low traffic wordpress site. Can anyone explain?
Dec 6 12:26:31 ip-172-31-29-103 kernel: [939492.654915] Out of memory: Kill process 25961 (mysqld) score 222 or sacrifice child
Dec 6 12:26:31 ip-172-31-29-103 kernel: [939492.658743] Killed process 25961 (mysqld) total-vm:1336104kB, anon-rss:4676kB, file-rss:0kB, shmem-rss:0kB
Dec 6 12:26:31 ip-172-31-29-103 kernel: [939493.023492] oom_reaper: reaped process 25961 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
Dec 6 12:26:38 ip-172-31-29-103 kernel: [939499.813569] mysqld invoked oom-killer: gfp_mask=0x24280ca(GFP_HIGHUSER_MOVABLE|__GFP_ZERO), nodemask=0, order=0, oom_score_adj=0
Dec 6 12:26:38 ip-172-31-29-103 kernel: [939499.820065] mysqld cpuset=/ mems_allowed=0
Dec 6 12:26:38 ip-172-31-29-103 kernel: [939499.822758] CPU: 0 PID: 30069 Comm: mysqld Tainted: G E 4.9.38-16.33.amzn1.x86_64 #1
Dec 6 12:26:38 ip-172-31-29-103 kernel: [939499.825371] Hardware name: Xen HVM domU, BIOS 4.2.amazon 08/24/2006
Dec 6 12:26:38 ip-172-31-29-103 kernel: [939499.825371] ffffc90001dd7b58 ffffffff812fa84f ffffc90001dd7cf8 ffff88000d998000 Dec 6 12:26:38 ip-172-31-29-103 kernel: [939499.825371] ffffc90001dd7be8 ffffffff811f4d5b ffffc90000000000 0000000000000000