Re: Performance

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