Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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`),
) ENGINE=InnoDB AUTO_INCREMENT=1010112 DEFAULT CHARSET=latin1

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 )

share|improve this question
    
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 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 at 7:32
    
I assume that UNIQUE KEY queueid` (messageid),` should be queueid not messageid. –  jeremycole Jan 16 at 2:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.