---
id: 759ba9a5-f7ae-4fcf-9f3f-29cf371abafe
---

# MySQL slow queries incident
---

A MySQL slow queries incident is when a MySQL server is experiencing slow queries. This can result in degraded performance and responsiveness of the system. It is important to resolve this incident quickly to minimize impact on users.

### Parameters
```shell
# Environment Variables

export USERNAME="PLACEHOLDER"

export PASSWORD="PLACEHOLDER"

export CLIENT_IP_ADDRESS="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

```

## Debug

### 1. Check the overall CPU and memory usage of the server
```shell
top
```

### 2. Check the MySQL process list to identify any long-running queries
```shell
mysql -u ${USERNAME} -p${PASSWORD} -e "SHOW FULL PROCESSLIST;"
```

### 3. Check the MySQL slow query log file for any recent entries
```shell
sudo tail -n 100 /var/log/mysql/mysql-slow.log
```

### 4. Check the MySQL configuration file for any misconfigurations
```shell
cat /etc/mysql/my.cnf
```

### 5. Check the disk usage of the server to ensure there is enough space
```shell
df -h
```

### 6. Check the system logs for any relevant errors or warnings
```shell
sudo tail -n 100 /var/log/syslog
```

### 7. Check the network connectivity between the server and client
```shell
ping ${CLIENT_IP_ADDRESS}
```

### 8. Check the status of the MySQL service
```shell
sudo systemctl status mysql.service
```

### Lack of appropriate indexes on database tables causing slow query performance.
```shell

#!/bin/bash

# Define variables

DB_NAME=${DATABASE_NAME}

DB_USER=${USERNAME}

DB_PASSWORD=${PASSWORD}

SLOW_QUERY="PLACEHOLDER"

# Connect to the database and run EXPLAIN on a sample slow query

mysql -u $DB_USER -p$DB_PASSWORD -e "USE $DB_NAME; EXPLAIN ${SLOW_QUERY}"

# Check output of EXPLAIN to see if any indexes are missing

if [[ $(mysql -u $DB_USER -p$DB_PASSWORD -e "USE $DB_NAME; EXPLAIN ${SLOW_QUERY}" | grep 'Using where') ]]; then

    echo "Query is using WHERE and no indexes are being used."

    echo "Add appropriate indexes to improve query performance."

else

    echo "Query is using indexes appropriately."

fi

```

## Repair

### Set MySQL credentials
```shell
DB_USER=${USERNAME}

DB_PASS=${PASSWORD}
```
### Connect to MySQL and optimize tables
```shell
mysql -u $DB_USER -p$DB_PASS -e "USE ${DATABASE_NAME}; OPTIMIZE TABLE ${TABLE_NAME};"
```

### Increase the number of database connections to handle more traffic and reduce the likelihood of slow queries.
```shell

#!/bin/bash

# Set variables

MAX_CONNECTIONS="PLACEHOLDER"

# Increase maximum connections in MySQL config file

sudo sed -i "s/max_connections=.*/max_connections=$MAX_CONNECTIONS/" /etc/mysql/my.cnf

# Restart MySQL service

sudo systemctl restart mysql

# Verify the change

mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -e "SHOW VARIABLES LIKE 'max_connections'" $DB_NAME


```

A MySQL slow queries incident is when a MySQL server is experiencing slow queries. This can result in degraded performance and responsiveness of the system. It is important to resolve this incident quickly to minimize impact on users.


This incident type refers to a situation where the Tomcat server's JDBC connection pool usage surpasses its configured limits. This can lead to a shortage of available connections for incoming requests, resulting in slow response times or even server crashes. It is important to monitor and manage the JDBC connection pool usage to prevent such incidents from occurring.


Tomcat High JDBC Connection Pool Usage Incident.

This incident type refers to a scenario where there is a high rate of statement timeouts in a Postgresql database instance. This can lead to degraded performance and potentially impact the availability of the database. It is important to quickly identify and address the underlying cause of the timeouts to ensure the stability of the system.


Postgresql high rate statement timeout incident.

The Low Postgresql commit rate incident is triggered when the number of transactions processed by Postgresql drops below a certain threshold, indicating a potential issue with the database's performance. This could be caused by a variety of factors, such as high load or inadequate resources. The incident requires investigation and resolution to ensure the database is functioning properly.


Low Postgresql commit rate incident

This incident type refers to a sudden and abnormal drop in the SELECT query rate on a MySQL server. This can potentially cause disruption in the service and affect the user experience. The cause of the drop could be due to a variety of factors such as database issues, server overload, or network problems. It requires immediate attention and investigation to identify the root cause and resolve the issue to prevent further impact.


Unusual drop in SELECT query rate on MySQL server

This incident type occurs when the number of concurrent connections to a MySQL database server exceeds the configured limit of 80%. This can lead to performance degradation or even database server crashes, making the application or system that relies on the database server unavailable for the end-users. This type of incident typically requires immediate attention and resolution to restore normal operation of the affected service.


MySQL too many connections incident

```shell
# Environment Variables

export USERNAME="PLACEHOLDER"

export PASSWORD="PLACEHOLDER"

export CLIENT_IP_ADDRESS="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

```


### 1. Check the overall CPU and memory usage of the server

```shell
top
```

### 2. Check the MySQL process list to identify any long-running queries

```shell
mysql -u ${USERNAME} -p${PASSWORD} -e "SHOW FULL PROCESSLIST;"
```

### 3. Check the MySQL slow query log file for any recent entries

```shell
sudo tail -n 100 /var/log/mysql/mysql-slow.log
```

### 4. Check the MySQL configuration file for any misconfigurations

```shell
cat /etc/mysql/my.cnf
```

### 5. Check the disk usage of the server to ensure there is enough space

```shell
df -h
```

### 6. Check the system logs for any relevant errors or warnings

```shell
sudo tail -n 100 /var/log/syslog
```

### 7. Check the network connectivity between the server and client

```shell
ping ${CLIENT_IP_ADDRESS}
```

### 8. Check the status of the MySQL service

```shell
sudo systemctl status mysql.service
```

### Lack of appropriate indexes on database tables causing slow query performance.

```shell

#!/bin/bash

# Define variables

DB_NAME=${DATABASE_NAME}

DB_USER=${USERNAME}

DB_PASSWORD=${PASSWORD}

SLOW_QUERY="PLACEHOLDER"

# Connect to the database and run EXPLAIN on a sample slow query

mysql -u $DB_USER -p$DB_PASSWORD -e "USE $DB_NAME; EXPLAIN ${SLOW_QUERY}"

# Check output of EXPLAIN to see if any indexes are missing

if [[ $(mysql -u $DB_USER -p$DB_PASSWORD -e "USE $DB_NAME; EXPLAIN ${SLOW_QUERY}" | grep 'Using where') ]]; then

    echo "Query is using WHERE and no indexes are being used."

    echo "Add appropriate indexes to improve query performance."

else

    echo "Query is using indexes appropriately."

fi

```


### Set MySQL credentials

```shell
DB_USER=${USERNAME}

DB_PASS=${PASSWORD}
```

### Connect to MySQL and optimize tables

```shell
mysql -u $DB_USER -p$DB_PASS -e "USE ${DATABASE_NAME}; OPTIMIZE TABLE ${TABLE_NAME};"
```

### Increase the number of database connections to handle more traffic and reduce the likelihood of slow queries.

```shell

#!/bin/bash

# Set variables

MAX_CONNECTIONS="PLACEHOLDER"

# Increase maximum connections in MySQL config file

sudo sed -i "s/max_connections=.*/max_connections=$MAX_CONNECTIONS/" /etc/mysql/my.cnf

# Restart MySQL service

sudo systemctl restart mysql

# Verify the change

mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -e "SHOW VARIABLES LIKE 'max_connections'" $DB_NAME


```


MySQL slow queries incident

Overview

Parameters

Debug

1. Check the overall CPU and memory usage of the server

2. Check the MySQL process list to identify any long-running queries

3. Check the MySQL slow query log file for any recent entries

4. Check the MySQL configuration file for any misconfigurations

5. Check the disk usage of the server to ensure there is enough space

6. Check the system logs for any relevant errors or warnings

7. Check the network connectivity between the server and client

8. Check the status of the MySQL service

Lack of appropriate indexes on database tables causing slow query performance.

Repair

Set MySQL credentials

Connect to MySQL and optimize tables

Increase the number of database connections to handle more traffic and reduce the likelihood of slow queries.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

Postgresql high rate statement timeout incident.

Low Postgresql commit rate incident

Unusual drop in SELECT query rate on MySQL server

Support