Re: Performance Question - Mailing list pgsql-admin

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
>>>>
>>>>  
>>>>
>>>>
>>>>
>>>> --
>>>>  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