Re: Does innodb have a temp table space?
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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does innodb have a temp table space?
Hello Johan, Thanks for the reply. On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman 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"? > > > On Thu, Sep 2, 2010 at 3:58 AM, Daevid Vincent wrote: >> >> InnoDB is one of MANY engines in the RDBMS mySQL. >> >> There IS in fact a few ways to store in temporary tables (both RAM and >> DISK >> based) >> >> http://dev.mysql.com/doc/refman/5.1/en/create-table.html >> >> Look at: >> >> TABLESPACE >> PARTITIONS >> ENGINE >> >> > -Original Message- >> > From: neutron [mailto:neutronsh...@gmail.com] >> > Sent: Wednesday, September 01, 2010 6:05 PM >> > To: mysql@lists.mysql.com >> > Subject: Does innodb have a temp table space? >> > >> > Hi all, >> > >> > As far as I know, some DB has a separate table space to store temp >> > data (such as for external sort). >> > >> > My questions are: >> > >> > 1. Does innodb also has a separate temp-tablespace? >> > 2. If I don't use "innodb_file_per_table", where is innodb's >> > temporary tablespace? Is it in the shared tablespace? >> > >> > >> > Thanks all! >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: >> > http://lists.mysql.com/mysql?unsub=dae...@daevid.com >> > >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be >> > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does innodb have a temp table space?
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. On Thu, Sep 2, 2010 at 3:58 AM, Daevid Vincent wrote: > InnoDB is one of MANY engines in the RDBMS mySQL. > > There IS in fact a few ways to store in temporary tables (both RAM and DISK > based) > > http://dev.mysql.com/doc/refman/5.1/en/create-table.html > > Look at: > > TABLESPACE > PARTITIONS > ENGINE > > > -Original Message- > > From: neutron [mailto:neutronsh...@gmail.com] > > Sent: Wednesday, September 01, 2010 6:05 PM > > To: mysql@lists.mysql.com > > Subject: Does innodb have a temp table space? > > > > Hi all, > > > > As far as I know, some DB has a separate table space to store temp > > data (such as for external sort). > > > > My questions are: > > > > 1. Does innodb also has a separate temp-tablespace? > > 2. If I don't use "innodb_file_per_table", where is innodb's > > temporary tablespace? Is it in the shared tablespace? > > > > > > Thanks all! > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: Does innodb have a temp table space?
InnoDB is one of MANY engines in the RDBMS mySQL. There IS in fact a few ways to store in temporary tables (both RAM and DISK based) http://dev.mysql.com/doc/refman/5.1/en/create-table.html Look at: TABLESPACE PARTITIONS ENGINE > -Original Message- > From: neutron [mailto:neutronsh...@gmail.com] > Sent: Wednesday, September 01, 2010 6:05 PM > To: mysql@lists.mysql.com > Subject: Does innodb have a temp table space? > > Hi all, > > As far as I know, some DB has a separate table space to store temp > data (such as for external sort). > > My questions are: > > 1. Does innodb also has a separate temp-tablespace? > 2. If I don't use "innodb_file_per_table", where is innodb's > temporary tablespace? Is it in the shared tablespace? > > > Thanks all! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org