Overview

Database issues can affect the performance and functionality of applications. These issues may manifest as errors in monitoring scripts, issues with connections or replication, or slow queries. It's crucial to know how to debug these issues to maintain optimal performance.

Addressing database issues

In Engine Yard, collectd is tasked with monitoring the DB for issues with multiple parameters. This means that problems will be reported through the use of the monitoring script under /engineyard/bin. The monitored variables include the number of connections, replication status, etc. In the case of PostgreSQL, the vacuum will also be monitored here. 

Initial steps

As a rule of thumb, any DB issue can begin to be troubleshoot with the following steps:

1. Collect information by running bro info.  The DB engine and version will be listed under "db stack".
2. Check the error logs corresponding to the DB engine, as detailed in the Database Instance Cheat Sheet. These will often point to slow queries or connectivity issues. 
3. Additionally, you can use the command free -m to check for swap usage. Note that swap usage means the DB is not running optimally.

Note: Engine Yard Cloud will create a schema per application, and ensure that backups happen for it. If a customer creates extra schemas, this will be outside of the scope of Support. The same applies for RDS DBs.

Issues with slow queries

Once an issue is considered to be related to slow queries, assuming you have identified the slow queries causing the problem in the logs, you should proceed with their analysis by running the following commands:

• MySQL: 

  mysql
  use <app_name>
  explain <query>

• PostgreSQL: 

  psql
  use <app_name>
  explain analyze <query>

While checking the used keys and indices may be helpful, it will often be the case that the issue is caused by the application using the database, which will need to be simplified. Alternatively, it could be that the database instance does not meet the requirements in terms of size, and needs to be upgraded.

Issues with the number of connections

To address issues where the number of connections is reaching the limit, we should identify the origin of these excess connections. The following commands will assist with this task when run from the DB instance:

• netstat -tup | grep -i postg: Identify connections to a PostgreSQL database.
• netstat -tupn | grep -i 5432: Identify connections made to port 5432 (PostgreSQL), by their originating IP.
• netstat -tup | grep -i mysql: Identify connections to a MySQL database.
• netstat -tupn | grep -i 3306: Identify connections made to port 3306 (MySQL), by their originating IP.

Summary

Debugging database issues involves checking error logs, swap usage, slow queries, and the number of connections. By following these steps, you can identify and resolve issues affecting your database's performance.