keepalive but still say too many clients already about pgmoon HOT 5 CLOSED

if a client is taken out of the collection pool it can't be used until it is put back into it. If you're creating a massive amount of concurrent requests then the pool is constantly empty and so new connections need to be made. You're then hitting postgres' limit for connections.

You can try increasing the maximum connection limit from postgres if you know what kind of traffic to expect in additional to adding rate limiting in your nginx config.

In your specific example, I recommend caching a connection across the request, instead of pulling one for every query.

We currently don't have a programmatic way of dealing with the "sorry, too many clients already" error. One option would be to sleep and try again later until we can get a new connection or pull one from the pool.

from pgmoon.

@leafo By saying so new connections need to be made, do you mean if the pool is constantly empty in my example (50 maxium), pgmoon will still try to make a new connection to database instead of simply refusing to do that?
Does the pool size of pgmoon's keepalive function have to (or the recommend way )be smaller than the max_connections of postgres?

test=# show max_connections;
 max_connections 
-----------------
 100
(1 row)

from pgmoon.

correct, nginx cosockets do not block you from creating a new connection even if the pool is empty. pgmoon doesn't have it's own pooling mechanism, it's directly calling https://github.com/openresty/lua-nginx-module#tcpsocksetkeepalive

the amount of connections you're going to need in postgres depnds on concurrent traffic, but you'll want minimally the pool size you set in lua

from pgmoon.

Ok, thanks.

from pgmoon.

@leafo I suddenly realized I should use disconnect when error happens in query. Now I won't get too many clients already error. But I'm not sure whether disconnect on error is OK

local function query(statement)
    local db, res, ok, err
    db = pg_driver.new(CONNECT_TABLE) -- always success
    db:settimeout(CONNECT_TIMEOUT) 
    res, err = db:connect()
    if not res then
        return nil, err
    end
    local a, b, c, d, e =  db:query(statement)
    if a ~= nil and db.sock_type == 'nginx' then
      ok, err = db:keepalive(max_idle_timeout, pool_size)
      if not ok then
        return nil, 'fail to keepalive:'..err
      else
        return a 
      end
    else
      ok, err = db:disconnect() -- non-openresty error
      if not ok then
        return nil, 'fail to disconnect:'..err
      else
        return a, b, c, d, e 
      end
    end
end

from pgmoon.