Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 8D3BFB5DBCE for ; Mon, 27 Jun 2011 12:58:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 10982-07 for ; Mon, 27 Jun 2011 15:58:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 9C0A1B5DBCB for ; Mon, 27 Jun 2011 12:58:27 -0300 (ADT) Received: by iwn6 with SMTP id 6so4201579iwn.19 for ; Mon, 27 Jun 2011 08:58:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=54bscLGHtdxw6Kw/Y15A9K02vnu9LaG2S6GF9V/i++w=; b=DqlO7BBAbNLXKDw/RTjky4OTSXOPuk3f7zbHRpoOKsv2IVEN94Vs4qOdav5CxPyD2f 5ZSsCqPiXQnku9W9y+3S+GG9AqvLCW1rnwy05wsrjUM1L5RuzlCEI6vUMTqzh4nlVSfL dgm643Zrw5XAshRhw/8gyFkFBITI+ncddZMcA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=V61vNE3YfGnKUwz8hob1t5Hs4Q+0xOmRzR4VgMkdDYbU6C7DSd+CDD5EOtUCsRrqAT 1IP3eeZlSlDRd3E8947GBFNvjrDfLWUT7MwYffrop02RcFJYi/GPgamykmUbfdzAUyqM vCLlD+bUhDors342ftX/wdiid45elDqj6dSQw= MIME-Version: 1.0 Received: by 10.231.82.131 with SMTP id b3mr6370601ibl.2.1309190306771; Mon, 27 Jun 2011 08:58:26 -0700 (PDT) Received: by 10.231.30.73 with HTTP; Mon, 27 Jun 2011 08:58:26 -0700 (PDT) In-Reply-To: <7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com> References: <4E084F71020000250003EC59@gw.wicourts.gov> <7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com> Date: Mon, 27 Jun 2011 18:58:26 +0300 Message-ID: Subject: Re: Performance issue with Insert From: Jenish To: tv@fuzzy.cz Cc: Kevin Grittner , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd6b3062db22804a6b39f36 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.888 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201106/248 X-Sequence-Number: 44181 --000e0cd6b3062db22804a6b39f36 Content-Type: text/plain; charset=ISO-8859-1 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, 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 > > --000e0cd6b3062db22804a6b39f36 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

Hi,

I have already checked all= the statements present in the trigger, no one is taking more then 20 ms.

I am using=A08-Processor, Quad-Core Server ,CPU uti= lization is more then 90-95 % for all. (htop result)

DB has 960 concurrent users.=A0

io : writing 3-4 MB per second or less (iotop result).

Scenario : =A0All insert are waiting for previous insert to complete= . Cant we=A0avoid=A0this=A0situation=A0?=A0=A0
What is the "max_connections" postgresql support?=A0

Plz help....


--= =A0
Thanks & regards,
JENISH VYAS
=A0=A0





On Mon, Jun 27, 2011 at 6:32 PM, <tv@fuzzy.cz> wrote:
> Hi,
>
> DB : POSTGRES 8.4.8
> OS =A0: Debian
> HD : SAS 10k rpm
>
> Shared_buffer is 4096 25 % of RAM , effective_cache is 8GB 75% of RAM<= br> >
> 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. upda= te
> is
> depends on hierarchy)

Hi,

it's very difficult to give you reliable recommendations with this litt= le
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<= br> 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<= br> plain queries. So use 'PREPARE' and 'EXPLAIN EXECUTE' to tu= ne them.

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

PostgreSQL does not support parallel queries (i.e. a query distribute= d 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




--000e0cd6b3062db22804a6b39f36--