…:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/tmp/#sql_21b2_0.MYI’; try to repair it 120326… what really happened. MySQL created a temporary table, using the MyISAM storage engine, and when the disk filled up, MyISAM got an error. Little-known secret: MyISAM doesn’t handle a disk-full error gracefully 
Sometimes I’ve seen people having trouble…
Post: How to diagnose errors in the MySQL error log
March 26, 2012 By 4 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1 1 Handler_mrr… nation ALL PRIMARY NULL NULL NULL 25 100.00 Using temporary; Using filesort 1 SIMPLE customer ref PRIMARY,i_c_nationkey… NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… on disk MyISAM temporary table. What i decided to do is to see how much overhead do on disk temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table and types which can…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 16 Comments
… “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…_table_size retains to (give a hint about a) limit of the on-disk temporary table size. The limit imposed upon the disk-based temporary tables is…
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
… 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… those which use on-disk temporary storage for intermediate results. SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_diskâ€; Wait…
Comment: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 30, 2009 By 16 Comments
… BUG #4291: max_heap_table_size affects creation of disk-based temporary table fix: the new system variable memory_tmp_table_size is introduced; it… says tmp_table_size used to do. tmp_table_size retains to (give a hint about a) limit of the on-disk temporary table size. The limit imposed upon the disk-based temporary tables is…
Comment: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… of long strings it usually spills out to be on disk temporary table. Even though we just store first 255 characters of the… full urls) temporary tables are created on disk because of the size – 5 mil rows * 260 bytes per row (MEMORY tables use static storage… too much for temporary table. Table with two integers per row is however much smaller and can fit in in memory temporary table. Strings comparisons…
Post: Spreading .ibd files across multiple disks; the optimization that isn't
December 25, 2010 By 22 Comments
… is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed. Your…. Striping a table across multiple disks effectively balances the ‘heavy hit’ access across many more disks. With 1 disk/table you are more…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… index access with data access, saving you IO for completely disk bound workloads. There are certain optimizations in works which would… tables being small it would not slow things down too much. On other hand join of few large tables, which is completely disk… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… huge temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk temporary table which as we know quite slow. Of course it would be possible to allocate more memory to the temporary table or…
