MySQL Optimization in Many Inserts Few Selects Environment

Question

I am working with a MySQL database that is mainly used to do inserts and almost never selects. Unfortunately, the system is already deployed and working, 24x7 roughly 14k inserts/day, and I cannot make many inserts in my test environment as it is very limited.

All the MySQL optimizations I read focus mainly in fast, cacheable selects, but this is no point in my system.

Is there any good guide to improve performance in such a system? Or a more focused question: do the vars *innodb_flush_method*, *innodb_buffer_pool_size* and *key_buffer_size* have any effect in such a system?

Answer 1

In general you would optimize for this situation by having as few indexes on the table(s) that get(s) inserted into as possible, as the updates to these can take a fair amount of time. The mentioned variables would not have much impact in this scenario.

OTOH, having said this, 14k inserts per day is not really anything to write home about, any decent MySQL server should easily handle this.

Obviously not having indexes that are useful for SELECTs will make these possibly quite slow, so it would probably be then useful to extract the inserted data into a separate database (of the same structure, but with the additional indexes) for querying purposes.