Created_tmp_disk_tables very high

  • Filter
  • Time
  • Show
Clear All
new posts

  • Created_tmp_disk_tables very high

    This MySQL server has been running for 0 days, 1 hours, 59 minutes and 42 seconds.

    Query statistics: Since its startup, 246,498 queries have been sent to the server.

    I dont understand why my tmp tables on disk would be so high with a 1G tmp_table_size and Max_heap_table_size

    I am running CentOS5.3 and 5.0.82 percona b16.. I have 12GB of ram and dual 3.04G CPU. Just upgraded to be able to change my temp directive.

    Created_tmp_disk_tables 34 k
    Created_tmp_files 5
    Created_tmp_tables 49 k

    tmp_table_size = 1G
    max_heap_table_size = 1G

    Any ideas?

  • #2
    Text columns cannot be sorted in memory. So improve your indices.


    • #3
      Im running wordpress 2.7 on all the domains.. Not sure if I can improve the indices?

      Sounds like its more then just the index since you say the text columns can not be sorted in memory. Almost every query includes a text column.

      I have created a tmpfs for the tmp data area I expect that is helping by allowing the tmp files to be created in ram disk.


      • #4
        Wordpress has very bad database utilization. The indices are bad, the queries are bad. Wordpress wasn't ever designed to handle large blogs.

        I have put some efforts in optimizations of Wordpress, you should update to the most recent version. That helps only slightly. You should use supercache or a similar plug-in, and in the long-term look out for better blogging software if you intend to grow.


        • #5
          Well, we have already grown to 1400 blogs.. that's the problem.. Backend posting is taking forever. (

          I have moved all SQL to a dedicated server and innobd only databases.


          • #6
            Then reduce the size of those in-memory tables to default values (couple of meg each iirc) and increase your innodb buffer pool. And take a look at other innodb tweaking parameters. That should help some, but you have got to live with on disk sorting for Wordpress.

            And remove all Wordpress plug-ins or make sure you can optimize them yourself. Most are bad as well.


            • #7
              Thanks for the feedback

              You just reconfirmed where my mind was going on all this.

              I am not going to waste the 1G on temp tables in ram if they are going to disk anyway. I am also going to put in a second MySQL server and split the load, besides the other items you suggested.

              Long term I need to find another blog that manages data and queries better. Its going to be a bear to move everything, but the problem is only going to get worse. For now I just need to throw more hardware at the problem.