Re: Performance Question - Mailing list pgsql-admin

It doesn't make all that much more sense. I'd keep posting to the lists
to let other people continue to take a crack at it. Is the system
dedicated entirely to postgres? Are you VACUUMing? What kinds of
operations are being performed and how much data is in the database?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Mar 14, 2005, at 10:03 AM, Werner vd Merwe wrote:

> Hi Thomas,
>
> Thank you for your response.
>
> Performance does not pick up after a service restart, needs to be a
> system
> restart.
>
> If we do not do that restart, then things are 'broken bad', as the
> system
> becomes incredibly slow. Not broken after the vacuum, it is a gradual
> decline in performance.
>
> Hope that makes more sense.
>
> Many thanks
>
>
> -----Original Message-----
> From: Thomas F. O'Connell [mailto:tfo@sitening.com]
> Sent: 14 March 2005 05:59 PM
> To: Werner vd Merwe
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Performance Question
>
> I think you need to provide more information to get any help with your
> setup.
>
> For one thing, why are you "restarting"? Are you restarting the server?
> Postgres? In general, there should be no need to restart either.
>
> Next, what do you mean by "broken bad" after a full vacuum?
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Mar 14, 2005, at 1:08 AM, Werner vd Merwe wrote:
>
>> Hi guys,
>>
>>  
>>
>> I have been browsing around and reading up on PostgreSQL performance
>> to try and tweak our system at the office, as its performance is not
>> that great.
>>
>>  
>>
>> Many people say that PG is a great DB, and I know that our problems
>> are purely a setup issue.
>>
>>  
>>
>> After a complete server restart, the system is ok, not fast, but
>> workable, the problem are increased by the fact that the PG slows
>> down, for example, we run a full vacuum every night, and after a
>> restart, it takes about an hour and half, which increases to about 3
>> hours in two weeks. At that stage everything is broken bad, and we are
>> forced to do a restart again.
>>
>>  
>>
>> I have tried to follow as much of the documents and HOWTO’s on the
>> web, but still have some issues.
>>
>>   
>>
>> Here is some info (did a restart last night, so this is best
>> performance atm)
>>
>>  
>>
>> System:
>>
>> Dual XEON 2.4GHz
>>
>> 3Gb RAM
>>
>>  
>>
>> Dedicated to PG
>>
>>  
>>
>> Type of apps:
>>
>> Mostly JDBC queries running via Tomcat.
>>
>>  
>>
>>  
>>
>> ----------------------------------------------------------------------
>> -
>> --------------------------------------------------------
>>
>> ------ Shared Memory Attach/Detach/Change Times --------
>>
>> shmid      owner      attached             detached    
>>         changed
>>
>>  
>>
>> 131072     postgres    Mar 14 09:02:43      Mar 14 09:02:23      Mar
>> 12 15:14:49
>>
>>  
>>
>>  
>>
>> ------ Shared Memory Operation/Change Times --------
>>
>> shmid    owner      last-op                    last-changed
>>
>> 786432   postgres    Sat Mar 12 15:14:49 2005   Sat Mar 12 15:14:49
>> 2005
>>
>> 819201   postgres    Sat Mar 12 15:14:49 2005   Sat Mar 12 15:14:49
>> 2005
>>
>> 851970   postgres    Sat Mar 12 15:14:49 2005   Sat Mar 12 15:14:49
>> 2005
>>
>> 884739   postgres    Sat Mar 12 15:14:49 2005   Sat Mar 12 15:14:49
>> 2005
>>
>> 917508   postgres    Sat Mar 12 15:14:49 2005   Sat Mar 12 15:14:49
>> 2005
>>
>> 950277   postgres    Mon Mar 14 09:02:44 2005   Mon Mar 14 09:02:43
>> 2005
>>
>> 983046   postgres    Mon Mar 14 09:02:43 2005   Mon Mar 14 09:02:23
>> 2005
>>
>> 1015815  postgres    Mon Mar 14 09:02:44 2005   Mon Mar 14 09:02:43
>> 2005
>>
>>  
>>
>> ------ Message Queues Send/Recv/Change Times --------
>>
>> msqid    owner      send                 recv                 change
>>
>>  
>>
>>  
>>
>> ------ Shared Memory Status --------
>>
>> segments allocated 1
>>
>> pages allocated 266324
>>
>> pages resident  257206
>>
>> pages swapped   8619
>>
>> Swap performance: 0 attempts     0 successes
>>
>>  
>>
>> ----------------------------------------------------------------------
>> -
>> --------------------------------------------------------
>>
>>  
>>
>> 09:03:48  up 2 days, 10:12,  3 users,  load average: 0.15, 0.36, 0.31
>>
>> 64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped
>>
>> CPU0 states:   2.2% user   1.4% system    0.0% nice   0.0% iowait 
>> 95.4% idle
>>
>> CPU1 states:   1.3% user   0.3% system    0.0% nice   0.0% iowait 
>> 97.4% idle
>>
>> CPU2 states:   2.3% user   0.1% system    0.0% nice   0.0% iowait 
>> 97.1% idle
>>
>> CPU3 states:   0.0% user   0.3% system    0.0% nice   0.0% iowait 
>> 99.2% idle
>>
>> Mem:  2063932k av, 2017520k used,   46412k free,       0k shrd,  
>> 79388k buff
>>
>>                    1434408k actv,     232k in_d,   46268k in_c
>>
>> Swap: 2040244k av,   63676k used, 1976568k free                
>> 1678480k cached
>>
>>  
>>
>>   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU
>> COMMAND
>>
>> 15985 postgres  15   0 88796  86M 86192 S     3.4  4.2   0:00   0
>> postmaster
>>
>> 16108 postgres  20   0 21252  20M 19236 S     1.6  1.0   0:00   1
>> postmaster
>>
>> 16094 postgres  18   0 12188  11M 10292 S     0.4  0.5   0:00   3
>> postmaster
>>
>> 24846 postgres  15   0   432  228   120 S     0.0  0.0   0:04   2
>> postmaster
>>
>> 24851 postgres  15   0  1320 1044    24 S     0.0  0.0   0:06   2
>> postmaster
>>
>> 24852 postgres  15   0   628  400   128 S     0.0  0.0   0:18   2
>> postmaster
>>
>> 11207 postgres  20   0 11536  10M  9700 S     0.0  0.5   0:00   2
>> postmaster
>>
>> 15113 postgres  20   0 20908  20M 18796 S     0.0  0.9   0:00   2
>> postmaster
>>
>> 15114 postgres  20   0 12732  11M 10792 S     0.0  0.5   0:00   2
>> postmaster
>>
>> 15606 postgres  20   0 12672  11M 10764 S     0.0  0.5   0:00   3
>> postmaster
>>
>> 15917 postgres  15   0 17172  16M 15220 S     0.0  0.8   0:00   1
>> postmaster
>>
>>  
>>
>> ----------------------------------------------------------------------
>> -
>> --------------------------------------------------------
>>
>>  
>>
>> Postgresql.conf extract
>>
>>  
>>
>> max_connections = 120
>>
>> shared_buffers = 131072
>>
>> sort_mem = 16384
>>
>> vacuum_mem = 8192
>>
>> effective_cache_size = 65536
>>
>>  
>>
>> ----------------------------------------------------------------------
>> -
>> --------------------------------------------------------
>>
>>  
>>
>> Any ideas will be greatly appreciated.
>>
>>  
>>
>> Kind regards
>>
>> Werner vd Merwe
>>
>>  
>>
>>
>>
>> --
>>  No virus found in this outgoing message.
>>  Checked by AVG Anti-Virus.
>>  Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
>>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
>
>