TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

Comments

1. Dathan Pattishall  says:
   January 20, 2007 at 5:10 pm

   I remember looking in the code, that tmp_table_size is also used for certain internal data structures, and setting it higher was a small tweak that one could do for overall performance gain. Now there is this, good to know.
2. peter  says:
   January 20, 2007 at 5:20 pm

   Yes quite possible.

   In fact I remember there were few other cases when non documented parameters are taken into consideration
3. Sergei Golubchik  says:
   January 21, 2007 at 5:18 am

   The description is slightly wrong.
   The lower value from tmp_table_size and use max_heap_table_size was used to define when temporary table was converted from memory to disk.
   Also tmp_table_size was used to limit the size of the temporary table – no matter whether it’s in memory or on disk.
   And max_heap_table_size was also used to limit the size of explicitly created HEAP tables.

   The fix is to introduce another variable memory_tmp_table_size and use it instead of the “lower value …”, to define when a temporary table is converted to MyISAM. Thus max_heap_table_size will have no effect on temporary tables, and tmp_table_size keeps its “upper boundary” meaning.
4. peter  says:
   January 21, 2007 at 2:11 pm

   Sergei,

   Yes I know lower value was used but it was not documented – I quoted the manual and config files give you the same idea – want larger in memory tables increase tmp_table_size.

   Now regarding “Also tmp_table_size was used to limit the size of the temporary table – no matter whether it’s in memory or on disk.” Where does this come from ?

   I constantly see temporary tables sized in gigabytes while tmp_table_size is set to 32M or similar.

   If that would be correct fix would be reasonable but it is not. Simply test it 
5. Sergei Golubchik  says:
   January 21, 2007 at 3:41 pm

   I saw that in the code. But now, after testing, I see that, indeed, tmp_table_size doesn’t limit the size of on-disk tables (table->s->max_rows is set to limit it, but it’s not copied to ci->max_rows for mi_create to see it) – it must be a bug too.
6. peter  says:
   January 21, 2007 at 3:44 pm

   Thank you for looking into in Sergei

   While fixing bug I just would make sure there are limited behavior changes compared to old timers expectations.
7. James Day  says:
   January 21, 2007 at 11:51 pm

   Peter, thanks for mentioning this. I expect that the plan will be changed now Sergei knows that it would break backwards compatibility. A new synonym for tmp_table_size that is the memory limit and a new variable for disk size would do the job without breaking existing setups. Then tmp_table_size can be deprecated.
8. James Holt  says:
   January 23, 2007 at 12:48 pm

   Peter, reading this I thought I recalled reading about it in the docs somewhere.
   I looked around and found it here:

   http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
   (scroll down to tmp_table_size)

   Not defending the behavior by any stretch; it is highly anti-intuitive.

   -James Holt
9. peter  says:
   January 23, 2007 at 1:03 pm

   Thank you James,

   I see it got added there. As MySQL old timer I did not get back to that page for other than new variables as I remember meaning about them.

   The quote I gave was from this url:

   http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

   I guess I would expect working do be different in this place to.

   It is probably hard to keep such large document synchronized.
10. rajeesh  says:
    November 15, 2007 at 3:36 am

    tmp_table_size=45M , if its increse or decrese what will the effect of mysql database
11. Rubén Ortiz  says:
    January 30, 2009 at 3:51 am

    Hi again

    last comment was on 2007. Do you know if bug is fixed now? Peter says that “I constantly see temporary tables sized in gigabytes”. Could you be so kind to explain to us how you see it?

    Thanks!!
12. Rubén Ortiz  says:
    January 30, 2009 at 4:25 am

    Hi, excuse me for ask before find it better. I read this on MySQL,

    “ChangeSet@stripped, 2007-01-03 14:45:26+03:00, ted@stripped +7 -0
    BUG #4291: max_heap_table_size affects creation of disk-based temporary table

    fix: the new system variable memory_tmp_table_size is introduced; it stands now for the exact purpose the Manual says tmp_table_size used to do.

    tmp_table_size retains to (give a hint about a) limit of the on-disk temporary table size. The limit imposed upon the disk-based temporary tables is still quite relative due to MyISAM current implementation restrictions.”

    So I understand this buf was fixed to 5.X and tmp_table_size is now deprecated, isn’t it?

    Thanks!
13. Andrew  says:
    December 9, 2010 at 2:25 pm

    mysqlreport reports:

    __ Created Temp ________________________________________________________
    Disk table 112 0.1/s
    Table 332 0.2/s Size: 512.0M
    File 88 0.1/s

    I read on the mysqlreport doco that ‘Disk table’ is used when a temporary table will not fit in the allocated tmp_table_size (in this case 512MB).

    My first question is: what is the difference between internal memory (tmp_table_size) and explicit memory (max_heap_table_size)?

    My second question is: how can you determine the
    1.largest
    2.most frequent

    table size created on disk so that tmp_table_size (or is it max_heap_table_size, or both?) can be adjusted upwards in size?
14. Byte  says:
    November 29, 2011 at 4:36 pm

    As it happends, we are running into performance issues when we set both of these variables to 128M on our MySQL server instead of leaving these values untouched (they were not set in my.cnf so 16M is the default). Alot of simple “SELECT RowById WHERE ColumnA IN (SELECT Values From TEMPTABLE)” queries suddenly took up to 10x as long as before.

    When we change the MySQL config back, everything was back to normal.

    Can anybody hint me to an explanation of some sort?

    Thanks!

    
    

    
    

    
    

    ---------------------------------------------

    http://dev.mysql.com/doc/refman/5.0/en/memory-use.html
    

     How MySQL Uses Memory

    The following list indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the system variable relevant to the memory use is given:

    • All threads share the MyISAM key buffer; its size is determined by the key_buffer_size variable. Other buffers used by the server are allocated as needed. See Section 8.9.2, “Tuning Server Parameters”.

    • Each thread that is used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:

      • A stack (variable thread_stack)

      • A connection buffer (variable net_buffer_length)

      • A result buffer (variable net_buffer_length)

      The connection buffer and result buffer each begin with a size equal to net_buffer_length bytes, but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks tonet_buffer_length bytes after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.

    • All threads share the same base memory.

    • When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

    • Only compressed MyISAM tables are memory mapped. This is because the 32-bit memory space of 4GB is not large enough for most big tables. When systems with a 64-bit address space become more common, we may add general support for memory mapping.

    • Each request that performs a sequential scan of a table allocates a read buffer (variable read_buffer_size).

    • When reading rows in an arbitrary sequence (for example, following a sort), a random-read buffer (variableread_rnd_buffer_size) may be allocated to avoid disk seeks.

    • All joins are executed in a single pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based hash tables. Temporary tables with a large row length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk.

      If an internal in-memory temporary table becomes too large, MySQL handles this automatically by changing the table from in-memory to on-disk format, to be handled by the MyISAM storage engine. You can increase the permissible temporary table size as described in Section 8.8.4, “How MySQL Uses Internal Temporary Tables”.

    • Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size. See Section C.5.4.4, “Where MySQL Stores Temporary Files”.

    • Almost all parsing and calculating is done in thread-local and reusable memory pools. No memory overhead is needed for small items, so the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings.

    • For each MyISAM table that is opened, the index file is opened once; the data file is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * N are allocated (where N is the maximum row length, not counting BLOB columns). A BLOBcolumn requires five to eight bytes plus the length of the BLOB data. The MyISAM storage engine maintains one extra row buffer for internal use.

    • For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.

    • Handler structures for all in-use tables are saved in a cache and managed as a FIFO. The initial cache size is taken from the value of the table_cache system variable. If a table has been used by two running threads at the same time, the cache contains two entries for the table. See Section 8.8.2, “How MySQL Opens and Closes Tables”.

    • A FLUSH TABLES statement or mysqladmin flush-tables command closes all tables that are not in use at once and marks all in-use tables to be closed when the currently executing thread finishes. This effectively frees most in-use memory. FLUSH TABLES does not return until all tables have been closed.

    • The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

    ps and other system status programs may report that mysqld uses a lot of memory. This may be caused by thread stacks on different memory addresses. For example, the Solaris version of ps counts the unused memory between stacks as used memory. To verify this, check available swap with swap -s. We test mysqld with several memory-leakage detectors (both commercial and Open Source), so there should be no memory leaks.

    Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved.  Legal Notices

    Previous /  Next /  Up /  Table of Contents

    
    

    User Comments

    Posted by Kelly Campbell on May 13 2005 5:03pm

    [Delete] [Edit]

    tmp_table_size is not the only variable that determines when a tmp table is written to disk. max_heap_table_size also applies.
    

    Posted by sheila yao on October 2 2007 6:01pm

    [Delete] [Edit]

    I got this formula from mysql error log complaining it doesn't have enough memory to start mysqld:
    key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory 
    
    I hope this document could be straight forward by providing a formula to calculate the memory usage for mysqld. 
    
    Sheila 
    
    

    Posted by Guy Baconniere on May 6 2009 3:50pm

    [Delete] [Edit]

    I use the following SQL query to guess MySQL memory usage 
    of MySQL unfortunately innodb_* and thread_stack are not
    part of MySQL system variables so you need to fill them
    manually.
    
    Best Regards,
    Guy Baconniere
    
    --
    
    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
    SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
    SHOW VARIABLES LIKE 'innodb_log_buffer_size';
    SHOW VARIABLES LIKE 'thread_stack';
    SET @kilo_bytes = 1024;
    SET @mega_bytes = @kilo_bytes * 1024;
    SET @giga_bytes = @mega_bytes * 1024;
    SET @innodb_buffer_pool_size = 2 * @giga_bytes;
    SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
    SET @innodb_log_buffer_size = 8 * @mega_bytes;
    SET @thread_stack = 192 * @kilo_bytes;
    SELECT
    ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
    + @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
    + @innodb_log_buffer_size
    + @@max_connections * (
    @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
    + @@join_buffer_size + @@binlog_cache_size + @thread_stack
    ) ) / @giga_bytes AS MAX_MEMORY_GB;
    

    +---------------+
    | MAX_MEMORY_GB |
    +---------------+
    |        3.7002 |
    +---------------+
    

    1 row in set (0.00 sec)
    

    Posted by Ligaya Turmelle on March 19 2010 7:06pm

    [Delete] [Edit]

    I disagree with how the previous comment handles the tmp_table_size value. They treat it as a single allocation on the global scope when for memory consumption purposes it is more in line with a per thread buffer.
    
    A single connection/query can use a single or multiple temporary tables in the duration of its processing. The connections do not use a single temporary table "area" reserved just for that purpose.
    
    If you are going to use a formula for memory consumption, the tmp_table-size should be located with the other per thread buffers - not in the single allocation listing.
    

    Add your own comment.