VACUUM FULL takes long time to complete
От | Eric B. Ridge |
---|---|
Тема | VACUUM FULL takes long time to complete |
Дата | |
Msg-id | 61F6217B-7055-482F-A714-EE82E415FC4A@tcdi.com обсуждение исходный текст |
Ответы |
Re: VACUUM FULL takes long time to complete
Re: VACUUM FULL takes long time to complete |
Список | pgsql-general |
Hi! We've got a Postgres 8.1.5 installation with a 60GBish database: =# select version(); version ------------------------------------------------------------------------ --------------- PostgreSQL 8.1.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 (Gentoo 4.1.1) (1 row) =# select pg_size_pretty(pg_database_size('xxx')); pg_size_pretty ---------------- 59 GB (please don't freak over the word Gentoo! This is Postgres compiled, by us, using the sources from postgresql.org, not some patched-up Gentoo version) Anyways, we run a VACUUM FULL ANALYZE VERBOSE every night, and it's now taking 3+ hours to complete. We also have autovacuum turned on with default settings. This 3+ hour vacuum time is cutting into our production hours. :( Note that while we're vacuuming Postgres is running in a single user mode and not listening. There's no other connections to the database. The hardware itself is a dual-core Intel 2gHz w/ 4G ram and adequate space in a hardware Raid 5 configuration. I realize Raid 5 isn't ideal, but in the general use cases of our database it doesn't noticeably impact performance. How can we begin to cut down the vacuum time? My first thought is simply change the schedule to run weekly (or biweekly) since we're also running autovacuum. Are there any other Postgres configuration changes that might help to improve vacuum performance? The settings we've explicitly set are: max_connections = 256 shared_buffers = 40000 temp_buffers = 5000 work_mem = 32768 maintenance_work_mem = 65535 max_fsm_pages = 120000 fsync = on wal_buffers = 16 effective_cache_size = 5000 log_connections = on log_duration = off log_line_prefix = '%m [xid=%x] [%p]: ' stats_start_collector = on stats_command_string = on stats_block_level = on stats_row_level = on autovacuum = on Thanks in advance for any suggestions! eric
В списке pgsql-general по дате отправления: