Thread: WAL filling up disk
Hi all, There was a question on the postgresql-br list the other day that I couldn't answer and was curious about it. This guy has is evaluating PG 7.1(.1?) and has a 1 million records table. He then does a "update tablefoo set somefield = 123" and WAL eventually causes his 4 Gb disk to fill up and never complete the transaction. According to him, there are over 250 16Mb files under pg_xlog (I understand 16 Mb is the default segment file size for WAL). Can anyone give me more details as to why this happens and the possible configurations that should be changed? Thanks in advance. -Roberto -- +----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer * * * <- Tribbles . . . <- Tribbles after a haircut
Roberto, in the postgres.conf file is a parameter: wal_files = 64 # range 0-64 I'm not sure if you can set this to > 64, I have never tried. Maybe that's the guy's problem. Set it to something smaller, like 16 or 32. Also, he could qualify the update (update xxx set yyy = zzz where ...) and do the million records in steps of 100'000. Best regards, Chris At 09:54 -0700 02/05/2002, Roberto Mello wrote: >Hi all, > >There was a question on the postgresql-br list the other day that I >couldn't answer and was curious about it. > >This guy has is evaluating PG 7.1(.1?) and has a 1 million records >table. He then does a "update tablefoo set somefield = 123" and WAL >eventually causes his 4 Gb disk to fill up and never complete the >transaction. > >According to him, there are over 250 16Mb files under pg_xlog (I >understand 16 Mb is the default segment file size for WAL). > >Can anyone give me more details as to why this happens and the possible >configurations that should be changed? > >Thanks in advance. > >-Roberto > >-- >+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ > http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer >* * * <- Tribbles . . . <- Tribbles after a haircut > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Roberto Mello <rmello@cc.usu.edu> writes:
> Can anyone give me more details as to why this happens and the possible
> configurations that should be changed?
Update to 7.1.3 (or better, 7.2).
regards, tom lane
Tom ... what are you telling me here - is 7.2 Final out? (too nice to be true ;-). Best regards, Chris At 12:28 -0500 02/05/2002, Tom Lane wrote: [snip] > >Update to 7.1.3 (or better, 7.2). [snip] -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Chris Ruprecht <chrup999@yahoo.com> writes:
> Tom ... what are you telling me here - is 7.2 Final out? (too nice to
> be true ;-).
RTFannouncelist.
regards, tom lane
Tom, > > Tom ... what are you telling me here - is 7.2 Final out? (too nice > to > > be true ;-). > > RTFannouncelist. Uh, Tom, the last annoucement was ver. 7.2b5 on Janary 15th. Is thiswhat you are referring to, or is there another announcementthatshould have gone out and didn't? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
>> RTFannouncelist.
> Uh, Tom, the last annoucement was ver. 7.2b5 on Janary 15th. Is this
> what you are referring to, or is there another announcement that
> should have gone out and didn't?
The release announcement went out this AM. I can't prove it right now
because I can't get into archives.postgresql.org --- which is the same
machine as ftp.postgresql.org, so I suppose it's slashdotted into
oblivion :-(. And it's also mail.postgresql.org, so perhaps not
everyone's copies have been delivered yet. But that would strongly
suggest that quite a few people did see the announcement, eh? Sorry
if you guys are at the tail end of the delivery list...
regards, tom lane
PS: my archived copy looks like:
Received: from server1.pgsql.org (www.postgresql.org [64.49.215.9])by sss.pgh.pa.us (8.11.4/8.11.4) with SMTP id
g15GC1f25200for<tgl@sss.pgh.pa.us>; Tue, 5 Feb 2002 11:12:01 -0500 (EST)
Received: (qmail 81101 invoked by alias); 5 Feb 2002 16:08:36 -0000
Received: from unknown (HELO postgresql.org) (64.49.215.8) by www.postgresql.org with SMTP; 5 Feb 2002 16:08:36 -0000
Received: from earth.hub.org (earth.hub.org [64.49.215.11])by postgresql.org (8.11.3/8.11.4) with ESMTP id
g15G1Sl78800;Tue,5 Feb 2002 11:01:28 -0500 (EST)(envelope-from scrappy@postgresql.org)
Received: from localhost (scrappy@localhost)by earth.hub.org (8.11.3/8.11.6) with ESMTP id g15G1OF20685;Tue, 5 Feb 2002
12:01:24-0400 (AST)(envelope-from scrappy@postgresql.org)
X-Authentication-Warning: earth.hub.org: scrappy owned process doing -bs
Date: Tue, 5 Feb 2002 12:01:24 -0400 (AST)
From: "Marc G. Fournier" <scrappy@postgresql.org>
X-X-Sender: scrappy@earth.hub.org
To: pgsql-announce@postgresql.org
cc: pgsql-hackers@postgresql.org, <pgsql-general@postgresql.org>
Subject: [GENERAL] PostgreSQL v7.2 Final Release
Message-ID: <20020205112231.A44048-100000@earth.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org
For Immediate Release February 5th, 2002
After almost a full year of development since PostgreSQL v7.1 was
released, the PostgreSQL Global Development Group is proud to announce the
availability of our latest development milestone ... PostgreSQL v7.2,
another step forward for the project.
A full list of changes to v7.2 can be found in the HISTORY file,
included with the release, as well as under all ftp mirrors as:
/pub/README.v7_2
Highlights of this release are as follows:
VACUUM Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new
"VACUUMFULL" command does old-style vacuum by locking the table and shrinking the on-disk copy of the
table.
Transactions There is no longer a problem with installations that exceed four billion
transactions.
OID's OID's are now optional. Users can now create tables without OID's for cases where OID usage
isexcessive.
Optimizer The system now computes histogram column statistics during "ANALYZE", allowing much
betteroptimizer choices.
Security A new MD5 encryption option allows more secure storage and transfer of passwords. A new
Unix-domainsocket authentication option is available on Linux and BSD systems.
Statistics Administrators can use the new table access statistics module to get fine-grained
informationabout table and index usage.
Internationalization Program and library messages can now be displayed in several languages.
.. with many many more bug fixes, enhancements and performance
related changes ...
Source for this release is available on all mirrors under:
/pub/source/v7.2
As always, any bugs with this release should be reported to
pgsql-bugs@postgresql.org ... and, as with all point releases, this
release requires a complete dump and reload from previous releases, due to
internal structure changes ...
Marc G. Fournier
Co-ordinator
PostgreSQL Global Development Group
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> Chris Ruprecht <chrup999@yahoo.com> writes: >> Tom ... what are you telling me here - is 7.2 Final out? (too nice to >> be true ;-). TL> RTFannouncelist. It never made over to the newsgroup side of the announce list. I found out by people discussing it on the other groups. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/