On the way to perfect computing

Majority of us knows that databases are read-optimized products and most of them are not optimized for writes.

In this post I would like to describe what’s going on under the hood of mysql that can help us to find origins of some well-known rules.

So, let’s start with MySQL MyIsam table:

- MyIsam tables caches only key data structures in memory and let OS caching mechanism to cope with data. Truly, particularly Linux is good at it at some point(Also MyIsam has read-ahead buffer for data). So, if insert/update query updates key data structure, MyIsam will flush key data on the disk. It slows down a whole process. To avoid this you can tweak mysql’s configuration to delay key writes.

- MyIsam comes with a very simple locking mechanism – it’s its table-level locking. So, when user writes data, another one won’t be able to read any row till the end of transaction. Okay, you can say, if insert time is relatively short, we can wait. So, let us see how MyIsam inserts record into a table: Firstly, it scans a table area and tries to find previously deleted row and re-use its room. If no deleted row was found, it appends data to the end of the table. So, if the space is not as important as execution time, you can speed it up telling database to append data to the end of table immediately and disable scans.

For the bulk data management, it’s good to create a table without indices first and import data. Once the import process is finished you can create indices. If you need to append large amount of data to production machine, you can do hot replacement: prepare such table and send signal to application to use another table instead of old one(the type of signal depends on your application architecture: IPC, record in database with polling on application side,etc)