[Bucardo-general] Soemthing weird....

Michelle Sullivan michelle at sorbs.net
Fri Jan 17 16:20:26 UTC 2014


Greg Sabino Mullane wrote:
>>> 28 million(!) rows is why things are so slow. If it is the delete portion 
>>> that is slow, you can try adjusting the statement_chunk_size. 
>>> It may be easier to find the large DELETE in your logs and just 
>>> re-run that at different sizes.
>>>
>>>   
>>>       
>> Recommendations, up or down?
>>     
>
> Up. Too high and you hit an internal Postgres limit, however. Maybe 
> try doubling it to start with. It probably will not make much of a 
> difference, to be honest, so I wouldn't burn a lot of time on it.
>
>   
FYI, I fixed it manually by stopping bucardo running:

BEGIN;
DELETE FROM htimestamps WHERE hostsid = 2399;
DELETE FROM audit WHERE hostsid = 2398;
DELETE FROM hosts2evid WHERE hostsid = 2398;
DELETE FROM hosts2uris WHERE hostsid = 2398;
DELETE FROM hosts WHERE hostsid = 2398;
DELETE FROM htimestamps WHERE hostsid = 28;
DELETE FROM audit WHERE hostsid = 28;
DELETE FROM hosts2evid WHERE hostsid = 28;
DELETE FROM hosts2uris WHERE hostsid = 28;
DELETE FROM hosts WHERE hostsid = 28;
COMMIT;
BEGIN;
DELETE FROM bucardo.delta_public_hosts2evid WHERE hostsid IN (28, 2398);
DELETE FROM bucardo.delta_public_htimestamps WHERE hostsid IN (28, 2398);
DELETE FROM bucardo.delta_public_hosts WHERE hostsid IN (28, 2398);
DELETE FROM bucardo.delta_public_hosts2uris WHERE hostsid IN (28, 2398);
CREATE TEMP TABLE tmpaudit ON COMMIT DROP AS SELECT * FROM
bucardo.delta_public_audit WHERE EXISTS(SELECT 1 FROM audit WHERE
audit.pk = bucardo.delta_public_audit.pk);
TRUNCATE bucardo.delta_public_audit;
INSERT INTO bucardo.delta_public_audit SELECT * FROM tmpaudit;
COMMIT;

On all master hosts then restarting bucardo.

Regards,

Michelle

-- 
Michelle Sullivan
http://www.mhix.org/



More information about the Bucardo-general mailing list