[ADMIN] 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,

[Craig Ringer]

Post by Radovan Jablonovsky
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

[Albe Laurenz]

Post by Radovan Jablonovsky
Could you please help with this peculiar problem?
2012-06-13 12:58:45.876 MDT [17536]: [1-1] FATAL: terminating

autovacuum process due to administrator

Post by Radovan Jablonovsky
command
The server worked for 48 minutes after and then it started refuse

non-superuser connections, which

Post by Radovan Jablonovsky
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]

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).

Post by Radovan Jablonovsky

Post by Radovan Jablonovsky
Could you please help with this peculiar problem?
2012-06-13 12:58:45.876 MDT [17536]: [1-1] FATAL: terminating

autovacuum process due to administrator

Post by Radovan Jablonovsky
command
The server worked for 48 minutes after and then it started refuse

non-superuser connections, which

Post by Radovan Jablonovsky
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 [phone] (ext. 256) | Fax
1-403-233-8046****


*

Replicon | Hassle-Free Time & Expense Management Software - 7,300 Customers
- 70 Countries
www.replicon.com | facebook <http://www.facebook.com/Replicon.inc> |
twitter<http://twitter.com/Replicon>
| blog <http://www.replicon.com/blog/> | contact
us<http://www.replicon.com/about_replicon/contact_us.aspx>

We are hiring! | search
jobs<http://tbe.taleo.net/NA2/ats/careers/searchResults.jsp?org=REPLICON&cws=1&act=sort&sortColumn=1&__utma=1.651918544.1299001662.1299170819.1299174966.10&__utmb=1.8.10.1299174966&__utmc=1&__utmx=-&__utmz=1.1299174985.10.3.utmcsr=google%7Cutmccn=(organic)%7Cutmcmd=organic%7Cutmctr=replicon%20careers&__utmv=1.%7C3=Visitor%20Type=Prospects=1,&__utmk=40578466>
*

[Craig Ringer]

Post by 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.

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

[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.
SEMMNIMaximum 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?

Sincerely,
Radovan Jablonovsky DBA

[Albe Laurenz]

Post by Radovan Jablonovsky
Could you, please navigate me how to explain 2 different calculation

kernel parameter SEMMNI.
http://www.postgresql.org/docs/9.1/static/kernel-resources.html#SYSVIPC

Post by Radovan Jablonovsky
is used this calculation of SEMMNI.
SEMMNI Maximum number of semaphore identifiers (i.e., sets)

at least ceil((max_connections +

Post by Radovan Jablonovsky
autovacuum_max_workers + 4) / 16)
In this document

http://deepakmurthy.wordpress.com/2012/01/04/configure-a-box-for-kernel-
and-postgres-

Post by Radovan Jablonovsky
configuration/ is used different calculation of SEMMNI.
SEMMNI = ((2 * max connections) * logical partitions on the server +

number of local applications (max

Post by Radovan Jablonovsky
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