Hi Greg, On Thu, Nov 5, 2015 at 9:57 AM, Greg Sabino Mullane <[email protected]> wrote:
> On Wed, Nov 04, 2015 at 02:37:58PM -0500, Ioana Danes wrote: > > Ok the query is not stuck it is just very slow. It could be networking > > problem or client problem. The 2 servers are on the same msa though. > > > > I can reproduce the problem quite easily if I stop the sync for few > minutes > > so I could collect around 400,000 records to be synced (~ 200,000 for > each > > of my 2 tables). > > > > The query is : > > SELECT DISTINCT "testtableid" > > FROM bucardo.delta_abrazo_testtable d > > WHERE NOT EXISTS > > ( > > SELECT > > 1 > > FROM bucardo.track_abrazo_testtable t > > WHERE d.txntime = t.txntime > > AND (t.target = 'dbgroup > > sync_all1'::text) > > > > ) > > > > A regular sync operation at 780 clients takes 2-3 seconds > > Then I start the sync script and the first few syncs are slower than > normal > > (< 75 secs) because they have a lot to sync but then the third or fourth > > takes even longer ~ 270 seconds. > > Thanks for your work on hunting this down. We need to determine next if > it is the query itself that is slow or something else. The Bucardo logs > should help (even better, running with log_level=debug). You could also > set log_min_duration_statement = 100 in postgresql.conf and see if > the above query shows up. > I had log_min_duration_statement set at 2 seconds and the time for the statement was logged at something over 200 seconds, I don't remember exactly but I know for sure that was two hundred and so. I will enable the debugging and do more tests. In meantime I got over another issue where couple of records were not replicated for each of my big tables. I only use one sync for all the tables as with the latest improvements it is fast enough. I checked the code and I think I found the scenario where records could be missed to be replicated.It is caused by the assumption that txntime is unique or all the records with the same txntime are processed during the same sync. Let's say I have 2 dbs: db1 = MASTER, all the clients connect to that db db2 = STANDBY 1 *First transaction* *begins* and inserts a record into table1 with table_id = 1. This generates an entry in delta_table1 with (table_id, txntime) = (1, '2015-11-05 13:45:55.657-04') 2 *Second transaction* *begins* in the same time and inserts a record into table1 with table_id = 2. This generates an entry in delta_table1 with (table_id, txntime) = (2, '2015-11-05 13:45:55.657-04') Because the transactions started in the same time, now() returns the same value for both so txntime will be the same for the 2 records in the delta table 3 Then the* first transaction commits* 4* First sync starts* 5 *Second transaction commits* 6 *First sync finishes *and it replicated only the record with table_id = 1 because the second transaction was not committed when the sync started. The sync process also inserts all the txntime records that were processed into the track table: INSERT INTO bucardo.track_table1 (txntime,target) SELECT DISTINCT txntime, 'dbgroup sync_all1'::text FROM bucardo.delta_table1 d WHERE NOT EXISTS ( SELECT 1 FROM bucardo.track_table1 t WHERE d.txntime = t.txntime AND (t.target = 'dbgroup sync_all1'::text) ); *7 The second sync starts* and it should sync the second record with table_id = 2 but it does not pick it up because it only takes the records with txntime that were not prcessed (not in track table): SELECT DISTINCT "table_id" FROM bucardo.delta_table1 d WHERE NOT EXISTS ( SELECT 1 FROM bucardo.track_table1 t WHERE d.txntime = t.txntime AND (t.target = 'dbgroup sync_all1'::text) ); It takes me about 30 mins to reproduce and it only happens because of the volume of inserts I have *.* I will change the default value for txntime with something unique to perform some tests and let you know if it resolves my problem. > > -- > Greg Sabino Mullane [email protected] > End Point Corporation > PGP Key: 0x14964AC8 > Ioana
_______________________________________________ Bucardo-general mailing list [email protected] https://mail.endcrypt.com/mailman/listinfo/bucardo-general
