Re: Problem with database performance, Debian 4gb ram ?

Grant Masan <grant.massan@gmail.com> wrote:

> max_connections = 80
> shared_buffers = 512MB
> temp_buffers = 8MB
> work_mem = 20MB
> maintenance_work_mem = 384MB
> wal_buffers = 8MB
> checkpoint_segments = 128MB
> effective_cache_size = 2304MB
> checkpoint_timeout = 1h

Pending further information, these seem sane to me.

> cpu_tuple_cost = 0.0030
> cpu_index_tuple_cost = 0.0010
> cpu_operator_cost = 0.0005

Why did you make these adjustments?  I usually have to change the
ratio between page and cpu costs toward the other direction.  Unless
you have carefully measured performance with and without these changes
and found a clear win with these, I would recommend going back to the
defaults for these three and tuning from there.

> fsync = off

Only use this if you can afford to lose all data in the database.
(There are some situations where this is actually OK, but they are
unusual.)

As others have stated, though, we'd need more information to really
give much useful advice.  An EXPLAIN ANALYZE of a query which isn't
performing to expectations would be helpful, especially if you include
the table definitions (with indexes) of all tables involved in the
query.  Output from vmstat or iostat with a fairly small interval (I
usually use 1) while the query is running would be useful, too.

Knowing the exact version of PostgreSQL (like from SELECT version();)
would be useful, as well as knowing more about you disk array and
controller(s).

-Kevin