I have a MySQL 5.5.23 DB that performs about 3k qps, 5% of which are writes. Lately, I've been having a big problem with random write freezes. Everything is going great, and then all of a sudden any write that comes in stops in the "update" or "Updating" state. They stay in this state for about a minute or two and then finally finish. The problem is that with such high amount of connections, when they freeze randomly like this, the connection stays open and then I inevitably get too many connection errors.
It's happening on all tables during both INSERT and UPDATE queries.
Has anyone seen this before? Is there anything that can be done about it?
I'm using Amazon RDS with the biggest instance they offer. I'd be more than happy to provide any information you guys need, just let me know what that is.
UPDATE: The main table I'm inserting into is:
CREATE TABLE `mytable` (
`hash` varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
`name` varchar(256) DEFAULT NULL,
`ip` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`deletehash` char(15) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
`datetime` datetime NOT NULL,
`api_key` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
`account_id` int(10) unsigned DEFAULT NULL,
`type` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`width` int(10) unsigned NOT NULL DEFAULT '0',
`height` int(10) unsigned NOT NULL DEFAULT '0',
`size` int(10) unsigned NOT NULL DEFAULT '0',
`animated` tinyint(1) NOT NULL DEFAULT '0',
`views` int(10) unsigned NOT NULL DEFAULT '0',
`lastviewed` datetime NOT NULL,
PRIMARY KEY (`hash`),
UNIQUE KEY `deletehash` (`deletehash`),
KEY `datetime` (`datetime`),
KEY `account_id` (`account_id`),
KEY `ip` (`ip`),
KEY `api_key` (`api_key`),
KEY `views` (`views`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Sample insert:
insert into mytable (hash, name, ip, deletehashe, datetime, api_key, account_id, type, width, height, size, animated, views, lastviewed) values('abcde', 'file name', '127.0.0.1', 'abcdefghijklmn', '2012-05-31 00:00:00', NULL, NULL, 'mime/type', 0, 0, 0, 0, 0, '0000-00-00 00:00:00');
Sample update:
update mytable set views = views+1, lastviewed = NOW() where hash = 'abcde';
Best Answer
I think the issue is due to the choice of the clustered index. From MySQL docs, Clustered and Secondary Indexes:
Also check the answer by @marc_s in this SO question: How to choose the clustered index in SQL Server?, where he mentions:
Now, your clustered index is the (Primary Key):
which (lets go through the check-list) is:
but is probably not:
So, what happens when you use a non-ever-increasing clustered index?
I can't answer better than Kimberly L. Trip: Ever-increasing clustering key - the Clustered Index Debate..........again!
Note that despite the mention of SQL-Server, the same concept applies to InnoDB clustered indexes as well. I suppose that the clustered index has 2 issues:
When you are inserting a new row (the "random" hash guarantees that) it gets inserted in a random location of the index. This means that it sometimes will find no space there available to be inserted (note that InnoDB always leaves some space free in the index but when that free-available space is filled) there has to be some rearrangement of the index - and that takes time.
What the rearrangement is also causing over time is fragmentation of the index. Which will eventually make other queries and statements slower.