Lots of ActiveRecord::ConnectionTimeoutError

I’m facing a lot of ActiveRecord::ConnectionTimeoutError errors in sidekiq since I tuned Mastodon configuration.

I tried to increase the DB_POOL, but i don’t know if it helps a lot…

Here is my .env.production

# Tuning

Sidekiq startup command : sidekiq -c 15 -q default -q mailers -q pull -q push

My postgres config (default i think?) :

max_connections = 100
shared_buffers = 128MB
effective_cache_size = 4GB
work_mem = 4MB
maintenance_work_mem = 64MB
min_wal_size = 80MB
max_wal_size = 1GB
checkpoint_completion_target = 0.5
wal_buffers = 4MB
default_statistics_target = 100

An example of error in sidekiq : pull | LinkCrawlWorker | 100146857493425188 | ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds (waited 5.000 seconds); all pooled connections were in use

What can I do to fix that ?

Some update :

I tried to increase DB_POOL to 100 and rescale the postgresql config according to PGTune.

Here my postgres config

max_connections = 200
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 1310kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

I still have some errors, but I have significantly less errors than before.
I don’t know if this issue is entirely solved… It works well enough ?

The DB_POOL option is per process, so everything depends on where you’ve set these variables. If you’ve set them in .env.production, every single process will use them. Ideally you would add a Environment="DB_POOL=100" line to the systemd service file of a specific type (web or sidekiq). But placement is not the only thing that may be wrong. If you set that option to 100 for the web service, and it’s configured with WEB_CONCURRENCY=4 (which means 4 processes), that’s 400 database connections occupied just by the web service.

You only need a connection pool as large as there are threads per process. So with 5 threads per web process, you need a DB_POOL=5 in the web service (for this reason, when DB_POOL is not specified, it defaults to the value of MAX_THREADS). But you are interested specifically in Sidekiq: There is by default only one sidekiq process, and the number of threads is specified with the -c option (in your case, 15). So, you need DB_POOL=15 in the systemd service file for sidekiq.

1 Like

Ooow that makes way more sense now !

Thanks for the explanation :smiley: