MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Rick James
Date: August 21, 2014 12:12PM

> THEN go off doing the various JOIN statements for only those rows, that alone would work much faster.

You got it. :)
And that probably shrank the tmp table size significantly.

innodb_buffer_pool_size should probably be 1500M to 2000M for your 4GB of RAM.

> and not run off to the tmp file option so much

That's a tricky question. There is perhaps one tunable; if you increase it, you need to decrease other important tunables. If you decrease it, you may spill to disk more. Damned if you do; damned if you don't. That tunable does not apply for your select (see below).

> mysql tuning-primer.sh

Sure, run it. But come back to me so I can point out some of its flaws. The most glaring is that it always says to OPTIMIZE tables, yet that is almost never useful.

I'll provide a different set of analysis if you provide
SHOW VARIABLES;
SHOW GLOBAL STATUS; (after being up at least a day)

Another issue... Temp tables are done in one of two ways:
* MEMORY table (no I/O), or
* MyISAM table (and likely to spill to disk)
However: this prevents use of MEMORY:
> civicrm_contribution.cancel_reason as `cancel_reason`,
> `cancel_reason` text ,
That is, if you are SELECTing a TEXT field, the optimizer does not even try to use MEMORY. Can you avoid any TEXT fields?
(There are other restrictions, plus cases where it starts with MEMORY, then converts to MyISAM, such as when it gets too big.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1000
August 21, 2014 12:12PM


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.