Обсуждение: Performance Question
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
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 >
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 > >
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, >>> >>> >>> >>> 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 > > -- > 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 > >
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
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 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 > > -- > 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 4: Don't 'kill -9' the postmaster -- 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
Werner vd Merwe wrote: >Output of VACUUM ANALYSE VERBOSE pg_listener: > >Query OK, 0 rows affected (0.06 sec) >INFO: vacuuming "pg_catalog.pg_listener" >INFO: "pg_listener": found 0 removable, 0 nonremovable row versions in 0 >pages >INFO: analyzing "pg_catalog.pg_listener" >INFO: "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows > > > No problems there. >At any given time we have between 10 and 20 IDLE connections. > > > How long have the transactions been IDLE for? Do the IDLE transactions have any locks on anything (check pg_locks)? I suspect that (gently) killing the IDLE transactions that are sitting around doing nothing will prevent you from having to restart the postmaster. -- Brad Nicholson Database Administrator, Afilias Canada Corp. >-----Original Message----- >From: pgsql-admin-owner@postgresql.org >[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson >Sent: 14 March 2005 07:10 PM >To: PgSQL Admin >Subject: Re: [ADMIN] Performance Question > >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; > > >
Hi Brad,
Just before I carry on - I am not sure if top-posting is 'allowed' on this
list, if not, please let me know and I'll stop :)
Will gently be level 15?
There are a couple of locks, both exclusive and shared...
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
Sent: 15 March 2005 05:33 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance Question
Werner vd Merwe wrote:
>Output of VACUUM ANALYSE VERBOSE pg_listener:
>
>Query OK, 0 rows affected (0.06 sec)
>INFO: vacuuming "pg_catalog.pg_listener"
>INFO: "pg_listener": found 0 removable, 0 nonremovable row versions in 0
>pages
>INFO: analyzing "pg_catalog.pg_listener"
>INFO: "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows
>
>
>
No problems there.
>At any given time we have between 10 and 20 IDLE connections.
>
>
>
How long have the transactions been IDLE for? Do the IDLE transactions
have any locks on anything (check pg_locks)?
I suspect that (gently) killing the IDLE transactions that are sitting
around doing nothing will prevent you from having to restart the postmaster.
--
Brad Nicholson
Database Administrator, Afilias Canada Corp.
>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
>Sent: 14 March 2005 07:10 PM
>To: PgSQL Admin
>Subject: Re: [ADMIN] Performance Question
>
>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;
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
-----Original Message----- From: weiping [mailto:laser@tacacs.zhengmai.net.cn] Sent: 15 March 2005 05:55 PM To: Werner vd Merwe Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Performance Question what' your JDBC version? if it's pretty old, then upgrade to newest one is a bet. Don't know if it could solve the problem, but old version of JDBC did have some problem in transaction handling, we've experienced such problem not so long before. regards laser -- Hi, We are using JDK1.4.2_06 Regards Werner -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
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 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 >> >> -- >> 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 4: Don't 'kill -9' the postmaster > > -- > 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 6: Have you searched our list archives? > > http://archives.postgresql.org
what' your JDBC version? if it's pretty old, then upgrade to newest one is a bet. Don't know if it could solve the problem, but old version of JDBC did have some problem in transaction handling, we've experienced such problem not so long before. regards laser
No, I mean JDBC version, not JDK version. go to jdbc.postgresql.org and download newest version of JDBC to have a try. regards laser
Hi Thomas, We perform a full VACUUM and ANALYSE every night, and a ANALYSE on selected tables throughout the day. Platform is Redhat 9.0 and PG 7.4 Snippet of postgresql.conf: Shared_buffers = 32768 Effective_cache_size = 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 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 HOWTOs 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 >> >> -- >> 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 4: Don't 'kill -9' the postmaster > > -- > 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 6: Have you searched our list archives? > > http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 2005/03/18 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 2005/03/21
Have you considered trying pg_autovacuum, which is in contrib? It actually sets and monitors thresholds to try to determine dynamically 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 > selected > tables throughout the day. > > Platform is Redhat 9.0 and PG 7.4 > > Snippet of postgresql.conf: > Shared_buffers = 32768 > Effective_cache_size = 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 >>>> 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