Re: Subject: Postgres processes have a burst of CPU usage

On Tue, Jan 23, 2007 at 07:47:26AM -0800, Subramaniam Aiylam wrote:
> Hello all,
>
> I have a setup in which four client machines access
> a Postgres database (8.1.1) running on a Linux box.
> So, there are connections from each machine to the
> database; hence, the Linux box has about 2 postgres
> processes associated with each machine.
>
> I am using the JDBC driver
> (postgresql-8.1-404.jdbc3.jar) to talk to the
> database. I am also using the Spring framework(1.2.2)
> and Hibernate (3.0.5) on top of JDBC. I use Apache's

Beware of Hibernate... it likes to abuse the RDBMS...

> DBCP database connection pool (1.2.1).
>
> Now, there is one particular update that I make from
> one of the client machines - this involves a
> reasonably large object graph (from the Java point of
> view). It deletes a bunch of rows (around 20 rows in
> all) in 4-5 tables and inserts another bunch into the
> same tables.
>
> When I do this, I see a big spike in the CPU usage
> of postgres processes that are associated with ALL the
> client machines, not just the one I executed the
> delete/insert operation on. The spike seems to happen
> a second or two AFTER the original update completes
> and last for a few seconds.
>
> Is it that this operation is forcibly clearing some
> client cache on ALL the postgres processes? Why is
> there such an interdependency? Can I set some
> parameter to turn this off?

No, wouldn't be clearing anything. Would the other connections be
updating the same data? If so they could be waiting on locks that the
graph-updating code has acquired.

I suggest you turn on query logging and see what commands are actually
being sent to the database.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)