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
journalingI 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