tmp_table_size, what is too large?

  • Filter
  • Time
  • Show
Clear All
new posts

  • tmp_table_size, what is too large?

    On this particular DB server we have some very very large tables, and large temp tables being created (many GB in some cases). However I am apprehensive about turning the max_heap_table_size and tmp_table_size up too far... We have a TON of free memory on the system (like 50 GB unused for example) and want many more tables to be created in memory. How far can I safely go with these values?

    Here is what it looks like now:

    Current max_heap_table_size = 512 M
    Current tmp_table_size = 512 M
    Of 282433 temp tables, 44% 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

  • #2
    Your values are insanely high. One reason reason tables are created on disk is having text columns:
    http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine .html
    "MEMORY tables cannot contain BLOB or TEXT columns. "