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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.