Unveiling the Mystery: How the `DESCRIBE` Command Can Bring Down a Server

Introduction

For the past eight months, our team has been on a detective mission. A mysterious graph started showing usage when some code went live, and it's been a rollercoaster ride trying to figure out the root cause. The culprit? The MySQL or MariaDB DESC, DESCRIBE, or EXPLAIN table commands.

The graph in Zabbix for temporary tables created per second began to fluctuate, and we were at a loss as to why. The situation escalated during a recent spike in usage, when our server became overloaded, and we started to notice a lot of DESCRIBE table commands in our process list.

This article uncovers the behavior of the DESCRIBE command and its link to temporary table creation, a discovery that illuminated our understanding and allowed us to resolve the issue.

What is the DESCRIBE Command?

The DESCRIBE command in MySQL or MariaDB is used to describe the structure of a table. It provides information about the columns, types, and other properties of the table. Here's a simple example:

DESCRIBE employees; -- also EXPLAIN employees; and DESC employees;

This would display the structure of the employees table, including column names, data types, etc.

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | NO   | PRI | NULL    |       |
| first_name | varchar(50)  | YES  |     | NULL    |       |
| last_name  | varchar(50)  | YES  |     | NULL    |       |
| salary     | decimal      | YES  |     | NULL    |       |
| hire_date  | date         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

The Unexpected Behavior: Temporary Table Creation

What caught our attention was that in MariaDB, the description "Creating Temporary Table" appeared when running the DESCRIBE command. This was puzzling and led us to investigate further.

Why are Temporary Tables Created?

Temporary tables are often used to store intermediate results for complex queries. However, we didn't expect them to be linked to the DESCRIBE command. After digging into the MySQL/MariaDB documentation and source code, we realized that the DESCRIBE command indeed creates a temporary table to store and display the structure of the queried table.

The Impact on Performance

The frequent creation of temporary tables was taking a toll on our system. The server became overloaded, and the website's performance deteriorated.

Our code was using the DESC command to populate database mappers, and it seemed that the cache was not working properly. This resulted in an excessive number of DESC commands, leading to the unexpected creation of temporary tables.

How We Fixed the Issue

Our solution involved correcting the caching mechanism. Once the cache was functioning properly, the number of DESC commands plummeted, and the graph flatlined.

Here's a brief outline of the steps we took:

1. Analyzed the Caching Behavior: We found that the cache was not retaining the DESCRIBE command results.
2. Implemented Proper Caching: We modified our code to properly store and retrieve the table descriptions.
3. Monitored Performance: We closely watched the system performance and the graph in Zabbix. The result was a noticeable improvement.

Conclusion

The discovery that the DESCRIBE command creates temporary tables in MySQL/MariaDB was an unexpected revelation that solved a long-standing mystery. By understanding this behavior and implementing proper caching, we were able to boost our system's performance.

Sometimes, the solutions to complex problems are hidden in the most unexpected places. This experience reminds us to leave no stone unturned in our pursuit of understanding and optimizing our systems. Whether you're a seasoned database administrator or new to MySQL/MariaDB, I hope this article sheds light on a lesser-known aspect of these database systems and helps you in your journey.