MYSQL Server Crashes frequently on Amazon EC2 - Magento Website
$30-250 NZD
Completed
Posted over 11 years ago
$30-250 NZD
Paid on delivery
We have client Magento site running on ec2 large instance.
My Magento MySQL on EC2 crashes continuously, around every 12 hours. This happens on the live site. The site has more than 30000 products. The crashes started after we migrated from shared hosting to ec2. The continuous crashes are related to Innodb.
Database size is around 650 MB. We migrated the data by using mysqldump command and restored it in EC2.
Server environment : Php-fpm, Nginx , Mysqld , Varnish
Server Config:
Linux 64 bit
RAM 7GB
100 GB space
When MySQL crashes, the following is written to the log file /var/log/[login to view URL]
121219 02:54:18 mysqld_safe Number of processes running now: 0
121219 02:54:19 mysqld_safe mysqld restarted
121219 2:54:21 [Note] Plugin 'FEDERATED' is disabled.
121219 2:54:21 InnoDB: The InnoDB memory heap is disabled
121219 2:54:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121219 2:54:21 InnoDB: Compressed tables use zlib 1.2.5
121219 2:54:21 InnoDB: Using Linux native AIO
121219 2:54:25 InnoDB: Initializing buffer pool, size = 3.0G
InnoDB: mmap(3296722944 bytes) failed; errno 12
121219 2:54:25 InnoDB: Completed initialization of buffer pool
121219 2:54:25 InnoDB: Fatal error: cannot allocate memory for the buffer pool
121219 2:54:25 [ERROR] Plugin 'InnoDB' init function returned error.
121219 2:54:25 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
121219 2:54:25 [ERROR] Failed to initialize plugins.
121219 2:54:25 [ERROR] Aborting
121219 2:54:25 [Note] /usr/libexec/mysqld: Shutdown complete
121219 02:54:25 mysqld_safe mysqld from pid file /var/run/mysqld/[login to view URL] ended
When we check status before restart MySQL it shows the following output:
“mysqld dead but subsys locked.”
In these cases, the only thing we have to restart the server, after which MySQL can be restarted.
In these cases, the output looks like this following:
121219 04:19:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121219 4:19:32 [Note] Plugin 'FEDERATED' is disabled.
121219 4:19:32 InnoDB: The InnoDB memory heap is disabled
121219 4:19:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121219 4:19:32 InnoDB: Compressed tables use zlib 1.2.5
121219 4:19:32 InnoDB: Using Linux native AIO
121219 4:19:32 InnoDB: Initializing buffer pool, size = 3.0G
121219 4:19:33 InnoDB: Completed initialization of buffer pool
121219 4:19:33 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 3185919093
121219 4:19:33 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3185974511
I have full file and [login to view URL] config attached to understand more about this issue.
Any queries contact me on PMB and this requires to fix ASAP.
The problem is that mysql cannot allocate the memory that it needs for your innodb engine to operate decently. I would suggest that we utilize dedicted mysql instance which can even be medium instead of a large instance for both application and mysql together. Or multiple small instances as a multi master cluster with very little or no query cache and highly optimized read writes. If this is not affordable, we should consider limiting the application server such that the memory usage does not go beyond a limit to crush the mysql server.
Your problem is easily fixed. It has to do with your InnoDB and MySQL config settings, and potentially one other issue, Which i will ask in PMB. With access to server, I can resolve in less than a day. I am an experienced linux/mysql admin with over 10 years of experience.