9

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?

d0rc
  • 91
  • 2

2 Answers2

1

First of all, concurrent writes are definitely not an option for MyISAM storage. Each of them will lock a whole table (except for reading in some cases). If InnoDB does not suite you well, try TokuDB. But it will be slower compared to MyISAM because of transactional nature of TokuDB (and InnoDB of course) engine (you should write the same data at least twice: journal and data files). Also, if your server will crash some day, you will be waiting for hours until your 40Gb MyISAM table repairs.

If you still want to load data into your MyISAM-tables and want to do it fast, I can recommend to use LOAD DATA INFILE instead of inserts. This is the fastest way to load large volumes of data to table. And yes, indexes will slow down insert performance in exponential way.

A word about partitions: INSERT-statements in MySQL do not support pruning, so all your partitions will be scanned on each statement for unique index matching. Also, all partitions will be locked until insert ends.

GreyWolf
  • 76
  • 3
  • One more interesting engine: https://code.facebook.com/posts/190251048047090/myrocks-a-space-and-write-optimized-mysql-database/ – GreyWolf Feb 27 '17 at 04:55
0

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.

Alex
  • 7,789
  • 4
  • 36
  • 51
  • 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
  • "InnoDB is too slow" - have you tried tuning it a bit? Default settings are quite poor. – rvs Feb 24 '17 at 10:12