This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 144 Table [TABLE_NAME] is marked as crashed and last (automatic?) repair failed'
I managed to repair it manually using:
myisamchk -r -f $TABLE_NAME
but of course this is not a long term solution.
I need to understand why is this happening, so I can prevent it from happening again in the future.
- Total database size is 2Gb, 100 tables, 7.5 million rows.
- The table where it happened is the largest in the database: 1.2 million rows, 650Mb.
- Server version: 5.5.50
- ubuntu 14.04.1
- PHP 5.5.9
- I checked and there was enough free disk space. About ram, it was not an issue so far.
- The host where the db reside is c3.xlarge (8Gb ram)
The database sits in the server harddisk. I am thinking about moving it to RDS, I wonder if that would help.
What do you suggest I should do? Any way to analyze mysql and understand where is the problem?
/var/log/mysql/error.log has this in it:
200213 14:57:21 [Warning] /usr/sbin/mysqld: Forcing close of thread 200315105 user: '[user1]' 200213 14:57:47 [Warning] Warning: Optimize table got errno 28 on [TABLENAME], retrying 200213 14:57:47 InnoDB: Starting shutdown... 200213 14:57:49 InnoDB: Shutdown completed; log sequence number 1983956605556
UPDATE - It is out of memory
found this in my syslog
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.270767] Out of memory: Kill process 14232 (mysqld) score 32 or sacrifice child Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.285148] Killed process 14232 (mysqld) total-vm:2447868kB, anon-rss:241432kB, file-rss:0kB Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744020.934420] init: mysql main process (14232) killed by KILL signal
Like I said before, my server is c3.xlarge with 8Gb RAM. Normal utilization is pretty low. I think there was a backup process that was running, maybe it could have caused higher resource utilization.
Any suggestions how to check further?