Stuck with a MySQL crash? We can help you.
Although easy to use and supports in all operating systems, MySQL crashes are common in many websites due to resource limits, database errors, etc.
As part of our Server Management Services, we assist our customers with several MySQL queries.
Today, let us discuss the MySQL crash.
What causes a MySQL crash?
Before proceeding on how to fix MySQL high memory usage errors, let’s take a look at the causes:
- Resource limits
- MyISAM corruption
- Large log size
- InnoDB corruption
- File system errors
How to recover from a crash?
Depending on what cause the memory bottleneck, the solution vary. However, our Support Techs explains top resolutions for MySQL high memory usage.
- Recover crashed InnoDB database
Removing redo logs should only be considered after a clean shutdown, with innodb_fast_shutdown set to 0 or 1. To avoid resource contention, even if it is enabled, the value is set to a minimal one of around 10MB.
- Upgrade server resources and optimize the allocation
When a website is under attack, an abnormally high number of connections establish in a short time. We use the “PROCESSLIST” in MySQL to identify the top users and block access to the abusive connections.
- Scan and fix file system errors
Queries that take a long time to execute are identified from the slow-query log. Slow queries would cause more disk reads which require more memory and CPU usage, which in turn affects the server performance.
- Limit log size
If the server constantly resorts to using swap memory, even after optimizing the database settings, we should increase the server RAM.
- Recover MyISAM tables of MySQL default database
We can fix the table using mysqlchk utility.
How to prevent MySQL crash
- Optimize tables regularly using
- Monitor resource usage and optimize the allocation. For instance, buffer size, cache size, etc.
- Use a database cluster such as Percona XtraDB Cluster so that the service won’t become unavailable
- Use fault resilient disks such as RAID so that we’ll have ample warning to replace disks.
[Need further assistance? Feel free to contact us]
To conclude, many issues such as resource limits, database table errors, file system errors, etc. can lead to a MySQL crash. Today, we saw how our Support Techs fix and prevent MySQL downtime.