Обсуждение: terminating autovacuum process due to administrator command
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
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
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
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
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:
You gave very little information.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?
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 We are hiring! | search jobs
www.replicon.com | facebook | twitter | blog | contact us
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 connections2012-06-13 13:45:38.889 MDT [25173]: [1-1] FATAL: remaining connection slots are reserved for non-replication superuser connections2012-06-13 13:45:38.895 MDT [25174]: [1-1] FATAL: remaining connection slots are reserved for non-replication superuser connectionsCould 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
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) |
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
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