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:
-
Analyzed the Caching Behavior: We found that the cache was not retaining the
DESCRIBE
command results. - Implemented Proper Caching: We modified our code to properly store and retrieve the table descriptions.
- 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.
Top comments (0)