Re: [PERFORM] Postgresql works too slow
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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Postgresql works too slow
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Postgresql works too slow
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Postgresql works too slow
Steve Poe <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Postgresql works too slow
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 580 0:00.01 0.2% postgres/1 14136 postgres 41M 37M sleep 580 0:00.03 0.2% postgres/1 14211 postgres 41M 37M sleep 580 0:00.01 0.2% postgres/1 14270 postgres 41M 37M sleep 580 0:00.00 0.2% postgres/1 13767 postgres 41M 37M sleep 580 0:00.18 0.2% postgres/1 13684 postgres 41M 36M sleep 580 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 1306M16% 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 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 580 0:00.01 0.2% postgres/1 14136 postgres 41M 37M sleep 580 0:00.03 0.2% postgres/1 14211 postgres 41M 37M sleep 580 0:00.01 0.2% postgres/1 14270 postgres 41M 37M sleep 580 0:00.00 0.2% postgres/1 13767 postgres 41M 37M sleep 580 0:00.18 0.2% postgres/1 13684 postgres 41M 36M sleep 580 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 1306M16% 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 [EMAIL PROTECTED]