0

I am testing database performance with bulk record inserts.

Both systems are identical apart from storage engine

**Database      1000        4000        12000**
MySQL_InnoDB    60.95330976 218.2572161 262.1174723
MySQL_MyISAM    88.55827134 307.503075  684.8142441

The metric here is throughput defined as operations/second.

As you can see when inserting 1000 records both are very similar, again at 4000.

Am am unsure why MyISAM spikes this high compared to InnoDB at 12000 record insert. Could it be due to buffer size? I have defaults set at 8MB, and I have worked out that both 1000 records and 4000 records are inside that buffer, but 120000 records exceeds the 8MB buffer size. Could this be the reason for the exponential rise in throughput?

I am more interested in why MyISAM has this exponential spike in operations/second at 12000 inserts when InnoDB seems to be much more linear.

johnnydrama
  • 3
  • 2
  • Is that time, records per second, or some completely different metric? – Fox Apr 23 '15 at 19:31
  • Its records per second, have updated question. – johnnydrama Apr 23 '15 at 19:33
  • I'm not sure why you have the expectation that the performance between the two should be similar. They shouldn't. Without knowing how the tool you're using to do does the inserts, my guess is that it's using multiple threads to achieve that high level of throughput. MyISAM does not support multiple concurrent writes to the same table, InnoDB does. One row being updated in a MyISAM table locks the entire table. InnoDB just locks the rows. –  Apr 23 '15 at 19:37
  • The simulation uses 1 thread. – johnnydrama Apr 23 '15 at 19:40
  • 1 thread is where MyISAM shines, since there is no contention. InnoDB being an ACID-compliant engine needs to write more data to achieve that reliability than MyISAM does. If your benchmark had 50 threads you'd see the exact opposite performance (due to MyISAM locking the entire table). –  Apr 23 '15 at 19:45

2 Answers2

0

Increase the pool size can surely have a very pronounced influence on InnoDB performance. Try to increase it to 128/256 MB and retry your test.

Here you can found some more informations.

shodanshok
  • 47,711
  • 7
  • 111
  • 180
0

With innodb you might be filling your transaction log (innodb_log_size). While inserting, InnoDB first writes everything to the transaction log. Once in the log transactions get applied to the database itself. So that if server crashes mid-transaction, it can replay the log (reapply the transactions) to keep the data consistent.

This actually means, every data gets written twice to the disk.

If your inserts do not fit into the log, you have to wait until transactions in the log get flushed to database. That may be the reason you see such a big spike in performance.

(Oh and mind the log size. If you make it too big, your recovery after server crash might take a lot of time)

Fox
  • 3,977
  • 18
  • 23