Re: Need setup help for Postgresql 8.1.3 on Solaris 10

Gellert, Andre wrote:
> Hello all,
>
> I need some hints how to setup Postgresql on a brand new testsystem.
>
>
> Due to heavy load on the current database servers, my boss decided to
> test a big server as a replacement for 5 other servers. ;-) The
> system is used in a extranet environment - over 50 percent of the
> load is produced from an online catalog. I doubt, that one system
> could handle the queries of 5 vehement used 3ghz-double-processor
> systems, so I would select another db scenario, but it worth to try.
>
> We have such a testsystem now, 4 x AMD Opteron (double core techn.)
> 885 2.6ghz , running with 32 GB Ram and fast 10.000rpm SAS discs,

How many disks? What RAID?

> build-in in a nice sun case ;-) Sounds nice, but it doesn't perform
> like a thought it should. Maybe this is a misconfiguration of
> PostgreSQL on Solaris 10, it's my first time on this platform, maybe
> it is a problem with the hardware.
>
> Reading , e.g. dumping a database, seems to run at expected
> performance, so I am going to test this system with read-querys from
> the live system on monday, to see how it performs with hundrets of
> parallel queries in a minute. But my concerns are here: Restoring a
> 800mb database dump, produced with pg_dump from this system, really
> takes long. On the "old" linux RHEL 3.2ghz systems the restore takes
> 10 minutes (while serving extranets additional), but on the new
> system this takes nearly 40 minutes. What happens while restoring ? :
> - Creating the tables is fast. - While loading the data into the DB
> i do not see significant load, a postgres process is running with 2-3
> % usage in the background, main of the time the "top" command claims
> that the postgres processes are "sleep"ing. System load is 0.1.

Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris
equivalent is).

> - While creating indexes the postgres daemon behaves like expected,
> nearly 12.5% usage, system load nearly 1 . Trying to store the
> sqldump on another partition than the harddisc did not help. Copying
> local on the harddisc is fast (serial read/write, okay).
>
> Why is the loading process so slow, what could block the write
> process ? Creating indizes is writing, too, so why is this "normal"
> fast ?
>
> I do not have configured autovacuum (would slow things down), I do
> not have moved databases or tables to different partitions (could be
> a speed improvement). Even with the current configuration, it should
> perform much better.
>
> Details to postgresql.conf , these are the values I changed (against
> defaults):

> maintenance_work_mem = 32768 # 32 MB for vacuumdb etc... work_mem =
> 102400 # min 64, size in KB #// 1024

I think your work_mem is too large (100MB) and maintenance_work_mem too
small, for a restore with 32GB of RAM anyway.

--
Richard Huxton
Archonet Ltd