# Too many connections to DB, how to optimize
**URL:** https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395
**Category:** Support
**Created:** [2017 年 7 月 18 日午後 3:40 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395 "2017-07-18T15:40:01Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 3:40 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/1 "2017-07-18T15:40:02Z")
I have frequent 500 errors due to exceeding number of connections to my DB.
This results in errors like this in /logs:
```
Job exception: FATAL: remaining connection slots are reserved for non-replication superuser connections
```
My config:
```
UNICORN_WORKERS: 12
UNICORN_SIDEKIQS: 2
DISCOURSE_DB_POOL: 90
```
My DB under load performs like this:
How can I optimize this? Please keep in mind the default config of PSQL has max connections limit set to 100.
I have plenty of RAM and CPUs for both web\_data container and data container (which is hosted on a second server).
Update: I have a lot of open connections to DB from sidekiq, a lot more than it should (I have just 2 sidekiqs with 5 threads each).
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [2017 年 7 月 18 日午後 3:53 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/2 "2017-07-18T15:53:39Z")
Did you change sidekiq threads manually? I believe the default is 25, and you have 2 process, so 50 connections, only with sidekiq.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2017 年 7 月 18 日午後 3:55 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/3 "2017-07-18T15:55:40Z")
At high loads I strongly recommend running pgbouncer, how is your db configured?
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 3:56 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/4 "2017-07-18T15:56:02Z")
I would have to check, but /sidekiq reports only 5 threads per sidekiq:
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2017 年 7 月 18 日午後 3:58 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/5 "2017-07-18T15:58:05Z")
Each web runs multiple threads as well, the numbers add up real quick
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 3:58 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/6 "2017-07-18T15:58:47Z")
I have a data container running on a second server.
I have changed the data.yml to expose db and redis ports and to give some extra memory:
```
db_shared_buffers: "8GB"
db_work_mem: "100MB"
```
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 4:00 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/7 "2017-07-18T16:00:35Z")
> [@sam](#):
>
> Each web runs multiple threads as well, the numbers add up real quick
I have 25 connections from unicorn workers and over 70 from sidekiqs.
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 4:08 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/8 "2017-07-18T16:08:23Z")
Restarting the web\_only container doesn’t solve the problem, it seems like sidekiq connection persist.
Restarting the data container solvers the problem for another couple of days.
It seems like there are some issues with sidekiq connections.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2017 年 7 月 18 日午後 4:47 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/9 "2017-07-18T16:47:32Z")
> [@TomekB](#):
>
> It seems like there are some issues with sidekiq connections.
Interesting. We are usually internally totally shielded from this bug cause we use pg bouncer, but we will have a look internally to see if we see similar leaks. 👨🏽🌾
Can you debug into the “connections” hanging there, do you have any info on what the last query is these stalled connections ran? how long are they stuck without running anything?
@tgxworld where is our official pg image with bouncer? we need a howto explaining how to use it.
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 5:52 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/10 "2017-07-18T17:52:02Z")
How do you rotate connections with pgbouncer (session pooling, transaction pooling)?
I will try to debug these stalled connections but I think most of them were idle.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2017 年 7 月 18 日午後 5:57 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/11 "2017-07-18T17:57:32Z")
We use transaction pooling, session pooling is a no-go with Rails.
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 18 日午後 5:58 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/12 "2017-07-18T17:58:14Z")
Thx I will set this up but this will make it much harder to diagnose these problems.
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 19 日午後 12:10 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/13 "2017-07-19T12:10:11Z")
After DB reset sidekiq connections slowly start piling up.
Right now I have 3 stuck connections with such queries:
```
INSERT INTO directory_items(period_type, user_id, likes_received, likes_given, topics_entered, days_visited, posts_read, topic_count, post_count) SELECT 5, u.id, 0, 0, 0, 0, 0, 0, 0 FROM users u LEFT JOIN directory_items di ON di.user_id = u.id AND di.period_type = 5 WHERE di.id IS NULL AND u.id > 0
INSERT INTO directory_items(period_type, user_id, likes_received, likes_given, topics_entered, days_visited, posts_read, topic_count, post_count) SELECT 5, u.id, 0, 0, 0, 0, 0, 0, 0 FROM users u LEFT JOIN directory_items di ON di.user_id = u.id AND di.period_type = 5 WHERE di.id IS NULL AND u.id > 0
WITH x AS (SELECT u.id user_id, SUM(CASE WHEN ua.action_type = 2 THEN 1 ELSE 0 END) likes_received, SUM(CASE WHEN ua.action_type = 1 THEN 1 ELSE 0 END) likes_given, COALESCE((SELECT COUNT(topic_id) FROM topic_views AS v WHERE v.user_id = u.id AND v.viewed_at >= '2017-07-18 10:08:47.784897'), 0) topics_entered, COALESCE((SELECT COUNT(id) FROM user_visits AS uv WHERE uv.user_id = u.id AND uv.visited_at >= '2017-07-18 10:08:47.784897'), 0) days_visited, COALESCE((SELECT SUM(posts_read) FROM user_visits AS uv2 WHERE uv2.user_id = u.id AND uv2.visited_at >= '2017-07-18 10:08:47.784897'), 0) posts_read, SUM(CASE WHEN ua.action_type = 4 THEN 1 ELSE 0 END) topic_count, SUM(CASE WHEN ua.action_type = 5 THEN 1 ELSE 0 END) post_count FROM users AS u LEFT OUTER JOIN user_actions AS ua ON ua.user_id = u.id LEFT
```
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2017 年 7 月 19 日午後 12:14 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/14 "2017-07-19T12:14:22Z")
Curious, how big is your database, how big is the directory\_items table on your instance?
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 19 日午後 12:19 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/15 "2017-07-19T12:19:41Z")
```
select pg_size_pretty(pg_database_size('discourse'));
```
returns 55 GB
The directory\_items table is 207 MB.
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [2017 年 7 月 19 日午後 1:26 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/16 "2017-07-19T13:26:20Z")
And in number of rows?
---
### Author:  [@TomekB](https://meta.discourse.org/u/TomekB)
#### Post date: [2017 年 7 月 19 日午後 1:28 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/17 "2017-07-19T13:28:35Z")
Record count for the table is 1698938
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [2017 年 7 月 27 日午前 7:02 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/18 "2017-07-27T07:02:54Z")
> [@sam](#):
>
> @tgxworld where is our official pg image with bouncer? we need a howto explaining how to use it.
Just updated the description with our README 🙂
> **[discourse/postgres - Docker Image](https://hub.docker.com/r/discourse/postgres/)**
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2019 年 7 月 11 日午後 11:19 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/19 "2019-07-11T23:19:43Z")
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2023 年 1 月 2 日午前 11:13 UTC](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395/20 "2023-01-02T11:13:49Z")
[Next page](https://meta.discourse.org/t/too-many-connections-to-db-how-to-optimize/66395.md?page=2)