pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance question
21+ messages / 11 participants
[nested] [flat]

* Performance question
@ 2002-02-26 09:39  Brian McCane <bmccane@mccons.net>
  0 siblings, 1 reply; 21+ messages in thread

From: Brian McCane @ 2002-02-26 09:39 UTC (permalink / raw)
  To: pgsql-admin

First of all, I have somewhat fixed my locking problems from before, at
least to the point that the app is mostly usable.  I will tweak it more
later, when I have some more free time to myself :)

Now:
	I use an english alphabet exclusively in an app I am working on
for another customer.  If I need to put non-english characters, I use the
HTML encoding functions from HTML::Entities in Perl.  This causes me to
store strings like:

You&#146;re antique vase cost &yen;5.000.000

Okay, you get the picture any way.

Since I am using a strict alphabet like this, would it be faster to build
PostgreSQL without Multibyte support?  Or has the indexing, etc. been
optimized so much for multibyte that it is actually faster than single
byte stuff?

- brian


Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"





^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance question
@ 2002-02-26 15:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Brian McCane <bmccane@mccons.net>
  0 siblings, 0 replies; 21+ messages in thread

From: Tom Lane @ 2002-02-26 15:15 UTC (permalink / raw)
  To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin

Brian McCane <bmccane@mccons.net> writes:
> Since I am using a strict alphabet like this, would it be faster to build
> PostgreSQL without Multibyte support?  Or has the indexing, etc. been
> optimized so much for multibyte that it is actually faster than single
> byte stuff?

A non-multibyte build will certainly be faster than a multibyte build.
How much is hard to say, especially without any specific operation mix
in mind.  We'd be interested to hear what sort of results you get ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Performance question
@ 2003-03-05 18:11  Bob Smith <bsmith@h-e.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Bob Smith @ 2003-03-05 18:11 UTC (permalink / raw)
  To: pgsql-admin


When I execute a query on a new connection, the performance is many 
times slower than if the query is repeated.  In other words, if I start 
psql, execute the query, then repeat it immediately, the second time it 
takes only about 20% as long to execute.  Now here's the confusing 
part, if I exit psql then start it up again, the same thing will occur 
on the new connection as well, the first execution takes 5x as long 
again.  I don't understand this, it would make sense to me that the 
second execution being faster is due to disk caching on the server, but 
then why is it slower again on every new connection?  Disk caching 
should benefit all current and new connections until the cache is 
flushed, which on this server shouldn't happen for a long time, the 
load is light and it has lots of RAM.  Is Postgres doing some kind of 
caching itself that lasts only for the life of one backend process?  If 
so, is there any way to make this caching persistent across backends?

Server particulars:

Postgres 7.2.1, Mac OS X Server 10.1.5, dual 1GHz CPUs, 1.5GB memory

Thanks!

Bob Smith
Hammett & Edison, Inc.
bsmith@h-e.com




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Performance Question
@ 2005-03-14 07:08  Werner vd Merwe <werner@saicom.co.za>
  0 siblings, 2 replies; 21+ messages in thread

From: Werner vd Merwe @ 2005-03-14 07:08 UTC (permalink / raw)
  To: pgsql-admin

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
 

^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-14 15:58  Thomas F.O'Connell <tfo@sitening.com>
  parent: Werner vd Merwe <werner@saicom.co.za>
  1 sibling, 0 replies; 21+ messages in thread

From: Thomas F.O'Connell @ 2005-03-14 15:58 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin

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
>




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-14 16:10  Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 1 reply; 21+ messages in thread

From: Thomas F.O'Connell @ 2005-03-14 16:10 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: 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
>
>




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-14 16:36  Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Thomas F.O'Connell @ 2005-03-14 16:36 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: 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
>
>




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-14 17:10  Brad Nicholson <bnichols@ca.afilias.info>
  parent: Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Brad Nicholson @ 2005-03-14 17:10 UTC (permalink / raw)
  To: pgsql-admin

I'm wondering if long running transacations might be the cause (you'll 
likely want to do this while perfomance is suffering).

Have a look at pg_stat_activity and see if there are any long running 
transacations (or any idle transactions).

I'd also be curious to see the output  of the following:

VACUUM ANALYZE VERBOSE pg_listener;

-- 
Brad Nicholson 
Database Administrator, Afilias Canada Corp. 



Thomas F.O'Connell wrote:

> 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





^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-15 06:33  Werner vd Merwe <werner@saicom.co.za>
  0 siblings, 1 reply; 21+ messages in thread

From: Werner vd Merwe @ 2005-03-15 06:33 UTC (permalink / raw)
  To: 'Thomas F.O'Connell' <tfo@sitening.com>

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





^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-15 15:32  Brad Nicholson <bnichols@ca.afilias.info>
  0 siblings, 1 reply; 21+ messages in thread

From: Brad Nicholson @ 2005-03-15 15:32 UTC (permalink / raw)
  To: pgsql-admin

Werner vd Merwe wrote:

>Output of VACUUM ANALYSE VERBOSE pg_listener:
>
>Query OK, 0 rows affected (0.06 sec)
>INFO:  vacuuming "pg_catalog.pg_listener"
>INFO:  "pg_listener": found 0 removable, 0 nonremovable row versions in 0
>pages
>INFO:  analyzing "pg_catalog.pg_listener"
>INFO:  "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows
>
>  
>
No problems there.

>At any given time we have between 10 and 20 IDLE connections.
>
>  
>
How long have the transactions been IDLE for?  Do the IDLE transactions 
have any locks on anything (check pg_locks)?

I suspect that (gently) killing the IDLE transactions  that are sitting 
around doing nothing will prevent you from having to restart the postmaster.

-- 
Brad Nicholson  
Database Administrator, Afilias Canada Corp. 



>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
>Sent: 14 March 2005 07:10 PM
>To: PgSQL Admin
>Subject: Re: [ADMIN] Performance Question
>
>I'm wondering if long running transacations might be the cause (you'll 
>likely want to do this while perfomance is suffering).
>
>Have a look at pg_stat_activity and see if there are any long running 
>transacations (or any idle transactions).
>
>I'd also be curious to see the output  of the following:
>
>VACUUM ANALYZE VERBOSE pg_listener;
>
>  
>





^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-15 15:54  weiping <laser@tacacs.zhengmai.net.cn>
  parent: Werner vd Merwe <werner@saicom.co.za>
  1 sibling, 1 reply; 21+ messages in thread

From: weiping @ 2005-03-15 15:54 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin

what' your JDBC version?
if it's pretty old, then upgrade to newest one is a bet.
Don't know if it could solve the problem, but old
version of JDBC did have some problem in transaction
handling, we've experienced such problem not so long
before.

regards laser



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-16 06:28  Werner vd Merwe <werner@saicom.co.za>
  parent: Brad Nicholson <bnichols@ca.afilias.info>
  0 siblings, 0 replies; 21+ messages in thread

From: Werner vd Merwe @ 2005-03-16 06:28 UTC (permalink / raw)
  To: pgsql-admin

Hi Brad,

Just before I carry on - I am not sure if top-posting is 'allowed' on this
list, if not, please let me know and I'll stop  :)

Will gently be level 15?

There are a couple of locks, both exclusive and shared... 


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
Sent: 15 March 2005 05:33 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance Question

Werner vd Merwe wrote:

>Output of VACUUM ANALYSE VERBOSE pg_listener:
>
>Query OK, 0 rows affected (0.06 sec)
>INFO:  vacuuming "pg_catalog.pg_listener"
>INFO:  "pg_listener": found 0 removable, 0 nonremovable row versions in 0
>pages
>INFO:  analyzing "pg_catalog.pg_listener"
>INFO:  "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows
>
>  
>
No problems there.

>At any given time we have between 10 and 20 IDLE connections.
>
>  
>
How long have the transactions been IDLE for?  Do the IDLE transactions 
have any locks on anything (check pg_locks)?

I suspect that (gently) killing the IDLE transactions  that are sitting 
around doing nothing will prevent you from having to restart the postmaster.

-- 
Brad Nicholson  
Database Administrator, Afilias Canada Corp. 



>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
>Sent: 14 March 2005 07:10 PM
>To: PgSQL Admin
>Subject: Re: [ADMIN] Performance Question
>
>I'm wondering if long running transacations might be the cause (you'll 
>likely want to do this while perfomance is suffering).
>
>Have a look at pg_stat_activity and see if there are any long running 
>transacations (or any idle transactions).
>
>I'd also be curious to see the output  of the following:
>
>VACUUM ANALYZE VERBOSE pg_listener;
>
>  
>



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
 




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-16 06:45  Werner vd Merwe <werner@saicom.co.za>
  parent: weiping <laser@tacacs.zhengmai.net.cn>
  0 siblings, 1 reply; 21+ messages in thread

From: Werner vd Merwe @ 2005-03-16 06:45 UTC (permalink / raw)
  To: pgsql-admin



-----Original Message-----
From: weiping [mailto:laser@tacacs.zhengmai.net.cn] 
Sent: 15 March 2005 05:55 PM
To: Werner vd Merwe
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance Question

what' your JDBC version?
if it's pretty old, then upgrade to newest one is a bet.
Don't know if it could solve the problem, but old
version of JDBC did have some problem in transaction
handling, we've experienced such problem not so long
before.

regards laser

-- 
Hi,

We are using JDK1.4.2_06

Regards
Werner 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
 




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-19 18:09  Thomas F.O'Connell <tfo@sitening.com>
  parent: Werner vd Merwe <werner@saicom.co.za>
  0 siblings, 1 reply; 21+ messages in thread

From: Thomas F.O'Connell @ 2005-03-19 18:09 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-21 03:40  Weiping <laser@qmail.zhengmai.net.cn>
  parent: Werner vd Merwe <werner@saicom.co.za>
  0 siblings, 0 replies; 21+ messages in thread

From: Weiping @ 2005-03-21 03:40 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin

No, I mean JDBC version, not JDK version.
go to jdbc.postgresql.org and download newest version of JDBC to
have a try.

regards laser



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-22 06:31  Werner vd Merwe <werner@saicom.co.za>
  parent: Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 1 reply; 21+ messages in thread

From: Werner vd Merwe @ 2005-03-22 06:31 UTC (permalink / raw)
  To: 'Thomas F. O'Connell' <tfo@sitening.com>; +Cc: 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
 




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance Question
@ 2005-03-22 15:55  Thomas F.O'Connell <tfo@sitening.com>
  parent: Werner vd Merwe <werner@saicom.co.za>
  0 siblings, 0 replies; 21+ messages in thread

From: Thomas F.O'Connell @ 2005-03-22 15:55 UTC (permalink / raw)
  To: Werner vd Merwe <werner@saicom.co.za>; +Cc: 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



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Performance question
@ 2006-12-02 05:18  Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 1 reply; 21+ messages in thread

From: Benjamin Krajmalnik @ 2006-12-02 05:18 UTC (permalink / raw)
  To: pgsql-admin

I am battling a performance issue and was wondering if someone could
help.  PostgreSQL 8.1.5, FreeBSD.

I have a very intense stored procedure which performs real time
aggregation of data.

I captured the stored procedure calls from a production system and
pumped them through psql, logging duration.
The stored procedure ran at about 30ms per stored procedure call - which
was significantly faster than the previous production system.  Based on
these results, I was extremely optimistic and migrated to the new
server.

Now that we turned up the new server, the stored procedure is taking
over 250ms.
These are the execution times as recorded by enable "all" logging.

The only difference is that when I pumped the data in the test
environment, I pumped it by reading the script via psql, whereas the
current system is calling the stored procedure via ODBC through a
100Mbit ethernet connection.

I cannot explain the difference between the 2 scenarios.  Does the
duration include the overhead of the ODBC layer?

Now, the second part that was troubling relates to the execution
planner.


Two of the tables in which data is being entered are partitioned tables.
Data is being routed to the partition by using rules.  Each partition
has a check constraint on a date range.  Constraint exclusion is on.

The first partitioned table is very simple, and the stored procedure
simply inserts a record for each test which is executed.  These are
later used by a procedure running in the background which calculates
statistical data.

My problem is with the second partitioned table.  As part of my
troubleshooting, I omitted the code from the stored procedure which
accesses this data.  In this table, I attempt to retrieve the daily
record for a specific test.  If it is not found, after aggregating data
I insert it.  If the record already exists, data is aggregated and the
record is updated.

The key is composed of a date field (monthdate) and an int4 field
(kstestsysid).
The check constraints are of the form of:

ALTER TABLE tblkssnapshot12
  ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >= '2006-12-01
00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
00:00:00'::timestamp without time zone);

I ran a sample query which would be issued by the stored procedure with
the explain option.
The query was:

select monthdate, kstestssysid from tblkssnapshotdaily where monthdate =
'2006-12-01' and kstestssysid = 3143

The explain is as follows:

select monthdate, kstestssysid from tblkssnapshotdaily where monthdate =
'2006-12-01' and kstestssysid = 3143
Result  (cost=0.00..122.49 rows=25 width=8)
  ->  Append  (cost=0.00..122.49 rows=25 width=8)
        ->  Index Scan using tblkssnapshotdaily_idx_monthtest on
tblkssnapshotdaily  (cost=0.00..3.52 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot01_idx_monthtest on
tblkssnapshot01 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot02_idx_monthtest on
tblkssnapshot02 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot03_idx_monthtest on
tblkssnapshot03 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot04_idx_monthtest on
tblkssnapshot04 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot05_idx_monthtest on
tblkssnapshot05 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot06_idx_monthtest on
tblkssnapshot06 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot07_idx_monthtest on
tblkssnapshot07 tblkssnapshotdaily  (cost=0.00..5.94 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot08_idx_monthtest on
tblkssnapshot08 tblkssnapshotdaily  (cost=0.00..4.73 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot09_idx_monthtest on
tblkssnapshot09 tblkssnapshotdaily  (cost=0.00..5.56 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot10_idx_monthtest on
tblkssnapshot10 tblkssnapshotdaily  (cost=0.00..5.75 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot11_idx_monthtest on
tblkssnapshot11 tblkssnapshotdaily  (cost=0.00..5.81 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot12_idx_monthtest on
tblkssnapshot12 tblkssnapshotdaily  (cost=0.00..4.33 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot13_idx_monthtest on
tblkssnapshot13 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot14_idx_monthtest on
tblkssnapshot14 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot15_idx_monthtest on
tblkssnapshot15 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot16_idx_monthtest on
tblkssnapshot16 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot17_idx_monthtest on
tblkssnapshot17 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot18_idx_monthtest on
tblkssnapshot18 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot19_idx_monthtest on
tblkssnapshot19 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot20_idx_monthtest on
tblkssnapshot20 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot21_idx_monthtest on
tblkssnapshot21 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot22_idx_monthtest on
tblkssnapshot22 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot23_idx_monthtest on
tblkssnapshot23 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot24_idx_monthtest on
tblkssnapshot24 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))

If constraint exclusion is on, why are index scans taking place on all
of the tables and not only on the only partitioned table for which the
constraint exists?

I went to a partitioned table thinking this would improve performance
from the previous schema in which all of the data was in the parent
table, but the end result was significantly slower performance (by
orders of magnitude).

 



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance question
@ 2006-12-05 00:53  Jim Nasby <decibel@decibel.org>
  parent: Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Jim Nasby @ 2006-12-05 00:53 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin

Are you sure you're doing an apples-apples comparison? Is the load on  
both machines the same, or does production have extra stuff running?  
Have you tried your original test again in the same setup without  
ODBC to eliminate that possibility? (or redone your original test  
with ODBC).

As for the constraint exclusion, try changing the query so that  
'2006-12-01' is cast to a timestamp (or change the constraints so  
that you're constraining dates... the check constraints should really  
match the type of the underlying table).

On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:

> I am battling a performance issue and was wondering if someone could
> help.  PostgreSQL 8.1.5, FreeBSD.
>
> I have a very intense stored procedure which performs real time
> aggregation of data.
>
> I captured the stored procedure calls from a production system and
> pumped them through psql, logging duration.
> The stored procedure ran at about 30ms per stored procedure call -  
> which
> was significantly faster than the previous production system.   
> Based on
> these results, I was extremely optimistic and migrated to the new
> server.
>
> Now that we turned up the new server, the stored procedure is taking
> over 250ms.
> These are the execution times as recorded by enable "all" logging.
>
> The only difference is that when I pumped the data in the test
> environment, I pumped it by reading the script via psql, whereas the
> current system is calling the stored procedure via ODBC through a
> 100Mbit ethernet connection.
>
> I cannot explain the difference between the 2 scenarios.  Does the
> duration include the overhead of the ODBC layer?
>
> Now, the second part that was troubling relates to the execution
> planner.
>
>
> Two of the tables in which data is being entered are partitioned  
> tables.
> Data is being routed to the partition by using rules.  Each partition
> has a check constraint on a date range.  Constraint exclusion is on.
>
> The first partitioned table is very simple, and the stored procedure
> simply inserts a record for each test which is executed.  These are
> later used by a procedure running in the background which calculates
> statistical data.
>
> My problem is with the second partitioned table.  As part of my
> troubleshooting, I omitted the code from the stored procedure which
> accesses this data.  In this table, I attempt to retrieve the daily
> record for a specific test.  If it is not found, after aggregating  
> data
> I insert it.  If the record already exists, data is aggregated and the
> record is updated.
>
> The key is composed of a date field (monthdate) and an int4 field
> (kstestsysid).
> The check constraints are of the form of:
>
> ALTER TABLE tblkssnapshot12
>   ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >= '2006-12-01
> 00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
> 00:00:00'::timestamp without time zone);
>
> I ran a sample query which would be issued by the stored procedure  
> with
> the explain option.
> The query was:
>
> select monthdate, kstestssysid from tblkssnapshotdaily where  
> monthdate =
> '2006-12-01' and kstestssysid = 3143
>
> The explain is as follows:
>
> select monthdate, kstestssysid from tblkssnapshotdaily where  
> monthdate =
> '2006-12-01' and kstestssysid = 3143
> Result  (cost=0.00..122.49 rows=25 width=8)
>   ->  Append  (cost=0.00..122.49 rows=25 width=8)
>         ->  Index Scan using tblkssnapshotdaily_idx_monthtest on
> tblkssnapshotdaily  (cost=0.00..3.52 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot01_idx_monthtest on
> tblkssnapshot01 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot02_idx_monthtest on
> tblkssnapshot02 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot03_idx_monthtest on
> tblkssnapshot03 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot04_idx_monthtest on
> tblkssnapshot04 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot05_idx_monthtest on
> tblkssnapshot05 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot06_idx_monthtest on
> tblkssnapshot06 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot07_idx_monthtest on
> tblkssnapshot07 tblkssnapshotdaily  (cost=0.00..5.94 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot08_idx_monthtest on
> tblkssnapshot08 tblkssnapshotdaily  (cost=0.00..4.73 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot09_idx_monthtest on
> tblkssnapshot09 tblkssnapshotdaily  (cost=0.00..5.56 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot10_idx_monthtest on
> tblkssnapshot10 tblkssnapshotdaily  (cost=0.00..5.75 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot11_idx_monthtest on
> tblkssnapshot11 tblkssnapshotdaily  (cost=0.00..5.81 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot12_idx_monthtest on
> tblkssnapshot12 tblkssnapshotdaily  (cost=0.00..4.33 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot13_idx_monthtest on
> tblkssnapshot13 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot14_idx_monthtest on
> tblkssnapshot14 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot15_idx_monthtest on
> tblkssnapshot15 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot16_idx_monthtest on
> tblkssnapshot16 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot17_idx_monthtest on
> tblkssnapshot17 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot18_idx_monthtest on
> tblkssnapshot18 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot19_idx_monthtest on
> tblkssnapshot19 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot20_idx_monthtest on
> tblkssnapshot20 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot21_idx_monthtest on
> tblkssnapshot21 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot22_idx_monthtest on
> tblkssnapshot22 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot23_idx_monthtest on
> tblkssnapshot23 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>         ->  Index Scan using tblkssnapshot24_idx_monthtest on
> tblkssnapshot24 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
>               Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>
> If constraint exclusion is on, why are index scans taking place on all
> of the tables and not only on the only partitioned table for which the
> constraint exists?
>
> I went to a partitioned table thinking this would improve performance
> from the previous schema in which all of the data was in the parent
> table, but the end result was significantly slower performance (by
> orders of magnitude).
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)





^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance question
@ 2006-12-05 03:25  Jim C. Nasby <decibel@decibel.org>
  0 siblings, 0 replies; 21+ messages in thread

From: Jim C. Nasby @ 2006-12-05 03:25 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin

Please cc the list so others can learn and help.

Yes, if ODBC is tearing the connection down after every call,
performance *WILL* suck. Setup some kind of persistent connection.
Better yet, don't make lots of tiny calls to the database if you can
avoid it.

On Mon, Dec 04, 2006 at 06:28:03PM -0700, Benjamin Krajmalnik wrote:
> Hi Jim,
> 
> Apples to apples (as best as I can tell).
> The test procedure was as follows:
> 
> I captured the SQL statements which call the stored procedures from our
> monitoring box to a file.
> I copied the file to the FreeBSD data server.
> Executed psql, and pumped the file to it through \i filename. 
> Execution speed for the code in question was sub 10 seconds.
> Running the query directly via ODBC is taking about 60 ms.
> 
> The numbers I just stated above are after I removed the code which was
> accessing the one partitioned table where the constraint exclusion was
> not taking place (more on that later).  This code happened to be
> updating a daily aggregate - so each SP call was selecting the test
> record for the day and aggregating data.  I ended up adding a few more
> fields to a records which keeps some data in arrays for quick access to
> generate our graphs, and now I run a scheduled task at midnight which
> takes the previous days aggregates and creates our snapshot records
> (which we use for historical trending of up to 24 months).  This process
> now takes about 5 seconds, whereas running it through the stored
> procedure it was adding almost 200 miliseconds to the execution time).
> I assume this was caused by the fact that there was a select - and the
> execution planner was not isolating to the single partitioned table,
> followed by either an insert or another update.
> The new code is much more efficient and loads the db much less, so it
> will be the preferred method.
> Right now the datra path between the monitoring appliacne and the data
> server is 100Mbit.  We are moving it to Gig tonight - so at least I hope
> I will realize some speed advantages.  I have been monitoring the
> switch, and the actual throughput is only 150kbit/sec, so I do not have
> a data throughput bpttlenecg, but rather I suspect latencies in the ODBC
> connection.
> 
> The item I found interesting is that the statement duration was much
> longer.
> What I am thinking is that when running through psql, it is using a
> local pipe.
> When running through ODBC, each stored procedure call is creating a
> connection and then disconnecting, so it has added a huge overhead.  I
> think the duration is including the time it is taking to
> connect/authenticate disconnect/teardown.
> 
> Thanks for the tip on the constraint exclusion.
> I need to go back and see how we are querying it for the reports, and
> will adjust it accordingly.
> Does it make a difference if it is timestamp or timestamp without
> timezone?
> 
> 
> > -----Original Message-----
> > From: Jim Nasby [mailto:decibel@decibel.org] 
> > Sent: Monday, December 04, 2006 5:53 PM
> > To: Benjamin Krajmalnik
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Performance question
> > 
> > Are you sure you're doing an apples-apples comparison? Is the 
> > load on both machines the same, or does production have extra 
> > stuff running?  
> > Have you tried your original test again in the same setup 
> > without ODBC to eliminate that possibility? (or redone your 
> > original test with ODBC).
> > 
> > As for the constraint exclusion, try changing the query so 
> > that '2006-12-01' is cast to a timestamp (or change the 
> > constraints so that you're constraining dates... the check 
> > constraints should really match the type of the underlying table).
> > 
> > On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:
> > 
> > > I am battling a performance issue and was wondering if 
> > someone could 
> > > help.  PostgreSQL 8.1.5, FreeBSD.
> > >
> > > I have a very intense stored procedure which performs real time 
> > > aggregation of data.
> > >
> > > I captured the stored procedure calls from a production system and 
> > > pumped them through psql, logging duration.
> > > The stored procedure ran at about 30ms per stored procedure call - 
> > > which
> > > was significantly faster than the previous production system.   
> > > Based on
> > > these results, I was extremely optimistic and migrated to the new 
> > > server.
> > >
> > > Now that we turned up the new server, the stored procedure 
> > is taking 
> > > over 250ms.
> > > These are the execution times as recorded by enable "all" logging.
> > >
> > > The only difference is that when I pumped the data in the test 
> > > environment, I pumped it by reading the script via psql, 
> > whereas the 
> > > current system is calling the stored procedure via ODBC through a 
> > > 100Mbit ethernet connection.
> > >
> > > I cannot explain the difference between the 2 scenarios.  Does the 
> > > duration include the overhead of the ODBC layer?
> > >
> > > Now, the second part that was troubling relates to the execution 
> > > planner.
> > >
> > >
> > > Two of the tables in which data is being entered are partitioned 
> > > tables.
> > > Data is being routed to the partition by using rules.  Each 
> > partition 
> > > has a check constraint on a date range.  Constraint exclusion is on.
> > >
> > > The first partitioned table is very simple, and the stored 
> > procedure 
> > > simply inserts a record for each test which is executed.  These are 
> > > later used by a procedure running in the background which 
> > calculates 
> > > statistical data.
> > >
> > > My problem is with the second partitioned table.  As part of my 
> > > troubleshooting, I omitted the code from the stored procedure which 
> > > accesses this data.  In this table, I attempt to retrieve the daily 
> > > record for a specific test.  If it is not found, after aggregating 
> > > data I insert it.  If the record already exists, data is aggregated 
> > > and the record is updated.
> > >
> > > The key is composed of a date field (monthdate) and an int4 field 
> > > (kstestsysid).
> > > The check constraints are of the form of:
> > >
> > > ALTER TABLE tblkssnapshot12
> > >   ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >= 
> > '2006-12-01 
> > > 00:00:00'::timestamp without time zone AND monthdate < '2007-01-01 
> > > 00:00:00'::timestamp without time zone);
> > >
> > > I ran a sample query which would be issued by the stored procedure 
> > > with the explain option.
> > > The query was:
> > >
> > > select monthdate, kstestssysid from tblkssnapshotdaily 
> > where monthdate 
> > > = '2006-12-01' and kstestssysid = 3143
> > >
> > > The explain is as follows:
> > >
> > > select monthdate, kstestssysid from tblkssnapshotdaily 
> > where monthdate 
> > > = '2006-12-01' and kstestssysid = 3143 Result  (cost=0.00..122.49 
> > > rows=25 width=8)
> > >   ->  Append  (cost=0.00..122.49 rows=25 width=8)
> > >         ->  Index Scan using tblkssnapshotdaily_idx_monthtest on 
> > > tblkssnapshotdaily  (cost=0.00..3.52 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot01_idx_monthtest on
> > > tblkssnapshot01 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot02_idx_monthtest on
> > > tblkssnapshot02 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot03_idx_monthtest on
> > > tblkssnapshot03 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot04_idx_monthtest on
> > > tblkssnapshot04 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot05_idx_monthtest on
> > > tblkssnapshot05 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot06_idx_monthtest on
> > > tblkssnapshot06 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot07_idx_monthtest on
> > > tblkssnapshot07 tblkssnapshotdaily  (cost=0.00..5.94 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot08_idx_monthtest on
> > > tblkssnapshot08 tblkssnapshotdaily  (cost=0.00..4.73 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot09_idx_monthtest on
> > > tblkssnapshot09 tblkssnapshotdaily  (cost=0.00..5.56 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot10_idx_monthtest on 
> > > tblkssnapshot10 tblkssnapshotdaily  (cost=0.00..5.75 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot11_idx_monthtest on
> > > tblkssnapshot11 tblkssnapshotdaily  (cost=0.00..5.81 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot12_idx_monthtest on
> > > tblkssnapshot12 tblkssnapshotdaily  (cost=0.00..4.33 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot13_idx_monthtest on
> > > tblkssnapshot13 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot14_idx_monthtest on
> > > tblkssnapshot14 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot15_idx_monthtest on
> > > tblkssnapshot15 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot16_idx_monthtest on
> > > tblkssnapshot16 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot17_idx_monthtest on
> > > tblkssnapshot17 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot18_idx_monthtest on
> > > tblkssnapshot18 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot19_idx_monthtest on
> > > tblkssnapshot19 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot20_idx_monthtest on 
> > > tblkssnapshot20 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot21_idx_monthtest on
> > > tblkssnapshot21 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot22_idx_monthtest on
> > > tblkssnapshot22 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot23_idx_monthtest on
> > > tblkssnapshot23 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot24_idx_monthtest on
> > > tblkssnapshot24 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > (kstestssysid = 3143))
> > >
> > > If constraint exclusion is on, why are index scans taking 
> > place on all 
> > > of the tables and not only on the only partitioned table 
> > for which the 
> > > constraint exists?
> > >
> > > I went to a partitioned table thinking this would improve 
> > performance 
> > > from the previous schema in which all of the data was in the parent 
> > > table, but the end result was significantly slower performance (by 
> > > orders of magnitude).
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> > >
> > 
> > --
> > Jim Nasby                                            jim@nasby.net
> > EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> > 
> > 
> > 
> 

-- 
Jim C. Nasby, Database Architect                decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: Performance question
@ 2006-12-05 03:40  Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Benjamin Krajmalnik @ 2006-12-05 03:40 UTC (permalink / raw)
  To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-admin

Unfortunately, the application doing the logging is not written by me,
so I have to play with the deck of cards which I am dealt.
The developer stated that he uses connection pooling, but apparently the
pg odbc driver does not support it, since I can see a new connection
being made for each statement execution.
I have asked him to see if he can create one connection and maintain it
- we'll see.

I have another scenario to try out, whereby instead of making a stored
procedure call via ODBC I will insert the parameters of the SP call into
a table via simple insert statements.  If I see significant improvements
(I am not holding my breath), I will use that route and run a background
peocess on the server to issue the SP calls off of that recordset.

> -----Original Message-----
> From: Jim C. Nasby [mailto:decibel@decibel.org] 
> Sent: Monday, December 04, 2006 8:25 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Performance question
> 
> Please cc the list so others can learn and help.
> 
> Yes, if ODBC is tearing the connection down after every call, 
> performance *WILL* suck. Setup some kind of persistent connection.
> Better yet, don't make lots of tiny calls to the database if 
> you can avoid it.
> 
> On Mon, Dec 04, 2006 at 06:28:03PM -0700, Benjamin Krajmalnik wrote:
> > Hi Jim,
> > 
> > Apples to apples (as best as I can tell).
> > The test procedure was as follows:
> > 
> > I captured the SQL statements which call the stored procedures from 
> > our monitoring box to a file.
> > I copied the file to the FreeBSD data server.
> > Executed psql, and pumped the file to it through \i filename. 
> > Execution speed for the code in question was sub 10 seconds.
> > Running the query directly via ODBC is taking about 60 ms.
> > 
> > The numbers I just stated above are after I removed the 
> code which was 
> > accessing the one partitioned table where the constraint 
> exclusion was 
> > not taking place (more on that later).  This code happened to be 
> > updating a daily aggregate - so each SP call was selecting the test 
> > record for the day and aggregating data.  I ended up adding 
> a few more 
> > fields to a records which keeps some data in arrays for 
> quick access 
> > to generate our graphs, and now I run a scheduled task at midnight 
> > which takes the previous days aggregates and creates our snapshot 
> > records (which we use for historical trending of up to 24 months).  
> > This process now takes about 5 seconds, whereas running it 
> through the 
> > stored procedure it was adding almost 200 miliseconds to 
> the execution time).
> > I assume this was caused by the fact that there was a 
> select - and the 
> > execution planner was not isolating to the single 
> partitioned table, 
> > followed by either an insert or another update.
> > The new code is much more efficient and loads the db much 
> less, so it 
> > will be the preferred method.
> > Right now the datra path between the monitoring appliacne 
> and the data 
> > server is 100Mbit.  We are moving it to Gig tonight - so at least I 
> > hope I will realize some speed advantages.  I have been 
> monitoring the 
> > switch, and the actual throughput is only 150kbit/sec, so I do not 
> > have a data throughput bpttlenecg, but rather I suspect 
> latencies in 
> > the ODBC connection.
> > 
> > The item I found interesting is that the statement duration 
> was much 
> > longer.
> > What I am thinking is that when running through psql, it is using a 
> > local pipe.
> > When running through ODBC, each stored procedure call is creating a 
> > connection and then disconnecting, so it has added a huge 
> overhead.  I 
> > think the duration is including the time it is taking to 
> > connect/authenticate disconnect/teardown.
> > 
> > Thanks for the tip on the constraint exclusion.
> > I need to go back and see how we are querying it for the 
> reports, and 
> > will adjust it accordingly.
> > Does it make a difference if it is timestamp or timestamp without 
> > timezone?
> > 
> > 
> > > -----Original Message-----
> > > From: Jim Nasby [mailto:decibel@decibel.org]
> > > Sent: Monday, December 04, 2006 5:53 PM
> > > To: Benjamin Krajmalnik
> > > Cc: pgsql-admin@postgresql.org
> > > Subject: Re: [ADMIN] Performance question
> > > 
> > > Are you sure you're doing an apples-apples comparison? Is 
> the load 
> > > on both machines the same, or does production have extra stuff 
> > > running?
> > > Have you tried your original test again in the same setup without 
> > > ODBC to eliminate that possibility? (or redone your original test 
> > > with ODBC).
> > > 
> > > As for the constraint exclusion, try changing the query so that 
> > > '2006-12-01' is cast to a timestamp (or change the constraints so 
> > > that you're constraining dates... the check constraints should 
> > > really match the type of the underlying table).
> > > 
> > > On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:
> > > 
> > > > I am battling a performance issue and was wondering if
> > > someone could
> > > > help.  PostgreSQL 8.1.5, FreeBSD.
> > > >
> > > > I have a very intense stored procedure which performs real time 
> > > > aggregation of data.
> > > >
> > > > I captured the stored procedure calls from a production 
> system and 
> > > > pumped them through psql, logging duration.
> > > > The stored procedure ran at about 30ms per stored 
> procedure call - 
> > > > which
> > > > was significantly faster than the previous production system.   
> > > > Based on
> > > > these results, I was extremely optimistic and migrated 
> to the new 
> > > > server.
> > > >
> > > > Now that we turned up the new server, the stored procedure
> > > is taking
> > > > over 250ms.
> > > > These are the execution times as recorded by enable 
> "all" logging.
> > > >
> > > > The only difference is that when I pumped the data in the test 
> > > > environment, I pumped it by reading the script via psql,
> > > whereas the
> > > > current system is calling the stored procedure via ODBC 
> through a 
> > > > 100Mbit ethernet connection.
> > > >
> > > > I cannot explain the difference between the 2 
> scenarios.  Does the 
> > > > duration include the overhead of the ODBC layer?
> > > >
> > > > Now, the second part that was troubling relates to the 
> execution 
> > > > planner.
> > > >
> > > >
> > > > Two of the tables in which data is being entered are 
> partitioned 
> > > > tables.
> > > > Data is being routed to the partition by using rules.  Each
> > > partition
> > > > has a check constraint on a date range.  Constraint 
> exclusion is on.
> > > >
> > > > The first partitioned table is very simple, and the stored
> > > procedure
> > > > simply inserts a record for each test which is executed.  These 
> > > > are later used by a procedure running in the background which
> > > calculates
> > > > statistical data.
> > > >
> > > > My problem is with the second partitioned table.  As part of my 
> > > > troubleshooting, I omitted the code from the stored procedure 
> > > > which accesses this data.  In this table, I attempt to retrieve 
> > > > the daily record for a specific test.  If it is not 
> found, after 
> > > > aggregating data I insert it.  If the record already 
> exists, data 
> > > > is aggregated and the record is updated.
> > > >
> > > > The key is composed of a date field (monthdate) and an 
> int4 field 
> > > > (kstestsysid).
> > > > The check constraints are of the form of:
> > > >
> > > > ALTER TABLE tblkssnapshot12
> > > >   ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >=
> > > '2006-12-01
> > > > 00:00:00'::timestamp without time zone AND monthdate < 
> '2007-01-01 
> > > > 00:00:00'::timestamp without time zone);
> > > >
> > > > I ran a sample query which would be issued by the 
> stored procedure 
> > > > with the explain option.
> > > > The query was:
> > > >
> > > > select monthdate, kstestssysid from tblkssnapshotdaily
> > > where monthdate
> > > > = '2006-12-01' and kstestssysid = 3143
> > > >
> > > > The explain is as follows:
> > > >
> > > > select monthdate, kstestssysid from tblkssnapshotdaily
> > > where monthdate
> > > > = '2006-12-01' and kstestssysid = 3143 Result  
> (cost=0.00..122.49
> > > > rows=25 width=8)
> > > >   ->  Append  (cost=0.00..122.49 rows=25 width=8)
> > > >         ->  Index Scan using 
> tblkssnapshotdaily_idx_monthtest on 
> > > > tblkssnapshotdaily  (cost=0.00..3.52 rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot01_idx_monthtest on
> > > > tblkssnapshot01 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot02_idx_monthtest on
> > > > tblkssnapshot02 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot03_idx_monthtest on
> > > > tblkssnapshot03 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot04_idx_monthtest on
> > > > tblkssnapshot04 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot05_idx_monthtest on
> > > > tblkssnapshot05 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot06_idx_monthtest on
> > > > tblkssnapshot06 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot07_idx_monthtest on
> > > > tblkssnapshot07 tblkssnapshotdaily  (cost=0.00..5.94 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot08_idx_monthtest on
> > > > tblkssnapshot08 tblkssnapshotdaily  (cost=0.00..4.73 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot09_idx_monthtest on
> > > > tblkssnapshot09 tblkssnapshotdaily  (cost=0.00..5.56 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot10_idx_monthtest on 
> > > > tblkssnapshot10 tblkssnapshotdaily  (cost=0.00..5.75 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot11_idx_monthtest on
> > > > tblkssnapshot11 tblkssnapshotdaily  (cost=0.00..5.81 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot12_idx_monthtest on
> > > > tblkssnapshot12 tblkssnapshotdaily  (cost=0.00..4.33 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot13_idx_monthtest on
> > > > tblkssnapshot13 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot14_idx_monthtest on
> > > > tblkssnapshot14 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot15_idx_monthtest on
> > > > tblkssnapshot15 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot16_idx_monthtest on
> > > > tblkssnapshot16 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot17_idx_monthtest on
> > > > tblkssnapshot17 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot18_idx_monthtest on
> > > > tblkssnapshot18 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot19_idx_monthtest on
> > > > tblkssnapshot19 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot20_idx_monthtest on 
> > > > tblkssnapshot20 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot21_idx_monthtest on
> > > > tblkssnapshot21 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot22_idx_monthtest on
> > > > tblkssnapshot22 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot23_idx_monthtest on
> > > > tblkssnapshot23 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >         ->  Index Scan using tblkssnapshot24_idx_monthtest on
> > > > tblkssnapshot24 tblkssnapshotdaily  (cost=0.00..4.83 
> rows=1 width=8)
> > > >               Index Cond: ((monthdate = '2006-12-01'::date) AND 
> > > > (kstestssysid = 3143))
> > > >
> > > > If constraint exclusion is on, why are index scans taking
> > > place on all
> > > > of the tables and not only on the only partitioned table
> > > for which the
> > > > constraint exists?
> > > >
> > > > I went to a partitioned table thinking this would improve
> > > performance
> > > > from the previous schema in which all of the data was in the 
> > > > parent table, but the end result was significantly slower 
> > > > performance (by orders of magnitude).
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 5: don't forget to increase your free space map settings
> > > >
> > > 
> > > --
> > > Jim Nasby                                            jim@nasby.net
> > > EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> > > 
> > > 
> > > 
> > 
> 
> -- 
> Jim C. Nasby, Database Architect                decibel@decibel.org 
> Give your computer some brain candy! www.distributed.net Team #1828
> 
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
> 



^ permalink  raw  reply  [nested|flat] 21+ messages in thread


end of thread, other threads:[~2006-12-05 03:40 UTC | newest]

Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-26 09:39 Performance question Brian McCane <bmccane@mccons.net>
2002-02-26 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 18:11 Performance question Bob Smith <bsmith@h-e.com>
2005-03-14 07:08 Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-14 15:58 ` Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-15 15:54 ` Re: Performance Question weiping <laser@tacacs.zhengmai.net.cn>
2005-03-16 06:45   ` Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-21 03:40     ` Re: Performance Question Weiping <laser@qmail.zhengmai.net.cn>
2005-03-14 16:10 Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-14 17:10 ` Re: Performance Question Brad Nicholson <bnichols@ca.afilias.info>
2005-03-14 16:36 Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-15 06:33 Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-19 18:09 ` Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-22 06:31   ` Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-22 15:55     ` Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-15 15:32 Re: Performance Question Brad Nicholson <bnichols@ca.afilias.info>
2005-03-16 06:28 ` Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2006-12-02 05:18 Performance question Benjamin Krajmalnik <kraj@illumen.com>
2006-12-05 00:53 ` Jim Nasby <decibel@decibel.org>
2006-12-05 03:25 Re: Performance question Jim C. Nasby <decibel@decibel.org>
2006-12-05 03:40 Re: Performance question Benjamin Krajmalnik <kraj@illumen.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox