From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Josh Trutwin" <josh(at)trutwins(dot)homeip(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Tuning Help - What did I do wrong? |
Date: | 2007-10-04 16:19:22 |
Message-ID: | dcc563d10710040919k716a51d4ha8522444ecf791fd@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 10/4/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:
> We have a pretty busy linux server running postgres 8.1.4, waiting to
> upgrade until 8.3 to avoid dump/restoring twice.
You should immediate update your version to 8.1.whateverislatest.
That requires no dump / restore and it is a bug fix update. I doubt
this problem is because you're out of date on patches, but who
knows...
> # cat /proc/meminfo
> total: used: free: shared: buffers: cached:
> Mem: 3704217600 3592069120 112148480 0 39460864 2316271616
> Swap: 2516918272 270336 2516647936
Well, you've got plenty of memory, and a large chunk is being used as cache.
> The postgresql.conf was basically the default so I decided to
> increase the cache size and a couple paramaters to make more use of
> that memory - here's what I did:
>
> shared_buffers = 16384 (was 1000)
> work_mem = 16384 (was 1024)
> wal_buffers = 24 (was 8)
> checkpoint_segments = 5 (was 3)
> effective_cache_size = 10000 (was 1000)
> stats_command_string = on (was off)
> stats_block_level = on (was off)
> stats_row_level = on (was off)
Your changes seem reasonable.
> Also, the entire cluster gets vacuumed analyzed nightly.
You should look into running the autovacuum daemon. for heavily used
databases nightly vacuuming may not be enough.
> After making these changes, the performance on the server actually
> worsened. I slowly backed off on some of the paramaters but didn't
> seem to help.
Most likely turning on stats collection slowed you down a bit.
We need to see examples of what's slow, including explain analyze
output for slow queries. Also a brief explanation of the type of load
your database server is seeing. I.e. is it a lot of little
transactions, mostly read, batch processing, lots of users, one user,
etc... Right now we don't have enough info to really help you.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2007-10-04 16:22:17 | Re: Tuning Help - What did I do wrong? |
Previous Message | Josh Trutwin | 2007-10-04 15:28:04 | Tuning Help - What did I do wrong? |