Re: Database performance problems

On Wed, Dec 23, 2009 at 7:45 AM, Renato Oliveira
<renato(dot)oliveira(at)grant(dot)co(dot)uk> wrote:
> There are 100 connections open to the database server at any given time,
> apparently using hibernate.
>
> We are using Postgres 8.2.4 compiled

Update to 8.2.latest at your earliest chance.

> The database size is 155GB
> We have 8GB of RAM
> 300GB Hard disk Raid1
> Everything is within a single Volume
> Today the load average was as high as 15
>
> Top result:
> Tasks: 169 total,  30 running, 139 sleeping,   0 stopped,   0 zombie
> Cpu(s): 69.6% us,  5.9% sy,  0.0% ni,  0.0% id, 24.3% wa,  0.2% hi,  0.0% si
> Mem:   8251404k total,  8228428k used,    22976k free,    16296k buffers
> Swap:  1052248k total,    47176k used,  1005072k free,  6664308k cached

So, 6.6G of kernel cache, so you're not starving your machine of memory.

24.3% wait means that you've got 1 out of 4 cores waiting on IO all
the time (assuming you've got a quad core machine here)

> I am quite sure:
> 1 – we do not have enough memory

Given how much is being used for kernel cache you're probably ok. You
could likely increase shared_buffers and work_mem and
maintenance_work_mem a bit each and use some more for the db instead
of letting the kernel have it all. Generally 1/4 mem to
shared_buffers is reasonable on smaller memory machines.

> 2 – our disk and RAID array setup is not good enough

Hard to be sure. What do

iostat -x 60
AND
vmstat 60

say after running for a few minutes?

> 3 – Postgres is not setup correctly and uses an older version

Not real old, but it is not up to date on security / bug fixes.

> 4 – It is using Slony and replication which does not work.

Slony is a rather complex piece of software. If you don't need it's
extra features and such, londiste from skype may be a better choice.

> I would like if anyone could help with the following:
>
> 1 – Ways of proving my thoughts
> 2 – Gather data evidence to prove

vmstat, iostat, explain analyze <yourqueryhere>