We already set our PostgreSQL's
max_connections from 1024, then 4096, but it start swapping, so we reduce them back to 64 (this is the limit before swapping).
Our RAM is 2GB, we configure the PostgreSQL's
working_mem to 16MB and
temp_buffers to 8MB,
shared_buffers to 800MB,
effective_cache_size to 1536MB (this configuration doesn't swap at all).
There are about 18 request per second everyday at 00:00 to 02:35, and there's about 8 errors per second, at that point only 10 transaction per hours that succeed, others are failed, with error message either:
- remaining connection slots are reserved for non-replication superuser connections
- too many clients already
How to overcome this problem other than caching the read-only page (since the page can be updated about 10 times per minutes) or upgrading the machine?