postgresql on FreeBSD 5.3 and high load

[Sean Chittenden]

>> Have you looked to see if you're using spin locks or
>> not?  Search for "amd64 x86_64 spin lock" in PostgreSQL's archives.
>> I think spin locks were disabled for amd64.  When you're at a load of
>> 70, look in pg_catalog.pg_locks to see if you have any ungranted  
>> locks.
>
> Thank you for your input.
>
> Searched postgresl.org's archives, both wihtin and via
> google, but only found information that related
> primarily to Linux.

Before I fired that off, I should've asked, if you do:

SELECT * FROM pg_catalog.pg_locks WHERE granted = FALSE

Are you getting any rows?  If not, slow locks aren't the issue.  Have  
you run iostat(1) to see if you're saturating your IO?

>> Also, is your application primarily read?  You may
>> want to investigate
>> using pgpool as a way of reducing the overhead for
>> connection startup.
>
> There are more reads than writes, but I haven't found
> the exact numbers (yet).

Look in pg_catalog.pg_stat* for an idea of your workload.

http://developer.postgresql.org/docs/postgres/monitoring- 
stats.html#MONITORING-STATS-VIEWS-TABLE

If you can come back with a more specific problem other than "load is  
high," it's much easier to get more precise help regarding whatever the  
problem is.  A load of 70 may just mean you've got a damn busy  
database.  :)  -sc

-- 
Sean Chittenden