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

[Naiglos]

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

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

[sorki]

Hi, thanks for the report!

Recent dbsync (13.1+) dropped some indices that affect our queries - you can (re)create these manually based on IntersectMBO/cardano-db-sync#1349 (comment) and it should fix the sequential scan affecting this query.

[Naiglos]

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:

• Worst case
• Better case

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

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

[mmahut]

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]

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]

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

[mmahut]

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.