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.

> 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