Renato Oliveira <renato(dot)oliveira(at)grant(dot)co(dot)uk> wrote:
> We have a web application written in JAVA, using jaboss as a
> servlet.
Is the web app on the same box as the database, or separate?
> There are 100 connections open to the database server at any given
> time
How many are typically active at peak times? You could run
something like this as a database superuser at peak times a few
times to get a feel for it:
select count(*) from pg_stat_activity
where current_query <> '<IDLE>';
> We are using Postgres 8.2.4 compiled
You're missing two years and eight months of fixes for 8.2.
http://www.postgresql.org/support/versioning
> The database size is 155GB
Any idea how much of that is "active" -- in the sense of being
frequently referenced versus more-or-less archival?
> 300GB Hard disk Raid1
Two spindles is rather small for a database of that size.
> Today the load average was as high as 15
On how many CPUs?
> Top result:
It would be more useful to run vmstat 1 or maybe even iostat 1 at
peak times and capture a view of activity over time. The memory
information from top isn't always that reliable.
> 1 - we do not have enough memory
More RAM would probably improve performance; hard to tell by how
much without more information.
> 2 - our disk and RAID array setup is not good enough
More spindles would probably improve performance; hard to tell by
how much without more information.
> 3 - Postgres is not setup correctly and uses an older version
Version upgrade would almost certainly help. For configuration,
could you strip all comments and blank lines from your
postgresql.conf file and show it?
> 4 - It is using Slony and replication which does not work.
Sorry, I don't know Slony....
-Kevin