Re: [GENERAL] While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?
- autovacuum is not off in the pre-snapshot (production) database. It auto-runs every 2 weeks to avoid transaction ID wraparound. - I tried modifying pgdump to have a more relaxed transaction isolation (READ UNCOMMITTED) instead of the default (serializable) but this didn't help. thanks for the info. On 10 December 2013 21:13, Jeff Janes jeff.ja...@gmail.com wrote: On Tue, Dec 10, 2013 at 12:43 AM, Spiros Ioannou siv...@inaccess.comwrote: Hi Jeff, autovacuum is off on the DB running on the filesystem snapshot. Which probably makes sense on the snapshot, but is it also off on the pre-snapshot database? What hint bits do you suppose it is setting? It's running only one COPY command for days. Do you have any suggestions to make it more read only ? When a query sees a tuple that is still listed as part of an open transaction, it needs to figure out whether that transaction has now completed, and if so whether it committed or aborted. This can be quite a bit of work to do, so once complete it sets a hint bit locally to that tuple, so that the next visitor doesn't have to repeat the work. I don't believe that there is any way to turn this off, unless you want to run your own custom build of PostgreSQL. Cheers, Jeff
Re: [GENERAL] While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?
Hi Jeff, autovacuum is off on the DB running on the filesystem snapshot. What hint bits do you suppose it is setting? It's running only one COPY command for days. Do you have any suggestions to make it more read only ? On 2 December 2013 18:14, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Dec 2, 2013 at 6:22 AM, Spiros Ioannou siv...@inaccess.comwrote: We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec writes on the filesystem where postgresql data is (iostat), and specifically inside the base/ directory. Since the only query running on this DB is the COPY initiated from pg_dump which only reads data, who is writing data to postgres datafiles? It is probably setting hint bits on the tuples it is visiting. We need to find a way to make postgres stop writing because the source copy-on-write filesystem gets filled up before the dump is finished. Vacuum the system before taking the snapshot. Postgresql version is 8.4, vacuum is not running (autovacuum = off), OS is debian 6. Since autovacuum is off, what are you doing about vacuuming? Cheers, Jeff
Re: [GENERAL] While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?
On Tue, Dec 10, 2013 at 12:43 AM, Spiros Ioannou siv...@inaccess.comwrote: Hi Jeff, autovacuum is off on the DB running on the filesystem snapshot. Which probably makes sense on the snapshot, but is it also off on the pre-snapshot database? What hint bits do you suppose it is setting? It's running only one COPY command for days. Do you have any suggestions to make it more read only ? When a query sees a tuple that is still listed as part of an open transaction, it needs to figure out whether that transaction has now completed, and if so whether it committed or aborted. This can be quite a bit of work to do, so once complete it sets a hint bit locally to that tuple, so that the next visitor doesn't have to repeat the work. I don't believe that there is any way to turn this off, unless you want to run your own custom build of PostgreSQL. Cheers, Jeff
Re: [GENERAL] While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?
On Mon, Dec 2, 2013 at 6:22 AM, Spiros Ioannou siv...@inaccess.com wrote: We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec writes on the filesystem where postgresql data is (iostat), and specifically inside the base/ directory. Since the only query running on this DB is the COPY initiated from pg_dump which only reads data, who is writing data to postgres datafiles? It is probably setting hint bits on the tuples it is visiting. We need to find a way to make postgres stop writing because the source copy-on-write filesystem gets filled up before the dump is finished. Vacuum the system before taking the snapshot. Postgresql version is 8.4, vacuum is not running (autovacuum = off), OS is debian 6. Since autovacuum is off, what are you doing about vacuuming? Cheers, Jeff
