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 4813556444 for ; Tue, 15 Mar 2005 06:40:11 +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 22290-04 for ; Tue, 15 Mar 2005 06:39:59 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 1994F5641D for ; Tue, 15 Mar 2005 06:39:53 +0000 (GMT) Received: (qmail 1228 invoked from network); 15 Mar 2005 06:39:47 -0000 Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?) (209.149.56.238) by 0 with SMTP; 15 Mar 2005 06:39:47 -0000 Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Resent-Date: Tue, 15 Mar 2005 00:39:47 -0600 Message-Id: Content-Transfer-Encoding: quoted-printable Resent-To: PgSQL Admin Resent-Message-Id: From: "Werner vd Merwe" Subject: Re: Performance Question Resent-From: Thomas F.O'Connell Date: Tue, 15 Mar 2005 08:33:28 +0200 To: "'Thomas F.O'Connell'" 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/218 X-Sequence-Number: 16716 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,=20 around 15000 records per day added. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas=20 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 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 performance >>> to try and tweak our system at the office, as its performance is 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 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 >>> >>> = --------------------------------------------------------------------- >>> - >>> - >>> -------------------------------------------------------- >>> >>> ------ 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 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 = 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 >>> >>> = --------------------------------------------------------------------- >>> - >>> - >>> -------------------------------------------------------- >>> >>> =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 >>> >>> = --------------------------------------------------------------------- >>> - >>> - >>> -------------------------------------------------------- >>> >>> =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 >>> >>> = --------------------------------------------------------------------- >>> - >>> - >>> -------------------------------------------------------- >>> >>> =A0 >>> >>> Any ideas will be greatly appreciated. >>> >>> =A0 >>> >>> Kind regards >>> >>> Werner vd Merwe >>> >>> =A0 >>> >>> >>> >>> -- >>> No virus found in this outgoing message. >>> Checked by AVG Anti-Virus. >>> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: >>> 2005/03/11 >>> >> >> --=20 >> No virus found in this incoming message. >> Checked by AVG Anti-Virus. >> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11 >> >> >> --=20 >> No virus found in this outgoing message. >> Checked by AVG Anti-Virus. >> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11 >> >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > --=20 > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11 > > > --=20 > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11 > > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster --=20 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11 --=20 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11