From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Renato Oliveira <renato(dot)oliveira(at)grant(dot)co(dot)uk> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Database performance problems |
Date: | 2009-12-28 20:50:39 |
Message-ID: | dcc563d10912281250t560a9aa3veaa6726e17be7fc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-12-28 20:56:05 | Re: postgres on Windows |
Previous Message | Mark Steben | 2009-12-28 19:42:14 | postgres on Windows |