How to improve postgres performace

From: "Rodrigo Moreno" <rodrigo(dot)miguel(at)terra(dot)com(dot)br>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: How to improve postgres performace
Date: 2005-04-18 14:36:01
Message-ID: 20050418143603.EE9F613001B@cuenca.terra.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-performance

Hi all,

Some months ago i post a similar problem here i it was solved by running
vaccumdb time by time.
So, when i started using the postgres, i never been used the vacuumdb, and
after 2 months i started using once a week, after few weeks, i tried once a
day and now twice a day.

At this weekend i have started to use pg_autovacuum with default settings.

I really worried about that, because it's no enough anymore, and users claim
about performace. But running the vacuumdb full, everthing starts to run
better again, so i think the problem is not related to a specific query.

What I can do to check what I have change to get more performance ?
Could I use vacuum verbose to check what is going on ? So, how ?

Most all the time, even user querying the server the machine is 96%-100%
idle. The discs are SCSI, FreeBSD 5.3, the size of database is 1.1Gb, max 30
connections and 10 concurrent conections. My server have 512Mb Ram and 256Mb
has changed to SHMAX. There is max 1000 inserted/excluded/Updated row by
day.

These are my kernel params:
--------------------------
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120

Postgresql.conf non-default settings
------------------------------------
tcpip_socket = true
max_connections = 30

shared_buffers = 1024
sort_mem = 2048
vacuum_mem = 16384

wal_buffers = 16
checkpoint_segments = 5

effective_cache_size = 16384
random_page_cost = 2

stats_start_collector = true
stats_row_level = true

I follow the most of all discussions in this group and tried myself change
the parameters, but now, I don't know more what to do to get better
performance.

Thanks a Lot
Rodrigo Moreno

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message S S 2005-04-18 15:11:47 PostgreSQL Guide for Oracle Users?
Previous Message brew 2005-04-18 12:53:10 Re: Debian stable, was Re: Storing Large Objects

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Stark 2005-04-18 14:59:05 Re: How to improve db performance with $7K?
Previous Message Stephan Szabo 2005-04-18 14:30:58 Re: FW: speed of querry?