| Bug #58031 | temp file full | ||
|---|---|---|---|
| Submitted: | 6 Nov 2010 15:22 | Modified: | 30 Nov 2010 11:19 |
| Reporter: | patcharee sirichavalit | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | 5.1.48 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[6 Nov 2010 15:30]
patcharee sirichavalit
Is it bug ?
[6 Nov 2010 15:38]
Peter Laursen
Has MySQL crashed for you frequently? MySQL will normally clean up its temporary tables, but if it crashes it cannot of course. Do you have HUGE views or do you execute SELECT .. JOIN queries or SUBQUERIES building such temporary tables (I see some are ~15 GB, what is a lot). My guess is that you write some very bad queries ... Peter (not a MySQL person)
[7 Nov 2010 11:10]
patcharee sirichavalit
Thank you Peter My problem will occur after backup . my backup run success and i saw script already unlock table. Anyway after that around 40 minute , temp full by #sqlXXX.MYI and #sqlXXX.MYD and It's not clear and make temp full. If i not backup ,this problem not occur. Is it a bug?
[7 Nov 2010 11:40]
Peter Laursen
then you should tell what backup too you are using!
[7 Nov 2010 12:21]
patcharee sirichavalit
My backup is 1) FLUSH TABLES WITH READ LOCK 2) copy mysql file 3)UNLOCK TABLES 4) exit
[10 Nov 2010 3:29]
patcharee sirichavalit
Hi All, Please help to answer me . Is it a bug ? and how to solve it ?
[10 Nov 2010 21:49]
Sveta Smirnova
Thank you for the feedback. Please send us output of SHOW FULL PROCESSLIST in time when temporary directory is full and full MySQL error log file.
[11 Nov 2010 18:22]
patcharee sirichavalit
when i show processlist it up from 80 to 600 After that it down to 50 -60 and up again but for temp file it not reduce ,it only up
[11 Nov 2010 18:23]
patcharee sirichavalit
mysql> show processlist; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> show processlist; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1016 Current database: *** NONE *** +------+------+-----------+------+---------+------+-------+-- ----------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-----------+------+---------+------+-------+-- ----------------+ | 1016 | root | localhost | NULL | Query | 0 | NULL | show processlist | +------+------+-----------+------+---------+------+-------+-- ----------------+ 1 row in set (0.00 sec)
[11 Nov 2010 18:25]
patcharee sirichavalit
i found Creating tmp table and Copying to tmp table a lot
[11 Nov 2010 18:47]
Sveta Smirnova
Thank you for the feedback. About 100 threads can easily create a lot of temporary files. As modification/creation time is almost same for every file looks like everything work fine. Error log shows mysqld crashed, because disk was full. After such crash temporary directory can contain stalled files. So this is not a bug. Use larger disks for temporary directory and read about how to tune queries to avoid filesort.
[12 Nov 2010 2:36]
patcharee sirichavalit
Hi all, If i not run backup , mysql is not create temp file on disk although session is high to 2000 . it occur only after i backup mysql success and unlock success . and if i restart mysql ,everything back to normal . it not create temp file on disk again . So why mysql do like this if it not a bug ?
[12 Nov 2010 2:40]
patcharee sirichavalit
i think mysql have some bug about unlock command that make mysql try to use temp file on disk ,is it possible ?
[12 Nov 2010 19:23]
Sveta Smirnova
Thank you for the feedback. Everything is possible, but having a lot of temporary files created during query executions itself is not a bug.
[15 Nov 2010 3:35]
patcharee sirichavalit
so please describe why same query not create temp file before backup ?
[15 Nov 2010 21:44]
Sveta Smirnova
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
[16 Nov 2010 1:59]
patcharee sirichavalit
I mean If you think it not a bug why it have temp file after i run command unlock . If it not a bug , it should not have any temp file after i run command unlock. I run same query before and after use unlock command But it have temp file only after use unlock command. And after i restart mysql , temp file not occur again . So please consider about it
[16 Nov 2010 10:50]
Sveta Smirnova
Thank you for the feedback. This still looks like expected behavior: MySQL creates a lot of temporary files to handle your queries. If queries were waiting in queue before you issued UNLOCK command it is expected MySQL server created a lot of temporary files when queries unlocked and started to execute. If you disagree you have to prove it by providing repeatable test case which clearly shows problem in MySQL code.
[16 Nov 2010 10:59]
patcharee sirichavalit
i already test and i can say step like this 1. i use command " flush table with read lock" 2. i run query 3. i unlock table 4. i see query already gone and i run new query 5. this query already create temp file 6. query already gone 7. new query come 8. this query create temp file . 9. step like 7 -8 until disk 90 GB full query since step 4 is new query after unlock table . But why it still create temp file ? If you said it's normal , it should not crate temp file if it not suspend from lock query ,right?
[16 Nov 2010 11:09]
Sveta Smirnova
Thank you for the feedback. > query since step 4 is new query after unlock table . > But why it still create temp file ? This depends from query. If temporary file is needed for query execution it is created. > If you said it's normal , it should not crate temp file if it not suspend from lock query ,right? This does not related to locks at all.
[16 Nov 2010 11:23]
patcharee sirichavalit
If i run only same query before and after lock table and query that i run before lock table not create temp file . but after unlock , i run same query and i saw it gone by create temp file . and i run new ( same query ) and it create temp . You still said it not a bug ,right?
[16 Nov 2010 11:36]
Sveta Smirnova
Thank you for the feedback. Please provide problem query, dump of all underlying tables and your configuration file.
[28 Nov 2010 14:31]
patcharee sirichavalit
Please provide problem query, dump of all underlying tables and your configuration file. Seem you don't understand It not cause from query so it not have certainly query that make mysql temp full. anyway i will upload my config file to you . and in error log said report this error to mysql bug Thanks
[28 Nov 2010 14:42]
patcharee sirichavalit
total used free shared buffers cached Mem: 16047 1910 14137 0 4 93 -/+ buffers/cache: 1812 14235 Swap: 18044 1 18043 hope this help
[29 Nov 2010 11:47]
Susanne Ebrecht
I also don't see a bug here. Please ask at http://forums.mysql.com for help.
[30 Nov 2010 1:58]
patcharee sirichavalit
InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 101130 2:21:44 - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2097152 max_used_connections=1440 max_threads=20000 threads_connected=538 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 82519309 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = (nil) thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x3a)[0x825eba] /usr/sbin/mysqld(handle_segfault+0x1fb)[0x5bb9bb] /lib64/libpthread.so.0[0x367620e7c0] /lib64/libc.so.6(gsignal+0x35)[0x3675a30265] /lib64/libc.so.6(abort+0x110)[0x3675a31d10] /usr/sbin/mysqld[0x77aad4] /usr/sbin/mysqld(fil_flush+0x1fe)[0x74cc1e] /usr/sbin/mysqld(log_write_up_to+0x6ef)[0x76c97f] /usr/sbin/mysqld(log_buffer_flush_to_disk+0x8a)[0x76ca9a] /usr/sbin/mysqld(srv_master_thread+0x1b3)[0x7a8b63] /lib64/libpthread.so.0[0x36762064a7] /lib64/libc.so.6(clone+0x6d)[0x3675ad3c2d] so that mean it's not a bug ,right? If you confirm , it's not a bug . I will paid for mysql service to solve this case.
[30 Nov 2010 2:00]
patcharee sirichavalit
Please comfirm to me it's bug or not . But if mysql service said ,it's cause from bug. I will not have to paid for Mysql service ,right?
[30 Nov 2010 11:19]
Sveta Smirnova
Thank you for the feedback. > Seem you don't understand ... > It not cause from query so it not have certainly query that make mysql temp full. We need test case which we can repeat on our side. If this problem is repeatable on your side please send such test case. Otherwise problem with temporarily tables can not be considered as a bug. Regarding to crash this is different case. Please open new bug report for this.
Description: i found temp folder full because of -rw-rw---- 1 mysql mysql 1024 Nov 6 21:57 #sql_3e3_163.MYI -rw-rw---- 1 mysql mysql 536870912 Nov 6 21:57 ib_logfile0 -rw-rw---- 1 mysql mysql 260341760 Nov 6 21:58 #sql_3e3_114.MYD -rw-rw---- 1 mysql mysql 41619456 Nov 6 21:58 #sql_3e3_130.MYD -rw-rw---- 1 mysql mysql 952762368 Nov 6 21:58 #sql_3e3_43.MYD -rw-rw---- 1 mysql mysql 1574404096 Nov 6 21:58 #sql_3e3_16.MYD -rw-rw---- 1 mysql mysql 133595136 Nov 6 21:58 #sql_3e3_122.MYD -rw-rw---- 1 mysql mysql 398458880 Nov 6 21:58 #sql_3e3_83.MYD -rw-rw---- 1 mysql mysql 849379328 Nov 6 21:58 #sql_3e3_52.MYD -rw-rw---- 1 mysql mysql 1396178944 Nov 6 21:58 #sql_3e3_20.MYD -rw-rw---- 1 mysql mysql 448204800 Nov 6 21:58 #sql_3e3_74.MYD -rw-rw---- 1 mysql mysql 487620608 Nov 6 21:58 #sql_3e3_68.MYD -rw-rw---- 1 mysql mysql 265363456 Nov 6 21:58 #sql_3e3_115.MYD -rw-rw---- 1 mysql mysql 314638336 Nov 6 21:58 #sql_3e3_102.MYD -rw-rw---- 1 mysql mysql 294551552 Nov 6 21:58 #sql_3e3_105.MYD -rw-rw---- 1 mysql mysql 536870912 Nov 6 21:58 ib_logfile1 -rw-rw---- 1 mysql mysql 826277888 Nov 6 21:58 #sql_3e3_0.MYD -rw-rw---- 1 mysql mysql 338862080 Nov 6 21:58 #sql_3e3_95.MYD -rw-rw---- 1 mysql mysql 20541440 Nov 6 21:58 #sql_3e3_133.MYD -rw-rw---- 1 mysql mysql 1083338752 Nov 6 21:58 #sql_3e3_41.MYD -rw-rw---- 1 mysql mysql 320569344 Nov 6 21:58 #sql_3e3_100.MYD -rw-rw---- 1 mysql mysql 1198325760 Nov 6 21:58 #sql_3e3_28.MYD -rw-rw---- 1 mysql mysql 580878336 Nov 6 21:58 #sql_3e3_58.MYD -rw-rw---- 1 mysql mysql 1084751872 Nov 6 21:58 #sql_3e3_42.MYD -rw-rw---- 1 mysql mysql 1356038144 Nov 6 21:58 #sql_3e3_31.MYD -rw-rw---- 1 mysql mysql 271781888 Nov 6 21:58 #sql_3e3_111.MYD -rw-rw---- 1 mysql mysql 343670784 Nov 6 21:58 #sql_3e3_93.MYD -rw-rw---- 1 mysql mysql 275742720 Nov 6 21:58 #sql_3e3_104.MYD -rw-rw---- 1 mysql mysql 1394016256 Nov 6 21:58 #sql_3e3_21.MYD -rw-rw---- 1 mysql mysql 356093952 Nov 6 21:58 #sql_3e3_89.MYD -rw-rw---- 1 mysql mysql 145784832 Nov 6 21:58 #sql_3e3_117.MYD -rw-rw---- 1 mysql mysql 939655168 Nov 6 21:58 #sql_3e3_45.MYD -rw-rw---- 1 mysql mysql 295272448 Nov 6 21:58 #sql_3e3_106.MYD -rw-rw---- 1 mysql mysql 867762176 Nov 6 21:58 #sql_3e3_51.MYD -rw-rw---- 1 mysql mysql 461287424 Nov 6 21:58 #sql_3e3_71.MYD -rw-rw---- 1 mysql mysql 1398538240 Nov 6 21:58 #sql_3e3_24.MYD -rw-rw---- 1 mysql mysql 346783744 Nov 6 21:58 #sql_3e3_92.MYD -rw-rw---- 1 mysql mysql 197165056 Nov 6 21:58 #sql_3e3_113.MYD -rw-rw---- 1 mysql mysql 374804480 Nov 6 21:58 #sql_3e3_87.MYD -rw-rw---- 1 mysql mysql 74387456 Nov 6 21:58 #sql_3e3_126.MYD -rw-rw---- 1 mysql mysql 1340837888 Nov 6 21:58 #sql_3e3_32.MYD -rw-rw---- 1 mysql mysql 332464128 Nov 6 21:58 #sql_3e3_99.MYD -rw-rw---- 1 mysql mysql 444628992 Nov 6 21:58 #sql_3e3_76.MYD -rw-rw---- 1 mysql mysql 640450560 Nov 6 21:58 #sql_3e3_48.MYD -rw-rw---- 1 mysql mysql 1343885312 Nov 6 21:58 #sql_3e3_34.MYD -rw-rw---- 1 mysql mysql 1396572160 Nov 6 21:58 #sql_3e3_18.MYD -rw-rw---- 1 mysql mysql 573833216 Nov 6 21:58 #sql_3e3_61.MYD -rw-rw---- 1 mysql mysql 1322549248 Nov 6 21:58 #sql_3e3_36.MYD -rw-rw---- 1 mysql mysql 1455915008 Nov 6 21:58 #sql_3e3_17.MYD -rw-rw---- 1 mysql mysql 1356234752 Nov 6 21:58 #sql_3e3_27.MYD -rw-rw---- 1 mysql mysql 90669056 Nov 6 21:58 #sql_3e3_124.MYD -rw-rw---- 1 mysql mysql 276856832 Nov 6 21:58 #sql_3e3_109.MYD -rw-rw---- 1 mysql mysql 253820928 Nov 6 21:58 #sql_3e3_116.MYD -rw-rw---- 1 mysql mysql 318668800 Nov 6 21:58 #sql_3e3_101.MYD -rw-rw---- 1 mysql mysql 344293376 Nov 6 21:58 #sql_3e3_96.MYD -rw-rw---- 1 mysql mysql 480280576 Nov 6 21:58 #sql_3e3_69.MYD -rw-rw---- 1 mysql mysql 1154875392 Nov 6 21:58 #sql_3e3_33.MYD -rw-rw---- 1 mysql mysql 287834112 Nov 6 21:58 #sql_3e3_107.MYD -rw-rw---- 1 mysql mysql 439984128 Nov 6 21:58 #sql_3e3_78.MYD -rw-rw---- 1 mysql mysql 1356201984 Nov 6 21:58 #sql_3e3_29.MYD -rw-rw---- 1 mysql mysql 1248149504 Nov 6 21:58 #sql_3e3_39.MYD -rw-rw---- 1 mysql mysql 1365139456 Nov 6 21:58 #sql_3e3_25.MYD -rw-rw---- 1 mysql mysql 238796800 Nov 6 21:58 #sql_3e3_119.MYD -rw-rw---- 1 mysql mysql 1293451264 Nov 6 21:58 #sql_3e3_4.MYD -rw-rw---- 1 mysql mysql 583630848 Nov 6 21:58 #sql_3e3_53.MYD -rw-rw---- 1 mysql mysql 502824960 Nov 6 21:58 #sql_3e3_50.MYD -rw-rw---- 1 mysql mysql 410386432 Nov 6 21:58 #sql_3e3_84.MYD -rw-rw---- 1 mysql mysql 583041024 Nov 6 21:58 #sql_3e3_57.MYD -rw-rw---- 1 mysql mysql 674799616 Nov 6 21:58 #sql_3e3_11.MYD -rw-rw---- 1 mysql mysql 346062848 Nov 6 21:58 #sql_3e3_94.MYD -rw-rw---- 1 mysql mysql 1381892096 Nov 6 21:58 #sql_3e3_23.MYD -rw-rw---- 1 mysql mysql 1363120128 Nov 6 21:58 #sql_3e3_22.MYD -rw-rw---- 1 mysql mysql 1372717056 Nov 6 21:58 #sql_3e3_26.MYD -rw-rw---- 1 mysql mysql 455213056 Nov 6 21:58 #sql_3e3_73.MYD -rw-rw---- 1 mysql mysql 769654784 Nov 6 21:58 #sql_3e3_6.MYD -rw-rw---- 1 mysql mysql 436240384 Nov 6 21:58 #sql_3e3_77.MYD -rw-rw---- 1 mysql mysql 540241920 Nov 6 21:58 #sql_3e3_62.MYD -rw-rw---- 1 mysql mysql 444633088 Nov 6 21:58 #sql_3e3_79.MYD -rw-rw---- 1 mysql mysql 911609856 Nov 6 21:58 #sql_3e3_46.MYD -rw-rw---- 1 mysql mysql 310542336 Nov 6 21:58 #sql_3e3_103.MYD -rw-rw---- 1 mysql mysql 1388085248 Nov 6 21:58 #sql_3e3_19.MYD -rw-rw---- 1 mysql mysql 608468992 Nov 6 21:58 #sql_3e3_56.MYD -rw-rw---- 1 mysql mysql 803401728 Nov 6 21:58 #sql_3e3_5.MYD -rw-rw---- 1 mysql mysql 471760896 Nov 6 21:58 #sql_3e3_70.MYD -rw-rw---- 1 mysql mysql 514002944 Nov 6 21:58 #sql_3e3_66.MYD -rw-rw---- 1 mysql mysql 1273262080 Nov 6 21:58 #sql_3e3_38.MYD -rw-rw---- 1 mysql mysql 62894080 Nov 6 21:58 #sql_3e3_125.MYD -rw-rw---- 1 mysql mysql 335347712 Nov 6 21:58 #sql_3e3_97.MYD -rw-rw---- 1 mysql mysql 429260800 Nov 6 21:58 #sql_3e3_80.MYD -rw-rw---- 1 mysql mysql 675282944 Nov 6 21:58 #sql_3e3_9.MYD -rw-rw---- 1 mysql mysql 350814208 Nov 6 21:58 #sql_3e3_90.MYD -rw-rw---- 1 mysql mysql 61800448 Nov 6 21:58 #sql_3e3_127.MYD -rw-rw---- 1 mysql mysql 1039220736 Nov 6 21:58 #sql_3e3_44.MYD -rw-rw---- 1 mysql mysql 400588800 Nov 6 21:58 #sql_3e3_86.MYD -rw-rw---- 1 mysql mysql 62914560 Nov 6 21:58 #sql_3e3_129.MYD -rw-rw---- 1 mysql mysql 827916288 Nov 6 21:58 #sql_3e3_54.MYD -rw-rw---- 1 mysql mysql 52559872 Nov 6 21:58 #sql_3e3_128.MYD -rw-rw---- 1 mysql mysql 95354880 Nov 6 21:58 #sql_3e3_120.MYD -rw-rw---- 1 mysql mysql 142671872 Nov 6 21:58 #sql_3e3_121.MYD -rw-rw---- 1 mysql mysql 1149304832 Nov 6 21:58 #sql_3e3_40.MYD -rw-rw---- 1 mysql mysql 739483648 Nov 6 21:58 #sql_3e3_8.MYD -rw-rw---- 1 mysql mysql 263892992 Nov 6 21:58 #sql_3e3_118.MYD -rw-rw---- 1 mysql mysql 111706112 Nov 6 21:58 #sql_3e3_123.MYD -rw-rw---- 1 mysql mysql 418480128 Nov 6 21:58 #sql_3e3_81.MYD -rw-rw---- 1 mysql mysql 453345280 Nov 6 21:58 #sql_3e3_75.MYD -rw-rw---- 1 mysql mysql 515637248 Nov 6 21:58 #sql_3e3_64.MYD -rw-rw---- 1 mysql mysql 258793472 Nov 6 21:58 #sql_3e3_110.MYD -rw-rw---- 1 mysql mysql 1326317568 Nov 6 21:58 #sql_3e3_30.MYD -rw-rw---- 1 mysql mysql 1329266688 Nov 6 21:58 #sql_3e3_37.MYD -rw-rw---- 1 mysql mysql 321716224 Nov 6 21:58 #sql_3e3_98.MYD -rw-rw---- 1 mysql mysql 1326743552 Nov 6 21:58 #sql_3e3_35.MYD -rw-rw---- 1 mysql mysql 600088576 Nov 6 21:58 #sql_3e3_55.MYD -rw-rw---- 1 mysql mysql 14188544 Nov 6 21:58 #sql_3e3_132.MYD -rw-rw---- 1 mysql mysql 23691264 Nov 6 21:58 #sql_3e3_131.MYD -rw-rw---- 1 mysql mysql 889901056 Nov 6 21:58 #sql_3e3_49.MYD -rw-rw---- 1 mysql mysql 685576192 Nov 6 21:58 #sql_3e3_10.MYD -rw-rw---- 1 mysql mysql 350224384 Nov 6 21:58 #sql_3e3_91.MYD -rw-rw---- 1 mysql mysql 417398784 Nov 6 21:58 #sql_3e3_82.MYD -rw-rw---- 1 mysql mysql 375164928 Nov 6 21:58 #sql_3e3_88.MYD -rw-rw---- 1 mysql mysql 503349248 Nov 6 21:58 #sql_3e3_67.MYD MysqlLog 84934656 84934656 0 100% /mysql_log How to repeat: backup or query data