Обсуждение: Need setup help for Postgresql 8.1.3 on Solaris 10
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
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
Hello Richard, > > 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? 4 Disks, RAID1. > Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris > equivalent is). Hard to say, due to account restrictions I cannot control more than this vmstat output gives me: This is during loading Data, 98% idle: -bash-3.00$ vmstat 5 kthr memory page disk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31865080 27723492 248 43 138 118 79 0 5 1 1 1 0 6873 4235 1481 2 1 98 0 0 0 31675384 27518448 603 14 0 0 0 0 0 0 0 0 0 15808 1695 1243 1 1 98 0 0 0 31675384 27518436 609 0 0 0 0 0 0 0 0 0 0 16027 1703 1266 1 1 98 0 0 0 31675372 27518408 602 0 0 0 0 0 0 0 0 0 0 15808 1682 1231 1 1 98 0 0 0 31675356 27513660 1280 0 0 0 0 0 0 0 0 0 0 44234 1908 861 1 1 98 0 0 0 31675356 27511444 369 0 0 0 0 0 0 0 0 0 0 13969 1270 952 1 1 99 0 0 0 31675356 27510328 343 0 0 0 0 0 0 4 4 4 0 13053 1192 908 1 1 99 0 0 0 31675344 27509688 426 1 0 0 0 0 0 0 0 0 0 14353 1371 1028 1 1 98 0 0 0 31675332 27513672 552 0 0 0 0 0 0 0 0 0 0 17503 1675 1243 1 1 98 0 0 0 31675332 27518468 570 0 0 0 0 0 0 0 0 0 0 17269 1737 1276 1 1 98 0 0 0 31675320 27519528 561 0 0 0 0 0 0 0 0 0 0 15942 1709 1276 1 1 98 0 0 0 31675320 27519440 598 169 0 0 0 0 0 0 0 0 0 15553 1738 1248 1 1 98 0 0 0 31674428 27527404 3311 222 0 120 120 0 0 0 0 0 0 44244 3384 1259 1 2 97 0 0 0 31675320 27539800 1665 0 0 0 0 0 0 0 0 0 0 37718 2151 1022 1 1 98 0 0 0 31674644 27520952 408 148 0 80 80 0 0 0 0 0 0 13916 1459 1066 1 1 99 0 0 0 31674196 27512960 403 146 0 40 40 0 0 0 0 0 0 13958 1425 1031 1 1 99 0 0 0 31675320 27510932 437 222 0 80 80 0 0 0 0 0 0 17203 1550 1097 1 1 98 0 0 0 31675320 27513352 402 75 0 0 0 0 0 0 0 0 0 16961 1425 1039 1 1 99 kthr memory page disk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31674644 27516248 354 148 0 40 40 0 0 0 0 0 0 15408 1283 953 1 1 99 0 0 0 31674644 27519036 345 296 0 80 80 0 0 0 0 0 0 14854 1297 915 1 1 99 0 0 0 31675320 27519880 357 0 0 0 0 0 0 0 0 0 0 14000 1254 957 1 1 99 0 0 0 31675320 27518796 335 0 0 0 0 0 0 0 0 0 0 28471 1575 886 1 1 98 0 0 0 31675288 27515356 252 2 0 6 6 0 0 0 0 0 0 15763 1650 1245 1 1 98 0 0 0 31675288 27517308 265 0 0 0 0 0 0 0 0 0 0 16911 1741 1285 1 1 98 0 0 0 31675288 27519112 281 0 0 0 0 0 0 0 0 0 0 28401 1858 1167 1 1 98 0 0 0 31674396 27518676 391 217 0 0 0 0 0 0 0 0 0 14281 1334 950 1 1 99 0 0 0 31675236 27519480 386 3 0 80 80 0 0 0 0 0 0 14389 1305 971 1 1 99 0 0 0 31675160 27517528 522 0 0 0 0 0 0 0 0 0 0 14958 1559 1142 1 1 99 0 0 0 31674968 27515712 522 0 0 0 0 0 0 0 0 0 0 15553 1568 1159 1 1 98 0 0 0 31674924 27518104 533 0 0 0 0 0 0 0 0 0 0 16687 1595 1173 1 1 99 0 0 0 31674924 27517816 529 0 0 0 0 0 0 0 0 0 0 15607 1581 1174 1 1 99 0 0 0 31674520 27516436 570 0 0 0 0 0 0 0 0 0 0 15876 1612 1172 1 1 99 0 0 0 31674520 27516280 588 0 0 0 0 0 0 0 0 0 0 15998 1612 1192 1 1 98 0 0 0 31674412 27515228 1312 0 0 0 0 0 0 0 0 0 0 46397 2165 1031 1 1 98 0 0 0 31673996 27510360 318 0 0 0 0 0 0 0 0 0 0 12950 1082 849 0 1 99 0 0 0 31673996 27508960 408 203 0 40 40 0 0 0 0 0 0 14094 1318 883 0 1 99 0 0 0 31673996 27507568 443 0 0 0 0 0 0 0 0 0 0 14330 1373 1052 1 1 98 and so on. During the whole import of data it keeps mainly 99% idle. This is during "working" on indexes creation: -bash-3.00$ vmstat 5 kthr memory page disk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31851400 27708172 252 44 129 111 74 0 5 1 0 0 0 7169 4006 1419 2 1 98 0 0 0 31598656 27425288 50 14 0 0 0 0 0 0 0 0 0 1608 72 156 13 0 87 0 0 0 31598656 27425224 6973 0 0 0 0 0 0 0 0 0 0 147690 3516 161 12 4 84 0 0 0 31598656 27424696 13194 0 0 3 3 0 0 0 0 0 0 279048 6620 147 12 7 81 0 0 0 31598656 27424116 14528 0 0 0 0 0 0 0 0 0 0 307360 7285 153 12 8 80 0 0 0 31598656 27423852 13503 0 0 0 0 0 0 0 0 0 0 285909 6767 149 12 7 81 0 0 0 31598656 27424584 3258 0 0 326 326 0 0 0 0 0 0 69522 1682 197 12 2 85 0 0 0 31598652 27425244 0 0 0 0 0 0 0 0 0 0 0 480 37 184 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 420 33 156 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 418 32 152 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 433 34 166 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 423 35 156 13 0 87 0 0 0 31598616 27425208 0 0 0 0 0 0 0 0 0 0 0 413 33 150 13 0 87 0 0 0 31598616 27425208 0 0 0 0 0 0 0 0 0 0 0 424 33 157 12 0 87 0 0 0 31598616 27425208 0 0 0 0 0 0 0 0 0 0 0 430 40 161 13 0 87 0 0 0 31598616 27425216 0 0 0 0 0 0 0 0 0 0 0 415 31 146 13 0 87 0 0 0 31598616 27425216 0 0 0 0 0 0 0 0 0 0 0 416 31 153 13 0 87 0 0 0 31598616 27425216 0 0 0 0 0 0 0 0 0 0 0 430 36 162 13 0 87 Looks fine, doesn't it? > I think your work_mem is too large (100MB) and maintenance_work_mem too > small, for a restore with 32GB of RAM anyway. I decreased the import time from >40 minutes to 32 minutes with changing the parameters to: shared_buffers = 450000 # kept same temp_buffers = 100000 # kept same work_mem = 32768 # now 32mb , before: 102400 = 100mb maintenance_work_mem = 2048000 # now 2.048mb, before: 32768 = 32mb Should I encrease these parameters? Thank you for helping, Andre
Gellert, Andre wrote: > Hello Richard, > >>> 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? > > 4 Disks, RAID1. > >> Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris >> equivalent is). > > Hard to say, due to account restrictions I cannot control more than this vmstat output gives me: > > This is during loading Data, 98% idle: > > -bash-3.00$ vmstat 5 > kthr memory page disk faults cpu > r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id > 0 0 0 31865080 27723492 248 43 138 118 79 0 5 1 1 1 0 6873 4235 1481 2 1 98 > 0 0 0 31675384 27518448 603 14 0 0 0 0 0 0 0 0 0 15808 1695 1243 1 1 98 > 0 0 0 31675384 27518436 609 0 0 0 0 0 0 0 0 0 0 16027 1703 1266 1 1 98 > 0 0 0 31675372 27518408 602 0 0 0 0 0 0 0 0 0 0 15808 1682 1231 1 1 98 > 0 0 0 31675356 27513660 1280 0 0 0 0 0 0 0 0 0 0 44234 1908 861 1 1 98 More interestingly your disks appear to be doing nothing. Which can't be right. They're not all zeroes though, so I'm not sure why you're seeing no disk activity. Nothing's blocking on disk I/O though... That also seems like a large number of interrupts for an idle machine. > 0 0 0 31673996 27510360 318 0 0 0 0 0 0 0 0 0 0 12950 1082 849 0 1 99 > 0 0 0 31673996 27508960 408 203 0 40 40 0 0 0 0 0 0 14094 1318 883 0 1 99 > 0 0 0 31673996 27507568 443 0 0 0 0 0 0 0 0 0 0 14330 1373 1052 1 1 98 > and so on. During the whole import of data it keeps mainly 99% idle. > > This is during "working" on indexes creation: > > -bash-3.00$ vmstat 5 > kthr memory page disk faults cpu > r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id > 0 0 0 31851400 27708172 252 44 129 111 74 0 5 1 0 0 0 7169 4006 1419 2 1 98 > 0 0 0 31598656 27425288 50 14 0 0 0 0 0 0 0 0 0 1608 72 156 13 0 87 > 0 0 0 31598656 27425224 6973 0 0 0 0 0 0 0 0 0 0 147690 3516 161 12 4 84 > 0 0 0 31598656 27424696 13194 0 0 3 3 0 0 0 0 0 0 279048 6620 147 12 7 81 > 0 0 0 31598656 27424116 14528 0 0 0 0 0 0 0 0 0 0 307360 7285 153 12 8 80 > 0 0 0 31598656 27423852 13503 0 0 0 0 0 0 0 0 0 0 285909 6767 149 12 7 81 Now that's a *huge* number of interrupts, unless I'm mis-reading this. I'm afraid I don't know enough about Solaris to say for sure, but there's something strange going on here. > Looks fine, doesn't it? > >> I think your work_mem is too large (100MB) and maintenance_work_mem too >> small, for a restore with 32GB of RAM anyway. > > I decreased the import time from >40 minutes to 32 minutes with changing the parameters to: > > shared_buffers = 450000 # kept same > temp_buffers = 100000 # kept same > > work_mem = 32768 # now 32mb , before: 102400 = 100mb > maintenance_work_mem = 2048000 # now 2.048mb, before: 32768 = 32mb > > Should I encrease these parameters? Nothing that should cause these problems. Anything unusual about your installation? Any oddities from ./configure or similar? -- Richard Huxton Archonet Ltd
Hello Richard, > > I decreased the import time from >40 minutes to 32 minutes with changing the parameters to: > > > > shared_buffers = 450000 # kept same > > temp_buffers = 100000 # kept same > > > > work_mem = 32768 # now 32mb , before: 102400 = 100mb > > maintenance_work_mem = 2048000 # now 2.048mb, before: 32768 = 32mb > > > > Should I encrease these parameters? > > Nothing that should cause these problems. > > Anything unusual about your installation? Any oddities from ./configure > or similar? I've got no news regarding my installation problems, but I had to reduce the maintenance_work_mem to 256mb, as vacuumdb hadthis error: vacuumdb: vacuuming of database "adp_trtr" failed: ERROR: invalid memory alloc request size 2097151998 ( 2.GB was a bit to large, I guess ;-) ) But this shouldn't cause my problems, as you say. The installation is a standard solaris postgresql package - my admins say, I've got no priviledges to check this, furtheron , of course, I have no chance to recompile it, but at the moment it seems to be good to think about compiling thelatest PG from sources ( http://www.postgresql.org/docs/8.2/interactive/install-procedure.html ). My system admins contacted the company from which we lent the system , to get more information, so I am waiting for this. Maybe the other alternative, RHEL + Postgres8.2.1 (off. RPMs for 64bit) is a good way to go. Thanks so long, Andre