List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:September 2 2010 8:32pm
Subject:Re: Does innodb have a temp table space?
View as plain text  
On 9/2/2010 1:39 PM, neutron wrote:
> Hello Johan,
> 
> Thanks for the reply.
> 
> On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman <vegivamp@stripped> wrote:
>> I suspect he is talking about the Temp Tablespace concept from Oracle, which
>> is different from a temporary table or a memory table.
>>
>> MySQL will allocate a memory table for sort operation and the like, up until
>> that table exceeds a preset limit, at which point it will automatically (and
>> costly !) be converted to a disk table.
>>
> ==>
> How to define the memory table limit?
> When  the temp table is converted to a disk table, where is this disk
> table stored? In the same shared tablespace file if I don't use
> "innodb_file_per_table"?
> 

The automatically-converted tables produced by the system as part of SQL 
command processing start off as MEMORY tables unless they contain data 
that the MEMORY storage engine does not support. If they do contain 
unsupported data types or if they exceed the size of the smaller of 
--max-heap-table-size or --tmp-table-size, then the table is converted 
to a MYISAM table.

The folder for temporary tables is controlled by the --tmpdir parameter.

This behavior and the configuration variables I discussed are covered in 
more detail in these links:

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size


-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
Does innodb have a temp table space?neutron2 Sep
  • RE: Does innodb have a temp table space?Daevid Vincent2 Sep
    • Re: Does innodb have a temp table space?Johan De Meersman2 Sep
      • Re: Does innodb have a temp table space?neutron2 Sep
        • Re: Does innodb have a temp table space?MySQL)2 Sep