Jump to content

tmp_table_size and max_heap_table_size

Recommended Posts


I've been optimizing my VPS with mysqltuner and tuning-primer and I've increased tmp_table_size and max_heap_table_size to a point that I don't understand it's behavior.

At this moment I have 112MB but with lower values (16MB) I get almost the same results:

Temporary tables created on disk: 29% (358 on disk / 1K total)

I read that Blob and Text fields are always created on disk, so maybe this is the problem since there are lot's of text fields

Any ideas?


Link to comment
Share on other sites

Yup blob and text not allowed in memory, raising RAM wil not make less tables on the disk from own experience.

Here's mine


Current max_heap_table_size = 512 M

Current tmp_table_size = 512 M

Of 50023 temp tables, 49% were created on disk

Perhaps you should increase your tmp_table_size and/or max_heap_table_size

to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your

ratio of on disk temp tables.

//Edit, btw can anyone explain the lock wait ratio?

Current Lock Wait ratio = 1 : 32
You may benefit from selective use of InnoDB.
Link to comment
Share on other sites

My vps is small, It's a 512 Linode and works very well with myisam.
If I convert post tables to innodb, the performance will be worst or I need to upgrade.

I changed both vars to it's default values.
Lets see how it goes.

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...