What is the best mysql table format for high insert load?
mysql
Solution
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, `MyISAM`can 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).
Problem
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.