-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Phoenix Kiula schrieb: > In my conf_pg, the autovacuum is on, so the DB should be (or I hope is > being) regularly vacuumed. > > These are my settings: > > > work_mem = 20MB > temp_buffers = 4096 > authentication_timeout = 10s > ssl = off > checkpoint_warning = 3600 > random_page_cost = 1 > autovacuum = on > autovacuum_vacuum_cost_delay = 20 > vacuum_cost_delay = 20 > autovacuum_naptime = 10 > stats_start_collector = on > stats_row_level = on > autovacuum_vacuum_threshold = 75 > autovacuum_analyze_threshold = 25 > autovacuum_analyze_scale_factor = 0.02 > autovacuum_vacuum_scale_factor = 0.01 > Hi , just a quick thought. What is your maintenance_work_mem parameter set to? I think with that lot Updates and Inserts this should not be too low ... Cheers Andy > > The autovacuum was clearly not enough, so we also have a crontab that > vacuums the tables every hour. This is PG 8.2.9. > > These cron jobs are taking over 35 minutes for a vacuum! What's the > use of a vacuum if it takes that long, and the DB performance is > tragic in the meantime? > > I'd truly appreciate some thoughts from people with experience of > vacuum management of highly available online databases. About 10-20 > million accesses for this one. Most are SELECTs. We have about 500,000 > INSERTs and about 800,000 UPDATEs. Just 11 tables, of which only one > is like 10 million rows. Two are close to 500,000 rows, rest are > really small. It is this 10 million row thing that's the worry. > > Thanks! > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJiEIgVa7znmSP9AwRAvUoAKCCuRycQVPCiEBkCxLvxrnXIa2ZqwCfZSI1 uooHCg8rIW6Zdt7pJU7YZMM= =vO+P -----END PGP SIGNATURE----- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
