3

We have web application using Golang, PostgreSQL, and sqlx (adapter, connection pooler), that each request requires 1 to 8 queries, sometimes 1 transaction with 5-8 select and 5-8 insert queries.

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?

  • 2
    max_connections = 1024 is way too much for such a small server. – a_horse_with_no_name Nov 11 '15 at 6:43
5

max_connections = 1024 ?! You need a connection pool.

Use PgBouncer in transaction-pooling mode if your app doesn't support built-in pooling.

You're running PostgreSQL on a tiny toy server. Keep the active connection count low and queue work up in series.

High max_connections introduces significant inefficiency, and having lots of actively working connections adds more.

  • sqlx have connection pooling. http://jmoiron.github.io/sqlx/#connectionPool – Kokizzu Nov 11 '15 at 6:39
  • @Kokizzu Then limit your pool size to slightly below your PostgreSQL max_connections. – Craig Ringer Nov 11 '15 at 6:40
  • High max_connections introduces significant inefficiency - what do you mean, exactly? – dezso Nov 11 '15 at 10:04
  • @dezso Pg does some work that scales linearly with the size of max_connections, even if there aren't any active workers. Compare a pgbench with 100 clients on 100 max_connections to the same pgbench with 1000 max_connections. – Craig Ringer Nov 11 '15 at 10:22
  • @CraigRinger is there somewhere a mailing list or blog post describing these? or the only way is to go and check the source? – dezso Nov 11 '15 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.