What is the best mysql table format for high insert load?

Question

I am in the process of adding a new feature to a system. The process will read live data from PLC´s and store them in a database. The data table will have 4 columns: variable_id (SMALLINT), timestamp (TIMESTAMP), value(FLOAT), quality(TINYINT). The primary key is (variable_id, timestamp).

The system needs to be able to insert 1000-2000 records every second.

The data table will keep the last 24 hours of data, older data is deleted from the table.

The data table also needs to handle 5-10 select statements every second. The select statement is selecting the lastest value from the table for a specific variable and displaying it on the web.

Should I use MyISAM or InnoDB table format? Does MyISAM lock the entire table while doing inserts, thus blocking the select statements from the web interface? Currently all the data tables in the data base are MyISAM tables.

Answer 1

MyISAM is quicker, and it locks the entire table. InnoDB is transaction-based, so it'll do row locking, but is slower.

Answer 2

In my experience MySQL will not survive this amount of input for a long time, so don't do it real-time. Maybe you should implement an internal data queuing algorithm, which buffers the data and distributes it downward the MySQL at a much relaxed pace, you will need to actually experiment how much the MySQL supports.

Answer 3

Should I use MyISAM or InnoDB table format?

For any project with frequent concurrent reads and writes, you should use InnoDB.

Does MyISAM lock the entire table while doing inserts, thus blocking the select statements from the web interface?

With @@concurrent_insert enabled, MyISAMcan append inserted rows to the end while still reading concurrently from another session.

However, if you ever to anything but the INSERT, this can fail (i. e. the table will lock).

The system needs to be able to insert 1000-2000 records every second.

It will be better to batch these inserts and do them in batches.

InnoDB is much faster in terms of rows per second than in transactions per second.

The data table will keep the last 24 hours of data, older data is deleted from the table.

Note that InnoDB locks all rows examined, not only those affected.

If your DELETE statement will ever use a fullscan, the concurrent INSERTs will fail (since the fullscan will make InnoDB to place the gap locks on all records browsed including the last one).