MySQL Temporary Tables

Question

I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this temporary tables? We have around 50 databases and many of the databases using create temporary table statement in its procedures. In /tmp directory I could see the .ibd, .frm and few .MYI & MYD files. Is it possible to find which database contributing more to creation of temporary tables? Please let me know, if you need any other information.

Thanks in advance.

Answer 1

Not quite what you asked for, but this query may help:

mysql> SELECT db, name FROM mysql.proc WHERE body LIKE '%temporary%';
+---------------+-----------------------------------+
| db            | name                              |
+---------------+-----------------------------------+
| common_schema | rdebug_compile_routine            |
| common_schema | _get_sql_dependencies_internal    |
| common_schema | query_checksum                    |
| common_schema | _split_cleanup_dependency_tables  |
| common_schema | _split_generate_dependency_tables |
+---------------+-----------------------------------+

If /tmp is in a separate filesystem, you are cruisin' for a bruisin'. Change tmpdir to point to some directory in the main FS.