Excessive tmp table creation for mysql
Nick
that my django DB had an excessive amount of temp tables created. Here
is the error:
***** Nagios 2.10 *****
Notification Type: PROBLEM
Service: mysql_tmp_disk_tables
Host:
Address:
State: WARNING
Date/Time: Mon Nov 8 15:19:18 CST 2010
Additional Info:
WARNING - 53.71% of 551680 tables were created on disk
I was doing some light querying in the shell following a few
recursive relationships for some foreignkeys but nothing major. I have
several applications with full API's making requests constantly. This
warning has only just now popped up.
Has anyone seen anything like this?
Marc Aymerich
This tables are created on disk when a query needs a tmp table bigger
than tmp_table_size and max_heap_table_size. The common situations
when a tmp table is needed are[1]:
*If there is an ORDER BY clause and a different GROUP BY clause, or if
the ORDER BY or GROUP BY contains columns from tables other than the
first table in the join queue, a temporary table is created.
*DISTINCT combined with ORDER BY may require a temporary table.
*If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory
temporary table, unless the query also contains elements (described
later) that require on-disk storage.
You can lookup in your django code where this conditions are met or
you can just can increase the tmp_table_size and max_heap_table_size
thresholds on mysql server. :)
[1] http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
br
--
Marc