Hello Kevin Thank you very much for your input. I appreciate it very much. > -----Original Message----- > From: Kevin Grittner [mailto:kgrittn@xxxxxxxxx] > Sent: Dienstag, 22. November 2016 22:37 > To: Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: max_connections limit violation not showing in pg_stat_activity > > On Tue, Nov 22, 2016 at 12:48 PM, Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> wrote: > > > We are using PostgreSQL 9.3.10 on RedHat (probably 6.x). > > Is it possible to upgrade? You are missing over a year's worth of fixes for serious bugs and security > vulnerabilities. Yes. Actually it is foreseen to move to 9.6 (and RedHat 7.x). > https://www.postgresql.org/support/versioning/ > > > Among other thing the database is the backend for a web application > > that expects a load of a some hundred users at a time (those are > > participans to online surveys that we use for computing economic > > indicators and access the system every month). The whole amount of > > people expected is above 5000, but we don't expect a too high > > concurrent access to the database. As mentioned a few hundreds at the beginning of the surveys. > > > > To be sure that we won't have problems with the peak times we created > > a load test using gatling that ramps up to 1000 users in 5 minutes in > > bunches of 10. At the beginning we had problems with the web server > > response that we were able to correct. Now we face problem with the > > max_connections limit of PostgreSQL. Currently it is set to the > > default of 100. We are going to look into it and either increase that > > limit or consider connections pooling. > > On a web site with about 3000 active users, I found (through adjusting the connection pool size on the production > database and monitoring performance) that we got best performance with a pool of about 40 connections. This was on > a machine with 16 cores (never count HT "threads" as cores), 512GB RAM, and a RAID with 40 drives of spinning rust. OK. I will have to check with our hosting people how many cores we have or can have on the new environment. I have seen that there is pgBouncer and pgPool. Would you recommend one of those? AFAICS both are installed on the client side, so that we should be able to use them. > http://tbeitr.blogspot.com/2015/11/for-better-service-please-take-number.html > > > What bothers me however is that running a query on pg_stat_activity > > with a watch of 1 seconds never shows any value higher than 37 of > > concurrent active connections. > > > > SELECT count(*) FROM pg_stat_activity; watch 1; > > At the times when the resources are overloaded by more connections than the resources can efficiently service -- > well that's precisely the time that a sleeping "monitoring" process is least likely to be given a time slice to run. > If you can manage to get pgbadger to run on your environment, and you turn on logging of connections and > disconnections, you will be able to get far more accurate information. Yes, it sounds reasonable. I assumed that this kind of measurements have a higher priority or reserved slots for them. In those occasions is when they are most needed. And thank you for the hint to pgbadger. I will take a look into it, but an installation on the server completely depends on our hosting service people. I am not sure this is feasible, but I can imagine an alternative scenario, using a local installation for tuning and then pass the parameters to the hosters for implementation. Might help. Regards Charles > > > Increasing max_connections has repercussions on the configuration of > > work_mem (if I remember well) > > Each connection can allocate one work_mem allocation per node which requires a sort, hash, CTE, etc. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
