Investigating MySQL Statistics Tables Using the DBUG Debugger

Problem

When creating a table in MySQL, several internal statistics tables (for example, mysql/innodb_index_stats) are automatically updated with rows describing the new table.

The article asks which tables are changed and how the statistics tables are modified.

Experiment

In this issue we use MySQL's DBUG tool to study the SQL processing flow.

First, we modify the instance start script, changing CUSTOM_MYSQLD to mysqld-debug, and restart the instance with the debug flag.

We then set two debugging options:

"d": enable output at each debug point.

"O,/tmp/mysqld.trace": write the debug output to the specified file.

After creating a table, the DBUG output shows detailed internal actions such as memory allocation with alloc_root during SQL parsing.

By adding more debug information we obtain a call‑tree view that makes it clear which memory allocations belong to which phase (e.g., mysql_parse).

Further enhancements add file names and line numbers to the output, allowing us to locate the exact code that updates the statistics tables.

Examining the output, we can see MySQL executes a built‑in stored procedure to update the statistics tables.

Other Statistics Tables

Following the que_eval_sql path reveals additional statistics tables, shown in the following screenshots.

The experiment demonstrates that MySQL provides several mechanisms (such as performance_schema and OPTIMIZER_TRACE) to expose internal processing details, aiding developers in understanding the engine's behavior.

Conclusion

Using the DBUG package we exposed MySQL's internal processing during table creation; similar techniques are available for deeper insight.