MySQL Forums
Forum List  »  Performance

Re: MySQL 5.6 update doubled IOs
Posted by: Dimitri Kravtchuk
Date: March 28, 2013 09:55AM

Hi Christian,

regarding your query - may also provide PROFILE reports for both executions?

just do:
mysql> set profiling = 1;

then execute your query..

then find the ID of your query within profiles:
mysql> show profiles;

and print profile report for it:
mysql> show profile for query ID;


Then, still without changing anything, may you also execute it on 5.5 and 5.6 as the following:

SELECT count(*)
FROM `configuration`.`ip2location`
WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676;

(without LIMIT, but just a count(*) -- I'm curious if the execution plan is completely wrong here or makes sense, and just wrong for "LIMIT 1")..

including EXPLAIN and PROFILE (of course) ;-)

(let's call these queries then as "Count-star" and "Limit-1")

And then for 5.6 (as it's available only since 5.6) print optimizer traces for both queries:

enable traces:
mysql> SET OPTIMIZER_TRACE="enabled=on,end_marker=on";
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

then execute your query..

then print a report:
mysql> SELECT TRACE INTO DUMPFILE '/tmp/trace.out' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Once you have all this stuff (and only then) try now:
mysql> ANALYZE TABLE ip2location;

and then try your query again.. - if time will be better (or worse), then GOTO on the top and replay all the steps again.. - if nothing will change then just stop here :-)

Please, send all these data here, and once it'll be available, I'll bring attention from Optimizer Team, I'm pretty sure they will be very curious to analyze and fix your case ;-)

(also, sorry to ask you to run all this stuff, but there is no other way here, except if you're able to create a test case with some non confidential data and let us play with it)..

Rgds,
-Dimitri

Options: ReplyQuote


Subject
Views
Written By
Posted
1281
March 20, 2013 08:52AM
1184
March 27, 2013 11:44PM
Re: MySQL 5.6 update doubled IOs
1240
March 28, 2013 09:55AM
1221
March 28, 2013 07:49PM
1149
April 02, 2013 12:20AM
1190
April 02, 2013 09:57PM
1162
April 10, 2013 08:31AM


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.