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

Question

I am in the process of adding a new function to the system. The process will read the current data from the PLC and store it in the database. The data table will contain 4 columns: variable_id (SMALLINT), timestamp (TIMESTAMP), value (FLOAT), quality (TINYINT). The primary key is (variable_id, timestamp).

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

The last 24 hours of data will be stored in the data table, older data is deleted from the table.

The data table should also process 5-10 select statements every second. The select statement selects the last value from the table for a particular variable and displays it on the Internet.

Should I use MyISAM or InnoDB table format? Does MyISAM lock the entire table while performing inserts, thereby blocking select operations from the web interface? Currently, all data tables in the database are MyISAM tables.

Answer 1

Should I use MyISAM or InnoDB table format?

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

Does MyISAM write the entire table during insertion, thereby blocking selection operations from the web interface?

With @@concurrent_insert enabled, MyISAM can append pasted lines to the end, while still reading from another session at the same time.

However, if you have ever received anything other than INSERT , this can lead to a crash (for example, a table will lock).

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

It would be better to perform these inserts and do them in packages.

InnoDB much faster than the number of rows per second.

The last 24 hours of data will be stored in the data table, older data is deleted from the table.

Note that InnoDB blocks all checked rows, not just the affected ones.

If your DELETE ever uses full-screen mode, parallel INSERTs will fail (since fullscan will do InnoDB to put space locks on all records viewed, including the last).

Answer 2

MyISAM is faster and it locks the entire table. InnoDB is transaction-based, so it will block rows, but slower.