Tasks for blobber's db optimization

[lpoli]

DB optimization and Root Hash Calculation

Consider there is an update in /a/b/c/d/e.txt file. To calculate ReferencePath we do the following:

1. Get refs / and all children of /
2. Get refs /a and all children of /a
3. Get refs /a/b and all children of /a/b
4. Get refs of /a/b/c and all children of /a/b/c
5. Get refs of /a/b/c/d and all children of /a/b/c/d.

All refs are queried at once. We get refs in a slice. Then we form a hierarchical object from the refs using path and parentPath field.
Roughly the following step:

1. Get root Ref and append all its direct children to Ref.Children variable.
2. Further refs are put into their parent Ref
3. Return root Ref.

As we can see above single Ref will hold all the refs queried from the database.

Then new root hash is calculated and verified with the commit request, updated hashes of all affected refs to the database and stored writemarker to redeem it later.

Above step is crucial to pass challenge issued by smart contract.

Now consider / has 10000 files, /a, /a/b, /a/b/c,etc. in similar ranges. So we would be querying around 40000 refs.
If we consider each ref to occupy 1KB memory, given that we have path, parentPath,allocation_id, merkle_root, etc. of a file. We would have 40MB of RAM memory consumption which is tolerable. If total refs would sum to one Million, it would consume around 1GB of RAM memory.

Above calculation has considered limit in path field to 1000 characters.

Note: My rough calculation for fields required only for hash calculateion is 1289 bytes. Whole fields would occupy around 3000 Bytes.

Note: We have decided to use max limit of 1000 for path field and for name field there is already max limit of 100 but 255 may be seisible.

Above is for single allocation's root hash calculation. There can be multiple allocations. If blobber uses 64 GB RAM memory it might be sufficient. But still we must have limit on some fields like path, parent_path, attributes, custom_meta,etc. otherwise client can overload database with longer path fields.

Other subject of concern is calculation of hashes. Hash calculation is computation wise not simple. It has to do several mathematical operations to calculate single hash. If there are millions of files in a directory hierarchy(meaning that it has multiple levels of directories), then there will be multiple level of hash calculations. So if this takes time such that context timeout exceeds then it is never going to be successful.
I am not sure if this will exactly happen, but we at least need to be confident of blobber performance with the given specifications.

Splitting tables

We have had a lots of discussion regarding managing reference_objects table because it has one row for one file. If there are 10 allocation with million files each then it will hold 10 millions rows. The subject of concern is performance and difficulty of properly managing indexes, auto-vacuum, etc.

We have decided to have separate table for each allocation for reference_objects. It is desirable to have table name as reference_objects_{allocation_id} but table name is limited to 63 characters and allocationID is itself 64 characters.
Also there are other tables which has some column as allocation_id. Many columns will have same value of allocation_id.

So it is best that we map allocation_id with some integer value and put into a map for quick access and also have a table to store this mapping. This will ease our table naming problem above. So table name can now be reference_objects_{n}.

Separating each reference_objects table for each allocation will have good performance improvement. First is retrieving rows will be lot faster, second is each table will have its own indexes.
Dropping data related to some allocation is simplified to Drop table command.

There is soft_deletion used here and there. We absolutely don't need that. Soft_deletion will unnecessarily occupy database space.

As per @sculptex, its critical to have file-size to db use ratio to 1-2%. So this means;
For 50TB of files, we should limit database usage to 500GB - 1TB.

Like allocation_id we can map other fields to some integer value and save space on cost of small performance latency.

AutoVacuum

Postgres has its own mechanism to update/delete rows. When a row is updated or deleted, a new row is created for update and old row is marked as stale. To clear this stale rows postgres runs auto-vacuum to a table when update/delete are 20% of total rows in a table. When number of rows grows to millions, this can pose some space issue. So if a table has 1 million rows, then auto-vacuum runs against this table if its number of rows updated/deleted is 200000.
The other main concern is we need to make sure, auto-vacuum runs. Sometimes due to mis-configuration of database auto-vacuum might not run and simply database will bloat.

Slack References

https://0chain.slack.com/archives/C03778S0QG0/p1647085682243839
https://0chain.slack.com/archives/C03778S0QG0/p1647081921922799
https://0chain.slack.com/archives/C03778S0QG0/p1647121579802569
https://0chain.slack.com/archives/C03778S0QG0/p1648205488358849