Performance Question

Hi guys,

I have been browsing around and reading up on PostgreSQL performance to try
and tweak our system at the office, as its performance is not that great.

Many people say that PG is a great DB, and I know that our problems are
purely a setup issue.

After a complete server restart, the system is ok, not fast, but workable,
the problem are increased by the fact that the PG slows down, for example,
we run a full vacuum every night, and after a restart, it takes about an
hour and half, which increases to about 3 hours in two weeks. At that stage
everything is broken bad, and we are forced to do a restart again.

I have tried to follow as much of the documents and HOWTO’s on the web, but
still have some issues.

Here is some info (did a restart last night, so this is best performance
atm)

System:

Dual XEON 2.4GHz

3Gb RAM

Dedicated to PG

Type of apps:

Mostly JDBC queries running via Tomcat.

----------------------------------------------------------------------------
---------------------------------------------------

------ Shared Memory Attach/Detach/Change Times --------

shmid owner attached detached changed

131072 postgres Mar 14 09:02:43 Mar 14 09:02:23 Mar 12
15:14:49

------ Shared Memory Operation/Change Times --------

shmid owner last-op last-changed

786432 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 2005

819201 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 2005

851970 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 2005

884739 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 2005

917508 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 2005

950277 postgres Mon Mar 14 09:02:44 2005 Mon Mar 14 09:02:43 2005

983046 postgres Mon Mar 14 09:02:43 2005 Mon Mar 14 09:02:23 2005

1015815 postgres Mon Mar 14 09:02:44 2005 Mon Mar 14 09:02:43 2005

------ Message Queues Send/Recv/Change Times --------

msqid owner send recv change

------ Shared Memory Status --------

segments allocated 1

pages allocated 266324

pages resident 257206

pages swapped 8619

Swap performance: 0 attempts 0 successes

----------------------------------------------------------------------------
---------------------------------------------------

09:03:48 up 2 days, 10:12, 3 users, load average: 0.15, 0.36, 0.31

64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped

CPU0 states: 2.2% user 1.4% system 0.0% nice 0.0% iowait 95.4%
idle

CPU1 states: 1.3% user 0.3% system 0.0% nice 0.0% iowait 97.4%
idle

CPU2 states: 2.3% user 0.1% system 0.0% nice 0.0% iowait 97.1%
idle

CPU3 states: 0.0% user 0.3% system 0.0% nice 0.0% iowait 99.2%
idle

Mem: 2063932k av, 2017520k used, 46412k free, 0k shrd, 79388k
buff

1434408k actv, 232k in_d, 46268k in_c

Swap: 2040244k av, 63676k used, 1976568k free 1678480k
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND

15985 postgres 15 0 88796 86M 86192 S 3.4 4.2 0:00 0 postmaster

16108 postgres 20 0 21252 20M 19236 S 1.6 1.0 0:00 1 postmaster

16094 postgres 18 0 12188 11M 10292 S 0.4 0.5 0:00 3 postmaster

24846 postgres 15 0 432 228 120 S 0.0 0.0 0:04 2 postmaster

24851 postgres 15 0 1320 1044 24 S 0.0 0.0 0:06 2 postmaster

24852 postgres 15 0 628 400 128 S 0.0 0.0 0:18 2 postmaster

11207 postgres 20 0 11536 10M 9700 S 0.0 0.5 0:00 2 postmaster

15113 postgres 20 0 20908 20M 18796 S 0.0 0.9 0:00 2 postmaster

15114 postgres 20 0 12732 11M 10792 S 0.0 0.5 0:00 2 postmaster

15606 postgres 20 0 12672 11M 10764 S 0.0 0.5 0:00 3 postmaster

15917 postgres 15 0 17172 16M 15220 S 0.0 0.8 0:00 1 postmaster

----------------------------------------------------------------------------
---------------------------------------------------

Postgresql.conf extract

max_connections = 120

shared_buffers = 131072

sort_mem = 16384

vacuum_mem = 8192

effective_cache_size = 65536

----------------------------------------------------------------------------
---------------------------------------------------

Any ideas will be greatly appreciated.

Kind regards

Werner vd Merwe

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11