I have a table with this structure:

CREATE TABLE `requests` (
 `userid` int(11) NOT NULL,
 `messageid` int(11) NOT NULL,
 `queueid` int(11) NOT NULL AUTO_INCREMENT,
 `problem_id` int(3) NOT NULL,
 `cluster` int(11) NOT NULL,
 `processed` tinyint(1) NOT NULL,
 `status` tinyint(1) NOT NULL,
 `legitimate` tinyint(1) NOT NULL,
 PRIMARY KEY (`userid`,`messageid`),
 UNIQUE KEY `queueid` (`messageid`),

The problem is that depending on the primary key chosen, I face two outcomes:

  • The autoincrement as the primary key - SELECTS are fast, inserting new data is painfully slow ( 70 rows inserted per second )
  • Userid as the primary key - Inserts are fast ( 3000 rows inserted per second ) but since the rows are not clustered around the userid field, SELECTS kill the HDD performance once I start doing "SELECT *" on the table for multiple userids.

My queries all start with the userid, basically, this table won't ever be queried without userid - that's why it improves selects by so much. Because the data can be read sequentially.

The problem is, that the stream of inserts comes with random userid values ( they come ordered by messageid which I extract from other table ). I don't get all rows for a userid - they all come in a random order. So each time I get a userid, I have to place it in its assigned page. Every incoming row-insert is for a different userid.

Please tell me - is the SSD the only viable option? Or I can somehow make both SELECTs and INSERTS to be sequential?

( the table is partitioned horizontally and I insert rows in bulk of 50,000 to 70,000 elements, using transactions )

  • By publishing the 2 outcomes, I assume you have benchmarked and tested and are left with these scenarios? Did you optimise for inserts and tune select queries using appropriate secondary indexes? Also what does your IO subsystem offer you in terms of IOPs? If you are using RAID, what form are you using? Have you got Write Back mode set or Write Through? – eroomydna Jan 13 '14 at 21:24
  • 1
    Yes, I've done a few tests. I was using an ordinary HDD with 100-150 IOPS. Now I've gotten an additional SSD that has 10-100 times more IOPS and the problem went away. Random look-ups are extremely IOPS sensitive, they don't really care about transfer per second. I think it's the only solution in this case. – user3010273 Jan 14 '14 at 7:32
  • I assume that UNIQUE KEY queueid` (messageid),` should be queueid not messageid. – jeremycole Jan 16 '14 at 2:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.