Re: Performance Question - Mailing list pgsql-admin

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