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 C947152953 for ; Wed, 20 Apr 2005 17:48:27 -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 40295-03 for ; Wed, 20 Apr 2005 20:48:18 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id E0EDB537FB for ; Wed, 20 Apr 2005 17:48:16 -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 j3KKmHtK008601; (envelope-from ) Wed, 20 Apr 2005 15:48:17 -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 j3KKmFs1020887 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 20 Apr 2005 15:48:16 -0500 (CDT) Message-ID: <4266C010.5090003@arbash-meinel.com> Date: Wed, 20 Apr 2005 15:48:16 -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 Cc: pgsql-performance@postgresql.org Subject: Re: postgres slowdown question References: <5A5058425C97FA409CE98C9B87B0CF3805FDE3@marnewexchange.mardomain.local> In-Reply-To: <5A5058425C97FA409CE98C9B87B0CF3805FDE3@marnewexchange.mardomain.local> X-Enigmail-Version: 0.91.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig5B2AE721528E1F8C167515EE" 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/535 X-Sequence-Number: 11932 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig5B2AE721528E1F8C167515EE Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable 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=20 > through a =91discovery=92 phase, whereas it adds large amount of data i= nto=20 > postgres. Typically, we are adding about a million records in various=20 > tables. The application also issues multiple queries to the database=20 > at the same time. We do not delete any records during the discovery=20 > 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=20 > record per minute. Gradually (after several hours), it slows down to=20 > 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=20 > up to about 30 records per minute. Now, postgres server takes between=20 > 50-70% CPU. > > We have the following in the postgresql.conf : > > max_fsm_pages =3D 500000 > > fsync =3D false > > We certainly can not live with this kind of performance. I believe=20 > postgres should be able to handle much larger datasets but I can not=20 > point my finger as to what are we doing wrong. Can somebody please=20 > point me to the right direction. > > With kind regards, > > -- Shachindra Agarwal. > A few questions first. How are you loading the data? Are you using=20 INSERT or COPY? Are you using a transaction, or are you autocommitting=20 each row? You really need a transaction, and preferably use COPY. Both can help=20 performance a lot. (In some of the tests, single row inserts can be=20 10-100x slower than doing it in bulk.) Also, it sounds like you have a foreign key issue. That as things fill=20 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=20 inform postgres that you have more rows, it might still use the old seqsc= an. There are other possibilities, but it would be nice to know about your=20 table layout, and possibly an EXPLAIN ANALYZE of the inserts that are=20 going slow. John =3D:-> PS> I don't know if JDBC supports COPY, but it certainly should support=20 transactions. --------------enig5B2AE721528E1F8C167515EE 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 iD8DBQFCZsAQJdeBCYSNAAMRAgc1AKDK15PKcX7QO8h+fvU1yB/+RyNIcQCfSYFe o4DSG/sdnr2PssCEatyw16o= =TkCR -----END PGP SIGNATURE----- --------------enig5B2AE721528E1F8C167515EE--