On 2020-06-24 13:55:00 -0400, Bee.Lists wrote:
> On Jun 24, 2020, at 6:47 AM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > The default is 100. What was your reason for reducing it to such a low
> > value?
>
> “PostgreSQL 9 High Availability” recommended core count * 3.
I suspected something like that.
I don't have that book, but I assume that the author meant that as a
limit on parallel active queries. An idle connection uses (almost) no
CPU, so it doesn't make sense to include it in the count.
The question is, how do you enforce that limit? Setting max_connections
on the database is no good: It will prevent new connections after the
limit is reached, but the application doesn't know why its getting
errors.
Instead you have to limit the application. If all/most of your database
accesses come from a single web application, configure that application
to open at most 12 connections (there may be an explicit pool size
configuration setting, or you may have to limit that implicitely through
the number of threads and/or processses). If you have several apps, you
might want to configure each to a lower limit so that the sum doesn't
exceed 12. But if you have configured your web app to 12 connections,
you still want max_connections to be higher: You want to be able to
connect with psql even when your web app is running at peak capacity.
You want to run your cron jobs. So always leave some head room.
> >> I’ve installed pg_stat_activity and pg_stat_statements.
> >>
> >> I access this server through a web app,
> >
> > So that's probably a handful connections already.
>
> Looks like 5 queries.
>
> As a comparison I have 37 queries asking for last login of a single user. No errors.
> (I increased the max_connections yesterday)
Does "I have 37 queries" mean you have seen 37 queries of this type in
some time window (e.g. the last day or hour) or does it mean you are
currently seeing 37 connections where the last query was of this type?
If it's the latter, you very obviously have at least 37 (more likely
37 + 5 = 42) connections. So you web app is configured to open dozens of
connections concurrently. You might want to look into that.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"