Mysql – thesql on ec2 linux AMI crashed occasionally

amazon ec2MySQL

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

Best Answer

MySQL stopped working because your system ran out of memory and killed the process. At the time MySQL was terminated it was using 1,336,104 KB. This is more memory than a T2.micro.

Your problem is very clearly memory usage:

  • Your sever is running out of RAM as stated in your logfiles
  • You are using 338 MB of swap space which also means low memory.
  • For a system with such low usage, you should not be using swap space at all.

Your solution is to add more memory to your server by switching to a larger instance size. I am one of those people that find T2.micro just too small for WordPress and a database.