vacuum freeze performance, wraparound issues
От | Natalie Wenz |
---|---|
Тема | vacuum freeze performance, wraparound issues |
Дата | |
Msg-id | 273F3E90-053B-4137-BE4D-3E7115A6A12E@ebureau.com обсуждение исходный текст |
Ответы |
Re: vacuum freeze performance, wraparound issues
|
Список | pgsql-admin |
Hi all, I have a few questions related to recovering from a near-miss with transactionid wraparound. I'm currently running a vacuum freeze in single user mode on our largest database (about 36 TB). It's been running for about10 days (since the database shut itself down to avoid xid wraparound). One cpu has been basically running at 100% thewhole time (except during short periods of write activity when it drops briefly to around 30%). Any idea how to guesshow long this might take? Is there anything I can adjust to speed the vacuum freeze up? It seems to be CPU limited--cananyone tell me what it would be doing that is so CPU intensive? Runs postgres 9.1.9 on FreeBSD 9.1 with ZFS, database was built --with-segsize=10, storage is a pool of 20 2-disk mirrors Maintenance_work_mem is set to 10GB; there is about 48 GB of memory in the machine. The size of our database may be unusual for postgres, but honestly, it has performed quite well for us over the years. Ourtrouble comes when every once in a while we get a perfect storm that causes the autovacuum to fall behind. We are generallyvery careful to try to keep this from happening, since all it takes is one query at the wrong time to take a largedatabase offline for days (or weeks). This is definitely our biggest cause of postgres outages, taking one of our databasesoffline about once a year, it seems. With the speed postgres is capable of, and the ever-falling prices of storagemaking larger, faster databases possible, has the possibility of changing the transaction id to a 64-bit (or even128-bit!) value been considered? Thanks! Natalie
В списке pgsql-admin по дате отправления: