Re: Performance - Mailing list pgsql-general
| From | Werdin Jens |
|---|---|
| Subject | Re: Performance |
| Date | |
| Msg-id | DB51EBFA5812D611B6200002A528BC2704948CF4@khes002a.khe1.siemens.de Whole thread Raw |
| In response to | Performance (Werdin Jens <jens.werdin@siemens.com>) |
| List | pgsql-general |
Werdin Jens wrote: > Hello, > > Ich have a big performance problem. > I'm running postgres 7.4.2 on Suse Linux 9.0 on a dual Xeon 3.0 GHz with 3 > Gbyte Ram. > In postgres.conf I'm using the defaults. That's the place to start. See the guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html For your hardware, the default configuration settings are far too small. Oh, and you should upgrade to the latest 7.4 as soon as convenient. # Thanks for that link. It helped me very in understanding the configuration file. I changed the shared buffers to 16384 and the shmmax and shmall to 137822208. # > Filesystem is ext3 with writeback > journaling > > I have 3 tables with ca 10 million entries with a gist index on GIS data and > 5 tables with 10 million entries with an index on (timestamp,double,double). > There are 10 tables with 1 million entries and index on int. and some > smaller tables. > > With 1 Gbyte Ram all went fine. Than I added a new table and it startet to > swap. I added 2 Gbyte but the Problem is still there. > The kswapd and kjournald are running nearly permanently. If the system is swapping that's not likely to be due to PostgreSQL, especially on the default configuration settings. > The first time I do a query it takes very long. But the second time it goes > a lot faster. That's because the data is cached in RAM the second time. > Is postgres only using a certain amount of Ram for the indexes? But why my > Ram is full then? > Am I too short of Ram? Is the filesystem too slow? What is "top" showing for memory usage? What does vmstat show for activity when you are having problems? -- Richard Huxton Archonet Ltd # Thank you for your help so far and here comes the output of top and vmstat: Tasks: 55 total, 2 running, 53 sleeping, 0 stopped, 0 zombie Cpu(s): 0.7% user, 9.6% system, 0.0% nice, 89.7% idle Mem: 3104688k total, 3025320k used, 79368k free, 21084k buffers Swap: 2104504k total, 4128k used, 2100376k free, 2788828k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ SWAP COMMAND 301 postgres 16 0 132m 132m 131m D 22.9 4.4 0:12.03 184 postmaster 11 root 15 0 0 0 0 R 6.0 0.0 6:00.68 0 kswapd 13969 root 16 0 616 572 460 S 0.3 0.0 3:45.64 44 top 313 root 15 0 940 940 744 R 0.3 0.0 0:00.08 0 top 1 root 15 0 80 64 60 S 0.0 0.0 0:04.19 16 init 2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 0 migration_CPU0 3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 0 migration_CPU1 4 root RT 0 0 0 0 S 0.0 0.0 0:00.00 0 migration_CPU2 5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 0 migration_CPU3 6 root 15 0 0 0 0 S 0.0 0.0 0:00.88 0 keventd 7 root 34 19 0 0 0 S 0.0 0.0 0:05.32 0 ksoftirqd_CPU0 8 root 34 19 0 0 0 S 0.0 0.0 0:01.18 0 ksoftirqd_CPU1 9 root 34 19 0 0 0 S 0.0 0.0 0:03.23 0 ksoftirqd_CPU2 10 root 34 19 0 0 0 S 0.0 0.0 0:01.50 0 ksoftirqd_CPU3 12 root 15 0 0 0 0 S 0.0 0.0 0:10.03 0 bdflush 13 root 15 0 0 0 0 S 0.0 0.0 0:11.52 0 kupdated 14 root 15 0 0 0 0 S 0.0 0.0 0:02.19 0 kinoded 15 root 25 0 0 0 0 S 0.0 0.0 0:00.00 0 mdrecoveryd 21 root 16 0 0 0 0 S 0.0 0.0 0:00.00 0 scsi_eh_0 24 root 15 0 0 0 0 S 0.0 0.0 0:19.95 0 kjournald 124 root 15 0 0 0 0 S 0.0 0.0 0:01.57 0 kjournald 487 root 15 0 264 236 224 S 0.0 0.0 28:43.54 28 syslogd 490 root 15 0 952 4 4 S 0.0 0.0 0:00.06 948 klogd 535 root 19 0 0 0 0 S 0.0 0.0 0:00.00 0 khubd 645 root 20 0 60 4 4 S 0.0 0.0 0:00.00 56 resmgrd 668 bin 16 0 72 4 4 S 0.0 0.0 0:00.03 68 portmap 713 root 20 0 68 4 4 S 0.0 0.0 0:00.03 64 acpid 722 root 15 0 428 252 200 S 0.0 0.0 0:00.03 176 sshd 761 ntp 15 0 2196 2196 1872 S 0.0 0.1 0:00.12 0 ntpd 927 root 15 0 384 180 140 S 0.0 0.0 0:00.24 204 master 983 root 15 0 420 396 340 S 0.0 0.0 0:00.12 24 nscd 984 root 15 0 420 396 340 S 0.0 0.0 0:00.01 24 nscd 985 root 15 0 420 396 340 S 0.0 0.0 0:00.09 24 nscd 986 root 15 0 420 396 340 S 0.0 0.0 0:00.06 24 nscd 987 root 15 0 420 396 340 S 0.0 0.0 0:00.05 24 nscd 988 root 15 0 420 396 340 S 0.0 0.0 0:00.07 24 nscd 989 root 15 0 420 396 340 S 0.0 0.0 0:00.05 24 nscd 990 root 15 0 156 128 92 S 0.0 0.0 0:00.10 28 cron 1070 root 19 0 68 4 4 S 0.0 0.0 0:00.12 64 mingetty 1071 root 18 0 68 4 4 S 0.0 0.0 0:00.04 64 mingetty 1072 root 18 0 68 4 4 S 0.0 0.0 0:00.05 64 mingetty 1073 root 15 0 304 4 4 S 0.0 0.0 0:00.07 300 login 1074 root 18 0 68 4 4 S 0.0 0.0 0:00.05 64 mingetty 32070 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 0 lvm-mpd 32170 root 15 0 0 0 0 S 0.0 0.0 4:51.93 0 kjournald 17632 root 19 0 68 4 4 S 0.0 0.0 0:00.09 64 mingetty 32745 root 16 0 372 4 4 S 0.0 0.0 0:00.07 368 bash 8777 postfix 15 0 380 188 124 S 0.0 0.0 0:00.20 192 qmgr 10996 postgres 15 0 596 280 228 S 0.0 0.0 0:03.67 316 postmaster procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 0 4140 80896 21020 2792540 0 0 1 1 0 0 1 0 99 0 procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 1 4180 79664 21072 2794700 0 0 1 1 0 0 1 0 99 0
pgsql-general by date: