From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2008 1:58 AM To: Jerry Schwartz Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com 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 <[EMAIL PROTECTED]> wrote: >-----Original Message----- >From: news [mailto:[EMAIL PROTECTED] On Behalf Of >[EMAIL PROTECTED] >Sent: Tuesday, August 26, 2008 2:36 AM >To: mysql@lists.mysql.com >Subject: Re: Huge temporary file > >In infinite wisdom "Jerry Schwartz" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
