X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 46BCC53757 for ; Wed, 20 Apr 2005 20:35:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78746-09 for ; Wed, 20 Apr 2005 23:35:36 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id B581353675 for ; Wed, 20 Apr 2005 20:35:32 -0300 (ADT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j3KNZYGP014677; (envelope-from ) Wed, 20 Apr 2005 18:35:34 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j3KNZXgA010032 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 20 Apr 2005 18:35:33 -0500 (CDT) Message-ID: <4266E746.5010508@arbash-meinel.com> Date: Wed, 20 Apr 2005 18:35:34 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Shachindra Agarwal , pgsql-performance@postgresql.org Subject: Re: postgres slowdown question References: <5A5058425C97FA409CE98C9B87B0CF3805FE11@marnewexchange.mardomain.local> In-Reply-To: <5A5058425C97FA409CE98C9B87B0CF3805FE11@marnewexchange.mardomain.local> X-Enigmail-Version: 0.91.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig931D427B07282408048652E1" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/542 X-Sequence-Number: 11939 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig931D427B07282408048652E1 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 =:-> --------------enig931D427B07282408048652E1 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCZudHJdeBCYSNAAMRAjCYAKCZCvXRfYSSZWp2m7/BiqE6He+w2gCgtIOc lWha19ZoEHoa+JSrRFMX5iw= =nFCc -----END PGP SIGNATURE----- --------------enig931D427B07282408048652E1--