Re: Performance Question - Mailing list pgsql-admin
| From | Thomas F.O'Connell |
|---|---|
| Subject | Re: Performance Question |
| Date | |
| Msg-id | 4ead39d3e412a38cba6096eafb765eb9@sitening.com Whole thread Raw |
| In response to | Performance Question ("Werner vd Merwe" <werner@saicom.co.za>) |
| Responses |
Re: Performance Question
(Brad Nicholson <bnichols@ca.afilias.info>)
|
| List | pgsql-admin |
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 > >
pgsql-admin by date: