***BOGO*** RE: Huge temporary file

["Jerry Schwartz" <jschwartz@xxxxxxxxxxxxxxxx>]

From: Johnny Withers [mailto:johnny@xxxxxxxxxxxxx] 
Sent: Tuesday, August 26, 2008 9:37 AM
To: Jerry Schwartz
Subject: Re: Huge temporary file

 

You are using the defaults then, I'm not sure what they are, but you can
view the location of the temp directory (probably /tmp) and the maximum size
of any temporary table by issuing the command: 

 

show variables like '%tmp%';

 

You can view the maximum size of heap tables by:

 

show variables like '%heap%';

 

I find a setting of 256MB for both of these works well for me (8gb ram).

 

[JS] I suspected that would be the one to tinker with, but MySQL is eating
up over 900MB and I'm not sure that I should set my heap that big. My real
question is, why does this need such an enormous amount of space?

 

-johnny

 

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

From: Ananda Kumar [mailto:anandkl@xxxxxxxxx]
Sent: Tuesday, August 26, 2008 12:09 AM
To: Jerry Schwartz
Cc: mysql
Subject: Re: Huge temporary file



what is the value for tmpdir parameter in you my.cnf.



[JS] I don't have one. The /tmp file system is what is being consumed. I
should add that the temporary space being used is many times the size of any
of the tables involved.



My entire /etc/my.cnf file looks like this:



[mysqld]

set-variable = max_connections=500

set-variable = ft_min_word_len=3

set-variable = character_set_server=utf8

safe-show-database



regards

anandkl



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

Awhile back I was having trouble with an INSERT ... SELECT taking up an
enormous amount of temporary file space. I've narrowed down, in fact
eliminated, my problem by making a minor change. Here is my original INSERT
command:


INSERT INTO consolidated_customer_data
  SELECT
      customers.customer_id,
      account.account_name,
      customers.email,
      customers.email_status,
              customers.dm_status,
              customers.status,
      customers.last_name,
      customers.first_name,
      customers.sal,
      customers.company,
      customers.address_1,
      customers.address_2,
      customers.address_3,
      customers.country,
      customers.zip,
      customers.input_source,
              customers.interest_category,
              customers.interest_subcategory,
      CONCAT("|", GROUP_CONCAT(giiexpr_db.topic.topic_code SEPARATOR "|"),
"|")
          AS topic_list,
              stage.stage_name
  FROM
      customers
  JOIN account ON account.account_id = customers.account_id
  JOIN stage ON customers.stage_id = stage.stage_id
  LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id
  LEFT JOIN giiexpr_db.topic ON cust_topics.topic_id =
giiexpr_db.topic.topic_id
  GROUP BY customers.customer_id;

When I removed the field `stage_name` from both the query and the
`consolidated_customer_data` table, the operation stopped using temporary
files altogether! 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.

The `stage` table is very small, it has only 9 rows.

CREATE TABLE `stage` (
`stage_id` int(11) NOT NULL auto_increment,
`stage_name` varchar(15) default NULL,
PRIMARY KEY  (`stage_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

I can supply the structures of the other tables, but I wanted to keep this
post reasonably short.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com






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







-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@xxxxxxxxxxxxx