Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a large MyISAM table (~30M rows). At some point I've switched it to fixed row format, so now table takes ~40Gb on disk and 2Gb for indexes. Table has a unique index and there are 100 'insert on duplicate key update' queries per second. As table grows these inserts are becoming slower and slower.

I'm not sure, but will partitions help me to speed up inserts?

share|improve this question

1 Answer 1

Are these insert queries concurrent or originated from the same process? If they are concurrent it's better to use InnoDB storage for this table because MyISAM locks the entire table and InnoDB utilizes row locks. If switching to another storage is not an option, you can try INSERT DELAYED statement and a number of other insert optimizations. Partitioning won't help unless you place different partitions on different physical discs.

share|improve this answer
    
These inserts are concurrent. But InnoDB is too slow at 'insert on duplicate key update', so it is not an option. As far as I can tell IO is not a bottleneck - amount of RAM >> size of tables, including indexes and writecaching is on. I think problem lies somewhere in internal mysql's locks or something. –  d0rc Aug 19 '11 at 22:32
    
INSERT DELAYED should be used only for INSERT statements that specify value lists. The server ignores DELAYED for INSERT ... SELECT or INSERT ... ON DUPLICATE KEY UPDATE statements. –  llazzaro Dec 13 '11 at 15:48

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.