pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
delete -> copy in -> WAL problem..?
2+ messages / 2 participants
[nested] [flat]

* delete -> copy in -> WAL problem..?
@ 2002-03-20 22:44 Mike Biamonte <mike@dbeat.com>
  2002-03-21 04:42 ` Re: delete -> copy in -> WAL problem..? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Mike Biamonte @ 2002-03-20 22:44 UTC (permalink / raw)
  To: pgsql-sql


Could use some insight on this one...

We have TWO machines running postgres: one is for
"real-time" data (registrations, order taking, sessions)
and the other is for "non real time" data (content
management, reporting, etc.).

The databases are identical - all 60 tables.  The application
knows which one to use when.   Nightly, we run a cron that \copy's out
the 10 "real-time" tables.  The data is moved to the 2nd machine
and uploaded like this:

     delete from [table];
     copy [table] from [table].txt
     .. etc.

This has worked well for us - the reporting database
can crunch away on all kinds of reports all day without
affecting the site.

Recently, I've noticed that, during this "copy in" process
(and the subsequent vacuum), the WAL files grow TREMENDOUSLY
in number - a thousand of them.  What's causing this?  Should
I vacuum each table separately after the delete/copy in?
Do I need to force "checkpoints"?

Thanks in advance.







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: delete -> copy in -> WAL problem..?
  2002-03-20 22:44 delete -> copy in -> WAL problem..? Mike Biamonte <mike@dbeat.com>
@ 2002-03-21 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-03-21 04:42 UTC (permalink / raw)
  To: Mike Biamonte <mike@dbeat.com>; +Cc: pgsql-sql

"Mike Biamonte" <mike@dbeat.com> writes:
> Recently, I've noticed that, during this "copy in" process
> (and the subsequent vacuum), the WAL files grow TREMENDOUSLY
> in number - a thousand of them.  What's causing this?

Update.  7.1.3 and later don't do this.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-03-21 04:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-20 22:44 delete -> copy in -> WAL problem..? Mike Biamonte <mike@dbeat.com>
2002-03-21 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox