Re: Performance Question - Mailing list pgsql-admin

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