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/ _______________________________________________ Bucardo-general mailing list [email protected] https://mail.endcrypt.com/mailman/listinfo/bucardo-general
