Re: Postgres is using 100% CPU

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