[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