Обсуждение: terminating autovacuum process due to administrator command

Поиск
Список
Период
Сортировка

terminating autovacuum process due to administrator command

От
Radovan Jablonovsky
Дата:
Could you please help with this peculiar problem?

In PostgreSQL log occurred this message:
2012-06-13 12:58:45.876 MDT [17536]: [1-1] FATAL:  terminating autovacuum process due to administrator command
The server  worked for 48 minutes after and then it started refuse non-superuser connections, which effectively rendered it unusable for client applications.
What could cause this behaviour?

Sincerely,
--

Radovan Jablonovsky DBA


Re: terminating autovacuum process due to administrator command

От
Craig Ringer
Дата:
On 06/23/2012 10:14 AM, Radovan Jablonovsky wrote:
Could you please help with this peculiar problem?

Not without more information than that - at the very minimum, your version of PostgreSQL.

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

You also need to provide some info about the load the server was on, about what messages (if any) appear in the logs during the period when it was refusing connections and leading up to it, the error message with which it was refusing connections, etc.

--
Craig Ringer

Re: terminating autovacuum process due to administrator command

От
"Albe Laurenz"
Дата:
Radovan Jablonovsky wrote:
> Could you please help with this peculiar problem?
>
>
> In PostgreSQL log occurred this message:
>
> 2012-06-13 12:58:45.876 MDT [17536]: [1-1] FATAL:  terminating
autovacuum process due to administrator
> command
> The server  worked for 48 minutes after and then it started refuse
non-superuser connections, which
> effectively rendered it unusable for client applications.
> What could cause this behaviour?

You gave very little information.

How were the connections refused (error message)?
Could it be that there are already max_connections sessions?

Yours,
Laurenz Albe

Re: terminating autovacuum process due to administrator command

От
Radovan Jablonovsky
Дата:
Thanks for response,

How were the connections refused (error message)? 
2012-06-13 13:45:38.809 MDT [25172]: [1-1] FATAL:  remaining connection slots are reserved for non-replication superuser connections
2012-06-13 13:45:38.889 MDT [25173]: [1-1] FATAL:  remaining connection slots are reserved for non-replication superuser connections
2012-06-13 13:45:38.895 MDT [25174]: [1-1] FATAL:  remaining connection slots are reserved for non-replication superuser connections

Could it be that there are already max_connections sessions? max_connection was set to 600, when issue occurred the db server had 85 connection and server was under medium load.

Server details:
We're running postgres 9.1.1 linux x64 centos 5.8

aspdata=# select version();                                                   version
---------------------------------------------------------------------------------------------------------------PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-52), 64-bit


We currently have 1 DB we use for multiple independent tenant schemas. The
database size is current 56227005240 bytes as reported by pg_database_size.
There are 557 schemas each with about 1300 objects (760 tables 520 views).

On Mon, Jun 25, 2012 at 2:03 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Radovan Jablonovsky wrote:
> Could you please help with this peculiar problem?
>
>
> In PostgreSQL log occurred this message:
>
> 2012-06-13 12:58:45.876 MDT [17536]: [1-1] FATAL:  terminating
autovacuum process due to administrator
> command
> The server  worked for 48 minutes after and then it started refuse
non-superuser connections, which
> effectively rendered it unusable for client applications.
> What could cause this behaviour?

You gave very little information.

How were the connections refused (error message)?
Could it be that there are already max_connections sessions?

Yours,
Laurenz Albe



--

Radovan Jablonovsky | SaaS DBA | Phone 1-403-262-6519 (ext. 256) | Fax 1-403-233-8046


Replicon | Hassle-Free Time & Expense Management Software - 7,300 Customers - 70 Countries
www.replicon.com | facebook | twitter | blog | contact us

We are hiring! | search jobs


Re: terminating autovacuum process due to administrator command

От
Craig Ringer
Дата:
On 06/25/2012 11:00 PM, Radovan Jablonovsky wrote:
Thanks for response,

How were the connections refused (error message)? 
2012-06-13 13:45:38.809 MDT [25172]: [1-1] FATAL:  remaining connection slots are reserved for non-replication superuser connections
2012-06-13 13:45:38.889 MDT [25173]: [1-1] FATAL:  remaining connection slots are reserved for non-replication superuser connections
2012-06-13 13:45:38.895 MDT [25174]: [1-1] FATAL:  remaining connection slots are reserved for non-replication superuser connections

Could it be that there are already max_connections sessions? max_connection was set to 600, when issue occurred the db server had 85 connection and server was under medium load.

I can't explain why it was topping out at 85 when it was set to 600, but - I strongly recommend that you switch to a connection pool and lower your max_connections considerably. You'll likely get much better performance with max connections at 20 or less - the usual rule of thumb is "num_hdds + num_cpus"  but in reality you need to benchmark and tune to work out what's best.

pgbouncer is a good light-weight pooling option.

--
Craig Ringer

Re: terminating autovacuum process due to administrator command

От
Radovan Jablonovsky
Дата:
Hello,

Could you, please navigate me how to explain 2 different calculation kernel parameter SEMMNI.

In PostgreSQL documentation: http://www.postgresql.org/docs/9.1/static/kernel-resources.html#SYSVIPC is used this calculation of SEMMNI.
SEMMNI Maximum number of semaphore identifiers (i.e., sets)at least ceil((max_connections + autovacuum_max_workers + 4) / 16)

SEMMNI = ((2 * max connections) * logical partitions on the server + number of local applications (max app server connections) )
Does it make sense? And if yes why?

Sincerely,
Radovan Jablonovsky DBA

Re: terminating autovacuum process due to administrator command

От
"Albe Laurenz"
Дата:
Radovan Jablonovsky wrote:
> Could you, please navigate me how to explain 2 different calculation
kernel parameter SEMMNI.
>
> In PostgreSQL documentation:
http://www.postgresql.org/docs/9.1/static/kernel-resources.html#SYSVIPC
> is used this calculation of SEMMNI.
> SEMMNI    Maximum number of semaphore identifiers (i.e., sets)
at least ceil((max_connections +
> autovacuum_max_workers + 4) / 16)
>
>
> In this document
http://deepakmurthy.wordpress.com/2012/01/04/configure-a-box-for-kernel-
and-postgres-
> configuration/ is used different calculation of SEMMNI.
> SEMMNI = ((2 * max connections) * logical partitions on the server +
number of local applications (max
> app server connections) )
> Does it make sense? And if yes why?

I don't know what a "logical partition" in Linux or PostgreSQL should be
(they can hardly be talking about disk partitions), so it does not make
sense to me.

Yours,
Laurenz Albe