In Percona, does creating a temporary table write to the global data dictionary?

MySQL & MariaDB Other MySQL® Questions
1

I’m trying to understand the output of a pt-stalk dump, and from what I can see in the innodb status is several threads waiting on the &dict_operation_lock (for 3 seconds each strangely enough). From what I’ve understood by searching the web, MySQL/Percona will grab the &dict_operation_lock mutex whenever tables are created, altered, or removed. There were no non-temporary tables being created, altered, or removed during the timeframe that pt-stalk was trigged on, but I do know our system does explicitly created some temporary tables and we have a lot of queries that create temporary tables as they execute.

I’ve been looking through the Percona Server source code and haven’t been able to determine whether it would grab &dict_operation_lock when a temporary table (explicit or as a query executes) is created, as I can’t tell if it uses the same table creation code for temporary and non-temporary tables.

2

Hi,

As you said, your system does explicitly created some temp tables so ​Yes, InnoDB data dictionary will be involved while creating explicit temp tables. Because table/column definition has to be stored somewhere for InnoDB table (If you can explicitly create temp tables as InnoDB, MySQL will follow all the principals of a regular InnoDB table and if you don’t specify the engine in the create_table statement then the table will be created using the whatever is set as the default_storage_engine)

So I guess that’s why you are getting dict_operation_locks.