---
id: 1d603de3-6906-46a7-b8f8-dea29eaca529
---
# Unusual drop in SELECT query rate on MySQL server
---

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.

### Parameters
```shell
# Environment Variables

export MYSQL_SERVER_IP="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USERNAME="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export INDEX_NAME="PLACEHOLDER"

export CACHED_MEMORY_ALLOCATION="PLACEHOLDER"

export QUERY_TIMEOUT="PLACEHOLDER"
```

## Debug

### Check MySQL service status
```shell
systemctl status mysql.service
```

### Check MySQL error log
```shell
tail -n 100 /var/log/mysql/error.log
```

### Check server CPU usage
```shell
top
```

### Check server memory usage
```shell
free -h
```

### Check network connectivity to MySQL server
```shell
ping ${MYSQL_SERVER_IP}
```

### Check MySQL query rate
```shell
mysqladmin -u root -p status | grep 'Queries'
```

### Database issues, such as corrupt tables or indexes, could cause queries to fail or take longer to complete.
```shell
bash

#!/bin/bash



# Step 1: Check for corrupt tables or indexes

corrupt_tables=$(mysql -u ${USERNAME} -p${PASSWORD} -e "CHECK TABLE ${DATABASE_NAME}.*" | grep -i "error" | awk '{print $1}')



if [ -n "$corrupt_tables" ]; then

  echo "Corrupt tables found: $corrupt_tables"

  

  # Step 2: Attempt to repair corrupt tables or indexes

  mysql -u ${USERNAME} -p${PASSWORD} -e "REPAIR TABLE $corrupt_tables"

  

  # Check if repair was successful

  if [ $? -eq 0 ]; then

    echo "Tables repaired successfully"

  else

    # Step 3: If repair is not possible, restore from backup

    echo "Repair failed, restoring from backup"

    # Add command to restore from backup

  fi

else

  echo "No corrupt tables found"

fi


```

## Repair

### Check CPU usage
```shell
CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *$[0-9.]*$%* id.*/\1/" | awk '{print 100 - $1}')

if (( $(echo "$CPU_USAGE > $CPU_THRESHOLD" | bc -l) )); then

  echo "High CPU usage detected: $CPU_USAGE%"

  # Perform remediation actions, such as restarting the database service

  systemctl restart mysql

fi
```

### Optimize the database configuration and server settings to improve performance and reduce the likelihood of future SELECT query rate drops. This can include adjusting cached memory allocation, tuning query timeouts, and optimizing indexes.
```shell


#!/bin/bash



# Set the ${DATABASE_NAME} variable to the name of the database to be optimized

database_name=${DATABASE_NAME}



# Set the ${CACHED_MEMORY_ALLOCATION} variable to the desired amount of memory to allocate for caching

cached_memory_allocation=${CACHED_MEMORY_ALLOCATION}



# Set the ${QUERY_TIMEOUT} variable to the desired amount of time in seconds for query timeouts

query_timeout=${QUERY_TIMEOUT}



# Set the ${INDEX_NAME} variable to the name of the index to be optimized

index_name=${INDEX_NAME}



# Optimize the server settings

mysql -u root -p -e "SET GLOBAL query_cache_size = $cached_memory_allocation;"

mysql -u root -p -e "SET GLOBAL query_cache_type = ON;"

mysql -u root -p -e "SET GLOBAL wait_timeout = $query_timeout;"



# Optimize the database configuration

mysql -u root -p -e "ALTER TABLE $database_name.$index_name ENGINE=InnoDB;"

mysql -u root -p -e "ANALYZE TABLE $database_name.$index_name;"



# Restart the MySQL server to apply the changes

systemctl restart mysql


```


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.


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 an increase in the number of errors per second on a Tomcat server, which could indicate an issue with the server itself, the host, a deployed application, or an application servlet. This could include errors generated when the Tomcat server runs out of memory, can't find a requested file or servlet, or is unable to serve a JSP due to syntax errors in the servlet codebase. This incident type requires immediate attention to diagnose and address the underlying issue.


Increase of the errors/second rate for Tomcat server

A Redis instance down incident refers to a situation where the Redis server, which is a popular open-source in-memory data structure store, is not functioning or accessible. This can cause interruption in the service and impact the performance of applications that rely on Redis for caching or data storage. Typically, this type of incident is considered high urgency and requires immediate attention from the responsible team to investigate, diagnose, and resolve the issue.


Redis instance down incident

This incident type refers to a situation where the Postgresql database has a high rollback rate, which means that a high percentage of transactions are being aborted compared to the committed ones. This can cause issues with data consistency and performance, and may require investigation and resolution by the responsible team. The incident details may include information about the affected database instance, the service or system impacted, the urgency level, and any related alerts or escalations.


Postgresql high rollback rate 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.

```shell
# Environment Variables

export MYSQL_SERVER_IP="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USERNAME="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export INDEX_NAME="PLACEHOLDER"

export CACHED_MEMORY_ALLOCATION="PLACEHOLDER"

export QUERY_TIMEOUT="PLACEHOLDER"
```


### Check MySQL service status

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

### Check MySQL error log

```shell
tail -n 100 /var/log/mysql/error.log
```

### Check server CPU usage

```shell
top
```

### Check server memory usage

```shell
free -h
```

### Check network connectivity to MySQL server

```shell
ping ${MYSQL_SERVER_IP}
```

### Check MySQL query rate

```shell
mysqladmin -u root -p status | grep 'Queries'
```

### Database issues, such as corrupt tables or indexes, could cause queries to fail or take longer to complete.

```shell
bash

#!/bin/bash



# Step 1: Check for corrupt tables or indexes

corrupt_tables=$(mysql -u ${USERNAME} -p${PASSWORD} -e "CHECK TABLE ${DATABASE_NAME}.*" | grep -i "error" | awk '{print $1}')



if [ -n "$corrupt_tables" ]; then

  echo "Corrupt tables found: $corrupt_tables"

  

  # Step 2: Attempt to repair corrupt tables or indexes

  mysql -u ${USERNAME} -p${PASSWORD} -e "REPAIR TABLE $corrupt_tables"

  

  # Check if repair was successful

  if [ $? -eq 0 ]; then

    echo "Tables repaired successfully"

  else

    # Step 3: If repair is not possible, restore from backup

    echo "Repair failed, restoring from backup"

    # Add command to restore from backup

  fi

else

  echo "No corrupt tables found"

fi


```


### Check CPU usage

```shell
CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *$[0-9.]*$%* id.*/\1/" | awk '{print 100 - $1}')

if (( $(echo "$CPU_USAGE > $CPU_THRESHOLD" | bc -l) )); then

  echo "High CPU usage detected: $CPU_USAGE%"

  # Perform remediation actions, such as restarting the database service

  systemctl restart mysql

fi
```

### Optimize the database configuration and server settings to improve performance and reduce the likelihood of future SELECT query rate drops. This can include adjusting cached memory allocation, tuning query timeouts, and optimizing indexes.

```shell


#!/bin/bash



# Set the ${DATABASE_NAME} variable to the name of the database to be optimized

database_name=${DATABASE_NAME}



# Set the ${CACHED_MEMORY_ALLOCATION} variable to the desired amount of memory to allocate for caching

cached_memory_allocation=${CACHED_MEMORY_ALLOCATION}



# Set the ${QUERY_TIMEOUT} variable to the desired amount of time in seconds for query timeouts

query_timeout=${QUERY_TIMEOUT}



# Set the ${INDEX_NAME} variable to the name of the index to be optimized

index_name=${INDEX_NAME}



# Optimize the server settings

mysql -u root -p -e "SET GLOBAL query_cache_size = $cached_memory_allocation;"

mysql -u root -p -e "SET GLOBAL query_cache_type = ON;"

mysql -u root -p -e "SET GLOBAL wait_timeout = $query_timeout;"



# Optimize the database configuration

mysql -u root -p -e "ALTER TABLE $database_name.$index_name ENGINE=InnoDB;"

mysql -u root -p -e "ANALYZE TABLE $database_name.$index_name;"



# Restart the MySQL server to apply the changes

systemctl restart mysql


```


Unusual drop in SELECT query rate on MySQL server

Overview

Parameters

Debug

Check MySQL service status

Check MySQL error log

Check server CPU usage

Check server memory usage

Check network connectivity to MySQL server

Check MySQL query rate

Database issues, such as corrupt tables or indexes, could cause queries to fail or take longer to complete.

Repair

Check CPU usage

Optimize the database configuration and server settings to improve performance and reduce the likelihood of future SELECT query rate drops. This can include adjusting cached memory allocation, tuning query timeouts, and optimizing indexes.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

Increase of the errors/second rate for Tomcat server

Redis instance down incident

Postgresql high rollback rate incident

Support