Creating sort index killing performance after converting from MyISAM
Hello Community,
if have a big performance problem that i totally don't understand, so I need your help.
I tried converting my vbulletin tables from MyISAM to InnoDB for performance reasons, but ended up with a system 10-100 times slower then before.
After reading a lot ob Blogs it can't be possible that InnoDB is so much slower, so I search the problem in my setup, but didn't find anything.
You can see the table layout, query, explain and profiling (first execution of the query, second execution of the query) at the end of the post, with a identical table comparing InnoDB an MyISAM.
My problem is the "Creating sort index 0.473379" in the first query execution. A lot of my queries go up to 20 seconds, only because of the "ORDER BY" part in the query. I can't believe is cost half a second to sort 6000 rows…
As it is a bulletin board with thousands of users, I have only less recurring queries, so a lot of "first execution" with shown problems (my slow query logs exploded in seconds).
I am at the point to reconvert to MyISAM, but would really like to understand the problem, because the file sort algorithm should be engine independent and I don't understand why other people don't suffer from that problem (cannot find a lot of hints in the web).
Thanks in advanced!
Bernd
SERVER:
MySQL 5.6.13 x86_64 Debian
CONFIG (PARTLY):
sort_buffer_size = 8M
read_buffer_size = 8M
innodb_buffer_pool_size = 6G
tmp_table_size = 128M
QUERY:
SELECT postid FROM vb3_post_thanks AS post
WHERE post.userid = 2
ORDER BY post.date DESC
LIMIT 1000
TABLE LAYOUT:
CREATE TABLE `vb3_post_thanks` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`userid` int(10) NOT NULL,
`username` varchar(50) NOT NULL,
`date` int(10) NOT NULL,
`postid` int(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `postid` (`postid`),
KEY `postid_2` (`postid`),
KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=2207335 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
2177629 ROWS, 100MB Data, 100MB Index
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE post ref userid userid 4 const 5905 Using where; Using filesort
PROFILING:
InnoDB first execution InnoDB second execution
Status Duration Duration
starting 0.000045 0.000082
checking permissions 0.000005 0.000006
Opening tables 0.000024 0.000016
init 0.000022 0.000022
System lock 0.000007 0.000007
optimizing 0.000009 0.000010
statistics 0.007222 0.000066
preparing 0.000020 0.000018
Sorting result 0.000002 0.000003
executing 0.000002 0.000002
Sending data 0.000008 0.000006
Creating sort index 0.473379 0.020356
end 0.000007 0.000003
query end 0.000008 0.000004
closing tables 0.000013 0.000006
freeing items 0.001172 0.000691
cleaning up 0.000015 0.000007
MyISAM first execution MyISAM second execution
Status Duration Duration
starting 0.000043 0.000046
checking permissions 0.000005 0.000005
Opening tables 0.000015 0.000013
init 0.000021 0.000017
System lock 0.000007 0.000006
optimizing 0.000009 0.000008
statistics 0.000092 0.000044
preparing 0.000018 0.000015
Sorting result 0.000002 0.000002
executing 0.000002 0.000002
Sending data 0.000005 0.000005
Creating sort index 0.011954 0.016139
end 0.000003 0.000002
query end 0.000001 0.000001
closing tables 0.000006 0.000005
freeing items 0.000677 0.000697
cleaning up 0.000007 0.000006