---
id: 5b84734a-15bd-4c24-bf03-a809f2bb06c4
---

# MySQL Service Out of Space.
---

This incident type refers to cases where the MySQL service, which is responsible for managing and storing data in a database, runs out of available space. This can happen due to a variety of reasons such as an unexpected increase in data volume or insufficient allocation of storage resources. When the MySQL service runs out of space, it can cause data loss, application errors, and even system crashes if not addressed promptly.

### Parameters
```shell
export MYSQL_DATA_DIRECTORY="PLACEHOLDER"

export MYSQL_LOG_DIRECTORY="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USERNAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"
```

## Debug

### Check available disk space
```shell
df -h
```

### Check the size of the MySQL data directory
```shell
du -sh ${MYSQL_DATA_DIRECTORY}
```

### Check the size of MySQL logs
```shell
du -sh ${MYSQL_LOG_DIRECTORY}
```

### Identify the largest tables in the MySQL database
```shell
mysql -u ${DATABASE_USERNAME} -p -e "SELECT table_schema as 'Database', table_name AS 'Table', ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;"
```

### Identify the largest databases in the MySQL instance
```shell
mysql -u ${DATABASE_USERNAME} -p -e "SELECT table_schema 'Database Name', SUM(data_length + index_length)/1024/1024 'Database Size (MB)' FROM information_schema.tables GROUP BY table_schema;"
```

### Check if any long-running queries are causing the issue
```shell
mysql -u ${DATABASE_USERNAME} -p -e "SHOW FULL PROCESSLIST;"
```

### The database is not being properly optimized, leading to inefficiencies and excessive use of storage space.
```shell


#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USERNAME}

DB_PASS=${DATABASE_PASSWORD}



# Check database size

DB_SIZE=$(mysql -u $DB_USER -p$DB_PASS -s -N -e "SELECT SUM(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='$DB_NAME';")

echo "Current size of database $DB_NAME is $DB_SIZE MB."



# Check for inefficient queries

SLOW_QUERIES=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW VARIABLES LIKE 'slow_query_log';" | awk '{print $2}')

if [ "$SLOW_QUERIES" == "ON" ]; then

  echo "Slow query logging is enabled. Checking for inefficient queries..."

  LOG_FILE=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW VARIABLES LIKE 'slow_query_log_file';" | awk '{print $2}')

  sudo mysqldumpslow -s t $LOG_FILE

else

  echo "Slow query logging is not enabled. Consider enabling to identify inefficient queries."

fi



# Check for unused indexes

echo "Checking for unused indexes..."

sudo mysqltuner --optimize --quiet --skipsize --skipsec --skipidx --skipfrag --forcesql --nocolor --recommendations | grep "Remove unused"



# Check for fragmented tables

echo "Checking for fragmented tables..."

sudo mysqlcheck -u $DB_USER -p$DB_PASS --auto-repair --optimize --check --analyze --silent $DB_NAME


```

## Repair

### Optimize the database by removing duplicate or unused data, which will reduce storage requirements.
```shell


#!/bin/bash



# Set the database name

DB_NAME=${DATABASE_NAME}



# Set the table name

TABLE_NAME=${TABLE_NAME}



# Connect to the database

mysql -u ${DATABASE_USERNAME} -p${DATABASE_PASSWORD} $DB_NAME ${< EOF



# Remove duplicate data

DELETE t1 FROM $TABLE_NAME t1

INNER JOIN $TABLE_NAME t2

WHERE t1.id < t2.id

AND t1.data = t2.data;



# Remove unused data

DELETE FROM $TABLE_NAME

WHERE {condition_for_unused_data};



# Optimize the table

OPTIMIZE TABLE $TABLE_NAME;



EOF


```

This incident type refers to cases where the MySQL service, which is responsible for managing and storing data in a database, runs out of available space. This can happen due to a variety of reasons such as an unexpected increase in data volume or insufficient allocation of storage resources. When the MySQL service runs out of space, it can cause data loss, application errors, and even system crashes if not addressed promptly.


This incident type refers to a situation where Spark tasks are failing due to out of memory errors. Spark is a distributed computing system used for big data processing. When the data volume exceeds the allocated memory, the Spark tasks fail, and the system generates an out of memory error. This type of incident can cause data processing delays or even system downtime, which can impact the overall performance of the application.


Spark tasks failing due to out of memory errors.

Apache Spark Out of Memory Error is a type of incident that occurs when the Apache Spark application running on a system is unable to allocate enough memory to perform its tasks. This can happen when the system's memory is insufficient or when there is a memory leak in the application's code. When this incident occurs, the application may crash, become unresponsive, or produce incorrect results. It is important to diagnose and resolve this issue as soon as possible to ensure the smooth functioning of the application.


Apache Spark Out of Memory Error incident.

This incident type occurs when the PostgreSQL tablespace of a database has reached its maximum storage capacity. When tablespace is full, it can no longer store any new data and any attempt to insert new data will fail. This can cause disruption in the normal operation of the database and affect the performance of applications that rely on the database.


PostgreSQL tablespace full

This incident type refers to a problem with a PostgreSQL database where it is reporting disk full errors. This means that the database has reached its full capacity and cannot store any more data. This can cause issues with the performance of the database and may result in data loss if not addressed promptly.


PostgreSQL database reporting disk full errors.

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
export MYSQL_DATA_DIRECTORY="PLACEHOLDER"

export MYSQL_LOG_DIRECTORY="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USERNAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"
```


### Check available disk space

```shell
df -h
```

### Check the size of the MySQL data directory

```shell
du -sh ${MYSQL_DATA_DIRECTORY}
```

### Check the size of MySQL logs

```shell
du -sh ${MYSQL_LOG_DIRECTORY}
```

### Identify the largest tables in the MySQL database

```shell
mysql -u ${DATABASE_USERNAME} -p -e "SELECT table_schema as 'Database', table_name AS 'Table', ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;"
```

### Identify the largest databases in the MySQL instance

```shell
mysql -u ${DATABASE_USERNAME} -p -e "SELECT table_schema 'Database Name', SUM(data_length + index_length)/1024/1024 'Database Size (MB)' FROM information_schema.tables GROUP BY table_schema;"
```

### Check if any long-running queries are causing the issue

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

### The database is not being properly optimized, leading to inefficiencies and excessive use of storage space.

```shell


#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USERNAME}

DB_PASS=${DATABASE_PASSWORD}



# Check database size

DB_SIZE=$(mysql -u $DB_USER -p$DB_PASS -s -N -e "SELECT SUM(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='$DB_NAME';")

echo "Current size of database $DB_NAME is $DB_SIZE MB."



# Check for inefficient queries

SLOW_QUERIES=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW VARIABLES LIKE 'slow_query_log';" | awk '{print $2}')

if [ "$SLOW_QUERIES" == "ON" ]; then

  echo "Slow query logging is enabled. Checking for inefficient queries..."

  LOG_FILE=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW VARIABLES LIKE 'slow_query_log_file';" | awk '{print $2}')

  sudo mysqldumpslow -s t $LOG_FILE

else

  echo "Slow query logging is not enabled. Consider enabling to identify inefficient queries."

fi



# Check for unused indexes

echo "Checking for unused indexes..."

sudo mysqltuner --optimize --quiet --skipsize --skipsec --skipidx --skipfrag --forcesql --nocolor --recommendations | grep "Remove unused"



# Check for fragmented tables

echo "Checking for fragmented tables..."

sudo mysqlcheck -u $DB_USER -p$DB_PASS --auto-repair --optimize --check --analyze --silent $DB_NAME


```


### Optimize the database by removing duplicate or unused data, which will reduce storage requirements.

```shell


#!/bin/bash



# Set the database name

DB_NAME=${DATABASE_NAME}



# Set the table name

TABLE_NAME=${TABLE_NAME}



# Connect to the database

mysql -u ${DATABASE_USERNAME} -p${DATABASE_PASSWORD} $DB_NAME ${< EOF



# Remove duplicate data

DELETE t1 FROM $TABLE_NAME t1

INNER JOIN $TABLE_NAME t2

WHERE t1.id < t2.id

AND t1.data = t2.data;



# Remove unused data

DELETE FROM $TABLE_NAME

WHERE {condition_for_unused_data};



# Optimize the table

OPTIMIZE TABLE $TABLE_NAME;



EOF


```


MySQL Service Out of Space.

Overview

Parameters

Debug

Check available disk space

Check the size of the MySQL data directory

Check the size of MySQL logs

Identify the largest tables in the MySQL database

Identify the largest databases in the MySQL instance

Check if any long-running queries are causing the issue

The database is not being properly optimized, leading to inefficiencies and excessive use of storage space.

Repair

Optimize the database by removing duplicate or unused data, which will reduce storage requirements.

Learn more

Related Runbooks

Spark tasks failing due to out of memory errors.

Apache Spark Out of Memory Error incident.

PostgreSQL tablespace full

PostgreSQL database reporting disk full errors.

Support