On Tue, Nov 9, 2010 at 7:49 PM, Nick <[email protected]> wrote: > Yesterday I received a warning from our DB monitoring system stating > 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?
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 -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
