Message-ID: <7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com>
In-Reply-To: <BANLkTimPHiOSAqyHX2_NdQCOtg2Zh2fZnA@mail.gmail.com>
References: <BANLkTik3DzYsBTbryAwp0=cqwG_uadR72g@mail.gmail.com>
	<4E084F71020000250003EC59@gw.wicourts.gov>
	<BANLkTimPHiOSAqyHX2_NdQCOtg2Zh2fZnA@mail.gmail.com>
Date: Mon, 27 Jun 2011 17:32:10 +0200
Subject: Re: Performance issue with Insert
From: tv@fuzzy.cz
To: "Jenish" <jenishvyas@gmail.com>
Cc: "Kevin Grittner" <kevin.grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

> Hi,
>
> DB : POSTGRES 8.4.8
> OS  : Debian
> HD : SAS 10k rpm
>
> Shared_buffer is 4096 25 % of RAM , effective_cache is 8GB 75% of RAM
>
> After insert trigger is again calling 2 more trigger and insert record in
> another table depends on condition.
>
> with all trigger enable there are 8 insert and 32 updates(approx. update
> is
> depends on hierarchy)

Hi,

it's very difficult to give you reliable recommendations with this little
info, but the triggers are obviously the bottleneck. We have no idea what
queries are executed in them, but I guess there are some slow queries.

Find out what queries are executed in the triggers, benchmark each of them
and make them faster. Just don't forget that those SQL queries are
executed as prepared statements, so they may behave a bit differently than
plain queries. So use 'PREPARE' and 'EXPLAIN EXECUTE' to tune them.

> Plz explain multiple connections. Current scenario application server is
> sending all requests.

PostgreSQL does not support parallel queries (i.e. a query distributed on
multiple CPUs) so each query may use just a single CPU. If you're CPU
bound (one CPU is 100% utilized but the other CPUs are idle), you can
usually parallelize the workload on your own - just use multiple
connections.

But if you're using an application server and there are multiple
connections used, this is not going to help you. How many connections are
active at the same time? Are the CPUs idle or utilized?

Tomas