Why is MySQL using /tmp?

--=_alternative 0020226B852575D2_=
Content-Type: text/plain; charset="US-ASCII"

I find my MySQL Community Edition 5.1.34 server running out of space on
/tmp (which is indeed small). Why is it using /tmp? How much free space
do I need on /tmp? Can/should I make the server use a different location
instead of /tmp?

Thanks,
Mike Spreitzer

--=_alternative 0020226B852575D2_=--

Re: Why is MySQL using /tmp?

--000e0cd485403eb039046c0d2eb5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

If I understand the manual correctly, it uses /tmp for creating temporary
tables if their size exceed the smaller of tmp_table_size or
max_heap_table_size, otherwise, it will create the temporary table in
memory. So if tmp_table_size is set to say 10Mb, and MySQL needs to create a
temporary table internally (which it does need for sorting and other stuff)
which would be about 20Mb, it's going to create it in /tmp. If that
temporary table had been 5Mb, it would have created it in memory.

I'm not sure how to calculate the needed space for /tmp. Check this for how
to change the tmp directory location:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html#sysvar_tmpdir

Cheers,
Amr

On Thu, Jun 11, 2009 at 8:51 AM, Mike Spreitzer wrote:

> I find my MySQL Community Edition 5.1.34 server running out of space on
> /tmp (which is indeed small). Why is it using /tmp? How much free space
> do I need on /tmp? Can/should I make the server use a different location
> instead of /tmp?
>
> Thanks,
> Mike Spreitzer
>

--000e0cd485403eb039046c0d2eb5--

Re: Why is MySQL using /tmp?

--000e0cd29d36c7c1a2046c0f0de2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Yes it is used for temporary tables.
You can also set the directory for temporary tables to some bigger
partition.

Cheers

Claudio


2009/6/11 Amr Mostafa

> If I understand the manual correctly, it uses /tmp for creating temporary
> tables if their size exceed the smaller of tmp_table_size or
> max_heap_table_size, otherwise, it will create the temporary table in
> memory. So if tmp_table_size is set to say 10Mb, and MySQL needs to create
> a
> temporary table internally (which it does need for sorting and other stuff)
> which would be about 20Mb, it's going to create it in /tmp. If that
> temporary table had been 5Mb, it would have created it in memory.
>
> I'm not sure how to calculate the needed space for /tmp. Check this for how
> to change the tmp directory location:
>
>
> http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html#sysvar_tmpdir
>
> Cheers,
> Amr
>
> On Thu, Jun 11, 2009 at 8:51 AM, Mike Spreitzer
> wrote:
>
> > I find my MySQL Community Edition 5.1.34 server running out of space on
> > /tmp (which is indeed small). Why is it using /tmp? How much free space
> > do I need on /tmp? Can/should I make the server use a different location
> > instead of /tmp?
> >
> > Thanks,
> > Mike Spreitzer
> >
>

--000e0cd29d36c7c1a2046c0f0de2--

Re: Why is MySQL using /tmp?

This might help:

http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html

On Jun 11, 2009, at 12:51 AM, Mike Spreitzer wrote:

> I find my MySQL Community Edition 5.1.34 server running out of space
> on
> /tmp (which is indeed small). Why is it using /tmp? How much free
> space
> do I need on /tmp? Can/should I make the server use a different
> location
> instead of /tmp?
>
> Thanks,
> Mike Spreitzer

--
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.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