Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
El 20/05/16 a las 10:19, Leonardo M. Ramé escribió: El 19/05/16 a las 12:39, Saiful Muhajir escribió: This has happened to us where we have dead or unmanaged consumer. Turns out londiste is keeping the event even if the consumer is unreachable. This is to ensure that the consumer gets what it should. To clean this up, delete the unused/dead consumer, with qadmin or manually if necessary. The table won't be deleted immediately though. We have to restart pgqd and workers and wait for two days. Thanks Rene and Saiful, I found two unused consumers, but after "unregister consumer " those aren't deleted, what can I do to remove them?. Sorry, I successfully deleted them by using: unregister consumer CONSUMER_NAME from QUEUE_NAME The 2nd param QUEUE_NAME is a *must*. Now I'm waiting for the events deletion... Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
El 19/05/16 a las 12:39, Saiful Muhajir escribió: This has happened to us where we have dead or unmanaged consumer. Turns out londiste is keeping the event even if the consumer is unreachable. This is to ensure that the consumer gets what it should. To clean this up, delete the unused/dead consumer, with qadmin or manually if necessary. The table won't be deleted immediately though. We have to restart pgqd and workers and wait for two days. Thanks Rene and Saiful, I found two unused consumers, but after "unregister consumer " those aren't deleted, what can I do to remove them?. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
This has happened to us where we have dead or unmanaged consumer. Turns out londiste is keeping the event even if the consumer is unreachable. This is to ensure that the consumer gets what it should. To clean this up, delete the unused/dead consumer, with qadmin or manually if necessary. The table won't be deleted immediately though. We have to restart pgqd and workers and wait for two days. ~ Saiful Muhajir On 19 May 2016 20:01, "Rene ." <rene...@hotmail.com> wrote: > Queue - Event are stored in queue tables i.e queues. Several producers can > write > into same queue and several consumers can read from the queue. Events are > kept > in queue until all the consumers have seen them. > > Maybe you have some inactive consumers holding a event tables. > > qadmin -h -p 5432 -U postgres -d -Q > > > Use 'show help;' to see available commands. > copy output of show consumer command > > show consumer; > > Rene > > > From: pgsql-general-ow...@postgresql.org < > pgsql-general-ow...@postgresql.org> on behalf of Leonardo M. Ramé < > l.r...@griensu.com> > Sent: Thursday, May 19, 2016 2:43 PM > To: PostgreSql-general > Subject: Re: [GENERAL] Londiste 3 pgq events_1_1 table huge > > El 18/05/16 a las 19:03, Rene . escribió: > > Hi, Check for long running Idle in transaction sessions. Idle in > transaction sessions may holding events table from cleaning itself up. > > If there is more then days long running idle in transaction sessions, > kill them, event table should be cleaned automatically after that. > > > > "select pid,state, query_start from pg_stat_activity where state='idle > in transaction';" for checking sessions. > > > > Rene > > Thanks Rene, I found only one "idle in transaction" and it dates from > just a couple of minutes ago, so, the reason of huge event table must be > something else. > > By looking at the event_1_1 table I found records from march, but > londiste3 status shows everything is already in sync: > > nodo_master (root) >| Tables: 146/0/0 >| Lag: 0s, Tick: 1112197 >+--: node_esclavo (leaf) >Tables: 146/0/0 >Lag: 0s, Tick: 1112197 > > So, what if I manually delete old events?. > > > Regards, > -- > Leonardo M. Ramé > Medical IT - Griensu S.A. > Av. Colón 636 - Piso 8 Of. A > X5000EPT -- Córdoba > Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 > Cel.: +54 9 (011) 40871877 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
Queue - Event are stored in queue tables i.e queues. Several producers can write into same queue and several consumers can read from the queue. Events are kept in queue until all the consumers have seen them. Maybe you have some inactive consumers holding a event tables. qadmin -h -p 5432 -U postgres -d -Q Use 'show help;' to see available commands. copy output of show consumer command show consumer; Rene From: pgsql-general-ow...@postgresql.org <pgsql-general-ow...@postgresql.org> on behalf of Leonardo M. Ramé <l.r...@griensu.com> Sent: Thursday, May 19, 2016 2:43 PM To: PostgreSql-general Subject: Re: [GENERAL] Londiste 3 pgq events_1_1 table huge El 18/05/16 a las 19:03, Rene . escribió: > Hi, Check for long running Idle in transaction sessions. Idle in transaction > sessions may holding events table from cleaning itself up. > If there is more then days long running idle in transaction sessions, kill > them, event table should be cleaned automatically after that. > > "select pid,state, query_start from pg_stat_activity where state='idle in > transaction';" for checking sessions. > > Rene Thanks Rene, I found only one "idle in transaction" and it dates from just a couple of minutes ago, so, the reason of huge event table must be something else. By looking at the event_1_1 table I found records from march, but londiste3 status shows everything is already in sync: nodo_master (root) | Tables: 146/0/0 | Lag: 0s, Tick: 1112197 +--: node_esclavo (leaf) Tables: 146/0/0 Lag: 0s, Tick: 1112197 So, what if I manually delete old events?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
El 18/05/16 a las 19:03, Rene . escribió: Hi, Check for long running Idle in transaction sessions. Idle in transaction sessions may holding events table from cleaning itself up. If there is more then days long running idle in transaction sessions, kill them, event table should be cleaned automatically after that. "select pid,state, query_start from pg_stat_activity where state='idle in transaction';" for checking sessions. Rene Thanks Rene, I found only one "idle in transaction" and it dates from just a couple of minutes ago, so, the reason of huge event table must be something else. By looking at the event_1_1 table I found records from march, but londiste3 status shows everything is already in sync: nodo_master (root) | Tables: 146/0/0 | Lag: 0s, Tick: 1112197 +--: node_esclavo (leaf) Tables: 146/0/0 Lag: 0s, Tick: 1112197 So, what if I manually delete old events?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
Hi, Check for long running Idle in transaction sessions. Idle in transaction sessions may holding events table from cleaning itself up. If there is more then days long running idle in transaction sessions, kill them, event table should be cleaned automatically after that. "select pid,state, query_start from pg_stat_activity where state='idle in transaction';" for checking sessions. Rene From: pgsql-general-ow...@postgresql.org <pgsql-general-ow...@postgresql.org> on behalf of Leonardo M. Ramé <l.r...@griensu.com> Sent: Wednesday, May 18, 2016 10:23 PM To: PostgreSql-general Subject: [GENERAL] Londiste 3 pgq events_1_1 table huge Hi, I couldn't find a mailing list or forum to ask londiste related questions, so I hope someone from this list can help me with this. I have a Londiste based replication setup that is working perfectly since last year. Now I noted the events_1_1 table grew too much (almost exactly the same size of the whole database), so I'm asking how can I clean up the events table without breaking the replication?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Londiste 3 pgq events_1_1 table huge
Hi, I couldn't find a mailing list or forum to ask londiste related questions, so I hope someone from this list can help me with this. I have a Londiste based replication setup that is working perfectly since last year. Now I noted the events_1_1 table grew too much (almost exactly the same size of the whole database), so I'm asking how can I clean up the events table without breaking the replication?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
