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 1C1F25663A for ; Mon, 14 Mar 2005 17:10:31 +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 90428-05 for ; Mon, 14 Mar 2005 17:10:22 +0000 (GMT) Received: from mail.libertyrms.com (unknown [207.219.45.62]) by svr1.postgresql.org (Postfix) with ESMTP id E1BEE56450 for ; Mon, 14 Mar 2005 17:10:20 +0000 (GMT) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1DAt5A-00021M-9M for pgsql-admin@postgresql.org; Mon, 14 Mar 2005 12:10:20 -0500 Message-ID: <4235C57C.9030708@ca.afilias.info> Date: Mon, 14 Mar 2005 12:10:20 -0500 From: Brad Nicholson User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PgSQL Admin Subject: Re: Performance Question References: <4ead39d3e412a38cba6096eafb765eb9@sitening.com> In-Reply-To: <4ead39d3e412a38cba6096eafb765eb9@sitening.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.018 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/213 X-Sequence-Number: 16711 I'm wondering if long running transacations might be the cause (you'll likely want to do this while perfomance is suffering). Have a look at pg_stat_activity and see if there are any long running transacations (or any idle transactions). I'd also be curious to see the output of the following: VACUUM ANALYZE VERBOSE pg_listener; -- Brad Nicholson Database Administrator, Afilias Canada Corp. Thomas F.O'Connell wrote: > 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, >>> >>> >>> >>> 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. >>> >>> >>> >>> Many people say that PG is a great DB, and I know that our problems >>> are purely a setup issue. >>> >>> >>> >>> 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. >>> >>> >>> >>> I have tried to follow as much of the documents and HOWTO’s on the >>> web, but still have some issues. >>> >>> >>> >>> Here is some info (did a restart last night, so this is best >>> performance atm) >>> >>> >>> >>> System: >>> >>> Dual XEON 2.4GHz >>> >>> 3Gb RAM >>> >>> >>> >>> Dedicated to PG >>> >>> >>> >>> Type of apps: >>> >>> Mostly JDBC queries running via Tomcat. >>> >>> >>> >>> >>> >>> ---------------------------------------------------------------------- >>> - >>> -------------------------------------------------------- >>> >>> ------ Shared Memory Attach/Detach/Change Times -------- >>> >>> shmid owner attached detached >>> changed >>> >>> >>> >>> 131072 postgres Mar 14 09:02:43 Mar 14 09:02:23 Mar >>> 12 15:14:49 >>> >>> >>> >>> >>> >>> ------ Shared Memory Operation/Change Times -------- >>> >>> shmid owner last-op last-changed >>> >>> 786432 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 >>> 2005 >>> >>> 819201 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 >>> 2005 >>> >>> 851970 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 >>> 2005 >>> >>> 884739 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 >>> 2005 >>> >>> 917508 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49 >>> 2005 >>> >>> 950277 postgres Mon Mar 14 09:02:44 2005 Mon Mar 14 09:02:43 >>> 2005 >>> >>> 983046 postgres Mon Mar 14 09:02:43 2005 Mon Mar 14 09:02:23 >>> 2005 >>> >>> 1015815 postgres Mon Mar 14 09:02:44 2005 Mon Mar 14 09:02:43 >>> 2005 >>> >>> >>> >>> ------ Message Queues Send/Recv/Change Times -------- >>> >>> msqid owner send recv change >>> >>> >>> >>> >>> >>> ------ Shared Memory Status -------- >>> >>> segments allocated 1 >>> >>> pages allocated 266324 >>> >>> pages resident 257206 >>> >>> pages swapped 8619 >>> >>> Swap performance: 0 attempts 0 successes >>> >>> >>> >>> ---------------------------------------------------------------------- >>> - >>> -------------------------------------------------------- >>> >>> >>> >>> 09:03:48 up 2 days, 10:12, 3 users, load average: 0.15, 0.36, 0.31 >>> >>> 64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped >>> >>> CPU0 states: 2.2% user 1.4% system 0.0% nice 0.0% iowait >>> 95.4% idle >>> >>> CPU1 states: 1.3% user 0.3% system 0.0% nice 0.0% iowait >>> 97.4% idle >>> >>> CPU2 states: 2.3% user 0.1% system 0.0% nice 0.0% iowait >>> 97.1% idle >>> >>> CPU3 states: 0.0% user 0.3% system 0.0% nice 0.0% iowait >>> 99.2% idle >>> >>> Mem: 2063932k av, 2017520k used, 46412k free, 0k shrd, >>> 79388k buff >>> >>> 1434408k actv, 232k in_d, 46268k in_c >>> >>> Swap: 2040244k av, 63676k used, 1976568k free >>> 1678480k cached >>> >>> >>> >>> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU >>> COMMAND >>> >>> 15985 postgres 15 0 88796 86M 86192 S 3.4 4.2 0:00 0 >>> postmaster >>> >>> 16108 postgres 20 0 21252 20M 19236 S 1.6 1.0 0:00 1 >>> postmaster >>> >>> 16094 postgres 18 0 12188 11M 10292 S 0.4 0.5 0:00 3 >>> postmaster >>> >>> 24846 postgres 15 0 432 228 120 S 0.0 0.0 0:04 2 >>> postmaster >>> >>> 24851 postgres 15 0 1320 1044 24 S 0.0 0.0 0:06 2 >>> postmaster >>> >>> 24852 postgres 15 0 628 400 128 S 0.0 0.0 0:18 2 >>> postmaster >>> >>> 11207 postgres 20 0 11536 10M 9700 S 0.0 0.5 0:00 2 >>> postmaster >>> >>> 15113 postgres 20 0 20908 20M 18796 S 0.0 0.9 0:00 2 >>> postmaster >>> >>> 15114 postgres 20 0 12732 11M 10792 S 0.0 0.5 0:00 2 >>> postmaster >>> >>> 15606 postgres 20 0 12672 11M 10764 S 0.0 0.5 0:00 3 >>> postmaster >>> >>> 15917 postgres 15 0 17172 16M 15220 S 0.0 0.8 0:00 1 >>> postmaster >>> >>> >>> >>> ---------------------------------------------------------------------- >>> - >>> -------------------------------------------------------- >>> >>> >>> >>> Postgresql.conf extract >>> >>> >>> >>> max_connections = 120 >>> >>> shared_buffers = 131072 >>> >>> sort_mem = 16384 >>> >>> vacuum_mem = 8192 >>> >>> effective_cache_size = 65536 >>> >>> >>> >>> ---------------------------------------------------------------------- >>> - >>> -------------------------------------------------------- >>> >>> >>> >>> Any ideas will be greatly appreciated. >>> >>> >>> >>> Kind regards >>> >>> Werner vd Merwe >>> >>> >>> >>> >>> >>> -- >>> 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 >>> >> >> -- >> 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 >> >> >> -- >> 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