MySQL Forums
Forum List  »  Performance

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

Hello guys


my.cnf is the same for 5.5 and 5.6.


One of the biggest slowdowns is IP2Location, we use DB14 version: http://ip2location.com/databases/db14-ip-country-region-city-latitude-longitude-zipcode-timezone-isp-domain-netspeed

The page also shows the table layout.

Feel free to buy it and test it :)


Our ip2location table:

Data 886.3 MiB
Index 115.9 MiB

Key Type Unique Packed Column Cardinality
ipeb BTREE No No ip_end 8498157
ip_begin 8498157


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

Duration in 5.6: 1 row in set (1.34 sec)
EXPLAIN output 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 |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+

Duration in 5.5: 1 row in set (0.03 sec)
EXPLAIN output 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 |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+


The weird thing is: looking at the EXPLAIN output 5.6 should be faster?

Options: ReplyQuote


Subject
Views
Written By
Posted
1254
March 20, 2013 08:52AM
1160
March 27, 2013 11:44PM
Re: MySQL 5.6 update doubled IOs
1186
March 28, 2013 02:17AM
1191
March 28, 2013 07:49PM
1115
April 02, 2013 12:20AM
1165
April 02, 2013 09:57PM
1137
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.