Re: [GENERAL] Dead rows not getting removed during vacuum
Thank you Tom. We will be upgrading soon. Regards, Granthana On Mon, Mar 24, 2014 at 7:28 PM, Tom Lane wrote: > Granthana Biswas writes: > > Version is PostgreSQL 9.1.4. > > You do realize you're missing almost two years' worth of bug fixes? > The current release in that branch is 9.1.13, and a quick look through > the git history shows quite a number of replication-related fixes. > > One that seems particularly notable in this connection is: > > commit 16222f32ed56d3ebc4136133662d932299188955 > Author: Simon Riggs > Date: Thu Jun 7 19:24:47 2012 +0100 > > Wake WALSender to reduce data loss at failover for async commit. > WALSender now woken up after each background flush by WALwriter, > avoiding > multi-second replication delay for an all-async commit workload. > Replication delay reduced from 7s with default settings to 200ms, > allowing > significantly reduced data loss at failover. > > Andres Freund and Simon Riggs > > You wouldn't happen to be running with synchronous_commit off, would you? > > Whether this is the explanation for your problem or not, it's really > irresponsible to still be running 9.1.4 at this point. There are several > known data-loss-inducing bugs in it that will eat your data sooner or > later. > > regards, tom lane >
Re: [GENERAL] Dead rows not getting removed during vacuum
Sergey, Version is PostgreSQL 9.1.4. max_standby_archive_delay = 120s max_standby_streaming_delay = 180s Long running activity or idle in transactions are not there on either master or slave as we stop all connections to both the DBs during vacuum. Regards, Granthana On Fri, Mar 21, 2014 at 8:51 PM, Sergey Konoplev wrote: > On Fri, Mar 21, 2014 at 2:46 AM, Granthana Biswas > wrote: > > We don't have two-phase commit prepared transactions. During vacuum, > along with this problem we are also facing streaming replication issue. > Hundreds of xlogs are in ready state in pg_xlog/archive_status. Those are > being copied manually after vacuum otherwise rsync happens very slowly and > replication lag keeps increasing. > > Okay, I see. Then next questions are: > > What postgres version is it? > What max_standby_archive_delay and max_standby_streaming_delay are set to? > Are there any long running activity or idling in transaction backends > on your slaves? > > -- > Kind regards, > Sergey Konoplev > PostgreSQL Consultant and DBA > > http://www.linkedin.com/in/grayhemp > +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 > gray...@gmail.com >
Re: [GENERAL] Dead rows not getting removed during vacuum
Hi Sergey, We don't have two-phase commit prepared transactions. During vacuum, along with this problem we are also facing streaming replication issue. Hundreds of xlogs are in ready state in pg_xlog/archive_status. Those are being copied manually after vacuum otherwise rsync happens very slowly and replication lag keeps increasing. Regards, Granthana On Fri, Mar 21, 2014 at 3:36 AM, Sergey Konoplev wrote: > On Thu, Mar 20, 2014 at 5:27 AM, Granthana Biswas > wrote: > > Has anyone ever faced the issue of dead rows not getting removed during > > vacuum even if there are no open transactions/connections? > > What does the pg_prepared_xacts view show? > > -- > Kind regards, > Sergey Konoplev > PostgreSQL Consultant and DBA > > http://www.linkedin.com/in/grayhemp > +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 > gray...@gmail.com >
Re: [GENERAL] Dead rows not getting removed during vacuum
Hi Venkata, No there are no other connections to the DB during vacuum. Regards, Granthana On Fri, Mar 21, 2014 at 3:12 AM, Venkata Balaji Nagothi wrote: > On Thu, Mar 20, 2014 at 5:27 AM, Granthana Biswas wrote: > > Hello All, >> >> Has anyone ever faced the issue of dead rows not getting removed during >> vacuum even if there are no open transactions/connections? >> >> We have been facing this during every scheduled vacuum which is done >> after closing all other database connections: >> >> 119278 dead row versions cannot be removed yet. >> >> This means there are transactions in progress when the vacuum was > running. Do you see any such situation ? > > Please let us know, which version of PostgreSQL is this. > > Venkata Balaji N > > Sr. Database Administrator > Fujitsu Australia > >
[GENERAL] Dead rows not getting removed during vacuum
Hello All, Has anyone ever faced the issue of dead rows not getting removed during vacuum even if there are no open transactions/connections? We have been facing this during every scheduled vacuum which is done after closing all other database connections: 119278 dead row versions cannot be removed yet. The number of dead rows varies with each table. Regards, Granthana
Re: [GENERAL] Correct query to check streaming replication lag
Yes byte lag as well as it makes more sense. Regards, Granthana Regards, Granthana On Tue, Jan 21, 2014 at 11:03 AM, Sameer Kumar wrote: > >> >> We are already using the following query: >> >> SELECT CASE WHEN pg_last_xlog_receive_location( >> ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() >> - pg_last_xact_replay_timestamp()) END AS log_delay; >> >> This is (delay) not the correct thing to monitor. > > We cannot use pg_xlog_location_diff as we use postgresql 9.1. >> >> You can still use the other two methods I mentioned. > > > Regards > Sameer > Ashnik Pte Ltd, Singapore >
Re: [GENERAL] Correct query to check streaming replication lag
Thanks a load Michael. This is really helpful. Regards, Granthana On Tue, Jan 21, 2014 at 12:19 PM, Michael Paquier wrote: > > > On Tue, Jan 21, 2014 at 2:33 PM, Sameer Kumar > wrote: > >> > >> > >> We are already using the following query: > >> > >> SELECT CASE WHEN pg_last_xlog_receive_location( > >> ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM > now() - > >> pg_last_xact_replay_timestamp()) END AS log_delay; > >> > > This is (delay) not the correct thing to monitor. > > > >> We cannot use pg_xlog_location_diff as we use postgresql 9.1. > >> > > You can still use the other two methods I mentioned. > > FYI, here is an equivalent written in plpgsql easily findable by googling > a bit, making a pg_xlog_location_diff-like function usable even in 9.1 and > 9.0 servers: > CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text) > RETURNS numeric > LANGUAGE plpgsql > AS > $function$ > DECLARE >offset1 text; >offset2 text; >xlog1 text; >xlog2 text; >SQL text; >diff text; > BEGIN >/* Extract the Offset and xlog from input in > offset and xlog variables */ > >offset1=split_part($1,'/',2); > xlog1=split_part($1,'/',1); >offset2=split_part($2,'/',2); > xlog2=split_part($2,'/',1); > >/* Prepare SQL query for calculation based on following formula > (FF00 * xlog + offset) - (FF00 * xlog + offset) > which gives value in hexadecimal. Since, hexadecimal calculation > is cumbersome > so convert into decimal and then calculate the difference */ > >SQL='SELECT (x'''||'FF00'||'''::bigint * > x'''||xlog1||'''::bigint > + x'''||offset1||'''::bigint)'||' > - >(x'''||'FF00'||'''::bigint * > x'''||xlog2||'''::bigint > + x'''||offset2||'''::bigint)'; >EXECUTE SQL into diff; > >/* Return the value in numeric by explicit casting */ > >RETURN diff::numeric; > END; > $function$; > > Source: > http://vibhorkumar.wordpress.com/2013/02/18/pg_xlog_location_diff-function-for-postgreqsqlppas/ > -- > Michael >
Re: [GENERAL] Correct query to check streaming replication lag
Hi Ray, We are already using the following query: SELECT CASE WHEN pg_last_xlog_receive_location( ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; We cannot use pg_xlog_location_diff as we use postgresql 9.1. Regards, Granthana On Fri, Jan 17, 2014 at 8:24 PM, Ray Stell wrote: > > On Jan 17, 2014, at 5:07 AM, Granthana Biswas wrote: > > Yes it's purely for monitoring purpose. > > > I use the pg_controldata cmd locally and via bash/ssh shared keys and > compare various values that seem interesting such as "Time of latest > checkpoint, Latest checkpoint location." My interest is recoverability and > checkpoints seemed relevant at the time. > > I found a comment in the docs: > > http://www.postgresql.org/docs/9.2/static/functions-admin.html > > "pg_xlog_location_diff calculates the difference in bytes between two > transaction log locations. It can be used with pg_stat_replication or > some functions shown in Table > 9-59<http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE>to > get the replication lag." > > and > "The functions shown in Table > 9-60<http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE>provide > information about the current status of the standby. These > functions may be executed both during recovery and in normal running." > These look interesting wrt lag studies and seem to work on the stby: > > template1=# select pg_last_xlog_receive_location(); > pg_last_xlog_receive_location > --- > 18/9E00 > (1 row) > > template1=# select pg_last_xlog_replay_location(); > pg_last_xlog_replay_location > -- > 18/9E00 > (1 row) > > > >
Re: [GENERAL] Correct query to check streaming replication lag
Yes we already do that. Count the number of ready wal files. Regards, Granthana On Sat, Jan 18, 2014 at 9:39 PM, Sameer Kumar wrote: > Well in that case monitoring pending wal bytes would make more sense. > > Regards > Sameer > > PS: Sent from my Mobile device. Pls ignore typo n abb >
Re: [GENERAL] Correct query to check streaming replication lag
Yes it's purely for monitoring purpose. Regards, Granthana On Fri, Jan 17, 2014 at 3:29 PM, Sameer Kumar wrote: > > > On Fri, Jan 17, 2014 at 5:31 PM, Granthana Biswas wrote: > >> >> >> Thank you Sameer for your reply. Is there any other query that would help >> get exact replication lag? >> >> > You 2nd Query is the most accurate you can get. > > >> 2. SELECT CASE WHEN pg_last_xlog_receive_location() = >>>> pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - >>>> pg_last_xact_replay_timestamp()) END AS log_delay; >>> >>> For reason same as above, this won't be exact but the most accurate you >>> can get. >>> >>> You can get lags in terms of bytes (and I have always believed that > makes more sense). > > > May I ask, why are you looking for this info? Is it purely monitoring? > > Regards > Sameer > Ashnik Pte Ltd. > >
Re: [GENERAL] Correct query to check streaming replication lag
Thank you Sameer for your reply. Is there any other query that would help get exact replication lag? Regards, Granthana Regards, Granthana On Fri, Jan 17, 2014 at 2:46 PM, Sameer Kumar wrote: > > On Tue, Jan 14, 2014 at 2:31 PM, Granthana Biswas wrote: > >> Can anyone please tell me which of the following is the correct >> replication lag query to find streaming replication lag in seconds? >> >> IMHO none is 'correct'. :-) > > 1. SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) >> AS time_lag; >> >> This is the difference between now and last replayed log. What if the > last transaction on master has happened a few minutes ago? > > >> 2. SELECT CASE WHEN pg_last_xlog_receive_location() = >> pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - >> pg_last_xact_replay_timestamp()) END AS log_delay; >> > > For reason same as above, this won't be exact but the most accurate you > can get. > > Best Regards, > *Sameer Kumar | Database Consultant* > > *ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore > 069533 > M : *+65 8110 0350 <%2B65%208110%200350>* T: +65 6438 3504 | > www.ashnik.com > www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz > > [image: email patch] > > This email may contain confidential, privileged or copyright material and > is solely for the use of the intended recipient(s). > <>
[GENERAL] Correct query to check streaming replication lag
Hello All, Can anyone please tell me which of the following is the correct replication lag query to find streaming replication lag in seconds? 1. SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag; 2. SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; Regards, Granthana
[GENERAL] AccessShareLock on pg_authid
Hi, Processes are failing due to the following error on Postgresql 8.3.5: FATAL: lock AccessShareLock on object 0/1260/0 is already held 1260 oid belongs to pg_authid. This error is not coming for every transaction. I have found these two links related to the above error but not quite helpful: http://web.archiveorange.com/archive/v/alpsnhads7a0RcdRyB5t http://www.postgresql.org/message-id/7386.1315434...@sss.pgh.pa.us Has anyone come across this issue and debugged it successfully? Thanks & Regards, GB
Re: [GENERAL] Transaction control in shards through PLPROXY
Hi Sergey, Thank you for your reply. Have you implemented this while sharding your database? Did it cause any performance issues? Warm regards, GB On Mon, Jul 15, 2013 at 10:51 AM, Sergey Konoplev wrote: > On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas > wrote: > > Inspite of being aware that PLPROXY does autocommit for DML functions > called > > on shards, I was wondering if there is any way around to put a set of DML > > functions called from Router on shards inside a transaction so that all > > updates on shards can be rolled back if any one among the set fails? > > It is called two-phase commit. You need to consult with this [1] > section of documentation. > > [1] http://www.postgresql.org/docs/9.2/static/sql-prepare-transaction.html > > > -- > Kind regards, > Sergey Konoplev > PostgreSQL Consultant and DBA > > Profile: http://www.linkedin.com/in/grayhemp > Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 > Skype: gray-hemp > Jabber: gray...@gmail.com >
[GENERAL] Transaction control in shards through PLPROXY
Hi, Even though this mailing list is for postgresql users only, I am sending this query related to postgresql sharding in case anyone has already faced this problem while sharding their database. Inspite of being aware that PLPROXY does autocommit for DML functions called on shards, I was wondering if there is any way around to put a set of DML functions called from Router on shards inside a transaction so that all updates on shards can be rolled back if any one among the set fails? It would be great if anyone has already done this in their sharded database. I was looking into transaction manager but it handles only one DML function at a time. Many Thanks, GB
