---
id: 8b77d536-01bb-41bf-a67e-6274ee143b52
---

# MySQL InnoDB log waits incident.
---

This incident type refers to an issue where MySQL InnoDB log writes are stalling. It may result in poor database performance, slow queries, and even server crashes in severe cases. The incident can be triggered by various factors such as high server load, large transaction logs, and insufficient disk space. It requires immediate attention and resolution to prevent any further impact on the system.

### Parameters
```shell
# Environment Variables

export PATH_TO_INNODB_LOG_FILE="PLACEHOLDER"

export PATH_TO_MYSQL_LOG="PLACEHOLDER"

export BUFFER_SIZE="PLACEHOLDER"

export MY_CNF="PLACEHOLDER"


```

## Debug

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

### Check disk usage
```shell
df -h
```

### Check InnoDB status
```shell
mysql -e "SHOW ENGINE INNODB STATUS\G"
```

### Check InnoDB log file size
```shell
ls -lh ${PATH_TO_INNODB_LOG_FILE}
```

### Check for slow queries
```shell
grep -i "slow" ${PATH_TO_MYSQL_LOG}
```

### Check for errors in MySQL log file
```shell
grep -i "error" ${PATH_TO_MYSQL_LOG}
```

### Check MySQL connections
```shell
netstat -anp | grep mysql
```

### Check server load
```shell
top
```

## Repair

### Set the <buffer_size> parameter to the desired size in bytes
```shell
buffer_size=${BUFFER_SIZE}
```

### Set the <my.cnf> file path to the MySQL configuration file
```shell
my_cnf=${MY_CNF}
```

### Find the transaction log buffer section in the configuration file
```shell
buffer_section=$(grep -n "innodb_log_buffer_size" $my_cnf | cut -d: -f1)
```

### If the buffer section is found, update the buffer size
```shell
if [ ! -z "$buffer_section" ]; then

    sed -i "${buffer_section}s/.*/innodb_log_buffer_size=$buffer_size/" $my_cnf

    echo "Transaction log buffer size updated to $buffer_size bytes"

else

    echo "Transaction log buffer section not found in $my_cnf"

fi
```

### Tune the server settings to allocate more resources to the database and reduce contention for system resources.
```shell
bash

#!/bin/bash


 NEW_FLUSH_LOG_VALUE="PLACEHOLDER"

 NEW_BUFFER_POOL_SIZE="PLACEHOLDER"

 NEW_WRITE_IO_THREADS="PLACEHOLDER"

 MYSQL_CONFIG_FILE_PATH="PLACEHOLDER"

 NEW_LOG_BUFFER_SIZE="PLACEHOLDER"


# Set the MySQL configuration file path

CONFIG_FILE=${MYSQL_CONFIG_FILE_PATH}



# Set the server settings to allocate more resources to the database

sed -i 's/innodb_buffer_pool_size=.*/innodb_buffer_pool_size=${NEW_BUFFER_POOL_SIZE}/' $CONFIG_FILE

sed -i 's/innodb_log_buffer_size=.*/innodb_log_buffer_size=${NEW_LOG_BUFFER_SIZE}/' $CONFIG_FILE

sed -i 's/innodb_flush_log_at_trx_commit=.*/innodb_flush_log_at_trx_commit=${NEW_FLUSH_LOG_VALUE}/' $CONFIG_FILE

sed -i 's/innodb_write_io_threads=.*/innodb_write_io_threads=${NEW_WRITE_IO_THREADS}/' $CONFIG_FILE



# Restart the MySQL service

systemctl restart mysql


```

This incident type refers to an issue where MySQL InnoDB log writes are stalling. It may result in poor database performance, slow queries, and even server crashes in severe cases. The incident can be triggered by various factors such as high server load, large transaction logs, and insufficient disk space. It requires immediate attention and resolution to prevent any further impact on the system.


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 involves a high number of processes being blocked in SQLServer. It may cause delays or errors in database operations and can impact the performance of the system.


High number of processes blocked in SQLServer

This incident type refers to the occurrence of deadlocks in a PostgreSQL database. A deadlock happens when two or more transactions are waiting for each other to release locks on resources, resulting in a situation where none of the transactions can proceed. This can cause significant disruption to the database and the applications that rely on it, as it prevents any further progress until the deadlock is resolved.


Postgresql Deadlocks 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 PATH_TO_INNODB_LOG_FILE="PLACEHOLDER"

export PATH_TO_MYSQL_LOG="PLACEHOLDER"

export BUFFER_SIZE="PLACEHOLDER"

export MY_CNF="PLACEHOLDER"


```


### Check MySQL status

```shell
systemctl status mysql
```

### Check disk usage

```shell
df -h
```

### Check InnoDB status

```shell
mysql -e "SHOW ENGINE INNODB STATUS\G"
```

### Check InnoDB log file size

```shell
ls -lh ${PATH_TO_INNODB_LOG_FILE}
```

### Check for slow queries

```shell
grep -i "slow" ${PATH_TO_MYSQL_LOG}
```

### Check for errors in MySQL log file

```shell
grep -i "error" ${PATH_TO_MYSQL_LOG}
```

### Check MySQL connections

```shell
netstat -anp | grep mysql
```

### Check server load

```shell
top
```


### Set the \<buffer\_size> parameter to the desired size in bytes

```shell
buffer_size=${BUFFER_SIZE}
```

### Set the \<my.cnf> file path to the MySQL configuration file

```shell
my_cnf=${MY_CNF}
```

### Find the transaction log buffer section in the configuration file

```shell
buffer_section=$(grep -n "innodb_log_buffer_size" $my_cnf | cut -d: -f1)
```

### If the buffer section is found, update the buffer size

```shell
if [ ! -z "$buffer_section" ]; then

    sed -i "${buffer_section}s/.*/innodb_log_buffer_size=$buffer_size/" $my_cnf

    echo "Transaction log buffer size updated to $buffer_size bytes"

else

    echo "Transaction log buffer section not found in $my_cnf"

fi
```

### Tune the server settings to allocate more resources to the database and reduce contention for system resources.

```shell
bash

#!/bin/bash


 NEW_FLUSH_LOG_VALUE="PLACEHOLDER"

 NEW_BUFFER_POOL_SIZE="PLACEHOLDER"

 NEW_WRITE_IO_THREADS="PLACEHOLDER"

 MYSQL_CONFIG_FILE_PATH="PLACEHOLDER"

 NEW_LOG_BUFFER_SIZE="PLACEHOLDER"


# Set the MySQL configuration file path

CONFIG_FILE=${MYSQL_CONFIG_FILE_PATH}



# Set the server settings to allocate more resources to the database

sed -i 's/innodb_buffer_pool_size=.*/innodb_buffer_pool_size=${NEW_BUFFER_POOL_SIZE}/' $CONFIG_FILE

sed -i 's/innodb_log_buffer_size=.*/innodb_log_buffer_size=${NEW_LOG_BUFFER_SIZE}/' $CONFIG_FILE

sed -i 's/innodb_flush_log_at_trx_commit=.*/innodb_flush_log_at_trx_commit=${NEW_FLUSH_LOG_VALUE}/' $CONFIG_FILE

sed -i 's/innodb_write_io_threads=.*/innodb_write_io_threads=${NEW_WRITE_IO_THREADS}/' $CONFIG_FILE



# Restart the MySQL service

systemctl restart mysql


```


MySQL InnoDB log waits incident.

Overview

Parameters

Debug

Check MySQL status

Check disk usage

Check InnoDB status

Check InnoDB log file size

Check for slow queries

Check for errors in MySQL log file

Check MySQL connections

Check server load

Repair

Set the <buffer_size> parameter to the desired size in bytes

Set the <my.cnf> file path to the MySQL configuration file

Find the transaction log buffer section in the configuration file

If the buffer section is found, update the buffer size

Tune the server settings to allocate more resources to the database and reduce contention for system resources.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

High number of processes blocked in SQLServer

Postgresql Deadlocks Incident

Unusual drop in SELECT query rate on MySQL server

Support