From: Jenish <jenishvyas@gmail.com>
To: tv@fuzzy.cz
Cc: Kevin Grittner <kevin.grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issue with Insert
Date: Mon, 27 Jun 2011 18:58:26 +0300
Message-ID: <BANLkTim-QmRMR4husxhEJmUfihPv6mUp4Q@mail.gmail.com> (raw)
In-Reply-To: <7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com>
References: <BANLkTik3DzYsBTbryAwp0=cqwG_uadR72g@mail.gmail.com>
<4E084F71020000250003EC59@gw.wicourts.gov>
<BANLkTimPHiOSAqyHX2_NdQCOtg2Zh2fZnA@mail.gmail.com>
<7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com>
Hi,
I have already checked all the statements present in the trigger, no one is
taking more then 20 ms.
I am using 8-Processor, Quad-Core Server ,CPU utilization is more then 90-95
% for all. (htop result)
DB has 960 concurrent users.
io : writing 3-4 MB per second or less (iotop result).
Scenario : All insert are waiting for previous insert to complete. Cant
we avoid this situation ?
What is the "max_connections" postgresql support?
Plz help....
--
Thanks & regards,
JENISH VYAS
On Mon, Jun 27, 2011 at 6:32 PM, <tv@fuzzy.cz> wrote:
> > 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: jenishvyas@gmail.com, tv@fuzzy.cz, kevin.grittner@wicourts.gov
Subject: Re: Performance issue with Insert
In-Reply-To: <BANLkTim-QmRMR4husxhEJmUfihPv6mUp4Q@mail.gmail.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