MySQL Forums
Forum List  »  Performance

Re: MySQL 5.6 update doubled IOs
Posted by: Christian Meisinger
Date: March 29, 2013 02:20AM

No problem Dimitri :)

Here we go. lots of text incoming...

This whole post is always here as txt file for better readability:
http://www.livingliquid.com/at/images/mysql/mysql55-56_profiling_20130329.txt


LIMIT-1 series:

Query: SELECT * FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 LIMIT 1;

MySQL 5.5:

+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | ip2location | range | ipeb | ipeb | 4 | NULL | 8131911 | Using where |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+

+----------+------------+-------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| 1 | 0.02444125 | SELECT * FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 LIMIT 1 |
+----------+------------+-------------------------------------------------------------------------------------------------------------+

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000066 |
| checking permissions | 0.000008 |
| Opening tables | 0.000019 |
| System lock | 0.000011 |
| init | 0.000078 |
| optimizing | 0.000014 |
| statistics | 0.000071 |
| preparing | 0.000024 |
| executing | 0.000004 |
| Sending data | 0.024106 |
| end | 0.000009 |
| query end | 0.000003 |
| closing tables | 0.000006 |
| freeing items | 0.000018 |
| logging slow query | 0.000003 |
| cleaning up | 0.000003 |
+----------------------+----------+


MySQL 5.6:

+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+
| 1 | SIMPLE | ip2location | range | ipeb | ipeb | 4 | NULL | 8131911 | Using index condition; Using MRR |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+

+----------+------------+-------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| 1 | 1.37092625 | SELECT * FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 LIMIT 1 |
+----------+------------+-------------------------------------------------------------------------------------------------------------+

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000047 |
| checking permissions | 0.000008 |
| Opening tables | 0.000013 |
| init | 0.000033 |
| System lock | 0.000010 |
| optimizing | 0.000010 |
| statistics | 0.000132 |
| preparing | 0.000020 |
| executing | 0.000005 |
| Sending data | 1.370303 |
| end | 0.000020 |
| query end | 0.000005 |
| closing tables | 0.000020 |
| freeing items | 0.000260 |
| cleaning up | 0.000043 |
+----------------------+----------+


COUNT-STAR series:

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

MySQL 5.5

+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | ip2location | range | ipeb | ipeb | 4 | NULL | 8131911 | Using where; Using index |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+

+----------+------------+------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------+
| 1 | 1.94352700 | SELECT count(*) FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 |
+----------+------------+------------------------------------------------------------------------------------------------------------+

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000101 |
| checking permissions | 0.000009 |
| Opening tables | 0.000121 |
| System lock | 0.000011 |
| init | 0.000030 |
| optimizing | 0.000021 |
| statistics | 0.000074 |
| preparing | 0.000018 |
| executing | 0.000005 |
| Sending data | 1.942431 |
| end | 0.000023 |
| query end | 0.000004 |
| closing tables | 0.000009 |
| freeing items | 0.000661 |
| logging slow query | 0.000007 |
| cleaning up | 0.000004 |
+----------------------+----------+


MySQL 5.6

+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | ip2location | range | ipeb | ipeb | 4 | NULL | 8131911 | Using where; Using index |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+

+----------+------------+------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------+
| 1 | 3.23581200 | SELECT count(*) FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 |
+----------+------------+------------------------------------------------------------------------------------------------------------+

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000041 |
| checking permissions | 0.000007 |
| Opening tables | 0.000012 |
| init | 0.000025 |
| System lock | 0.000038 |
| optimizing | 0.000015 |
| statistics | 0.040483 |
| preparing | 0.000041 |
| executing | 0.000005 |
| Sending data | 3.194836 |
| end | 0.000020 |
| query end | 0.000005 |
| closing tables | 0.000011 |
| freeing items | 0.000219 |
| cleaning up | 0.000056 |
+----------------------+----------+


OPTIMIZER trace output in MySQL 5.6:

SET OPTIMIZER_TRACE="enabled=on,end_marker=on";
ERROR 1231 (42000): Variable 'optimizer_trace' can't be set to the value of 'end_marker=on'

Mmmhhh... strange. I use 5.6.10 Linux - Generic (glibc 2.5) (x86, 64-bit)

mysql> show variables like '%trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=on,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 1000000 |
| optimizer_trace_offset | -1 |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

No 'end_marker=' available. Is this ok?
I've read the manual and end_marker seems to be optional and helps 'reading' the output, so i skipped it.


ANALYZE TABLE `ip2location` doesn't change anything.
OPTIMIZE TABLE `ip2location` too.


Both trace outputs:
http://www.livingliquid.com/at/images/mysql/mysql_56_ip2location_countstar.trace.out
http://www.livingliquid.com/at/images/mysql/mysql_56_ip2location_limit1.trace.out


Can i compile MySQL 5.5 to support tracing?



Edited 2 time(s). Last edit at 03/29/2013 02:23AM by Christian Meisinger.

Options: ReplyQuote


Subject
Views
Written By
Posted
1258
March 20, 2013 08:52AM
1165
March 27, 2013 11:44PM
Re: MySQL 5.6 update doubled IOs
1797
March 29, 2013 02:20AM
1195
March 28, 2013 07:49PM
1122
April 02, 2013 12:20AM
1170
April 02, 2013 09:57PM
1142
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.