Tom Lane wrote:
> I'd ask you the same question I asked Thomas: do you continue to get those log messages
> during subsequent checkpoints?

No, I don't. The error did not reappear during ~2h of continuous
inserts since my report, didn't reappear after a forced checkpoint
(i.e., via psql), and did not reappear on a recent stop/start cycle.

There was a period when my cron-driven vacuuming was broken and, in
principle, I might have been susceptible to wraparound. However, I
don't see how we could have had 1B transactions in that period.

One other tidbit: a colleague inadvertently updated ~10M records. After
this, I started getting errors like:
number of page slots needed (2952496) exceeds max_fsm_pages (500000)
I restored from a backup, but still have:
'number of page slots needed (183248) exceeds max_fsm_pages (50000)'
(I reduced max_fsm_pages after the restore.)

I'm not sure whether the vacuum and fsm info is relevant.


-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Reece --

The "number of slots needed exceeds" messages are telling you that the current FSM (Free Space Map) does not have enough space allocated to track all of the old tuples that are to be reused. I suspect that having such a situation would effect the wraparound issue, since you'd have "dead wood" which hasn't been recycled.

You need to edit the postgresql.conf file and increase the max_fsm_pages and max_fsm_relations parameters and then restart postgres (I think you have to actually stop and restart, as opposed to a reload, but I could be wrong). You may end up needing to adjust the total amount of RAM allocated to Shared Memory to allow for as large an FSM as you'll need. That requires a system reboot.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Reece Hart
Sent: Fri 7/14/2006 12:30 PM
To: pgsql-general
Cc:
Subject: Re: [GENERAL] apparent wraparound

Tom Lane wrote:
> I'd ask you the same question I asked Thomas: do you continue to get those log messages
> during subsequent checkpoints?

No, I don't. The error did not reappear during ~2h of continuous
inserts since my report, didn't reappear after a forced checkpoint
(i.e., via psql), and did not reappear on a recent stop/start cycle.

There was a period when my cron-driven vacuuming was broken and, in
principle, I might have been susceptible to wraparound. However, I
don't see how we could have had 1B transactions in that period.

One other tidbit: a colleague inadvertently updated ~10M records. After
this, I started getting errors like:
number of page slots needed (2952496) exceeds max_fsm_pages (500000)
I restored from a backup, but still have:
'number of page slots needed (183248) exceeds max_fsm_pages (50000)'
(I reduced max_fsm_pages after the restore.)

I'm not sure whether the vacuum and fsm info is relevant.


-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

!DSPAM:44b7f15495741414113241!





---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Gregory S. Williamson wrote:
> You need to edit the postgresql.conf file and increase the max_fsm_pages and
> max_fsm_relations parameters and then restart postgres (I think you
> have to actually stop and restart, as opposed to a reload, but I could be
> wrong). You may end up needing to adjust the total amount of RAM
> allocated to Shared Memory to allow for as large an FSM as you'll
> need. That requires a system reboot.
Actually, it doesn't require a system reboot. On debian/ubunut, and
AFAIK on redhat too, you just need to edit /etc/sysctl.conf, and
then call sysctl -p to update the in-kernel settings.

greetings, Florian Pflug

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Florian G. Pflug wrote:
> Gregory S. Williamson wrote:
>> You need to edit the postgresql.conf file and increase the
>> max_fsm_pages and
> > max_fsm_relations parameters and then restart postgres (I think you
> > have to actually stop and restart, as opposed to a reload, but I
> could be
> > wrong). You may end up needing to adjust the total amount of RAM
> > allocated to Shared Memory to allow for as large an FSM as you'll
> > need. That requires a system reboot.
> Actually, it doesn't require a system reboot. On debian/ubunut, and
> AFAIK on redhat too, you just need to edit /etc/sysctl.conf, and
> then call sysctl -p to update the in-kernel settings.

Most modern day unix does not need to reboot...

FreeBSD (although there are a couple it still needs to reboot for IIRC)
Linux
MacOSX

None of these need to reboot to set those parameters.

Sincerely,

Joshua D. Drake



>
> greetings, Florian Pflug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Greg, Florian, Joshua, Tom-

On Fri, 2006-07-14 at 17:02 -0700, Gregory S. Williamson wrote:
> You need to edit the postgresql.conf file and increase the
> max_fsm_pages and max_fsm_relations parameters and then restart
> postgres

I did this and vacuumed. I didn't need to up shmmax. The problem's
disappeared (and, fortunately, my data are still intact).

Thanks everyone for your help.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend