Research: New validator projection running speed is too slow

[calvinaco]

Task Details

• Identify the reason why the projection is slow
• (Optional) Research how Cosmos can still achieve a reasonable performance
  • In case DB optimization cannot achieve a reasonable speed

Definition of Done

• Improve the projection such that it can index Testnet within 2 days

[ysong42]

Spotted one cause of the slow speed.

It is caused by cloning the delegations table, due to a missing index. The fix is to add the missing index on the delegations table, and then the projection runs way faster than before.

[ysong42]

The total sync time of testnet data at 2,665,000 is around 30 hours. Less than 2 days, good 😂 .

Below is during the local testing, I recorded some sync speed for the transaction.

height: 4906
height: 7044 (after 60s)
speed: 35.6 blocks/second -> (if total height 2,630,000, then total sync time is 20.87 hours)

height: 326474
height: 328263 (after 60s)
speed: 29.8 blocks/second -> (total sync time 24.3 hours)

height: 1910761
height: 1912139
speed: 23 blocks/second -> (total sync time 31.76 hours)

[ysong42]

Summary On Running the new projection against Testnet

Final Sync Height

Last Height Performance Log

duration is in millisecond, indicates the time that operation takes. 0 means it takes less than 1 millisecond.

I think this image indicates that for a normal block, the biggest cost is at CloneDelegations and CloneValidators. That is creating a snapshot for that block.

Average Duration For Each Action (only counts duraion > 20 ms)

Average Duration For Each Action (only counts duration > 100 ms)

From both the average duration images, I think it indicates that at the moment, the CloneDelegations and CloneValidators are our first choice to do optimization.

DB Folder Size

[ysong42]

Summary On Running the new projection against Mainnet

Sync speed:

height: 11332
height: 11735 (after 60s)
speed: 6.72 blocks/second, current highest block is 4,255,287. Let’s say 4260000 blocks in total, then 7.35 days to sync up.

height: 19642
height: 19655 (after 60s)
speed: speed: 0.22 blocks/second, current highest block is 4,255,287. Let’s say 4260000 blocks in total, then 224 days to sync up.

So when syncing up to around 20,000 block, the speed already be very slow. Almost 5 seconds a block. And in the above calculation it will take in total 224 days to sync up all blocks.

Not to mention that the speed will be slower when the height goes up, as more delegation records need to be cloned at each height.

DB Folder Size

I did not clean the data for testnet. So in total, for 20,000 blocks, it takes around 202 - 188 = 14 GB spaces. That is 7 GB for 10k blocks. If in total we have 4,260,000 blocks, the storage for DB will take 2982 GB (In reality it will takes more, as higher block height will take more space).

Sync up to 19687

As both the sync speed is too slow and the projection is taking too many storage, I decide to stop running it at height 19687.

Here are some extra information:

From this image, we could see the most expensive operation is still CloneDelegations and CloneValidators.

Here we can see at height 19687, we already have more than 4k delegation records at each height.

At latest height, we have an validator called All Nodes, it has more than 10k delegations under it... So we can foresee how many delegation records to clone if we keep running it...

Query Planning CloneDelegations

[ysong42]

Close this as the research is done. The following action is created in #678.