Re: MySQL 5.6 update doubled IOs

[Dimitri Kravtchuk]

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