pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql works too slow
6+ messages / 6 participants
[nested] [flat]
* Postgresql works too slow
@ 2005-04-18 04:50 Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>
0 siblings, 2 replies; 6+ messages in thread
From: Nurlan Mukhanov (AL/EKZ) @ 2005-04-18 04:50 UTC (permalink / raw)
To: pgsql-performance
Hello.
I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow.
Number of rows about 100 000 000,
RAM: 8192M
CPU: Ultra Sparc 3
Number of CPU: 4
OS: SunOS sun 5.8
RDBMS: PostgreSQL 8.0
==================== prstat info ====================
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
14231 postgres 41M 37M sleep 58 0 0:00.01 0.2% postgres/1
14136 postgres 41M 37M sleep 58 0 0:00.03 0.2% postgres/1
14211 postgres 41M 37M sleep 58 0 0:00.01 0.2% postgres/1
14270 postgres 41M 37M sleep 58 0 0:00.00 0.2% postgres/1
13767 postgres 41M 37M sleep 58 0 0:00.18 0.2% postgres/1
13684 postgres 41M 36M sleep 58 0 0:00.14 0.2% postgres/1
NPROC USERNAME SIZE RSS MEMORY TIME CPU
74 root 272M 191M 2.3% 0:26.29 24%
124 postgres 1520M 1306M 16% 0:03.05 5.0%
How to encrease postgresql speed? Why postgres took only 5.0% of CPU time?
Nurlan Mukhanov
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgresql works too slow
@ 2005-04-18 05:42 Steve Poe <spoe@sfnet.cc>
parent: Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>
1 sibling, 1 reply; 6+ messages in thread
From: Steve Poe @ 2005-04-18 05:42 UTC (permalink / raw)
To: Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>; +Cc: pgsql-performance
Nurlan,
Try enabliing your checkpoint_segments. In my example, our database
restore took 75mins. After enabling checkpoints_segments to 20, we cut
it down to less than 30 minutes. Is your pg_xlog on a seperate disc..or
at least a partition? This will help too. A checkpoints_segments of 20,
if memory serves correctly, will occupy around 800-900M of disc space in
pg_xlog.
Steve Poe
Nurlan Mukhanov (AL/EKZ) wrote:
>Hello.
>
>I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow.
>Number of rows about 100 000 000,
>RAM: 8192M
>CPU: Ultra Sparc 3
>Number of CPU: 4
>OS: SunOS sun 5.8
>RDBMS: PostgreSQL 8.0
>
>==================== prstat info ====================
>
> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
> 14231 postgres 41M 37M sleep 58 0 0:00.01 0.2% postgres/1
> 14136 postgres 41M 37M sleep 58 0 0:00.03 0.2% postgres/1
> 14211 postgres 41M 37M sleep 58 0 0:00.01 0.2% postgres/1
> 14270 postgres 41M 37M sleep 58 0 0:00.00 0.2% postgres/1
> 13767 postgres 41M 37M sleep 58 0 0:00.18 0.2% postgres/1
> 13684 postgres 41M 36M sleep 58 0 0:00.14 0.2% postgres/1
>
> NPROC USERNAME SIZE RSS MEMORY TIME CPU
> 74 root 272M 191M 2.3% 0:26.29 24%
> 124 postgres 1520M 1306M 16% 0:03.05 5.0%
>
>
>How to encrease postgresql speed? Why postgres took only 5.0% of CPU time?
>
>Nurlan Mukhanov
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgresql works too slow
@ 2005-04-18 06:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Steve Poe <spoe@sfnet.cc>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-04-18 06:11 UTC (permalink / raw)
To: Steve Poe <spoe@sfnet.cc>; +Cc: Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>; pgsql-performance
Steve Poe <spoe@sfnet.cc> writes:
> Try enabliing your checkpoint_segments. In my example, our database
> restore took 75mins. After enabling checkpoints_segments to 20, we cut
> it down to less than 30 minutes.
Increasing maintenance_work_mem might help too ... or several other
settings ... with no information about exactly *what* is slow, it's
hard to say.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgresql works too slow
@ 2005-04-18 06:40 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Christopher Kings-Lynne @ 2005-04-18 06:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Steve Poe <spoe@sfnet.cc>; Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>; pgsql-performance
>>Try enabliing your checkpoint_segments. In my example, our database
>>restore took 75mins. After enabling checkpoints_segments to 20, we cut
>>it down to less than 30 minutes.
>
>
> Increasing maintenance_work_mem might help too ... or several other
> settings ... with no information about exactly *what* is slow, it's
> hard to say.
Try turning fsync = false for the duration of your reload.
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgresql works too slow
@ 2005-04-19 14:46 Simon Riggs <simon@2ndquadrant.com>
parent: Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>
1 sibling, 1 reply; 6+ messages in thread
From: Simon Riggs @ 2005-04-19 14:46 UTC (permalink / raw)
To: Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>; +Cc: pgsql-performance
On Mon, 2005-04-18 at 08:50 +0400, Nurlan Mukhanov (AL/EKZ) wrote:
> I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow.
> Number of rows about 100 000 000,
> RAM: 8192M
> CPU: Ultra Sparc 3
> Number of CPU: 4
> OS: SunOS sun 5.8
> RDBMS: PostgreSQL 8.0
> How to encrease postgresql speed? Why postgres took only 5.0% of CPU time?
When you say restore...what are you actually doing?
An archive recovery?
A reload?
A file-level restore of database?
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgresql works too slow
@ 2005-04-19 15:06 Brad Nicholson <bnichols@ca.afilias.info>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Brad Nicholson @ 2005-04-19 15:06 UTC (permalink / raw)
To: pgsql-performance
Simon Riggs wrote:
>On Mon, 2005-04-18 at 08:50 +0400, Nurlan Mukhanov (AL/EKZ) wrote:
>
>
>>I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow.
>>Number of rows about 100 000 000,
>>RAM: 8192M
>>CPU: Ultra Sparc 3
>>Number of CPU: 4
>>OS: SunOS sun 5.8
>>RDBMS: PostgreSQL 8.0
>>
>>
>
>
>
>>How to encrease postgresql speed? Why postgres took only 5.0% of CPU time?
>>
>>
>
>When you say restore...what are you actually doing?
>An archive recovery?
>A reload?
>A file-level restore of database?
>
>
>
If you are doing a restore off a pg_dump, did you dump the data as
inserts? This takes a lot more time to restore.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-04-19 15:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-18 04:50 Postgresql works too slow Nurlan Mukhanov (AL/EKZ) <Nurlan.Mukhanov@ericsson.com>
2005-04-18 05:42 ` Steve Poe <spoe@sfnet.cc>
2005-04-18 06:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-18 06:40 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-04-19 14:46 ` Simon Riggs <simon@2ndquadrant.com>
2005-04-19 15:06 ` Brad Nicholson <bnichols@ca.afilias.info>
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