X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A8B5E569A9 for ; Tue, 22 Mar 2005 15:55:38 +0000 (GMT) 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 75274-08 for ; Tue, 22 Mar 2005 15:55:35 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 25BBB5691B for ; Tue, 22 Mar 2005 15:55:34 +0000 (GMT) Received: (qmail 23522 invoked from network); 22 Mar 2005 15:55:38 -0000 Received: from w080.z064003242.bna-tn.dsl.cnc.net (HELO ?192.168.1.22?) (64.3.242.80) by 0 with SMTP; 22 Mar 2005 15:55:38 -0000 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Message-Id: <2d1af770a2232de84fad7e43a06ba372@sitening.com> Content-Transfer-Encoding: quoted-printable Cc: "'PgSQL Admin'" From: Thomas F.O'Connell Subject: Re: Performance Question Date: Tue, 22 Mar 2005 09:55:38 -0600 To: "Werner vd Merwe" X-Mailer: Apple Mail (2.619.2) 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: 200503/344 X-Sequence-Number: 16842 Have you considered trying pg_autovacuum, which is in contrib? It =20 actually sets and monitors thresholds to try to determine dynamically =20= when tables need vacuuming. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 22, 2005, at 12:31 AM, Werner vd Merwe wrote: > Hi Thomas, > > We perform a full VACUUM and ANALYSE every night, and a ANALYSE on =20 > selected > tables throughout the day. > > Platform is Redhat 9.0 and PG 7.4 > > Snippet of postgresql.conf: > Shared_buffers =3D 32768 > Effective_cache_size =3D 131072 > > Hardware: > Dual Xeon 2.4 > 2Gb RAM > Raid 5 SCSI (3 X 73Gb) > > If any more info will be helpful, please let me know! > > Many thanks > > -----Original Message----- > From: Thomas F. O'Connell [mailto:tfo@sitening.com] > Sent: 19 March 2005 08:09 PM > To: Werner vd Merwe > Cc: PgSQL Admin > Subject: Re: [ADMIN] Performance Question > > The long and short of it is that you should never need to restart > either the main server or postgres in order to achieve better > performance. If the issue is that you are not vacuuming frequently > enough, then you might consider pg_autovacuum, which is located in > contrib. > > You could also post more information about your system, including > platform, postgres version, and salient features of your > postgresql.conf file. > > The more information you're able to provide, the more help people on > this list will be able to give you. > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Mar 15, 2005, at 12:33 AM, Werner vd Merwe wrote: > >> Hi Thomas, >> >> The DB has 134 tables, sizes vary, one of the most used and thus >> biggest >> problems currently have around 3,000,000 records in, with 15 fields, >> around >> 15000 records per day added. >> >> >> -----Original Message----- >> From: pgsql-admin-owner@postgresql.org >> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas >> F.O'Connell >> Sent: 14 March 2005 06:37 PM >> To: Werner vd Merwe >> Cc: PgSQL Admin >> Subject: Re: [ADMIN] Performance Question >> >> Well, there's always the dbsize module in contrib to check actual = size >> on disk. I was thinking more in terms of approximate numbers of = tables >> and rows in those tables. >> >> -tfo >> >> -- >> Thomas F. O'Connell >> Co-Founder, Information Architect >> Sitening, LLC >> http://www.sitening.com/ >> 110 30th Avenue North, Suite 6 >> Nashville, TN 37203-6320 >> 615-260-0005 >> >> On Mar 14, 2005, at 10:21 AM, Werner vd Merwe wrote: >> >>> Yeah - only postgres running on the server, VACUUM happens every >>> night, with >>> intermitted ANALYSE on selected tables during the day. >>> >>> Not sure how much data in the DB, not sure how to check that? >>> Operations is all via JDBC, mostly standard queries, with quite a = bit >>> if >>> inner joins and inline selects. >>> >>> >>> -----Original Message----- >>> From: pgsql-admin-owner@postgresql.org >>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas >>> F.O'Connell >>> Sent: 14 March 2005 06:10 PM >>> To: Werner vd Merwe >>> Cc: PgSQL Admin >>> Subject: Re: [ADMIN] Performance Question >>> >>> It doesn't make all that much more sense. I'd keep posting to the >>> lists >>> to let other people continue to take a crack at it. Is the system >>> dedicated entirely to postgres? Are you VACUUMing? What kinds of >>> operations are being performed and how much data is in the database? >>> >>> -tfo >>> >>> -- >>> Thomas F. O'Connell >>> Co-Founder, Information Architect >>> Sitening, LLC >>> http://www.sitening.com/ >>> 110 30th Avenue North, Suite 6 >>> Nashville, TN 37203-6320 >>> 615-260-0005 >>> >>> On Mar 14, 2005, at 10:03 AM, Werner vd Merwe wrote: >>> >>>> Hi Thomas, >>>> >>>> Thank you for your response. >>>> >>>> Performance does not pick up after a service restart, needs to be a >>>> system >>>> restart. >>>> >>>> If we do not do that restart, then things are 'broken bad', as the >>>> system >>>> becomes incredibly slow. Not broken after the vacuum, it is a =20 >>>> gradual >>>> decline in performance. >>>> >>>> Hope that makes more sense. >>>> >>>> Many thanks >>>> >>>> >>>> -----Original Message----- >>>> From: Thomas F. O'Connell [mailto:tfo@sitening.com] >>>> Sent: 14 March 2005 05:59 PM >>>> To: Werner vd Merwe >>>> Cc: pgsql-admin@postgresql.org >>>> Subject: Re: [ADMIN] Performance Question >>>> >>>> I think you need to provide more information to get any help with >>>> your >>>> setup. >>>> >>>> For one thing, why are you "restarting"? Are you restarting the >>>> server? >>>> Postgres? In general, there should be no need to restart either. >>>> >>>> Next, what do you mean by "broken bad" after a full vacuum? >>>> >>>> -tfo >>>> >>>> -- >>>> Thomas F. O'Connell >>>> Co-Founder, Information Architect >>>> Sitening, LLC >>>> http://www.sitening.com/ >>>> 110 30th Avenue North, Suite 6 >>>> Nashville, TN 37203-6320 >>>> 615-260-0005 >>>> >>>> On Mar 14, 2005, at 1:08 AM, Werner vd Merwe wrote: >>>> >>>>> Hi guys, >>>>> >>>>> =A0 >>>>> >>>>> I have been browsing around and reading up on PostgreSQL =20 >>>>> performance >>>>> to try and tweak our system at the office, as its performance is =20= >>>>> not >>>>> that great. >>>>> >>>>> =A0 >>>>> >>>>> Many people say that PG is a great DB, and I know that our = problems >>>>> are purely a setup issue. >>>>> >>>>> =A0 >>>>> >>>>> After a complete server restart, the system is ok, not fast, but >>>>> workable, the problem are increased by the fact that the PG slows >>>>> down, for example, we run a full vacuum every night, and after a >>>>> restart, it takes about an hour and half, which increases to about = =20 >>>>> 3 >>>>> hours in two weeks. At that stage everything is broken bad, and we >>>>> are >>>>> forced to do a restart again. >>>>> >>>>> =A0 >>>>> >>>>> I have tried to follow as much of the documents and HOWTO=92s on = the >>>>> web, but still have some issues. >>>>> >>>>> =A0 >>>>> >>>>> Here is some info (did a restart last night, so this is best >>>>> performance atm) >>>>> >>>>> =A0 >>>>> >>>>> System: >>>>> >>>>> Dual XEON 2.4GHz >>>>> >>>>> 3Gb RAM >>>>> >>>>> =A0 >>>>> >>>>> Dedicated to PG >>>>> >>>>> =A0 >>>>> >>>>> Type of apps: >>>>> >>>>> Mostly JDBC queries running via Tomcat. >>>>> >>>>> =A0 >>>>> >>>>> =A0 >>>>> >>>>> = -------------------------------------------------------------------=20 >>>>> - >>>>> - >>>>> - >>>>> - >>>>> -------------------------------------------------------- >>>>> >>>>> ------ Shared Memory Attach/Detach/Change Times -------- >>>>> >>>>> shmid=A0=A0=A0=A0=A0 owner=A0=A0=A0=A0=A0 attached=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 detached=A0=A0=A0=A0 >>>>> =A0=A0=A0=A0=A0=A0=A0=A0changed >>>>> >>>>> =A0 >>>>> >>>>> 131072=A0=A0=A0=A0 postgres=A0=A0=A0 Mar 14 09:02:43=A0=A0=A0=A0=A0 = Mar 14 09:02:23=A0=A0=A0=A0=A0 =20 >>>>> Mar >>>>> 12 15:14:49 >>>>> >>>>> =A0 >>>>> >>>>> =A0 >>>>> >>>>> ------ Shared Memory Operation/Change Times -------- >>>>> >>>>> shmid=A0=A0=A0 owner=A0=A0=A0=A0=A0 last-op=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 last-changed >>>>> >>>>> 786432=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat = Mar 12 15:14:49 >>>>> 2005 >>>>> >>>>> 819201=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat = Mar 12 15:14:49 >>>>> 2005 >>>>> >>>>> 851970=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat = Mar 12 15:14:49 >>>>> 2005 >>>>> >>>>> 884739=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat = Mar 12 15:14:49 >>>>> 2005 >>>>> >>>>> 917508 =A0=A0postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat = Mar 12 15:14:49 >>>>> 2005 >>>>> >>>>> 950277=A0=A0 postgres=A0=A0=A0 Mon Mar 14 09:02:44 2005=A0=A0 Mon = Mar 14 09:02:43 >>>>> 2005 >>>>> >>>>> 983046=A0=A0 postgres=A0=A0=A0 Mon Mar 14 09:02:43 2005=A0=A0 Mon = Mar 14 09:02:23 >>>>> 2005 >>>>> >>>>> 1015815=A0 postgres=A0=A0=A0 Mon Mar 14 09:02:44 2005=A0=A0 Mon = Mar 14 09:02:43 >>>>> 2005 >>>>> >>>>> =A0 >>>>> >>>>> ------ Message Queues Send/Recv/Change Times -------- >>>>> >>>>> msqid=A0=A0=A0 owner=A0=A0=A0=A0=A0 send=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 recv=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =20= >>>>> change >>>>> >>>>> =A0 >>>>> >>>>> =A0 >>>>> >>>>> ------ Shared Memory Status -------- >>>>> >>>>> segments allocated 1 >>>>> >>>>> pages allocated 266324 >>>>> >>>>> pages resident=A0 257206 >>>>> >>>>> pages swapped=A0=A0 8619 >>>>> >>>>> Swap performance: 0 attempts=A0=A0=A0=A0 0 successes >>>>> >>>>> =A0 >>>>> >>>>> = -------------------------------------------------------------------=20 >>>>> - >>>>> - >>>>> - >>>>> - >>>>> -------------------------------------------------------- >>>>> >>>>> =A0 >>>>> >>>>> 09:03:48=A0 up 2 days, 10:12,=A0 3 users,=A0 load average: 0.15, = 0.36, >>>>> 0.31 >>>>> >>>>> 64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped >>>>> >>>>> CPU0 states:=A0=A0 2.2% user=A0=A0 1.4% system=A0=A0=A0 0.0% = nice=A0=A0 0.0% iowait=A0 >>>>> 95.4% idle >>>>> >>>>> CPU1 states:=A0=A0 1.3% user=A0=A0 0.3% system=A0=A0=A0 0.0% = nice=A0=A0 0.0% iowait=A0 >>>>> 97.4% idle >>>>> >>>>> CPU2 states:=A0=A0 2.3% user=A0=A0 0.1% system=A0=A0=A0 0.0% = nice=A0=A0 0.0% iowait=A0 >>>>> 97.1% idle >>>>> >>>>> CPU3 states:=A0=A0 0.0% user=A0=A0 0.3% system=A0=A0=A0 0.0% = nice=A0=A0 0.0% iowait=A0 >>>>> 99.2% idle >>>>> >>>>> Mem:=A0 2063932k av, 2017520k used,=A0=A0 46412k free,=A0=A0=A0=A0=A0= =A0 0k shrd,=A0=A0 >>>>> 79388k buff >>>>> >>>>> =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 1434408k = actv,=A0=A0=A0=A0 232k in_d,=A0=A0 46268k in_c >>>>> >>>>> Swap: 2040244k av,=A0=A0 63676k used, 1976568k = free=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 >>>>> 1678480k cached >>>>> >>>>> =A0 >>>>> >>>>> =A0 PID USER=A0=A0=A0=A0 PRI=A0 NI=A0 SIZE=A0 RSS SHARE STAT %CPU = %MEM=A0=A0 TIME CPU >>>>> COMMAND >>>>> >>>>> 15985 postgres=A0 15=A0=A0 0 88796=A0 86M 86192 S=A0=A0=A0=A0 3.4=A0= 4.2=A0=A0 0:00=A0=A0 0 >>>>> postmaster >>>>> >>>>> 16108 postgres=A0 20=A0=A0 0 21252=A0 20M 19236 S=A0=A0=A0=A0 1.6=A0= 1.0=A0=A0 0:00=A0=A0 1 >>>>> postmaster >>>>> >>>>> 16094 postgres=A0 18=A0=A0 0 12188=A0 11M 10292 S=A0=A0=A0=A0 0.4=A0= 0.5=A0=A0 0:00=A0=A0 3 >>>>> postmaster >>>>> >>>>> 24846 postgres=A0 15=A0=A0 0=A0=A0 432=A0 228=A0=A0 120 S=A0=A0=A0=A0= 0.0=A0 0.0=A0=A0 0:04=A0=A0 2 >>>>> postmaster >>>>> >>>>> 24851 postgres=A0 15=A0=A0 0=A0 1320 1044=A0=A0=A0 24 S=A0=A0=A0=A0 = 0.0=A0 0.0=A0=A0 0:06=A0=A0 2 >>>>> postmaster >>>>> >>>>> 24852 postgres=A0 15=A0=A0 0=A0=A0 628=A0 400=A0=A0 128 S=A0=A0=A0=A0= 0.0=A0 0.0=A0=A0 0:18=A0=A0 2 >>>>> postmaster >>>>> >>>>> 11207 postgres=A0 20=A0=A0 0 11536=A0 10M=A0 9700 S=A0=A0=A0=A0 = 0.0=A0 0.5=A0=A0 0:00=A0=A0 2 >>>>> postmaster >>>>> >>>>> 15113 postgres=A0 20=A0=A0 0 20908=A0 20M 18796 S=A0=A0=A0=A0 0.0 = =A00.9=A0=A0 0:00=A0=A0 2 >>>>> postmaster >>>>> >>>>> 15114 postgres=A0 20=A0=A0 0 12732=A0 11M 10792 S=A0=A0=A0=A0 0.0=A0= 0.5=A0=A0 0:00=A0=A0 2 >>>>> postmaster >>>>> >>>>> 15606 postgres=A0 20=A0=A0 0 12672=A0 11M 10764 S=A0=A0=A0=A0 0.0=A0= 0.5=A0=A0 0:00=A0=A0 3 >>>>> postmaster >>>>> >>>>> 15917 postgres=A0 15=A0=A0 0 17172=A0 16M 15220 S=A0=A0=A0=A0 0.0=A0= 0.8=A0=A0 0:00=A0=A0 1 >>>>> postmaster >>>>> >>>>> =A0 >>>>> >>>>> = -------------------------------------------------------------------=20 >>>>> - >>>>> - >>>>> - >>>>> - >>>>> -------------------------------------------------------- >>>>> >>>>> =A0 >>>>> >>>>> Postgresql.conf extract >>>>> >>>>> =A0 >>>>> >>>>> max_connections =3D 120 >>>>> >>>>> shared_buffers =3D 131072 >>>>> >>>>> sort_mem =3D 16384 >>>>> >>>>> vacuum_mem =3D 8192 >>>>> >>>>> effective_cache_size =3D 65536 >>>>> >>>>> =A0 >>>>> >>>>> = -------------------------------------------------------------------=20 >>>>> - >>>>> - >>>>> - >>>>> - >>>>> -------------------------------------------------------- >>>>> >>>>> =A0 >>>>> >>>>> Any ideas will be greatly appreciated. >>>>> >>>>> =A0 >>>>> >>>>> Kind regards >>>>> >>>>> Werner vd Merwe=