…pushdown=off’ optimizer_switch=’mrr=on‘ optimizer_switch=’mrr_sort_keys=on‘ (only on MariaDB 5.5) optimizer_switch… mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1 1…100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY…
Post: How to diagnose errors in the MySQL error log
March 26, 2012 By 4 Comments
…:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/tmp/#sql_21b2_0.MYI’; try to repair it 120326… on device That’s the key to understanding what really happened. MySQL created a temporary table, using the MyISAM storage engine, and when the disk… an error. Little-known secret: MyISAM doesn’t handle a disk-full error gracefully 
Sometimes I’ve seen people having trouble…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table…
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
… a large number of temporary tables being created on disk. show global status like ‘Created_tmp%’ | Created_tmp_disk_tables | 91970 | | Created_tmp_files | 19624 | | Created_tmp_tables | 1617031 | Looking at a… the filter to “tmp_table_on_disk,filesort_on_disk†and I would get only those which use on-disk temporary storage for intermediate results. SET…
Comment: Choosing innodb_buffer_pool_size
March 30, 2010 By 38 Comments
…: 100% (101/100) [!!] Query cache prunes per day: 26846 [!!] Temporary tables created on disk: 38% (1M on disk / 4M total) [!!] InnoDB data size / buffer pool: 123.1G…_pool_size more than the memory on the system. Any thoughts to increase performance/decrease disk I/O or redesign idea ? Regards…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 16 Comments
We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it… option “disk-tmp-table-size” introduced to set maximum expected on-disk temporary table size and avoid mix-up of tmp_table_size and max_heap_table_size…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… maximum size implicit temporary table (created during query execution automatically) will grow as MEMORY table before it will be converted to on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even…
Post: Just do the math!
July 6, 2009 By 4 Comments
… or on disk which affects scan speed. Another aspect is the temporary table – the number of rows MySQL can insert/update depends on whenever temporary table can… to the disk as MyISAM table. Finally there is a sort which can happen in memory or require files to be created on disk. Understanding…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… temporary table is required for query execution Tmp_table will be set. Sometimes that table must be created on disk instead of in memory, in such case Disk_tmp_table will state…
Comment: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
Dale, There are two reasons for temporary tables to be on disk. First it is created on disk if there are any BLOB or TEXT columns because… to on disk table if it growths too large. In this case it was converted. Sorting is whole another beast it uses temporary file if sort size is too large. For some queries you get both temporary table and temporary file but these…
