niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » PostgreSQL
apparent wraparound
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
Author Message
Tom Lane
*nix forums Guru


Joined: 24 Mar 2005
Posts: 2070

PostPosted: Thu Jul 20, 2006 12:49 am    Post subject: Re: apparent wraparound Reply with quote
I wrote:
Quote:
Reece Hart <hart.reece@gene.com> writes:
After a system crash, postgresql 8.1.4 restarted but reported that I
have an apparent wraparound:
2006-07-13 14:03:40 PDT [10092] LOG: could not truncate directory "pg_multixact/offsets": apparent wraparound
2006-07-13 14:03:40 PDT [10092] LOG: could not truncate directory "pg_multixact/members": apparent wraparound

[ looks at code... ] I wonder if this is happening because
shared->latest_page_number hasn't been set up yet when we do the
end-of-recovery checkpoint.

Indeed, I was able to reproduce this after advancing the next-mxact
counters far enough. The consequences seem only cosmetic (ie, a scary
log message during recovery), but still worth fixing. I committed a
patch in HEAD and 8.1.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Back to top
Reece Hart
*nix forums beginner


Joined: 13 Jul 2006
Posts: 4

PostPosted: Tue Jul 18, 2006 10:28 pm    Post subject: Re: apparent wraparound Reply with quote
Greg, Florian, Joshua, Tom-

On Fri, 2006-07-14 at 17:02 -0700, Gregory S. Williamson wrote:
Quote:
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
Back to top
Joshua D. Drake
*nix forums Guru


Joined: 09 Mar 2005
Posts: 341

PostPosted: Sat Jul 15, 2006 5:14 pm    Post subject: Re: apparent wraparound Reply with quote
Florian G. Pflug wrote:
Quote:
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



Quote:

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
Back to top
Florian Pflug
*nix forums Guru Wannabe


Joined: 03 Mar 2005
Posts: 100

PostPosted: Sat Jul 15, 2006 4:46 pm    Post subject: Re: apparent wraparound Reply with quote
Gregory S. Williamson wrote:
Quote:
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
Back to top
Gregory S. Williamson
*nix forums beginner


Joined: 14 Jul 2005
Posts: 19

PostPosted: Sat Jul 15, 2006 12:02 am    Post subject: Re: apparent wraparound Reply with quote
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:
Quote:
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
Back to top
Reece Hart
*nix forums beginner


Joined: 13 Jul 2006
Posts: 4

PostPosted: Fri Jul 14, 2006 7:30 pm    Post subject: Re: apparent wraparound Reply with quote
Tom Lane wrote:
Quote:
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
Back to top
Tom Lane
*nix forums Guru


Joined: 24 Mar 2005
Posts: 2070

PostPosted: Fri Jul 14, 2006 3:48 pm    Post subject: Re: apparent wraparound Reply with quote
Reece Hart <hart.reece@gene.com> writes:
Quote:
After a system crash, postgresql 8.1.4 restarted but reported that I
have an apparent wraparound:

...
2006-07-13 14:03:40 PDT [10092] LOG: next MultiXactId: 5475264; next MultiXactOffset: 13765525
...
2006-07-13 14:03:40 PDT [10092] LOG: could not truncate directory "pg_multixact/offsets": apparent wraparound
2006-07-13 14:03:40 PDT [10092] LOG: could not truncate directory "pg_multixact/members": apparent wraparound

There are two files left in pg_multixact:
-rw------- 1 postgres postgres 16K 2006-07-13 14:13 pg_multixact/members/00D2
-rw------- 1 postgres postgres 144K 2006-07-13 14:13 pg_multixact/offsets/0053

That's odd. Those files correspond to the next MultiXactId and
MultiXactOffset, so there shouldn't have been any such complaint.
[ looks at code... ] I wonder if this is happening because
shared->latest_page_number hasn't been set up yet when we do the
end-of-recovery checkpoint.

Quote:
In
http://www.mail-archive.com/pgsql-general@postgresql.org/msg76635.html
Tom Lane implies that I can probably ignore these messages.

No, I was saying that the "invalid server process ID -1" was harmless.
The "apparent wraparound" is a distinct issue, and I'd ask you the same
question I asked Thomas: do you continue to get those log messages
during subsequent checkpoints?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Back to top
Reece Hart
*nix forums beginner


Joined: 13 Jul 2006
Posts: 4

PostPosted: Thu Jul 13, 2006 11:43 pm    Post subject: apparent wraparound Reply with quote
After a system crash, postgresql 8.1.4 restarted but reported that I
have an apparent wraparound:

2006-07-13 14:03:40 PDT [10092] LOG: database system was interrupted at 2006-07-13 13:22:19 PDT
2006-07-13 14:03:40 PDT [10092] LOG: checkpoint record is at 1DD/26283E18
2006-07-13 14:03:40 PDT [10092] LOG: redo record is at 1DD/26283E18; undo record is at 0/0; shutdown FALSE
2006-07-13 14:03:40 PDT [10092] LOG: next transaction ID: 169855318; next OID: 787933
2006-07-13 14:03:40 PDT [10092] LOG: next MultiXactId: 5475264; next MultiXactOffset: 13765525
2006-07-13 14:03:40 PDT [10092] LOG: database system was not properly shut down; automatic recovery in progress
2006-07-13 14:03:40 PDT [10092] LOG: record with zero length at 1DD/26283E68
2006-07-13 14:03:40 PDT [10092] LOG: redo is not required
2006-07-13 14:03:40 PDT [10092] LOG: could not truncate directory "pg_multixact/offsets": apparent wraparound
2006-07-13 14:03:40 PDT [10092] LOG: could not truncate directory "pg_multixact/members": apparent wraparound
2006-07-13 14:03:41 PDT [10093] [unknown]%[unknown] LOG: connection received: host=[local]
2006-07-13 14:03:41 PDT [10093] postgres%postgres FATAL: the database system is starting up
2006-07-13 14:03:41 PDT [10092] LOG: database system is ready
2006-07-13 14:03:41 PDT [10092] LOG: transaction ID wrap limit is 1243594092, limited by database "csb-dev"

This is from:
PostgreSQL 8.1.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.0.2 20050901 (prerelease) (SUSE Linux)

I'm using autovacuum and it ran around 13:15 on database csb-dev. I see
no errors in the logs and therefore assume that the vacuum was
successful.

There are two files left in pg_multixact:
-rw------- 1 postgres postgres 16K 2006-07-13 14:13 pg_multixact/members/00D2
-rw------- 1 postgres postgres 144K 2006-07-13 14:13 pg_multixact/offsets/0053

The system crash occurred during scsi rescanning that was initiated by
an admin. The machine has been extremely stable otherwise and I have no
reason to suspect hardware flakiness.

In
http://www.mail-archive.com/pgsql-general@postgresql.org/msg76635.html
Tom Lane implies that I can probably ignore these messages.

So, the 64K questions: Can I really ignore this? Is there anything I
can do to ascertain whether it's a false alarm?

Thanks,
Reece

--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650-225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://harts.net/reece/
South San Francisco, CA 94080-4990 reece@harts.net, GPG:0x25EC91A0



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
The time now is Fri Nov 21, 2008 12:45 pm | All times are GMT
navigation Forum index » Databases » PostgreSQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts autovacuum, xid wraparound, pg_database column values shakah PostgreSQL 1 Tue Jun 06, 2006 6:24 pm
No new posts pg_dump after transaction id wraparound failure Michael L. Boscia PostgreSQL 9 Mon May 15, 2006 6:44 pm
No new posts apparent irq conflict with pccard and videocard paul1brian@gmail.com FreeBSD 2 Sat Apr 15, 2006 9:14 pm
No new posts apparent loss of sys tables!! - help Noel Faux PostgreSQL 5 Thu Mar 16, 2006 1:33 am
No new posts Apparent eval() leak for python 2.3.5 John Marshall python 1 Fri Feb 17, 2006 6:21 pm

W810i | Debt | Mortgages | Personal Finance | Indoor Flag Pole Sets
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.3095s ][ Queries: 20 (0.1795s) ][ GZIP on - Debug on ]