Обсуждение: vacuum_freeze_table_age for 9.3.2
The docs say vacuum, but the param is vacuum_freeze_table_age, so do I need to "vacuum freeze" all the tables, or is vacuum enough? Also, will "set vacuum_freeze_table_age = 0; vacuum freeze;" work, or do I need to modify the postgresql.conf and reload? -Andy
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Il 05/12/2013 17:16, Andy Colson ha scritto: Setting vacuum_freeze_table_age to 0 forces VACUUM to always scan all pages, effectively ignoring the visibility map. In this way a scan of the whole table is done, ensuring all old XIDs are replaced by FrozenXID. vacuum_freeze_table_age is a parameter with context 'user', meaning that you can set it during a session and run a "vacuum freeze" with the modified setting. Giuseppe. - -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it -----BEGIN PGP SIGNATURE----- Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSoLfqAAoJELtGpyN/y3jedG4P/0yaz4RpHoKT6mm53rT1KrRC 5+UEKffji5P8x1cPWbkUyK8jvSbODByWHMqhAmMtDo5+Mdc9XW/MXrfJ7aJINVU7 rVI/VUaHRwJmwxQSKR0FM01Gqgjgx8W0mAcmIpk4nZpus+OH/8Ib6ImO58iebSaL 2E/ii7itZovtp816n2Pffk2HrftDfBCAQ1/XxQksTW8uGXRRx3qpF/EJm6lqsv4d xNxlOFjCqDM0j3poPnJaeprFyeWgzCiUCyg75NoW8na0VI7QunpwEI99QP7XcFmi drvx2uipzoajBSUOnPvtGSWRnJAoP7jTg4nooPDxn0DvMrf3YoqQ7xK4fKV0psQX xoXTF1IAiI6EW0Tr2uGoO4akWjiO0Yo4grLJ8W9oYca29Ai+qwtOA46qyyMmZrtm c9p+B1mK0DWwkgLpyuJJYYAvTyiCS+h9iLrooiIQdz/7bC0GlwjMKZo8rGvHFy6M RPCHi2w7Uw4emj0E0/ZpeeHhA2Aa8wJKUS/uoR8I+MTgf8VeVjOY3hOWuUHS/PNa S1XsrzfYp5z3uQ4dzMbVbdvgG6q7DqS8qSb+JocUg1HFRzdWFcgEE9M/0mLJOlDL 0VAwhpYPG/UBX0gXQkGoFavBorYA2vTuedKaKpCQuxtFxeaaKfbYPPfU/Ib1/6fP AwQUxlTBde8aC0ATVi5+ =WGwy -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Il 05/12/2013 17:16, Andy Colson ha scritto: > The docs say vacuum, but the param is vacuum_freeze_table_age, so > do I need to "vacuum freeze" all the tables, or is vacuum enough? > > Also, will "set vacuum_freeze_table_age = 0; vacuum freeze;" work, > or do I need to modify the postgresql.conf and reload? Setting vacuum_freeze_table_age to 0 forces VACUUM to always scan all pages, effectively ignoring the visibility map. In this way a scan of the whole table is done, ensuring all old XIDs are replaced by FrozenXID. vacuum_freeze_table_age is a parameter with context 'user', meaning that you can set it during a session and run a "vacuum freeze" with the modified setting. Giuseppe. - -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it -----BEGIN PGP SIGNATURE----- Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSoLfwAAoJELtGpyN/y3je278QAJomNDxax15x2MCxT4hoXyFA pJX4z7H7QjHXILcZ1ha5ajRvWrEtJEy3VvAZ2VBwAmk/VhAfcNAkU1tWA61K6UYD 47Npu1NhOzjDI9j9AfdA9cr/p3b/HRyx2qxjda6jBfhhLiDSZbQZXocC+FZicwtn qn3QQPpx6Ty9rt18OkliP9TKKjwm3tDGz1goOa58pbeH3TAjCipdLR+6Fn9WGM9b XLvNTMsHB3157VS7A6CjRRKvLj8Dxj/JoWvQC0q8ROZ728IpaEh+EqjeMmKoF8G2 /7xFyHFLINgUyAqVpv1scua5pO9RBYTsy4NCdnxLeGuSJt/ucfm+EkazNAMHLAj8 Vyq/lT9XUN2SB7OALa79TTcB+gFYrD+6yswBExnutTUZUP7gFrB6X7PFnmKxVPVO 2ovBzTXGcLRGp9lRoaJ/gwWRkyv1oKW39vYT6UBUjFM2NwGizX0z6BMd2vRC93r/ wq8Ll8KCMOe8vE/6qhnFMQ90pfJm+Zv4yT4CtdN5eP0b2Os6vVZVu9EMHgGtii0l ribqPQqmczySssdOSpTnlnq00du8moG7M5KCktRcuVsbDUpBS8yAbb2b4ccLxW/S VmSn2TuRWim/4oOfi1fsZJMB6ihmIv+e6qXjNeyZNZQI19dDxfnvfey5Wt3g0SnH mwqOBqn/DMia79z0048K =pp71 -----END PGP SIGNATURE-----
On 12/5/2013 11:29 AM, Giuseppe Broccolo wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA256 > > Il 05/12/2013 17:16, Andy Colson ha scritto: >> The docs say vacuum, but the param is vacuum_freeze_table_age, so >> do I need to "vacuum freeze" all the tables, or is vacuum enough? >> >> Also, will "set vacuum_freeze_table_age = 0; vacuum freeze;" work, >> or do I need to modify the postgresql.conf and reload? > > Setting vacuum_freeze_table_age to 0 forces VACUUM to always scan all > pages, effectively ignoring the visibility map. In this way a scan of > the whole table is done, ensuring all old XIDs are replaced by FrozenXID. > > vacuum_freeze_table_age is a parameter with context 'user', meaning > that you can set it during a session and run a "vacuum freeze" with > the modified setting. > > Giuseppe. I gather, then, that vacuum alone is enough. It'll scan the entire table and if it finds something wonky it'll freeze it. A "vacuum freeze" would be over kill and might freeze a bunch of stuff that isnt broken. (It wouldn't hurt to freeze it, but I have enough IO at the moment and just wanna fix whats broken). For now I'm only doing vacuum's, so hopefully that's enough. Thanks for the help. -Andy