Database is locked. What is the reason? What is the possible solution?

[Odmin]

I migrated my databases to the SSD and collected statistics. As I wrote before, we have a huge fragmentation of the databases, but after migration, to the SSD this problem is not actual.

Let’s look into statistics of SSD with databases:

As you can see, we have spikes every hour (sent orders to the satellite)

Let’s look more detailed to one of these spikes

Here we can see a lot of IO operations! (1600 for reads + 1400 for writes) but these requests are very small because bandwidth is low (5.9MiB/s for reading and 7.8MiB/s for write). This spike takes about 5 sec on SSD.

Let’s also look into other interesting parameters of these spikes:

Here we can see that one operation takes about 2ms.

Here we can see the average bandwidth of operation

So, let’s summarize and make a conclusion.

What I expected before starting the migration to the SSD:

@Odmin

I researched this problem a few months ago, I got this result: During the working of storage node we have a lot of writes to the database, this writes increase fragmentation of database files (extremely increase) and we will get performance degradation (a lot of IOPS for simply query). Calling vacuum is not a silver bullet but it really helps because during this operation we rewrite the whole file sequentially (like defragmentation on disk), the same result we will have when just move a database to /tmp and back to the original place (rewrite the whole file sequentially).
So, root cause is - file fragmentation on HDD (not a database structure fragmentation).
I very happy that we have a possibility to move databases on this release to the SSD, for me it best solution for keep database always “defragmented” (we just will not care about defragmentation) I will put it to my mirrored system SSD and will monitor how it working. I expect a lot of benefits at the same time:

1. Solve issue with “locks”
2. No database performance degradation during a time (one month with active ingress is enough to heavy performance impact)
3. We offload main storage for pieces and improve IOPS

1. Solve issue with “locks” - yes, it solved. As we can see every hour we have a lot of small IO operations that will definitely can’t be handle by regular HDD, but SSD can handle it easily. Also more orders (traffic) = more small IO operations.
2. No database performance degradation during a time (one month with active ingress is enough to heavy performance impact) - yes, it also solved. SSD no need move heads and file system fragmentation is not affecting database performance. Before migration, we saw a huge database fragmentation on the HDD that extremely impact performance with a lot of small IO opearion.
3. We offload main storage for pieces and improve IOPS - yes, it definitely improved. Because we move out fragmented databases than needing a lot of small IO operations our main storage for pieces now has a big offload of unnecessary head movements and we speed up of delivery pieces is improved.

Conclusion: Move the database to SSD it an awesome feature that we must have especially if you advanced storage node operator and have big storage node. Because a lot of small IO operations can easily kill the performance of every single HDD or any RAID with HDDs and produce a lot of problems like database locks because HDD is will be so busy to handle these operations.