Does innodb have a temp table space?

Does innodb have a temp table space?

am 02.09.2010 03:04:43 von Neutron

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ï¼=9F 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Does innodb have a temp table space?

am 02.09.2010 03:58:11 von Daevid Vincent

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:neutronsharc@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=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Does innodb have a temp table space?

am 02.09.2010 11:34:30 von Johan De Meersman

--000e0cd592506cb70a048f4385c5
Content-Type: text/plain; charset=ISO-8859-1

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:neutronsharc@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=daevid@daevid.com
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@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

--000e0cd592506cb70a048f4385c5--

Re: Does innodb have a temp table space?

am 02.09.2010 19:39:28 von Neutron

Hello Johan,

Thanks for the reply.

On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman wrot=
e:
> I suspect he is talking about the Temp Tablespace concept from Oracle, wh=
ich
> is different from a temporary table or a memory table.
>
> MySQL will allocate a memory table for sort operation and the like, up un=
til
> 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:neutronsharc@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. =A0If I don't use "innodb_file_per_table", =A0 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=3Ddaevid@daevid.com
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dvegivamp@tux=
era.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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Does innodb have a temp table space?

am 02.09.2010 22:32:40 von shawn.l.green

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-ta bles.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#o ption_mysqld_tmpdir
http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html#sysvar_max_heap_table_size
http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.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=gcdmg-mysql-2@m.gmane.org