pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arshavir Grigorian <ag@m-cam.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres on RAID5
Date: Fri, 11 Mar 2005 17:37:10 -0500
Message-ID: <5897.1110580630@sss.pgh.pa.us> (raw)
In-Reply-To: <42321BB7.9030808@m-cam.com>
References: <423209E1.6090200@m-cam.com>
	<5669.1110578855@sss.pgh.pa.us>
	<42321BB7.9030808@m-cam.com>

Arshavir Grigorian <ag@m-cam.com> writes:
> Tom Lane wrote:
>> How are you measuring that write rate (seeing that pg_restore doesn't
>> provide any such info)?

> Well, if the restore is going on for X number of hours and you have Y 
> records loaded, it's not hard to ballpark.

Yeah, but how do you know that you have Y records loaded?

What I'm trying to get at is what the restore is actually spending its
time on.  It seems unlikely that a COPY per se would run that slowly;
far more likely that the expense is involved with index construction
or foreign key verification.  You could possibly determine what's what
by watching the backend process with "ps" to see what statement type
it's executing most of the time.

BTW, is this a full database restore (schema + data), or are you trying
to load data into pre-existing tables?  The latter is generally a whole
lot slower because both index updates and foreign key checks have to be
done retail instead of wholesale.  There are various ways of working
around that but you have to be aware of what you're doing.

Also, if it is indexing that's eating the time, boosting the sort_mem
setting for the server would help a lot.

			regards, tom lane



view thread (26+ messages)  latest in thread

Message-ID: <5897.1110580630@sss.pgh.pa.us>
Permalink:  ../5897.1110580630@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5897.1110580630@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ag@m-cam.com
  Subject: Re: Postgres on RAID5
  In-Reply-To: <5897.1110580630@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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