Steps to diagnose issues with MySQL service going up and down (AWS)

As DevOps engineer you always tend to run into issues related to databases, which is normal, as every application is different therefore it requires proper tunning of database inorder for them to work seemlessly. According to my past experiences one of the most common issue related to databases is with MySQL service going up and down.

The factors leading to this issues can be many, but inorder to recognize the reason for this issue takes systematic and detailed approach.

So then when a MySQL service going up and down for an unknown reason, what are your first steps to diagnose (not solve) the problem on the server?

Here is a list of tasks you should follow inorder to determine the problem:

1. Check version of MySQL and make sure its the latest stable version
2. Check error logs (/var/log/mysqld/) for any errors or warnings
3. Consider all the system logs – /var/log/messages or /var/log/syslog – the master logs in there may give more of a clue if something specifically is restarting MySQL.
4. Check if the database stored on a raid array, single drive. It could be a bottleneck issue.
5. Check the slow query log to find which queries need work to improve their performance
6. Check if any query that’s locking tables
7. Check access logs for any malicious IPs
8. Check Memory utilization and CPU utilization for any memory leakage issues
9. Check number of connections using netstat on application side for any thread pooling related issues.
   • Command to do the same : netstat -ton | grep CLOSE_WAIT 
10. Check number of connection in CLOSE_WAIT state, whether MySQL connections are not being closed properly by application
11. Check MySQL innodb configs (max_connections, logs size, packet size etc) in my.cnf
12. Login in MySQL via check following things:
    • Check number of threads:
      • mysql> show status where `variable_name` = 'Threads_connected';
    • Check number of aborted clients:
      • mysql> show status where `variable_name` = 'aborted';
    • Check number of aborted connects
    • Check max_connections/ max_used_connections
      • mysql> show status where `variable_name` = 'max_used_connections';

Following above tasks should help you figure out the issue of MySQL service going up and down. If you find any error codes the you can refer documentation available at https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

Let me know if any issues related to MySQL, I’ll be happy to help. You can comment below or drop a mail at mail@bhargavamin.com

-Bhargav