Thread: Postgresql works too slow
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
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 > > >
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
>>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
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
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.