Re: [ADMIN] Performance Question

[Thomas F . O'Connell]

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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]
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