MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Michael Lueck
Date: August 12, 2014 03:19PM

Greetings Rick,

All right, the client VM server has been upgraded to 4GB RAM as of yesterday afternoon. The query in question performs now at twice the speed. Yea! :-)

Peak memory draw for the entire system while executing the one query directly via MySQL and a Bash script resulted in the following utilization:

1,156MB RAM
2GB tmp file
Query Duration: 22 seconds

In a 4GB RAM system, I perceive some adjustments to the settings would result in more/all of the query being executed in RAM rather than in a tmp file scratchpad.

Could you kindly suggest what settings to change within MySQL 5.5.38 in order to tip that RAM/disk ballence point towards RAM?

Question Part B: Could you briefly describe why the query in question was behaving badly? I will copy/paste that feedback into the CiviCRM forum to conclude that discussion thread there. I suspect perhaps since I see a couple of different queries all running slowly / generating "2GB tmp files" that perhaps their optimization issue is common for many of their queries.

I am thankful,

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

Options: ReplyQuote


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


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.