From: John A Meinel <john@arbash-meinel.com>
To: Shachindra Agarwal <sagarwal@metallect.com>
To: pgsql-performance@postgresql.org
Subject: Re: postgres slowdown question
Date: Wed, 20 Apr 2005 18:35:34 -0500
Message-ID: <4266E746.5010508@arbash-meinel.com> (raw)
In-Reply-To: <5A5058425C97FA409CE98C9B87B0CF3805FE11@marnewexchange.mardomain.local>
References: <5A5058425C97FA409CE98C9B87B0CF3805FE11@marnewexchange.mardomain.local>
Shachindra Agarwal wrote:
>Thanks for the note. Please see my responses below:
>
>
...
>
>
>>>We are using JDBC which supports 'inserts' and 'transactions'. We are
>>>
>>>
>using both. The business logic adds one business object at a time. Each
>object is added within its own transaction. Each object add results in 5
>records in various tables in the the database. So, a commit is performed
>after every 5 inserts.
>
>
>
Well, 5 inserts per commit is pretty low. It would be nice to see more
like 100 inserts per commit. Would it be possible during the "discovery"
phase to put the begin/commit logic a little bit higher?
Remember, each COMMIT requires at least one fsync. (I realize you have
fsync off for now). But commit is pretty expensive.
>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.
>
>
>
>>>This could be the issue. I will start 'analyze' in a cron job. I will
>>>
>>>
>update you with the results.
>
>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.
>
>
Let us know if ANALYZE helps. If you are not deleting or updating
anything, you probably don't need to do VACUUM ANALYZE, but you might
think about it. It is a little more expensive since it has to go to
every tuple, rather than just a random sampling.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: <4266E746.5010508@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