DELETE query running for over 12 hours with 100% CPU - why?

[CherryDT]

Hi, I observed a strange behavior today that I can't explain. Is this normal...? If not, what could cause this?

I saw that a postgres process was using 732 minutes of CPU time already, maxing out a core for over 12 hours. It turns out it is a query DELETE FROM "block" WHERE "id"=6786846:

Why would such a simple query take 12+ hours...? Is it safe to kill this process?

[rhyslbw]

The block table is managed by @cardano-db-sync. Maybe @erikd can advise

[dorijan]

Yea, on testnet it is deleting [db-sync-node:Info:58] [2022-01-12 02:09:45.05 UTC] Deleting 3185999 blocks up to slot 46184426 with rate of 100 entrys per miunute this would take 22 days to delete!!!

[erikd]

If db-sync ever tries to rollback anything more than an epoch's worth of blocks (ie 21600 blocks) something else is BADLY wrong. I suspect this is an instance of https://github.com/input-output-hk/cardano-db-sync/issues/978 .

[dorijan]

Ok, this is how you can speed up all, if anyone needs this: modify docker-compose.yml to store files on some folder on disk run as described in documentation. When testnet_cardano-node-ogmios_1 finished(on the tip), run docker-compose down delete all from stored files on disk except node-db folder run again whole docker-compose with RESTORE_SNAPSHOT