Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aTclS-0004bo-B9 for pgsql-admin@arkaria.postgresql.org; Wed, 10 Feb 2016 21:57:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aTclR-0000r9-DL for pgsql-admin@arkaria.postgresql.org; Wed, 10 Feb 2016 21:57:33 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aTclQ-0000qp-3N for pgsql-admin@postgresql.org; Wed, 10 Feb 2016 21:57:32 +0000 Received: from mail-bl2on0083.outbound.protection.outlook.com ([65.55.169.83] helo=na01-bl2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aTclL-00011d-Ec for pgsql-admin@postgresql.org; Wed, 10 Feb 2016 21:57:30 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=journyx.onmicrosoft.com; s=selector1-journyx-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=P6PsDOWVb1jAFnSIFAM50I9xR9bDVZrYsewjstYQS+I=; b=X4RELId2b4B9FkpuWwyOgMyXgGPgLUL61tzx2zYcq7msgaw6PegtVfKmdKNwo80SqWxllVwkKbku46E2Th4JtUUpvbjU7CY0tWKT6hsd/mVOa6yVY5HHmqtg4rxhE+Iy0QIR/CkpXcctVwGf6d/QwaLwEiktNZqdaHViZe8/YWk= Received: from BY1PR11MB0390.namprd11.prod.outlook.com (10.162.146.14) by BY1PR11MB0392.namprd11.prod.outlook.com (10.162.146.141) with Microsoft SMTP Server (TLS) id 15.1.403.16; Wed, 10 Feb 2016 21:57:23 +0000 Received: from BY1PR11MB0390.namprd11.prod.outlook.com ([10.162.146.14]) by BY1PR11MB0390.namprd11.prod.outlook.com ([10.162.146.14]) with mapi id 15.01.0403.017; Wed, 10 Feb 2016 21:57:23 +0000 From: Scott Whitney To: Kiriakos Georgiou , "pgsql-admin@postgresql.org" Subject: Re: is it cool to restart servers as preventive maintenance? Thread-Topic: [ADMIN] is it cool to restart servers as preventive maintenance? Thread-Index: AQHRZDbnB+NIXauytUmjiDqNDLWYep8luBEAgAAYt4CAAALUSw== Date: Wed, 10 Feb 2016 21:57:22 +0000 Message-ID: References: <11957.1455135528@sss.pgh.pa.us>, <27A8518A-47E5-4BD0-9EF0-6AFEBE4A4DB3@olympiakos.com> In-Reply-To: <27A8518A-47E5-4BD0-9EF0-6AFEBE4A4DB3@olympiakos.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: olympiakos.com; dkim=none (message not signed) header.d=none; olympiakos.com; dmarc=none action=none header.from=journyx.com; x-originating-ip: [24.27.227.0] x-microsoft-exchange-diagnostics: 1; BY1PR11MB0392; 5:NJSJRGbebGcHhH5MhACim2qfEbPNeq6aKcmrMzvIKiYCno8J9ttqMR7CgWwjbLdxingTBVa/ICJkd7gLYf2uTpTeqfOoL/iiuB6EyQsrNyEgjOJxLBXFHvzCzY2lOz3P47W14fZ2RCb5oNb3MJoXNw==; 24:qxMwsx6r4cV6EMFTGzO0SrT8vw+D+XlhTZmIk/S+SmXIYN+lcgJxKiU6RAEP8MBaXARqXAmBEdFbqxgNggP2vCjvlN1dQVVxcqgBIW8//Fc= x-microsoft-antispam: UriScan:; BCL:0; PCL:0; RULEID:(42140001); SRVR:BY1PR11MB0392; x-ms-office365-filtering-correlation-id: 1978756b-2897-47ad-c200-08d332652804 x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046); SRVR:BY1PR11MB0392; BCL:0; PCL:0; RULEID:; SRVR:BY1PR11MB0392; x-forefront-prvs: 0848C1A6AA x-forefront-antispam-report: SFV:NSPM; SFS:(10009020)(24454002)(377454003)(479174004)(15974865002)(95246002)(106116001)(107886002)(5001960100002)(19617315012)(19580395003)(99286002)(5001770100001)(19580405001)(3660700001)(2900100001)(2950100001)(86362001)(11100500001)(87936001)(1220700001)(63666004)(66066001)(19625215002)(551934003)(77096005)(10400500002)(5008740100001)(33646002)(16236675004)(5002640100001)(92566002)(122556002)(40100003)(15975445007)(6116002)(1096002)(102836003)(189998001)(76176999)(586003)(50986999)(2906002)(5004730100002)(3280700002)(54356999)(2501003)(7756004); DIR:OUT; SFP:1101; SCL:1; SRVR:BY1PR11MB0392; H:BY1PR11MB0390.namprd11.prod.outlook.com; FPR:; SPF:None; MLV:sfv; LANG:en; spamdiagnosticoutput: 1:23 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_glm8bn1p8u5437jn119d1tun1455141497888emailandroidcom_" MIME-Version: 1.0 X-OriginatorOrg: journyx.com X-MS-Exchange-CrossTenant-originalarrivaltime: 10 Feb 2016 21:57:22.9639 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 83db917a-cb55-44ba-82ef-25aa3e32343f X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0392 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_glm8bn1p8u5437jn119d1tun1455141497888emailandroidcom_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I would absolutely recommend experimenting with higher max_lock, but I woul= d also HIGHLY recommend checking all relevant logs when this is happening t= o you. If restarting the app server is failing to connect, SOMEWHERE, SOMEONE shou= ld have logged SOMETHING. -------- Original message -------- From: Kiriakos Georgiou Date: 02/10/2016 3:48 PM (GMT-06:00) To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] is it cool to restart servers as preventive maintenanc= e? > On Feb 10, 2016, at 3:18 PM, Tom Lane wrote: > > Kiriakos Georgiou writes: >> In the last 12 months I have noticed 3-4 instances of database flakiness= that is cured by restarting. >> I=92ve been using PostgreSQL since 2007 and I haven=92t seen such issues= requiring a reboot, but on my current project we do some rather heavy duty= PostGIS analysis that apparently stresses the system enough to occasionall= y cause this (that=92s my theory anyway.) I=92m beginning to seriously con= sider restarting servers on a monthly basis. > > What sort of "database flakiness"? > > It's possible you're encountering some kind of bug (memory leak?) in > PostGIS, but that would be a bug you ought to get them to fix, not a > reason why periodic restarts are a good idea. > > regards, tom lane > Flaky =3D the database appears to be running OK (I can run queries via psql= ) but our app is down for no apparent reason. Restarting the app servers m= ultiple times did not help. Although the database seemed to respond fine t= o queries via psql, I decided to restart it. That was a good move, our app= worked fine after the database restart. There is more to it: about 8 hours earlier our warm standby postgresql fill= ed up the volume it puts the server logs by repeating the following two lin= es in the server log, millions of times: WARNING: out of shared memory CONTEXT: xlog redo AccessExclusive locks: xid 2002212 db 16384 rel 107987= 9 I had seen this on our primary about a year ago and I kept doubling max_loc= ks_per_transaction all the way to 1024, at which point the problem did not = reoccur (on the primary.) I still occasionally (once every 3-4 months) see the =93out of shared memor= y=94 message on the standby although max_locks_per_transaction has the same= 1024 value as the primary. When this happens I have to rebuilt it from th= e primary via pg_basebackup. When the =93out of shared memory=94 happens o= n the standby, it=92s a coin toss whether the primary will behave flaky or = not. This time it did, and the restart fixed it. Writing this email made me realize the likely cause of our problem. It=92s= the =93out of shared memory=94 issue. We do have a plpgsql function that = calls 30+ other plpgsql functions, some of which create temp tables. Some = of the calls are within loops. So depending on data inputs we can get hund= reds of locks to temp tables within a single transaction. The odd thing is= that at max_locks_per_transaction =3D 1024 we no longer get any =93out of = shared memory=94 on the primary, but we do on the stand by. Any ideas abou= t that? Should I increase max_locks_per_transaction yet again? thanks, Kiriakos Georgiou -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin Journyx, Inc. 7600 Burnet Road #300 Austin, TX 78757 www.journyx.com p 512.834.8888 f 512-834-8858 Do you receive our promotional emails? You can subscribe or unsubscribe to = those emails at http://go.journyx.com/emailPreference/e/4932/714/ --_000_glm8bn1p8u5437jn119d1tun1455141497888emailandroidcom_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
I would absolutely recommend experimenting with higher max_lock, but I= would also HIGHLY recommend checking all relevant logs when this is happen= ing to you.

If restarting the app server is failing to connect, SOMEWHERE, SOMEONE= should have logged SOMETHING.


-------- Original message --------
From: Kiriakos Georgiou <kg.postgresql@olympiakos.com>
Date: 02/10/2016 3:48 PM (GMT-06:00)
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] is it cool to restart servers as preventive maintenanc= e?


> On Feb 10, 2016, at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:=
>
> Kiriakos Georgiou <kg.postgresql@olympiakos.com> writes:
>> In the last 12 months I have noticed 3-4 instances of database fla= kiness that is cured by restarting.
>> I=92ve been using PostgreSQL since 2007 and I haven=92t seen such = issues requiring a reboot, but on my current project we do some rather heav= y duty PostGIS analysis that apparently stresses the system enough to occas= ionally cause this (that=92s my theory anyway.)  I=92m beginning to seriously consider restarting servers on a monthly basi= s.
>
> What sort of "database flakiness"?
>
> It's possible you're encountering some kind of bug (memory leak?) in > PostGIS, but that would be a bug you ought to get them to fix, not a > reason why periodic restarts are a good idea.
>
>            = ;            regards= , tom lane
>



Flaky =3D the database appears to be running OK (I can run queries via psql= ) but our app is down for no apparent reason.  Restarting the app serv= ers multiple times did not help.  Although the database seemed to resp= ond fine to queries via psql, I decided to restart it.  That was a good move, our app worked fine after the database res= tart.

There is more to it: about 8 hours earlier our warm standby postgresql fill= ed up the volume it puts the server logs by repeating the following two lin= es in the server log, millions of times:

WARNING:   out of shared memory
CONTEXT:   xlog redo AccessExclusive locks: xid 2002212 db 16384 = rel 1079879

I had seen this on our primary about a year ago and I kept doubling max_loc= ks_per_transaction all the way to 1024, at which point the problem did not = reoccur (on the primary.)
I still occasionally (once every 3-4 months) see the =93out of shared memor= y=94 message on the standby although max_locks_per_transaction has the same= 1024 value as the primary.  When this happens I have to rebuilt it fr= om the primary via pg_basebackup.  When the =93out of shared memory=94 happens on the standby, it=92s a coin toss whet= her the primary will behave flaky or not.  This time it did, and the r= estart fixed it.

Writing this email made me realize the likely cause of our problem.  I= t=92s the =93out of shared memory=94 issue.  We do have a plpgsql func= tion that calls 30+ other plpgsql functions, some of which create temp = tables.  Some of the calls are within loops.  So depending on data inputs we can get hundreds of locks to temp tables within a single= transaction.  The odd thing is that at max_locks_per_transaction =3D = 1024 we no longer get any =93out of shared memory=94 on the primary, but we= do on the stand by.  Any ideas about that?  Should I increase max_locks_per_transaction yet again?

thanks,
Kiriakos Georgiou

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postg= resql.org/mailpref/pgsql-admin


Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscrib= e to those emails at http://go.journyx.com/emailPreference/e/4932/714/ = ;
--_000_glm8bn1p8u5437jn119d1tun1455141497888emailandroidcom_--