pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to improve postgres performace
3+ messages / 3 participants
[nested] [flat]

* How to improve postgres performace
@ 2005-04-18 14:36 Rodrigo Moreno <rodrigo.miguel@terra.com.br>
  2005-04-18 15:58 ` Re: How to improve postgres performace Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-20 00:35 ` Re: How to improve postgres performace Jim C. Nasby <decibel@decibel.org>
  0 siblings, 2 replies; 3+ messages in thread

From: Rodrigo Moreno @ 2005-04-18 14:36 UTC (permalink / raw)
  To: 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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to improve postgres performace
  2005-04-18 14:36 How to improve postgres performace Rodrigo Moreno <rodrigo.miguel@terra.com.br>
@ 2005-04-18 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2005-04-18 15:58 UTC (permalink / raw)
  To: Rodrigo Moreno <rodrigo.miguel@terra.com.br>; +Cc: pgsql-performance

"Rodrigo Moreno" <rodrigo.miguel@terra.com.br> writes:
> 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.

It sounds like you may not have the FSM settings set large enough for
your database.  The default settings are only enough for a small DB
(perhaps a few hundred meg).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to improve postgres performace
  2005-04-18 14:36 How to improve postgres performace Rodrigo Moreno <rodrigo.miguel@terra.com.br>
@ 2005-04-20 00:35 ` Jim C. Nasby <decibel@decibel.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Jim C. Nasby @ 2005-04-20 00:35 UTC (permalink / raw)
  To: Rodrigo Moreno <rodrigo.miguel@terra.com.br>; +Cc: pgsql-performance

On Mon, Apr 18, 2005 at 11:36:01AM -0300, Rodrigo Moreno wrote:
> 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.

Vacuum full will skew your results, unless you plan on running vacuum
full all the time. This is because you will always have some amount of
dead tuples in a table that has any update or delete activity. A regular
vacuum doesn't remove these tuples, it just marks them as available. So
over time, depending on how frequently a table is vacuumed, it will
settle down to a steady-state size that is greater than it's size after
a vacuum full.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-04-20 00:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-18 14:36 How to improve postgres performace Rodrigo Moreno <rodrigo.miguel@terra.com.br>
2005-04-18 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-20 00:35 ` Jim C. Nasby <decibel@decibel.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox