Обсуждение: Postgresql works too slow

Список
Период
Сортировка

Postgresql works too slow

От
"Nurlan Mukhanov (AL/EKZ)"
Дата:
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

Re: Postgresql works too slow

От
Steve Poe
Дата:
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 
>
>
>


Re: Postgresql works too slow

От
Tom Lane
Дата:
Steve Poe <> 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

Re: Postgresql works too slow

От
Christopher Kings-Lynne
Дата:
>>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

Re: Postgresql works too slow

От
Simon Riggs
Дата:
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


Re: Postgresql works too slow

От
Brad Nicholson
Дата:
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.