From: Johnny Withers [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> wrote: From: Ananda Kumar [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- ----------------------------- Johnny Withers 601.209.4985 [EMAIL PROTECTED]
