Skip navigation links

MySQL Forums :: InnoDB :: Larger than expected tmp files querying InnoDB format database


Advanced Search

Larger than expected tmp files querying InnoDB format database
Posted by: Michael Lueck ()
Date: July 07, 2014 02:32PM

Greetings,

I am seeing excessive size of MySQL tmp files when executing queries against an InnoDB format database. Otherwise, no apparent errors or concerns.

With one query I compared our server and a peer's server:

Our Server:
Query generated a 2GB tmp file
Primary table of query focus had 100,000 records in it
Total database size is just under 200MB
Took 41 seconds to complete

Peer's Server:
Query generated a 50MB tmp file
Primary table of query focus had 200,000 records in it
Total database size is around 6GB
Tool 20 seconds to complete

My main objection is to the excessive size of tmp files created when the query is executing.

Is there some type of health check up utilities for InnoDB database support / care & feeding?

Second query compared between current production server and new production server exports a list of contacts - approximately 2400 records in total. That query will not complete on the current production server, ending in a generic Apache timeout message - not even a web application customized error message. On the new server the query does complete after a long LONG time, and again 2GB file was generated while the query was processing.

Current production server has the following software versions:
OS: CentOS release 5.9 (Final)
MySQL: 5.0.95

New server:
OS: Ubuntu 12.04
MySQL: 5.5.37

As a way of diagnosing what is going on, using the new server I obtained the full query which runs against the 100,000 record table, then added the EXPLAIN keyword to the beginning of the query. The EXPLAIN SELECT output is as follows:

+----+-------------+----------------------------------+--------+------------------------------------------------------------------------------+----------------------------------------------------------+---------+---------------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table                            | type   | possible_keys                                                                | key                                                      | key_len | ref                                                     | rows  | Extra                                        |
+----+-------------+----------------------------------+--------+------------------------------------------------------------------------------+----------------------------------------------------------+---------+---------------------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | cog                              | const  | PRIMARY,UI_name                                                              | UI_name                                                  | 194     | const                                                   |     1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | cov                              | ref    | index_option_group_id_value,index_option_group_id_name                       | index_option_group_id_name                               | 391     | const,const                                             |     1 | Using where                                  |
|  1 | SIMPLE      | civicrm_financial_account        | ALL    | PRIMARY                                                                      | NULL                                                     | NULL    | NULL                                                    |    12 | Using join buffer                            |
|  1 | SIMPLE      | civicrm_entity_financial_account | ref    | FK_civicrm_entity_financial_account_financial_account_id                     | FK_civicrm_entity_financial_account_financial_account_id | 4       | micc_civicrm.civicrm_financial_account.id               |     2 | Using where                                  |
|  1 | SIMPLE      | civicrm_contribution             | ref    | FK_civicrm_contribution_contact_id,FK_civicrm_contribution_financial_type_id | FK_civicrm_contribution_financial_type_id                | 5       | micc_civicrm.civicrm_entity_financial_account.entity_id | 24322 | Using where                                  |
|  1 | SIMPLE      | civicrm_financial_type           | eq_ref | PRIMARY,UI_id                                                                | PRIMARY                                                  | 4       | micc_civicrm.civicrm_contribution.financial_type_id     |     1 | Using where                                  |
|  1 | SIMPLE      | civicrm_contribution_product     | ref    | FK_civicrm_contribution_product_contribution_id                              | FK_civicrm_contribution_product_contribution_id          | 4       | micc_civicrm.civicrm_contribution.id                    |     1 |                                              |
|  1 | SIMPLE      | civicrm_product                  | eq_ref | PRIMARY                                                                      | PRIMARY                                                  | 4       | micc_civicrm.civicrm_contribution_product.product_id    |     1 |                                              |
|  1 | SIMPLE      | contact_a                        | eq_ref | PRIMARY,index_is_deleted                                                     | PRIMARY                                                  | 4       | micc_civicrm.civicrm_contribution.contact_id            |     1 | Using where                                  |
|  1 | SIMPLE      | civicrm_entity_batch             | ref    | index_entity                                                                 | index_entity                                             | 199     | const,micc_civicrm.civicrm_contribution.id              |     1 |                                              |
|  1 | SIMPLE      | civicrm_batch                    | eq_ref | PRIMARY                                                                      | PRIMARY                                                  | 4       | micc_civicrm.civicrm_entity_batch.batch_id              |     1 |                                              |
|  1 | SIMPLE      | civicrm_note                     | ref    | index_entity                                                                 | index_entity                                             | 198     | const,micc_civicrm.civicrm_contribution.id              |     1 |                                              |
|  1 | SIMPLE      | option_group_payment_instrument  | const  | UI_name                                                                      | UI_name                                                  | 194     | const                                                   |     1 | Using index                                  |
|  1 | SIMPLE      | payment_instrument               | ref    | index_option_group_id_value,index_option_group_id_name                       | index_option_group_id_name                               | 4       | micc_civicrm.option_group_payment_instrument.id         |     8 |                                              |
|  1 | SIMPLE      | option_group_contribution_status | const  | UI_name                                                                      | UI_name                                                  | 194     | const                                                   |     1 | Using index                                  |
|  1 | SIMPLE      | contribution_status              | ref    | index_option_group_id_value,index_option_group_id_name                       | index_option_group_id_name                               | 4       | micc_civicrm.option_group_contribution_status.id        |     8 |                                              |
+----+-------------+----------------------------------+--------+------------------------------------------------------------------------------+----------------------------------------------------------+---------+---------------------------------------------------------+-------+----------------------------------------------+

Out in the Extra column, for the top row I see "Using index; Using temporary; Using filesort", which that is the only row that indicates temporary... I assume that row is what is causing the tmp file to be generated.

Heavy diagnostic tool suggestions for InnoDB format databases would be greatly appreciated.

I am thankful,

Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/

Options: ReplyQuote


Subject Views Written By Posted
Larger than expected tmp files querying InnoDB format database 902 Michael Lueck 07/07/2014 02:32PM
Re: Larger than expected tmp files querying InnoDB format database 458 Michael Lueck 07/07/2014 04:12PM
Re: Larger than expected tmp files querying InnoDB format database 382 Rick James 07/08/2014 02:45PM
Re: Larger than expected tmp files querying InnoDB format database 453 Michael Lueck 07/10/2014 10:32AM
Re: Larger than expected tmp files querying InnoDB format database 382 Rick James 07/11/2014 12:06PM
Re: Larger than expected tmp files querying InnoDB format database 388 Michael Lueck 07/11/2014 01:24PM
Re: Larger than expected tmp files querying InnoDB format database 398 Rick James 07/12/2014 09:20AM
Re: Larger than expected tmp files querying InnoDB format database 332 Michael Lueck 07/15/2014 09:57AM
Re: Larger than expected tmp files querying InnoDB format database 342 Michael Lueck 07/15/2014 10:06AM
Re: Larger than expected tmp files querying InnoDB format database 370 Michael Lueck 07/15/2014 08:30PM
Re: Larger than expected tmp files querying InnoDB format database 327 Rick James 07/16/2014 05:10PM
Re: Larger than expected tmp files querying InnoDB format database 318 Michael Lueck 08/12/2014 03:19PM
Re: Larger than expected tmp files querying InnoDB format database 329 Rick James 08/15/2014 03:49PM
Re: Larger than expected tmp files querying InnoDB format database 304 Michael Lueck 08/20/2014 12:48PM
Re: Larger than expected tmp files querying InnoDB format database 292 Rick James 08/21/2014 12:12PM
Re: Larger than expected tmp files querying InnoDB format database 498 Michael Lueck 08/22/2014 09:34AM
Re: Larger than expected tmp files querying InnoDB format database 286 Michael Lueck 08/23/2014 08:08AM
Re: Larger than expected tmp files querying InnoDB format database 308 Rick James 08/23/2014 04:50PM
Re: Larger than expected tmp files querying InnoDB format database 315 Michael Lueck 08/23/2014 08:19PM
Re: Larger than expected tmp files querying InnoDB format database 263 Rick James 08/24/2014 08:53PM
Re: Larger than expected tmp files querying InnoDB format database 261 Michael Lueck 08/26/2014 09:58AM
Re: Larger than expected tmp files querying InnoDB format database 272 Rick James 08/27/2014 02:27PM
Re: Larger than expected tmp files querying InnoDB format database 302 Michael Lueck 08/28/2014 03:48PM
Re: Larger than expected tmp files querying InnoDB format database 294 Rick James 08/29/2014 12:40PM
Re: Larger than expected tmp files querying InnoDB format database 296 Michael Lueck 08/30/2014 09:36AM
Re: Larger than expected tmp files querying InnoDB format database 261 Michael Lueck 09/01/2014 02:16PM


Sorry, you can't reply to this topic. It has been closed.

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.