Home / server / Questions / 302587
In Process
d0rc
Asked: 2011-08-19 06:55:26 +0800 CST

Speed up MySQL inserts with partitions on MyISAM with unique key

  • 772

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?

mysql optimization table-partitioning

2 Answers

  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.

    • 1

  2. 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.

    • 0