Message-ID: <4E08DE04.8050605@fuzzy.cz>
Date: Mon, 27 Jun 2011 21:46:12 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.17) Gecko/20110619 Lightning/1.0b3pre Thunderbird/3.1.10
MIME-Version: 1.0
To: Jenish <jenishvyas@gmail.com>
CC: Kevin Grittner <kevin.grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Subject: Re: Performance issue with Insert
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>
In-Reply-To: <BANLkTim-QmRMR4husxhEJmUfihPv6mUp4Q@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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