0

I'm trying to improve the performance of cardano-db-sync's database by removing unused tables (truncate block table) and deleting old data from the tx_out table.

However after re-start the cardano-db-sync instance, seems that it is trying to re-fill old data even when I specified ledger-state from the snapshot state https://github.com/IntersectMBO/cardano-db-sync/blob/master/doc/syncing-and-rollbacks.md#ledger-state

2024-02-08 00:46:15 [db-sync-node:Info:165] [2024-02-07 17:46:15.39 UTC] Setting ConsistencyLevel to Unchecked
2024-02-08 00:46:15 [db-sync-node:Info:165] [2024-02-07 17:46:15.39 UTC] Starting chainSyncClient
2024-02-08 00:46:15 [db-sync-node:Info:165] [2024-02-07 17:46:15.39 UTC] Suggesting intersection points from memory: [] and from disk: []
2024-02-08 00:46:15 [db-sync-node:Info:165] [2024-02-07 17:46:15.40 UTC] Cardano.Db tip is at slot 16311, block 16312
2024-02-08 00:46:15 [db-sync-node:Info:172] [2024-02-07 17:46:15.40 UTC] Running Offline fetch thread
2024-02-08 00:46:15 [db-sync-node:Info:171] [2024-02-07 17:46:15.40 UTC] Running DB thread
2024-02-08 00:46:15 [db-sync-node:Info:171] [2024-02-07 17:46:15.96 UTC] Delaying delete of 16312 while rolling back to genesis. Applying blocks until a new block is found. The node is currently at Tip (SlotNo 54073215) 3888541878ad6584df95be05ca8816c32ba9976011c4cd5f0703a773cf61eaf8 (BlockNo 6918590)
2024-02-08 00:46:16 [db-sync-node:Info:171] [2024-02-07 17:46:16.74 UTC] Found snapshot file for genesis
2024-02-08 00:46:16 [db-sync-node:Info:171] [2024-02-07 17:46:16.74 UTC] Setting ConsistencyLevel to DBAheadOfLedger
2024-02-08 00:47:03 [db-sync-node:Info:171] [2024-02-07 17:47:03.07 UTC] Received block which is not in the db with HeaderFields {headerFieldSlot = SlotNo 16312, headerFieldBlockNo = BlockNo 16313, headerFieldHash = a0bce776e49a0998e7acde647e7cb3a1794395abdb162d45c9c6f8ca16bdd049}. Time to restore consistency.
2024-02-08 00:47:03 [db-sync-node:Info:171] [2024-02-07 17:47:03.08 UTC] Starting at epoch 0
2024-02-08 00:47:03 [db-sync-node:Info:171] [2024-02-07 17:47:03.14 UTC] insertByronBlock: epoch 0, slot 16312, block 16313, hash a0bce776e49a0998e7acde647e7cb3a1794395abdb162d45c9c6f8ca16bdd049
2024-02-08 00:47:03 [db-sync-node:Info:171] [2024-02-07 17:47:03.14 UTC] Setting ConsistencyLevel to Consistent
2024-02-08 00:48:00 [db-sync-node:Info:171] [2024-02-07 17:48:00.26 UTC] insertByronBlock: epoch 0, slot 16999, block 17000, hash be219f7f8e2d4116ea718f34a7741eaf404c835ca0365b8f94426618b1a576a8
2024-02-08 00:48:04 [db-sync-node:Info:171] [2024-02-07 17:48:04.48 UTC] insertByronBlock: epoch 0, slot 17999, block 18000, hash e711abf703ebb7aa7c1ad235dafacdb6880fbc4a5ff27f368046ee6a2975724d
2024-02-08 00:48:07 [db-sync-node:Info:171] [2024-02-07 17:48:07.75 UTC] insertByronBlock: epoch 0, slot 18999, block 19000, hash 5c469c0c52cc8ad9b0d21d029c9aae75a8e0d82e686eb98e026c2ca71ee4fb21
2024-02-08 00:50:22 [db-sync-node:Info:171] [2024-02-07 17:50:22.59 UTC] insertByronBlock: epoch 0, slot 19999, block 20000, hash 683be7324c47df71e2a234639a26d7747f1501addbba778636e66f3a18a46db7
2024-02-08 00:50:25 [db-sync-node:Info:171] [2024-02-07 17:50:25.57 UTC] insertByronBlock: epoch 0, slot 20999, block 21000, hash e744980befb7bfbc2251efcce3897842545a766575bbfc931d42c875a9573dbf
2024-02-08 00:52:22 [db-sync-node:Info:171] [2024-02-07 17:52:22.76 UTC] Took a ledger snapshot at /var/lib/cexplorer/21599-3bd04916b6-0.lstate. It took 0.034869166s.
2024-02-08 00:52:22 [db-sync-node:Info:171] [2024-02-07 17:52:22.79 UTC] Persistant SQL Statement Cache size is 14
2024-02-08 00:52:22 [db-sync-node:Info:171] [2024-02-07 17:52:22.79 UTC] 
2024-02-08 00:52:22 Cache Statistics:
2024-02-08 00:52:22   Stake Addresses: cache size: 0, hits: 0, misses: 0
2024-02-08 00:52:22   Pools: cache size: 0, hits: 0, misses: 0
2024-02-08 00:52:22   Datums: cache capacity: 50000, cache size: 0, hits: 0, misses: 0
2024-02-08 00:52:22   Multi Assets: cache capacity: 250000, cache size: 0, hits: 0, misses: 0
2024-02-08 00:52:22   Previous Block: hit rate: 49%, hits: 5273, misses: 5275
2024-02-08 00:52:22 [db-sync-node:Info:171] [2024-02-07 17:52:22.79 UTC] Starting epoch 1
2024-02-08 00:52:22 [db-sync-node:Info:171] [2024-02-07 17:52:22.80 UTC] insertABOBBoundary: epoch 1, hash 1941d944df546dea699791c318aeb9cc63b94e4cdb133d79856cda35bf7ecbb1
2024-02-08 00:59:32 [db-sync-node:Info:171] [2024-02-07 17:59:32.52 UTC] insertByronBlock: epoch 1, slot 22013, block 22000, hash aad3ea4b6f582418364b4396e52d13742a20251c0c24bd00b87a166c89721da5
2024-02-08 01:02:33 [db-sync-node:Info:171] [2024-02-07 18:02:33.09 UTC] insertByronBlock: epoch 1, slot 23013, block 23000, hash 66c167990b622ce28943a2bf518dbe810de7bb3356a8c8975ae0a1f9d621698f
2024-02-08 05:41:11 [db-sync-node:Info:171] [2024-02-07 22:41:11.79 UTC] insertByronBlock: epoch 1, slot 24013, block 24000, hash 1a8ef01c1a92288b269638552c051a64d0a9b741abd37d78d7762070f9333810
2024-02-08 09:40:14 [db-sync-node:Info:171] [2024-02-08 02:40:14.70 UTC] insertByronBlock: epoch 1, slot 25013, block 25000, hash 37e44110466a04ee127106043c7b20fe7ccc19de01e7cf5da99a4d001865ed2c

So could we remove old data in cardano-db-sync's tables (PostgreSQL) safely?

Could anyone help, please?

Thank you.

1 Answer 1

0

If I understand the problem correctly, there is this command line flag --prune-tx-out which will do the pruning for you.

There are also other performance flags available that may be able to help you further, though some are experimental at the moment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.