The sql backend block import too slow

[boundless-forest]

I wonder to know if Moonbeam has ever encounter that the sql backend RPC node block import slow which lead to some rpc methods errors.

We found this issue recently when using https://github.com/ponder-sh/ponder. In detail, the pond will track the latest block for the specific contract, I noticed that when the ponder send get latest block request to the rpc node, the rpc node give the substrate.client.best_hash to the ponder, then the ponder will try to get the correspond block via eth_getBlockByHash, but in some cases, the block has not been written into the sql backend tables yet. This mislead ponder that it receive bad block hash at first and panic.

This might be a flaw for the current sql backend RPC node implementation. Seeks for more information.

[RomarQ]

@ahmadkaouk does this sound familiar?

[ahmadkaouk]

We recently noticed an issue of slow block import of the best block into the mapping database of frontier, but was affecting the kv database and not SQL, so might not be related. The issue became evident when we enabled asynchronous backing, and doubled the speed of block production. The Mapping DB was not syncing the best_block fast enough due to how the sync process is implemented.

As a result, when using eth_getBlockbyNumber for the HEAD, it returned the correct block. However, when attempting to retrieve the corresponding block using eth_getBlockByHash, it wasn't working.

Fix in this PR: moonbeam-foundation/frontier#201

[boundless-forest]

As a result, when using eth_getBlockbyNumber for the HEAD, it returned the correct block. However, when attempting to retrieve the corresponding block using eth_getBlockByHash, it wasn't working.

That's sounds the same as what we've encountered with the sql backend node rpc.

[boundless-forest]

From what we've discussed, the root cause is that the db either sql or kv backend is not fast enough to sync to the latest block hash given out by the RPC node. I think your patched solution will help to reduce the possibility of the problem occurring. However, it still possible that the RPC request arrives before the corresponding block has been written to the DB.

Before I raised this issue, I came up with another solution (see darwinia-network/frontier#20). It looks like this can also be extended to the kv backend. I'd love to hear any feedback.