Slow responese on /assets/{asset}/addresses endpoint.

Naiglos commented 1 year ago

Problem Description

Querying /assets/{asset}/addresses endpoint response time ranges form 7 to 35 seconds.

curl -w "@curl-format.txt" -o /dev/null -s "http://localhost:3000/assets/{$asset}/addresses"
     time_namelookup:  0.000014s
        time_connect:  0.032365s
     time_appconnect:  0.000000s
    time_pretransfer:  0.032393s
       time_redirect:  0.000000s
  time_starttransfer:  31.020171s
                     ----------
          time_total:  31.020421s

Environment

Hardware

Dedicated server
AMD ryzen 9 5950x
128gb ddr4 ecc
2x nmve ssd 3.4tb software RAID 1

Software

Debian 11.7
Docker version 23.0.5

Applications version

Cardano DB Sync 13.1.0.2
Cardano Node 1.35.5
PostgresSQL 15.2
Blockfrost-backend-ryo 1.5.0

Applications specific settings

All running in docker.
Resource limits are in place
PostgresSQL is limited to 12 CPU and 64gb RAM
Blockfrost-backend-ryo is limited to 4 CPU and 8GB RAM
Cardano Node limited to 4 CPU 24gb RAM
Cardano DB Sync limited to 4 CPU 24gb RAM

PostgresSQL settings are:

shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2796kB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 12
max_parallel_workers_per_gather = 6
max_parallel_workers = 12
max_parallel_maintenance_workers = 4

Indices mentioned in README are added.
Troubleshooting steps taken

Directly executed query form source code got same result.
Executed query using EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) Can be viewed here

sorki commented 1 year ago

Hi, thanks for the report!

Recent dbsync (13.1+) dropped some indices that affect our queries - you can (re)create these manually based on https://github.com/input-output-hk/cardano-db-sync/issues/1349#issuecomment-1416017488 and it should fix the sequential scan affecting this query.

Naiglos commented 1 year ago

Hello, thank you for your suggestion. I created indices mentioned in linked comment. Somehow, it got worse. Now it can hover between 47 and 189 seconds.

Once again i executed query directly with EXPLAIN:

in the meantime here some steps i took but without success:

Rebooting server
Running VACUUM ANALYZE and VACUUM
Running VACUUM FULL and REINDEX on the tables involved in query.

mmahut commented 1 year ago

This is almost certainly due to low resources for the database. The 64GB is the bare minimum, try to increase it and play with the postgresql settings, that query is on a pretty large dataset and is probably doing the best it can.

That being said, if you find a way to optimize it, a PR is most certainly welcome!

Naiglos commented 1 year ago

Hello, I actually did some additional testing. @mmahut you where absolutely right about resource constrain. It turns out asset used for testing indeed contains abnormally large amount addresses compared to other assets. With other assets performance has increased dramatically, from 7sec to ~130ms.

Can I suggest adding those indices to README instructions? It could be a life-saver for some. And about resource estimation it could come in useful to have recommanded hardware spec listed per component .

Thank you for your time.

klntsky commented 1 year ago

Would it be possible to automate creation of these indexes on the Blockfrost side?

mmahut commented 6 months ago

Would it be possible to automate creation of these indexes on the Blockfrost side?

Yes, that's a good idea. We should add it to blockfrost-bootstrap.

blockfrost / blockfrost-bootstrap