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 A1C945643D for ; Mon, 14 Mar 2005 15:59:13 +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 66101-09 for ; Mon, 14 Mar 2005 15:58:56 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id D392B52A5A for ; Mon, 14 Mar 2005 15:58:54 +0000 (GMT) Received: (qmail 12493 invoked from network); 14 Mar 2005 15:58:56 -0000 Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?) (209.149.56.238) by 0 with SMTP; 14 Mar 2005 15:58:56 -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: <8ee47c568e4efeff69cf92f6127a30e7@sitening.com> Content-Transfer-Encoding: quoted-printable Cc: From: Thomas F.O'Connell Subject: Re: Performance Question Date: Mon, 14 Mar 2005 09:58:53 -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.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/209 X-Sequence-Number: 16707 I think you need to provide more information to get any help with your =20= setup. For one thing, why are you "restarting"? Are you restarting the server? =20= 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 =20= > to try and tweak our system at the office, as its performance is not =20= > that great. > > =A0 > > Many people say that PG is a great DB, and I know that our problems =20= > are purely a setup issue. > > =A0 > > After a complete server restart, the system is ok, not fast, but =20 > workable, the problem are increased by the fact that the PG slows =20 > down, for example, we run a full vacuum every night, and after a =20 > restart, it takes about an hour and half, which increases to about 3 =20= > hours in two weeks. At that stage everything is broken bad, and we are = =20 > forced to do a restart again. > > =A0 > > I have tried to follow as much of the documents and HOWTO=92s on the =20= > web, but still have some issues. > > =A0 > > Here is some info (did a restart last night, so this is best =20 > 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 Mar =20 > 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 =20 > 2005 > > 819201=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat Mar = 12 15:14:49 =20 > 2005 > > 851970=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat Mar = 12 15:14:49 =20 > 2005 > > 884739=A0=A0 postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat Mar = 12 15:14:49 =20 > 2005 > > 917508 =A0=A0postgres=A0=A0=A0 Sat Mar 12 15:14:49 2005=A0=A0 Sat Mar = 12 15:14:49 =20 > 2005 > > 950277=A0=A0 postgres=A0=A0=A0 Mon Mar 14 09:02:44 2005=A0=A0 Mon Mar = 14 09:02:43 =20 > 2005 > > 983046=A0=A0 postgres=A0=A0=A0 Mon Mar 14 09:02:43 2005=A0=A0 Mon Mar = 14 09:02:23 =20 > 2005 > > 1015815=A0 postgres=A0=A0=A0 Mon Mar 14 09:02:44 2005=A0=A0 Mon Mar 14 = 09:02:43 =20 > 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 > > = -----------------------------------------------------------------------=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 =20 > 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 =20 > 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 =20 > 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 =20 > 99.2% idle > > Mem:=A0 2063932k av, 2017520k used,=A0=A0 46412k free,=A0=A0=A0=A0=A0=A0= 0k shrd,=A0=A0 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 =20 > 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 > > =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 >