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
