…: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…
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: Just do the math!
July 6, 2009 By 4 Comments
… model significantly. The table may be in memory 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…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… give you couple of hints: Temporary Tables You can build query (with derived tables) which uses as many temporary tables as you like and you… true for Partitioned tables in MySQL 5.1 Disk Space For MyISAM tables hosting providers used to use disk quotas for MyISAM tables. You can… procedures are implemented as temporary tables, so by opening large amount of cursors which are handled as in-memory temporary tables you can consume…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… example it may use indexes or do a full table scan, or a temporary table may be needed. These are the things that… a table. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp_table will be set. Sometimes that table must be created on disk instead of in…
