Long running transactions are a problem. Do you have them?  What's  
your maintenance regime (vacuum and so on?)

Andrew Sullivan
ajs at shinkuro.com

On 2009-06-26, at 12:08, "Burak Bender" <Burak.Bender at gmx.net> wrote:

> Hi there,
>
> I am using Slony 1.2.15 on gentoo with postgres 8.3.4
> Context is a java web application running on tomcat.
>
> I encountered some weird behaviour when doing multiple select  
> statements with a plain jdbc connection triggered by a script on the  
> master db.
>
> It seems that the select statements are slowing down the slony  
> replication in a major way, so that slony commits wont be processed
> until the select statement finishes.
> The connection pool gets exhausted by queries that do not return and  
> stay at the COMMIT statement. Selecting pg_stat_activity shows up to  
> 42 "hanging" COMMIT statements!
>
> In pg_logs I can see lots of waiting queries:
>
> duration: 5557.956 ms  statement: select "_km_cluster".forwardConfirm 
> (2, 3,'1814465', '2009-06-26 15:55:02.034346');
> 15:55:09   km_prod LOG:  duration: 5219.536 ms  statement: notify  
> "_km_cluster_Event"; insert into "_km_cluster".sl_event
>    (ev_origin, ev_seqno, ev_timestamp,      ev_minxid, ev_maxxid,  
> ev_xip, ev_type     ) values ('2','1814466', '2009-06-26  
> 15:55:03.98378', '75848912', '75848912', '', 'SYNC');
>    insert into "_km_cluster".sl_confirm ^I(con_origin,con_received,  
> con_seqno, con_timestamp)    values (2, 1, '1814466', now()); commit  
> transaction;
> 15:55:10 comm_prod LOG:  duration: 17750.271 ms  statement: listen  
> "_comm_cluster_Event";
> 15:55:10 comm_prod LOG:  duration: 17694.024 ms  statement: listen  
> "_comm_cluster_Event";
> 15:55:11 comm_prod LOG:  duration: 16337.644 ms  statement: notify  
> "_comm_cluster_Event"; insert
> 15:55:11 km_prod LOG:  duration: 13147.354 ms  execute S_2: COMMIT  
> into "_comm_cluster".sl_event     (ev_origin, ev_seqno,  
> ev_timestamp,      ev_minxid, ev_maxxid, ev_xip, ev_type     ) values
>    ('3', '1213250', '2009-06-26 15:54:53.709398', '50637491', '50637491 
> ', '', 'SYNC'); insert into "_comm_cluster".sl_confirm
>    ^I(con_origin, con_received, con_seqno, con_timestamp)    values  
> (3, 1, '1213250', now()); commit transaction;
> 15:55:12   comm_prod LOG:  duration: 24362.984 ms  statement: notify  
> "_comm_cluster_Event"; insert into "_comm_cluster".sl_event      
> (ev_origin, ev_seqno, ev_timestamp,      ev_minxid, ev_maxxid,  
> ev_xip, ev_type     ) values
>    ('2', '1815023', '2009-06-26 15:54:47.1518', '75848890',  
> '75848890', '', 'SYNC'); insert into "_comm_cluster".sl_confirm ^I 
> (con_origin, con_received, con_seqno, con_timestamp)    values (2,  
> 1, '1815023', now()); commit transaction;
> 15:55:12   comm_prod LOG:  duration: 5728.775 ms  statement: select  
> "_comm_cluster".createEvent('_comm_cluster', 'SYNC', NULL);
> 15:55:13 km_prod LOG:  duration: 6187.793 ms  execute S_2: COMMIT
> 15:55:13 km_prod LOG:  duration: 12125.475 ms  execute S_2: COMMIT
> 15:55:15 km_prod LOG:  duration: 7538.928 ms  execute S_2: COMMIT
> 15:55:15 km_prod LOG:  duration: 7532.898 ms  execute S_2: COMMIT
> 15:55:15 km_prod LOG:  duration: 7531.024 ms  execute S_2: COMMIT
> 15:55:15 km_prod LOG:  duration: 7763.779 ms  execute S_2: COMMIT
> ... lots of waiting commits
>
>
> Any idea or hint what may causes the problem?
> I am confused because "Select statements" do not bother Slony ,  
> don't they?
>
> Best regards
> Burak
> -- 
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general