pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John A Meinel <john@arbash-meinel.com>
To: Shachindra Agarwal <sagarwal@metallect.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgres slowdown question
Date: Wed, 20 Apr 2005 15:48:16 -0500
Message-ID: <4266C010.5090003@arbash-meinel.com> (raw)
In-Reply-To: <5A5058425C97FA409CE98C9B87B0CF3805FDE3@marnewexchange.mardomain.local>
References: <5A5058425C97FA409CE98C9B87B0CF3805FDE3@marnewexchange.mardomain.local>

Shachindra Agarwal wrote:

> Dear Postgres Masters:
>
> We are using postgres 7.4 in our java application on RedHat linux. The 
> Java application connects to Postgres via JDBC. The application goes 
> through a ‘discovery’ phase, whereas it adds large amount of data into 
> postgres. Typically, we are adding about a million records in various 
> tables. The application also issues multiple queries to the database 
> at the same time. We do not delete any records during the discovery 
> phase. Both the java application and the postgres are installed on the 
> same machine.
>
> At the beginning, the application is able to add in the order of 100 
> record per minute. Gradually (after several hours), it slows down to 
> less than 10 records per minute. At this time, postgres processes take 
> between 80-99% of CPU. When we reindex the database, the speed bumps 
> up to about 30 records per minute. Now, postgres server takes between 
> 50-70% CPU.
>
> We have the following in the postgresql.conf :
>
> max_fsm_pages = 500000
>
> fsync = false
>
> We certainly can not live with this kind of performance. I believe 
> postgres should be able to handle much larger datasets but I can not 
> point my finger as to what are we doing wrong. Can somebody please 
> point me to the right direction.
>
> With kind regards,
>
> -- Shachindra Agarwal.
>
A few questions first. How are you loading the data? Are you using 
INSERT or COPY? Are you using a transaction, or are you autocommitting 
each row?

You really need a transaction, and preferably use COPY. Both can help 
performance a lot. (In some of the tests, single row inserts can be 
10-100x slower than doing it in bulk.)

Also, it sounds like you have a foreign key issue. That as things fill 
up, the foreign key reference checks are slowing you down.
Are you using ANALYZE as you go? A lot of times when you only have <1000 
rows a sequential scan is faster than using an index, and if you don't 
inform postgres that you have more rows, it might still use the old seqscan.

There are other possibilities, but it would be nice to know about your 
table layout, and possibly an EXPLAIN ANALYZE of the inserts that are 
going slow.

John
=:->

PS> I don't know if JDBC supports COPY, but it certainly should support 
transactions.

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (4+ messages)  latest in thread

Message-ID: <4266C010.5090003@arbash-meinel.com>
Permalink:  ../4266C010.5090003@arbash-meinel.com/
Also on:    postgresql.org/message-id/4266C010.5090003@arbash-meinel.com

 · 

reply

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: john@arbash-meinel.com, sagarwal@metallect.com
  Subject: Re: postgres slowdown question
  In-Reply-To: <4266C010.5090003@arbash-meinel.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