[Bucardo-general] This doesn't look right...

[Michelle Sullivan]

Michelle Sullivan wrote:
> Joshua Tolley wrote:
>   
>> On Wed, Jun 30, 2010 at 10:58:27PM +0200, Michelle Sullivan wrote:
>>   
>>     
>>> Greg Sabino Mullane wrote:
>>>     
>>>       
>>>>> Type:                 pushdelta
>>>>> Source herd/database: sorbs2slave / usmaster
>>>>> Target database:      backup_server
>>>>> Tables in sync:       58
>>>>> Last good:            10m 45s (time to run: 20m 18s)
>>>>> Last good time:       Jun 27, 2010 17:37:29  Target: backup_server
>>>>> Ins/Upd/Del:          568676 / 0 / 2262
>>>>>     
>>>>>         
>>>>>           
>>>> ...
>>>>   
>>>>       
>>>>         
>>>>> Type:                 swap
>>>>> Source herd/database: sorbs2master / aumaster
>>>>> Target database:      usmaster
>>>>> Tables in sync:       58
>>>>> Last good:            1h 15m 43s (time to run: 29m 17s)
>>>>> Last good time:       Jun 27, 2010 16:32:39  Target: usmaster
>>>>> Ins/Upd/Del:          33183 / 171 / 0
>>>>>     
>>>>>         
>>>>>           
>>>> ...
>>>>   
>>>>       
>>>>         
>>>>> Note the insert counts...  how can the delta be more than the swap for
>>>>> inserts... it's all supposed to be in sync....
>>>>>     
>>>>>         
>>>>>           
>>>> Hard to say from just this data. What is more disconcerting to me is 
>>>> the amount of time the syncs are taking - 20 minutes for half a 
>>>> million rows in a pushdelta sync indicates something wrong is 
>>>> going on. Do the logs indicate why things are taking so long?
>>>>   
>>>>       
>>>>         
>>> Nothing - though I do see extended query times some times.. I have
>>> auto-explain on so lets see:
>>>     
>>>       
>> Could it be that bucardo_track and/or bucardo_delta are getting bloated?
>> It's often not a bad idea to vacuum them pretty aggresively.
>>
>>   
>>     
> I have VACUUM ANALYSE'd the tables, but there wasn't any noticable
> difference.
>   

I'm guessing there is a problem:

vacuum analyze verbose "bucardo"."bucardo_track";
INFO:  vacuuming "bucardo.bucardo_track"
INFO:  index "bucardo_track_target" now contains 6593002 row versions in
61646 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.39s/0.22u sec elapsed 13.18 sec.
INFO:  "bucardo_track": found 0 removable, 4867 nonremovable row
versions in 31 out of 42035 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.39s/0.23u sec elapsed 13.18 sec.
INFO:  vacuuming "pg_toast.pg_toast_15073933"
INFO:  index "pg_toast_15073933_index" now contains 0 row versions in 1
pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_15073933": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "bucardo.bucardo_track"
INFO:  "bucardo_track": scanned 42035 of 42035 pages, containing 6593002
live rows and 0 dead rows; 60000 rows in sample, 6593002 estimated total
rows
VACUUM

Michelle