List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:August 27 2008 2:29pm
Subject:RE: Huge temporary file
View as plain text  
From: Ananda Kumar [mailto:anandkl@stripped] 
Sent: Wednesday, August 27, 2008 1:58 AM
To: Jerry Schwartz
Cc: rajlist@stripped; mysql@stripped
Subject: Re: Huge temporary file

 

do,

 

show varaibles like '%tmp%'.

 

show variables like '%tmp%';

| tmpdir            | /tmp/  

 

This is case of your tmp file system, running out space.

[JS] tmpdir is left to the default.

You can change this parameter to a different file system having more space,
and then your job should run fine.

[JS] True, but I'd rather not if I don't have to. It looks like that query
would chew up over a gigabyte, if I let it. I'd rather have it fail than
have other parts of the system fall over. This query is not critical to our
production. 

regards

anandkl

 

On 8/26/08, Jerry Schwartz <jschwartz@stripped> wrote: 

>-----Original Message-----
>From: news [mailto:news@stripped] On Behalf Of
>rajlist@stripped
>Sent: Tuesday, August 26, 2008 2:36 AM
>To: mysql@stripped
>Subject: Re: Huge temporary file
>
>In infinite wisdom "Jerry Schwartz" <jschwartz@stripped> spoke
>thus:
>> I'm at a loss as to why, other than that I must have hit
>> some threshold. If anyone can tell me what I need to change in my
>> configuration, I'd appreciate it.
>
>MySQL creates the tmp tables in memory if the size of the table matches
>these thresholds
>max_heap_table_size
>tmp_table_size
>
>Whichever of these two values is smaller is the one that applies.
>
>Is the datatype of consolidated_customer_data.stage_name varchar(15)?
>
>(There are other conditions too, but since you can fit the table by
>removing one column, I am assuming you are hitting size threshold
><http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html>)
>
[JS] According to that article, it is the presence of a TEXT field that is
forcing the use of a temporary table. The JOIN to the `stage` table must
have been causing the temporary table to disk.

That doesn't explain why the without the `stage` table, the temporary table
fits in the (default) heap size of 16777216 and (default) tmp_table_size of
33554432; but with the `stage` table MySQL needs more than 973M.

I might have to just give up on this and put it in the X file.
>--
>raj shekhar
>facts: http://rajshekhar.net
>opinions: http://rajshekhar.net/blog
>I've never made anyone's life easier and you know it!
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

 



Thread
Huge temporary fileJerry Schwartz25 Aug
  • Re: Huge temporary fileAnanda Kumar26 Aug
    • RE: Huge temporary fileJerry Schwartz26 Aug
  • Re: Huge temporary filerajlist26 Aug
    • RE: Huge temporary fileJerry Schwartz26 Aug
    • RE: Huge temporary fileJerry Schwartz26 Aug
      • Re: Huge temporary fileAnanda Kumar27 Aug
        • RE: Huge temporary fileJerry Schwartz27 Aug
RE: Huge temporary fileJerry Schwartz26 Aug