Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X1hTA-00028M-VN for pgsql-performance@arkaria.postgresql.org; Mon, 30 Jun 2014 19:42:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X1hTA-0004j6-2P for pgsql-performance@arkaria.postgresql.org; Mon, 30 Jun 2014 19:42:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X1hT8-0004iz-RN for pgsql-performance@postgresql.org; Mon, 30 Jun 2014 19:42:26 +0000 Received: from smtp128.ord.emailsrvr.com ([173.203.6.128]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1X1hT5-0008GJ-JP for pgsql-performance@postgresql.org; Mon, 30 Jun 2014 19:42:24 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp17.relay.ord1a.emailsrvr.com (SMTP Server) with ESMTP id 6E01E3802BF; Mon, 30 Jun 2014 15:42:22 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp17.relay.ord1a.emailsrvr.com (Authenticated sender: jeff.frost-AT-pgexperts.com) with ESMTPSA id 6B449380210; Mon, 30 Jun 2014 15:42:21 -0400 (EDT) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.2\)) Subject: Re: Postgres Replaying WAL slowly From: Jeff Frost In-Reply-To: <20300.1404156750@sss.pgh.pa.us> Date: Mon, 30 Jun 2014 12:42:19 -0700 Cc: Andres Freund , Soni M , Heikki Linnakangas , pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <53AFCEE3.4080009@vmware.com> <53B10C4C.8090701@vmware.com> <53B18CE0.4060501@vmware.com> <20140630171416.GK21422@awork2.anarazel.de> <8DA8C140-818D-4184-AAE6-CA37278B983C@pgexperts.com> <20140630183908.GT26930@awork2.anarazel.de> <0A7B96D4-DB10-49A8-A268-F28960F2E137@pgexperts.com> <20300.1404156750@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.1878.2) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Jun 30, 2014, at 12:32 PM, Tom Lane wrote: > Jeff Frost writes: >> Sampling pg_locks on the primary shows ~50 locks with ExclusiveLock mode: >=20 >> mode | count >> --------------------------+------- >> AccessExclusiveLock | 11 >> AccessShareLock | 2089 >> ExclusiveLock | 46 >> RowExclusiveLock | 81 >> RowShareLock | 17 >> ShareLock | 4 >> ShareUpdateExclusiveLock | 5 >=20 > That's not too helpful if you don't pay attention to what the lock is on; > it's likely that all the ExclusiveLocks are on transactions' own XIDs, > which isn't relevant to the standby's behavior. The AccessExclusiveLocks > are probably interesting though --- you should look to see what those > are on. You're right about the ExclusiveLocks. Here's how the AccessExclusiveLocks look: locktype | database | relation | page | tuple | virtualxid | transaction= id | classid | objid | objsubid | virtualtransaction | pid | = mode | granted ----------+----------+------------+------+-------+------------+------------= ---+---------+------------+----------+--------------------+-------+--------= -------------+--------- relation | 111285 | 3245291551 | | | | = | | | | 233/170813 | 23509 | AccessE= xclusiveLock | t relation | 111285 | 3245292820 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292833 | | | | = | | | | 173/1723993 | 23407 | AccessE= xclusiveLock | t relation | 111285 | 3245287874 | | | | = | | | | 133/3818415 | 23348 | AccessE= xclusiveLock | t relation | 111285 | 3245292836 | | | | = | | | | 173/1723993 | 23407 | AccessE= xclusiveLock | t relation | 111285 | 3245292774 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292734 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292827 | | | | = | | | | 173/1723993 | 23407 | AccessE= xclusiveLock | t relation | 111285 | 3245288540 | | | | = | | | | 133/3818415 | 23348 | AccessE= xclusiveLock | t relation | 111285 | 3245292773 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292775 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292743 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292751 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245288669 | | | | = | | | | 133/3818415 | 23348 | AccessE= xclusiveLock | t relation | 111285 | 3245292817 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245288657 | | | | = | | | | 133/3818415 | 23348 | AccessE= xclusiveLock | t object | 111285 | | | | | = | 2615 | 1246019760 | 0 | 233/170813 | 23509 | AccessE= xclusiveLock | t relation | 111285 | 3245292746 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245287876 | | | | = | | | | 133/3818415 | 23348 | AccessE= xclusiveLock | t relation | 111285 | 3245292739 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292826 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292825 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245292832 | | | | = | | | | 173/1723993 | 23407 | AccessE= xclusiveLock | t relation | 111285 | 3245292740 | | | | = | | | | 5/22498235 | 23427 | AccessE= xclusiveLock | t relation | 111285 | 3245287871 | | | | = | | | | 133/3818415 | 23348 | AccessE= xclusiveLock | t (25 rows) And if you go fishing in pg_class for any of the oids, you don't find anyth= ing: SELECT s.procpid, s.query_start, n.nspname, c.relname, l.mode, l.granted, s.current_query FROM pg_locks l, pg_class c, pg_stat_activity s, pg_namespace n WHERE l.relation =3D c.oid AND l.pid =3D s.procpid AND c.relnamespace =3D n.oid AND l.mode =3D 'AccessExclusiveLock'; procpid | query_start | nspname | relname | mode | granted | current_query ---------+-------------+---------+---------+------+---------+--------------- (0 rows) Temp tables maybe? --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance