There is some options that can cause temp tables to materialize as MyISAM tables or can is configured to delay it. Keep in mind so for disk-based temp tables, there is no .frm files, but only .MYD and .MYI files (of course. MYI file is never used since it was impossible index an internal temp table). Here is the options:
- Temp Table Variables that should be the same Size
- Tmp_table_size
- Max_heap_table_size
- Sort_buffer_size
- Join_buffer_size
You should also consider the MySQL documentation on Internal Temp Table Usage The situations where In-memory temp tables is made are
- If there is an ORDER BY clause and a different GROUP BY clause, or if the order by or GROUP by contains columns from table S and than the first table in the join queue, and 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.
When an in-memory temp table exceeded the minimum of (tmp_table_size or max_heap_table_size), MYSQLD does the following:
- Suspends the query
- Copies The In-memory table ' s contents into a MyISAM temp table
- Discards the In-memory table
- Continues the query, sending the temp data into the MyISAM temp table
The situations where In-memory temp tables is bypassed in favor of disk is
- Presence of a BLOB or TEXT column in the table
- Presence of any column in a GROUP by or DISTINCT clause larger than bytes
- Presence of any column larger than bytes in the SELECT list, if union or union all is used
Some due diligence is required-reduce temp table creation on disk
- Setting join_buffer_size Bigger
- Setting sort_buffer_size Bigger
- Setting Tmp_table_size and Max_heap_table_size bigger
- Tuning queries to minimize or even prevent temp tables
- Creating indexes to create presorted view of the data from individual tables
- Installing additional RAM to accommodate large in-memory temp tables
If after such due diligence, there be still temp tables being formed on Disk, this is one desperate move:mapping disk-ba Sed temp table creation to memory. Here is a quick-and-dirty-to-set up a 16GB RAM Disk using tmpdir STEP01) Create RAM Disk Folder mkdir /var/mysql_tmpfs
STEP02) Add this tomy.cnf [mysqld]tmpdir=/var/mysql_tmpfs
STEP03) Add This To/etc/fstab echo "none /var/mysql_tmpfs tmpfs defaults,size=16g 1 2" >> /etc/fstab
STEP04) Reload/etc/fstab mount -a
STEP05)service mysql restart After this, all temp table that become MyISAM is written to the RAM Disk. This should speed disk-based temp table creation. Give it a Try!!!
ShareImprove this answer |
answered Dec ' at 23:19 rolandomysqldba 63.4k148 |
|