- SHOW TEMP TABLE STATUS displays the table status for every temp table on a server including those created implicitly to process queries with GROUP BY and ORDER BY
- SHOW TEMP TABLE INDEXES displays index cardinality statistics for all temp tables
Sunday, September 21, 2008
What is in my temp tables?
How do you monitor the usage of temp tables in a MySQL database? I want to know what tables exist and how much disk space and memory they consume. I also want to view the index statistics for them when there are long running queries with bad plans. The following SQL commands might make this easier to do:
Subscribe to:
Post Comments (Atom)
Posts
A really simple thing that could help a lot too: another SHOW STATUS variable that shows the maximum size a temp table has ever been. (Bytes written to temp tables would also be good to know.) I'm speaking about implicit temp tables, here.
ReplyDeleteHumm, is this only for MySQL 5.1 and up ?
ReplyDeleteThanks Mark. This is helpful. BTW does it only shows current table or there is some history it tracks back this information.
ReplyDeleteRegarding other anonymous - indeed it is very interesting to understand max ever memory table which was allocated as well as how much space on disk it took. Ie using tmpfs it may be showstopper.
I have not implemented this yet. I want to do it but that may take a few months.
ReplyDeleteMark, good idea. It will be nice if the stat can include what query caused it as well.
ReplyDelete