Why does MySQL produce so many temporary MYD files?

Source: Internet
Author: User

Http://dba.stackexchange.com/questions/30505/why-does-mysql-produce-so-many-temporary-myd-files

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their Database skills and learn from others in the community. It ' s 100% free, no registration required. Why does MySQL produce so many temporary MYD files?
Up vote 3 down vote favorite3

On a Debian Linux server, hosting many php/mysql websites (photo galleries), sometimes I had "many" files like /tmp/#sql_6405_58.MYD .

For example today:

[2012-12-15 15:18:11] /Tmp/#Sql_6405_6.MYD: 88MB[2012-12-15 15:18:11] /Tmp/#Sql_6405_3.MYD: 22MB[2012-12-15 15:18:11] /Tmp/#Sql_6405_4.MYD: 138MB[2012-12-15 15:18:11] /Tmp/#Sql_6405_10.MYD: 88MB...[2012-12-15 15:18:11] /Tmp/#Sql_6405_9.MYD: 15MB[2012-12-15 15:18:11  /tmp/#sql_6405_65< Span class= "pun". myd : 49MB [2012-12-15 15:18:11] /< Span class= "PLN" >tmp/#sql_6405_44. MYD : 69mb      

(same time, for more than 6GB ... yes I monitor big files in/tmp)

Unfortunately, is on the /tmp same partition than / and it temporary breaks the Web server, because was full / I s Uppose. Then files disappear and the server are back to normal.

All the file names follow the #sql_6405_*.MYD pattern. I would like to understand which MySQL operation implies so many temporary files. I have approximately-databases on this server. Is it possible to know which database is concerned?

MySQL Temp-tables
shareimprove this question edited Dec + ' at 23:34< /span> rolandomysqldba
63.4k1062 148
asked dec at 20:21   Plegall
migrated from stackoverflow.com Dec "at 2:08

This question came from our site for professional and enthusiast programmers.

1  
i ' m guessing they ' re temp dat A for large operations so use Filesort , and 6405 are the PID of MySQL to keep temp files from different serv ER instances separate. –  marc B dec "at 20:27
should I ask a admin to move my question?– plegall Dec "at 22:06
Add a Comment

3 Answers Active oldest votes

Up vote 6 down vote

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
Add a Comment

Up vote 1 down vote

These is queries that is rolling over onto disk because the results is too large for memory.< /p>

When the query was done, the space clears.

There's no-to-match these temp files definitively to queries, and you can get clues-a good guess from SHOW Full processlist; or SHOW INNODB STATUS; or by looking in your error log if the queries fail.

/table>
shareimprove this answer answered Dec 5:50 Valerie parham-thompson
386< Span title= "2 silver Badges" >25
Add a Comment

Up vote 0 down vote

Whenever we use ALTER statements on table IT creates the #sql_6405_3. MYD temporay files and once It ' s done throws the output and disappears.

Alter on tables make MySQL to copy whole data into temporary files #sql. Xxx.myd and make changes to created temporary f Iles then drop original data files TableName. MYD and renames the Temporay files to table name.

Also for some kinda sorting queries it creates temporary files.

As I traced out. This thing happens.

/table>
shareimprove this answer answered Dec 16 ' At 13:48 Vinay
358
Add a Comment

Your Answer

Why does MySQL produce so many temporary MYD files?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.