From: Tomas Vondra <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 21:46:12 +0200
Message-ID: <4E08DE04.8050605@fuzzy.cz> (raw)
In-Reply-To: <BANLkTim-QmRMR4husxhEJmUfihPv6mUp4Q@mail.gmail.com>
References: <BANLkTik3DzYsBTbryAwp0=cqwG_uadR72g@mail.gmail.com>
<4E084F71020000250003EC59@gw.wicourts.gov>
<BANLkTimPHiOSAqyHX2_NdQCOtg2Zh2fZnA@mail.gmail.com>
<7d7f310e31bbaa62793e13897d889e1d.squirrel@sq.gransy.com>
<BANLkTim-QmRMR4husxhEJmUfihPv6mUp4Q@mail.gmail.com>
Dne 27.6.2011 17:58, Jenish napsal(a):
>
> 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)
So all cores are 95% utilized? That means you're CPU bound and you need
to fix that somehow.
How much of that belongs to postgres? Are there other processes
consuming significant portion of CPU? And what do you mean by
'utilized'? Does that mean user/sys time, or wait time?
> DB has 960 concurrent users.
Whad does that mean? Does that mean there are 960 active connections?
> io : writing 3-4 MB per second or less (iotop result).
Sequential or random? Post a few lines of 'iostat -x 1' and a few lines
of 'vmstat 1' (collected when the database is busy).
> Scenario : All insert are waiting for previous insert to complete. Cant
> we avoid this situation ?
What do you mean by 'previous'? Does that mean another insert in the
same session (connection), or something performed in another session?
> What is the "max_connections" postgresql support?
That limits number of background processes - each connection is served
by a dedicated posgres process. You can see that in top / ps output.
High values usually mean you need some kind of pooling (you probably
already have one as you're using application server). And if the
connections are really active (doing something all the time), this
should not be significantly higher than the number of cores.
See, you have 8 cores, which means 8 seconds of CPU time each second. No
matter how many connections you allow, you still have just those 8
seconds. So if you need to perform 100x something that takes 1 second,
you need to spend 100 seconds of CPU time. So with those 8 cores, you
can do that in about 12,5 seconds.
Actually if you create too many connections, you'll notice it takes much
more - there's an overhead with process management, context switching,
locking etc.
regards
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: <4E08DE04.8050605@fuzzy.cz>
* 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