Re: [HACKERS] WAL segments pile up during standalone mode
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? I really don't care for the idea of standalone mode doing *anything* the user didn't explicitly tell it to. In its role as a disaster recovery tool, that's just a recipe for shooting yourself in the foot. Perhaps this problem would be adequately addressed by documentation, ie suggest that when vacuuming very large tables in standalone mode, you should issue CHECKPOINT after each one. I documented that there is no automatic background processing (checkpoints) in single-user mode. I did not mention the idea of running checkpoints manually. Applied patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index d332cba..bd4ae33 100644 *** a/doc/src/sgml/ref/postgres-ref.sgml --- b/doc/src/sgml/ref/postgres-ref.sgml *** PostgreSQL documentation *** 745,751 para Note that the single-user mode server does not provide sophisticated ! line-editing features (no command history, for example). /para /refsect1 --- 745,754 para Note that the single-user mode server does not provide sophisticated ! line-editing features (no command history, for example). ! Single-User mode also does not do any background processing, like ! automatic checkpoints. ! /para /refsect1 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011: On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
Alvaro Herrera alvhe...@commandprompt.com writes: I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? I really don't care for the idea of standalone mode doing *anything* the user didn't explicitly tell it to. In its role as a disaster recovery tool, that's just a recipe for shooting yourself in the foot. Perhaps this problem would be adequately addressed by documentation, ie suggest that when vacuuming very large tables in standalone mode, you should issue CHECKPOINT after each one. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
On Thu, Mar 3, 2011 at 10:16 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011: On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? I doubt it's necessary to force a checkpoint after each command - I assume that if you want one, you can just execute the CHECKPOINT command explicitly. The multi-table VACUUM case could be handled similarly - VACUUM each table, then checkpoint, and so on. It'd probably be more worthwhile to pursue the approach of allowing the system to be brought up in multi-user mode, but allow only super-users to log in and don't allow them to do anything except VACUUM until some semblance of sanity is achieved. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WAL segments pile up during standalone mode
I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. I can reproduce in HEAD easily by doing a large bulk insertion in standalone mode. If I stop the server, start in normal mode and shutdown (or checkpoint), the extra segments go away as expected. This was reported in pgsql-es-ayuda yesterday. -- Álvaro Herrera alvhe...@alvh.no-ip.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
