How to improve INSERT speed in large table that has unique constraint

Question

I have a simple MyISAM table:

explain entities;
+------------+-------------+------+-----+-------------------+----------------+
| Field      | Type        | Null | Key | Default           | Extra          |
+------------+-------------+------+-----+-------------------+----------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment |
| name       | varchar(50) | YES  | UNI | NULL              |                |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-------------+------+-----+-------------------+----------------+

that is expected to have 10s of millions of rows (or even more) eventually. I noticed that as the number of rows in the table increases, insert statements start to take longer. This is expected, as the documentation states that "the size of the table slows down the insertion of indexes by log N, assuming B-tree indexes". I read in another question that LOAD INDEX INTO CACHE can be used to improve insert speed, but I found this to have a negligible effect. Are there any other pointers on how to improve insertion speed, other than the ones listed here? For example, would partitioning the table help with insertion speed? What guidelines can I use for determining if my insertion speeds are already at a near optimum?

System info: MySQL 5.1.67-0ubuntu0.10.04.1 Running on Linode VM with 512 MB RAM and quad-core processor

Answer 1

I think the problem could be your RAM, for LOAD INDEX INTO CACHE to make a difference you should have the index size < key_buffer_size < Available RAM

To see your current index size use SHOW TABLE STATUS.

Also LOAD INDEX INTO CACHE works only for MyISAM, for InnoDB you could try the blackhole method:

create table t like innodbtable;
alter table t engine = blackhole;
insert into t select * from innodbtable WHERE indexedRow<>''; -- Get creative in order to stimulate reading of data and indexes you need

source , for more information on InnoDB pre-loading see this and this.

To estimate index usage see this or this .

For example I have a table with 32 Mil records in it and the data size is 2.33 GB (2,496,806,620) and the index size is 2.08 GB (2,230,159,360). So your table should be at least 5 times smaller to be able to fit the RAM limit. Also don't forget I said Available RAM = the ram left after excluding system, mysql (other than the key_buffer_size) & other software, so that would leave you at best with 350MB.

Here is my table for reference:

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` int(10) unsigned DEFAULT NULL,
  `b_id` int(10) unsigned DEFAULT NULL,
  `varchar1` varchar(255) DEFAULT NULL,
  `varchar2` varchar(255) DEFAULT NULL,
  `varchar3` varchar(8) DEFAULT NULL,
  `lastModified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `links_id_email` (`a_id`,`varchar1`),
  KEY `domain_index` (`varchar2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;