Message-ID: <5569C730.3010701@2ndquadrant.com>
Date: Sat, 30 May 2015 16:20:32 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Postgres is using 100% CPU
References: 
 <CAC=-3DfaqG0OguwRaUP1AzS-hY4=v9RCjZoiqbWtvGHAR43ecQ@mail.gmail.com>
	<CAC=-3DcOyOi3Ubxf0LpOTV07wVOfFowcEw1SBtPOvKGnNn+PqA@mail.gmail.com>
	<CAC=-3Dcp4XGhM9BjO+6DQhVr+8ac-vEQCduokc3D6U9SheKf1g@mail.gmail.com>
	<5568BBFF.8080807@2ndquadrant.com>
 <CAC=-3Dc6RhLgCqO4sNscOQFT0TybVNHciPdvFEwfhvK0Ta3YMw@mail.gmail.com>
In-Reply-To: 
 <CAC=-3Dc6RhLgCqO4sNscOQFT0TybVNHciPdvFEwfhvK0Ta3YMw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 05/30/15 15:46, Ashik S L wrote:
> We are using postgres SQL version 8.4.17..

FYI 8.4 is already unsupported for ~1 year, so you should consider 
upgrading to a newer release. Also, the newest version in that branch is 
8.4.22, so with 8.4.17 you're missing ~1 year of patches.

> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
> each row is of 60 bytes.Every time we insert 16380 bytes of data.

So you insert 273 rows and it gets stuck for 40 minutes? That's really 
strange, and I find it rather unlikely even with a badly misconfigured 
instance. It should easily insert thousands of rows per second.

Can you elaborate more about the database structure, or at least the 
table(s) you're inserting into. Are there any foreign keys (in either 
direction), indexes or triggers?

> I tried to make some config changes using above link. But I did not
> see any improvement.
> I made following changes in postgres.conf file:
> shared_buffers = 512MB // It was 32MB
> work_mem = 30MB
> effective_cache_size = 512MB // I tried with 128MB 256MB also
>
> Please let me know any config changes that I can try out.

I don't think this has anything to do with configuration. This seems 
like an issue at the application level, or maybe poorly designed schema.

You mentioned you have multiple machines, and only some of them are 
having this issue. What are the differences between the machines? Are 
all the machines using the same schema? I assume each has a different 
amount of data.

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance