GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql occupies 300 gb tmp folder space. i think its dirty pages flushing

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql occupies 300 gb tmp folder space. i think its dirty pages flushing

    **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

  • #2
    Dirty pages don't get flushed into the temp directory. Those are temporary tables from queries. You have a problem with bad execution plans.

    Comment


    • #3
      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?

      Comment


      • #4
        Yes. Too much concurrency can cause a lot of contention on resources. Sometimes a query is fine if there is only one of them, but a serious problem with many. This is a common and pretty basic type of issue a DBA needs to be prepared to diagnose, solve, and prevent.

        I'd suggest getting a copy of High Performance MySQL. You can see a sample chapter at http://www.highperfmysql.com. Percona is also available for support. http://www.percona.com/mysql-support/

        Comment

        Working...
        X