MySQL Forums
Forum List  »  Performance

Re: Performance tuning - comparing two computers
Posted by: Rick James
Date: January 26, 2013 12:11AM

These numbers continually increase:
SHOW STATUS LIKE 'Handler%';
so you need to run it both before and after the SELECT, then see how much the values increased. Meanwhile, assuming they started with zero, I see that it scanned through 4 million rows. Could there be that many rows in 2010 and 2011?

The EXPLAIN picked the PRIMARY KEY and estimated 3253104 rows. (That is close enough to agree with the 4M.)

GROUP BY FUND caused the filesort.

If mysql is 32-bit, then this won't work, but I don't know what will happen:
innodb_buffer_pool_size,4294967296

innodb_flush_log_at_trx_commit,1 -- 1 is more secure, but slower than 2 -- you have a difference here.

innodb_log_file_size,1073741824 is quite different. It _might_ matter for a 4-million row query.

innodb_file_per_table,ON is better. (But won't explain the speed diff.)

Look for more differences and study what they mean.

A "summary table" (for this case) would have daily subtotals for FUND, SUM(PURCHASES). Then, the 2-year query would only need to look at 730 rows, not a 4 million. It would, however, mean adding a new row each day.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance tuning - comparing two computers
1059
January 26, 2013 12:11AM


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.