From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres is using 100% CPU
Date: Sat, 30 May 2015 16:20:32 +0200
Message-ID: <5569C730.3010701@2ndquadrant.com> (raw)
In-Reply-To: <CAC=-3Dc6RhLgCqO4sNscOQFT0TybVNHciPdvFEwfhvK0Ta3YMw@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: tomas.vondra@2ndquadrant.com
Subject: Re: Postgres is using 100% CPU
In-Reply-To: <5569C730.3010701@2ndquadrant.com>
* 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