Insert ignore speedup

Insert ignore speedup

When executing insert ignore SQL statements on large tables with random primary key insertions, performance is limited by the random read rate of the storage system. For some types of tables and some MySQL configurations, TokuDB can avoid doing reads when executing these statements, which can result in a huge gain in performance.

Effect of insert ignore

If a row with the given primary key exists, then do nothing, else insert a new row.

Implementation

Query the tree for a row with the given primary key. If the row does exists, then do nothing, else send an insert message into the fractal tree.

Performance

A read I/O may be required if the insertion primary key pattern is random and the table is larger than memory. Application throughput is limited to the random read rate of the storage system.

Objective

Speed up insert ignore by skipping the query and just sending an insert_no_overwrite message into the fractal tree. For random primary key insertion patterns and large tables, the speedup can be huge.

Limitations

1. Table must only have a primary key.
2. No triggers defined.
3. Binlog is on and statement logging is being used.