Skip site navigation (1) Skip section navigation (2)

Re: Performance issue with Insert

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: Jenish <jenishvyas(at)gmail(dot)com>
Cc: Kevin Grittner <kevin(dot)grittner(at)wicourts(dot)gov>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance issue with Insert
Date: 2011-06-27 19:46:12
Message-ID: 4E08DE04.8050605@fuzzy.cz (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Tomas VondraDate: 2011-06-27 19:48:17
Subject: Re: Performance issue with Insert
Previous:From: Robert KlemmeDate: 2011-06-27 19:29:21
Subject: Re: Long Running Update - My Solution

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group