# mysql occupies 300 gb tmp folder space. i think its dirty pages flushing **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [February 29, 2012, 11:46am UTC](https://forums.percona.com/t/mysql-occupies-300-gb-tmp-folder-space-i-think-its-dirty-pages-flushing/1771 "2012-02-29T11:46:18Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![mysqlhelp](https://avatars.discourse-cdn.com/v4/letter/m/f14d63/32.png) [@mysqlhelp](https://forums.percona.com/u/mysqlhelp)\ **Post date:** [February 29, 2012, 11:46am UTC](https://forums.percona.com/t/mysql-occupies-300-gb-tmp-folder-space-i-think-its-dirty-pages-flushing/1771/1 "2012-02-29T11:46:18Z")
\*\*I have two innodb tables which has about 15 million records and 1.8 Million records respectively. I have tried all possible settings and everything is only worsening the problem. Earlier it use to create tmp files which occupies only 70 GB file. But today it is still running and it occupies about 350 GB and its running for last 6 hours. This happened only after I changed innodb\_flush\_log\_at\_trx\_commit = 0 (earlier it was 2) and innodb\_max\_dirty\_pages\_pct = 10 (ealier it as 75)My site is not coming regular intervals. In my old setting it use to run everyday and occupied 70 gb and now its running every week. Yesterday we updated around 90000 records in the table. I am not getting any solution for this innodb issue. my.cnf setting is below innodb\_flush\_log\_at\_trx\_commit = 0 innodb\_log\_buffer\_size = 4096M innodb\_io\_capacity=5000 innodb\_adaptive\_hash\_index = ON innodb\_max\_dirty\_pages\_pct = 10 innodb\_adaptive\_flushing = false query\_cache\_limit = 32M #16M #1M query-cache-size = 2GB #1GB query-cache-type = 0 myisam\_sort\_buffer\_size = 48GB #16GB bulk\_insert\_buffer\_size = 1024M #512M #32M innodb\_log\_file\_size = 1GB #512M thread\_concurrency = 512 #256 #16 open\_files\_limit = 65535 #9786432 join\_buffer\_size = 262144 table\_definition\_cache = 1024M #512M #6M table\_open\_cache = 1024M #512M #4M table\_cache = 1024M #512M #4M max\_connect\_errors = 12000 interactive\_timeout = 1100 #down from 28800 wait\_timeout = 1100 #down from 28800 max\_heap\_table\_size = 120M #32M tmp\_table\_size = 120M #32M max\_connections = 1000 read\_rnd\_buffer\_size = 32M #16M #1536K read\_buffer\_size = 32M #16M #1536K sort\_buffer\_size = 32M #16M #2M thread\_cache\_size = 8192 #4096 #2048 thread\_stack = 1M #512K 256K max\_allowed\_packet = 1024M #512M 16M key\_buffer = 8G #4G slow-query-log-file=/var/log/mysql/mysql-slow-queries.log long\_query\_time = 4 innodb\_buffer\_pool\_size=13GB #1GB innodb\_file\_per\_table=1 symbolic-links=0 tmpdir=/backup-data/mysql-tmp user=mysql contents of tmp folder This is the tmp folder contents -rwxrwxrwx 1 root root 2937368576 Feb 29 22:16 #sql\_a46\_0.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:07 #sql\_a46\_0.MYI -rwxrwxrwx 1 root root 6218518528 Feb 29 22:16 #sql\_a46\_102.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:16 #sql\_a46\_102.MYI -rwxrwxrwx 1 root root 6189330432 Feb 29 22:16 #sql\_a46\_108.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:18 #sql\_a46\_108.MYI -rwxrwxrwx 1 root root 6329581568 Feb 29 22:16 #sql\_a46\_10.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_10.MYI -rwxrwxrwx 1 root root 6144032768 Feb 29 22:16 #sql\_a46\_115.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:21 #sql\_a46\_115.MYI -rwxrwxrwx 1 root root 6330241024 Feb 29 22:16 #sql\_a46\_11.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_11.MYI -rwxrwxrwx 1 root root 2828972032 Feb 29 22:16 #sql\_a46\_121.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:31 #sql\_a46\_121.MYI -rwxrwxrwx 1 root root 3016097792 Feb 29 22:16 #sql\_a46\_129.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:53 #sql\_a46\_129.MYI -rwxrwxrwx 1 root root 6330261504 Feb 29 22:16 #sql\_a46\_12.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_12.MYI -rwxrwxrwx 1 root root 6185283584 Feb 29 22:16 #sql\_a46\_132.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:18 #sql\_a46\_132.MYI -rwxrwxrwx 1 root root 6252457984 Feb 29 22:16 #sql\_a46\_139.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:12 #sql\_a46\_139.MYI -rwxrwxrwx 1 root root 6321152000 Feb 29 22:16 #sql\_a46\_13.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_13.MYI -rwxrwxrwx 1 root root 6330122240 Feb 29 22:16 #sql\_a46\_14.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_14.MYI -rwxrwxrwx 1 root root 2663772160 Feb 29 22:16 #sql\_a46\_152.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:49 #sql\_a46\_152.MYI -rwxrwxrwx 1 root root 6204637184 Feb 29 22:16 #sql\_a46\_155.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:17 #sql\_a46\_155.MYI -rwxrwxrwx 1 root root 6175641600 Feb 29 22:16 #sql\_a46\_156.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:19 #sql\_a46\_156.MYI -rwxrwxrwx 1 root root 6330081280 Feb 29 22:16 #sql\_a46\_15.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_15.MYI -rwxrwxrwx 1 root root 2852290560 Feb 29 22:16 #sql\_a46\_168.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:25 #sql\_a46\_168.MYI -rwxrwxrwx 1 root root 6043385856 Feb 29 22:16 #sql\_a46\_16.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:22 #sql\_a46\_16.MYI -rwxrwxrwx 1 root root 6246834176 Feb 29 22:16 #sql\_a46\_174.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:13 #sql\_a46\_174.MYI -rwxrwxrwx 1 root root 2683600896 Feb 29 22:16 #sql\_a46\_176.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:47 #sql\_a46\_176.MYI -rwxrwxrwx 1 root root 0 Feb 29 22:16 #sql\_a46\_17.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_17.MYI -rwxrwxrwx 1 root root 3150417920 Feb 29 22:16 #sql\_a46\_181.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:28 #sql\_a46\_181.MYI -rwxrwxrwx 1 root root 3150077952 Feb 29 22:16 #sql\_a46\_185.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:29 #sql\_a46\_185.MYI -rwxrwxrwx 1 root root 4594163712 Feb 29 22:16 #sql\_a46\_18.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:42 #sql\_a46\_18.MYI -rwxrwxrwx 1 root root 6243561472 Feb 29 22:16 #sql\_a46\_192.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:13 #sql\_a46\_192.MYI -rwxrwxrwx 1 root root 4158464000 Feb 29 22:16 #sql\_a46\_19.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:52 #sql\_a46\_19.MYI -rwxrwxrwx 1 root root 3108360192 Feb 29 22:16 #sql\_a46\_202.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:36 #sql\_a46\_202.MYI -rwxrwxrwx 1 root root 2772246528 Feb 29 22:16 #sql\_a46\_203.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:38 #sql\_a46\_203.MYI -rwxrwxrwx 1 root root 2770219008 Feb 29 22:16 #sql\_a46\_207.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:38 #sql\_a46\_207.MYI -rwxrwxrwx 1 root root 2988494848 Feb 29 22:16 #sql\_a46\_20.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:58 #sql\_a46\_20.MYI -rwxrwxrwx 1 root root 2877579264 Feb 29 22:16 #sql\_a46\_214.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:19 #sql\_a46\_214.MYI -rwxrwxrwx 1 root root 6226350080 Feb 29 22:16 #sql\_a46\_219.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:14 #sql\_a46\_219.MYI -rwxrwxrwx 1 root root 6081789952 Feb 29 22:16 #sql\_a46\_21.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:22 #sql\_a46\_21.MYI -rwxrwxrwx 1 root root 2859552768 Feb 29 22:16 #sql\_a46\_226.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:24 #sql\_a46\_226.MYI -rwxrwxrwx 1 root root 2282143744 Feb 29 22:16 #sql\_a46\_22.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:10 #sql\_a46\_22.MYI -rwxrwxrwx 1 root root 2800869376 Feb 29 22:16 #sql\_a46\_236.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:37 #sql\_a46\_236.MYI -rwxrwxrwx 1 root root 2862034944 Feb 29 22:16 #sql\_a46\_23.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:23 #sql\_a46\_23.MYI -rwxrwxrwx 1 root root 3023896576 Feb 29 22:16 #sql\_a46\_241.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:51 #sql\_a46\_241.MYI -rwxrwxrwx 1 root root 2870628352 Feb 29 22:16 #sql\_a46\_244.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:21 #sql\_a46\_244.MYI -rwxrwxrwx 1 root root 0 Feb 29 22:16 #sql\_a46\_24.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_24.MYI -rwxrwxrwx 1 root root 2865655808 Feb 29 22:16 #sql\_a46\_256.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:22 #sql\_a46\_256.MYI -rwxrwxrwx 1 root root 2579501056 Feb 29 22:16 #sql\_a46\_25.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:53 #sql\_a46\_25.MYI -rwxrwxrwx 1 root root 0 Feb 29 22:16 #sql\_a46\_26.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_26.MYI -rwxrwxrwx 1 root root 5036814336 Feb 29 22:16 #sql\_a46\_27.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:32 #sql\_a46\_27.MYI -rwxrwxrwx 1 root root 0 Feb 29 22:16 #sql\_a46\_28.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_28.MYI -rwxrwxrwx 1 root root 4670119936 Feb 29 22:16 #sql\_a46\_29.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:40 #sql\_a46\_29.MYI -rwxrwxrwx 1 root root 24978251776 Feb 29 22:16 #sql\_a46\_2.MYD -rwxrwxrwx 1 root root 2048 Feb 29 15:50 #sql\_a46\_2.MYI -rwxrwxrwx 1 root root 0 Feb 29 22:16 #sql\_a46\_30.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_30.MYI -rwxrwxrwx 1 root root 40280 Feb 29 22:16 #sql\_a46\_31.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_31.MYI -rwxrwxrwx 1 root root 0 Feb 29 22:16 #sql\_a46\_32.MYD -rwxrwxrwx 1 root root 1024 Feb 29 22:16 #sql\_a46\_32.MYI -rwxrwxrwx 1 root root 2027438080 Feb 29 22:16 #sql\_a46\_33.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:23 #sql\_a46\_33.MYI -rwxrwxrwx 1 root root 3161894912 Feb 29 22:16 #sql\_a46\_36.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_36.MYI -rwxrwxrwx 1 root root 3161849856 Feb 29 22:16 #sql\_a46\_37.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_37.MYI -rwxrwxrwx 1 root root 2021462016 Feb 29 22:16 #sql\_a46\_38.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:23 #sql\_a46\_38.MYI -rwxrwxrwx 1 root root 4556615680 Feb 29 22:16 #sql\_a46\_39.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:43 #sql\_a46\_39.MYI -rwxrwxrwx 1 root root 22346735616 Feb 29 22:16 #sql\_a46\_3.MYD -rwxrwxrwx 1 root root 2048 Feb 29 15:52 #sql\_a46\_3.MYI -rwxrwxrwx 1 root root 3161235456 Feb 29 22:16 #sql\_a46\_40.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_40.MYI -rwxrwxrwx 1 root root 3161214976 Feb 29 22:16 #sql\_a46\_41.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_41.MYI -rwxrwxrwx 1 root root 3161083904 Feb 29 22:16 #sql\_a46\_42.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_42.MYI -rwxrwxrwx 1 root root 3161034752 Feb 29 22:16 #sql\_a46\_43.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_43.MYI -rwxrwxrwx 1 root root 3160903680 Feb 29 22:16 #sql\_a46\_44.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_44.MYI -rwxrwxrwx 1 root root 3160731648 Feb 29 22:16 #sql\_a46\_45.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:25 #sql\_a46\_45.MYI -rwxrwxrwx 1 root root 6225440768 Feb 29 22:16 #sql\_a46\_46.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:15 #sql\_a46\_46.MYI -rwxrwxrwx 1 root root 2807676928 Feb 29 22:16 #sql\_a46\_47.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:36 #sql\_a46\_47.MYI -rwxrwxrwx 1 root root 2922082304 Feb 29 22:16 #sql\_a46\_49.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:11 #sql\_a46\_49.MYI -rwxrwxrwx 1 root root 23870640128 Feb 29 22:16 #sql\_a46\_4.MYD -rwxrwxrwx 1 root root 2048 Feb 29 15:50 #sql\_a46\_4.MYI -rwxrwxrwx 1 root root 2348777472 Feb 29 22:16 #sql\_a46\_50.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:07 #sql\_a46\_50.MYI -rwxrwxrwx 1 root root 1945227264 Feb 29 22:16 #sql\_a46\_53.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:28 #sql\_a46\_53.MYI -rwxrwxrwx 1 root root 1936265216 Feb 29 22:16 #sql\_a46\_55.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:28 #sql\_a46\_55.MYI -rwxrwxrwx 1 root root 2204057600 Feb 29 22:16 #sql\_a46\_56.MYD -rwxrwxrwx 1 root root 2048 Feb 29 20:14 #sql\_a46\_56.MYI -rwxrwxrwx 1 root root 2840174592 Feb 29 22:16 #sql\_a46\_59.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:27 #sql\_a46\_59.MYI -rwxrwxrwx 1 root root 6331748352 Feb 29 22:16 #sql\_a46\_5.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_5.MYI -rwxrwxrwx 1 root root 2804363264 Feb 29 22:16 #sql\_a46\_61.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:37 #sql\_a46\_61.MYI -rwxrwxrwx 1 root root 2709909504 Feb 29 22:16 #sql\_a46\_67.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:43 #sql\_a46\_67.MYI -rwxrwxrwx 1 root root 6332678144 Feb 29 22:16 #sql\_a46\_6.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_6.MYI -rwxrwxrwx 1 root root 6259769344 Feb 29 22:16 #sql\_a46\_70.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:11 #sql\_a46\_70.MYI -rwxrwxrwx 1 root root 2769498112 Feb 29 22:16 #sql\_a46\_76.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:38 #sql\_a46\_76.MYI -rwxrwxrwx 1 root root 2524237824 Feb 29 22:16 #sql\_a46\_78.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:57 #sql\_a46\_78.MYI -rwxrwxrwx 1 root root 2835894272 Feb 29 22:16 #sql\_a46\_79.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:28 #sql\_a46\_79.MYI -rwxrwxrwx 1 root root 6331310080 Feb 29 22:16 #sql\_a46\_7.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_7.MYI -rwxrwxrwx 1 root root 2836283392 Feb 29 22:16 #sql\_a46\_80.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:28 #sql\_a46\_80.MYI -rwxrwxrwx 1 root root 3058335744 Feb 29 22:16 #sql\_a46\_87.MYD -rwxrwxrwx 1 root root 2048 Feb 29 18:45 #sql\_a46\_87.MYI -rwxrwxrwx 1 root root 6258896896 Feb 29 22:16 #sql\_a46\_88.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:11 #sql\_a46\_88.MYI -rwxrwxrwx 1 root root 6331346944 Feb 29 22:16 #sql\_a46\_8.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_8.MYI -rwxrwxrwx 1 root root 2834038784 Feb 29 22:16 #sql\_a46\_90.MYD -rwxrwxrwx 1 root root 2048 Feb 29 19:29 #sql\_a46\_90.MYI -rwxrwxrwx 1 root root 6220681216 Feb 29 22:16 #sql\_a46\_92.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:15 #sql\_a46\_92.MYI -rwxrwxrwx 1 root root 6330892288 Feb 29 22:16 #sql\_a46\_9.MYD -rwxrwxrwx 1 root root 2048 Feb 29 17:05 #sql\_a46\_9.MYI ---
**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [February 29, 2012, 2:19pm UTC](https://forums.percona.com/t/mysql-occupies-300-gb-tmp-folder-space-i-think-its-dirty-pages-flushing/1771/2 "2012-02-29T14:19:38Z")
Dirty pages don’t get flushed into the temp directory. Those are temporary tables from queries. You have a problem with bad execution plans. ---
**Author:** ![mysqlhelp](https://avatars.discourse-cdn.com/v4/letter/m/f14d63/32.png) [@mysqlhelp](https://forums.percona.com/u/mysqlhelp)\ **Post date:** [March 1, 2012, 10:51am UTC](https://forums.percona.com/t/mysql-occupies-300-gb-tmp-folder-space-i-think-its-dirty-pages-flushing/1771/3 "2012-03-01T10:51:08Z")
yes actually i found this after running show process list. I had to kill the queries manually. We are using he same query for long time. I use the same query after killing it, it worked fine. Do you have any idea why this happenned?