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, 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.
- 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):
lc_messages = 'de.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'C'
listen_addresses = '*'
maintenance_work_mem = 32768 # 32 MB for vacuumdb etc...
max_connections = 2000
max_fsm_pages = 300000
max_fsm_relations = 5000
shared_buffers = 450000 # min 16 or max_connections*2, 8KB each #// default 1000
stats_block_level = off
stats_command_string = off
stats_reset_on_server_start = off
stats_row_level = off
stats_start_collector = on
temp_buffers = 100000
work_mem = 102400 # min 64, size in KB #// 1024
Details to Semaphores and shared memory:
prctl -n project.max-sem-ids -i task 330
task: 330
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-sem-ids
privileged 512 - deny -
prctl -n project.max-shm-memory -i task 330
task: 330
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 12.0GB - deny -
Maybe somebody has an idea,
thanx in advance,
Andre Gellert
