From: tv@fuzzy.cz
To: Jenish <jenishvyas@gmail.com>
Cc: Kevin Grittner <kevin.grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issue with Insert
Date: Mon, 27 Jun 2011 17:32:10 +0200
Message-ID: <7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com> (raw)
In-Reply-To: <BANLkTimPHiOSAqyHX2_NdQCOtg2Zh2fZnA@mail.gmail.com>
References: <BANLkTik3DzYsBTbryAwp0=cqwG_uadR72g@mail.gmail.com>
<4E084F71020000250003EC59@gw.wicourts.gov>
<BANLkTimPHiOSAqyHX2_NdQCOtg2Zh2fZnA@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, jenishvyas@gmail.com, kevin.grittner@wicourts.gov
Subject: Re: Performance issue with Insert
In-Reply-To: <7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox