pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Low Performance for big hospital server ..
38+ messages / 19 participants
[nested] [flat]

* Low Performance for big hospital server ..
@ 2005-01-02 02:54 amrit@health2.moph.go.th
  2005-01-02 09:23 ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  0 siblings, 2 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-02 02:54 UTC (permalink / raw)
  To: pgsql-performance

I try to adjust my server for a couple of weeks with some sucess but it still
slow when the server has stress in the moring from many connection . I used
postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4 Gb.
Since 1 1/2 yr. when I started to use the database server after optimizing the
postgresql.conf everything went fine until a couple of weeks ago , my database
grew up to 3.5 Gb and there were more than 160 concurent connections.
The server seemed to be slower in the rush hour peroid than before . There
is some swap process too. My top and meminfo are shown here below:

207 processes: 203 sleeping, 4 running, 0 zombie, 0 stopped
CPU0 states:  15.0% user  12.1% system    0.0% nice   0.0% iowait  72.2% idle
CPU1 states:  11.0% user  11.1% system    0.0% nice   0.0% iowait  77.2% idle
CPU2 states:  22.3% user  27.3% system    0.0% nice   0.0% iowait  49.3% idle
CPU3 states:  15.4% user  13.0% system    0.0% nice   0.0% iowait  70.4% idle
Mem:  4124720k av, 4085724k used,   38996k free,       0k shrd,   59012k buff
                   3141420k actv,   48684k in_d,   76596k in_c
Swap: 20370412k av,   46556k used, 20323856k free                 3493136k
cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
16708 postgres  15   0  264M 264M  261M S    14.7  6.5   0:18   2 postmaster
16685 postgres  15   0  264M 264M  261M S    14.5  6.5   1:22   0 postmaster
16690 postgres  15   0  264M 264M  261M S    13.7  6.5   1:35   3 postmaster
16692 postgres  15   0  264M 264M  261M S    13.3  6.5   0:49   1 postmaster
16323 postgres  16   0  264M 264M  261M R    11.1  6.5   1:48   2 postmaster
16555 postgres  15   0  264M 264M  261M S     9.7  6.5   1:52   3 postmaster
16669 postgres  15   0  264M 264M  261M S     8.7  6.5   1:58   3 postmaster
16735 postgres  15   0  264M 264M  261M S     7.7  6.5   0:15   0 postmaster
16774 postgres  16   0  256M 256M  254M R     7.5  6.3   0:09   0 postmaster
16247 postgres  15   0  263M 263M  261M S     7.1  6.5   0:46   0 postmaster
16696 postgres  15   0  263M 263M  261M S     6.7  6.5   0:24   1 postmaster
16682 postgres  15   0  264M 264M  261M S     4.3  6.5   1:19   3 postmaster
16726 postgres  15   0  263M 263M  261M S     1.5  6.5   0:21   3 postmaster
   14 root      15   0     0    0     0 RW    1.3  0.0 126:42   1 kscand/HighMem
16766 postgres  15   0  134M 134M  132M S     1.1  3.3   0:01   2 postmaster
16772 postgres  15   0  258M 258M  256M S     1.1  6.4   0:04   1 postmaster
16835 root      15   0  1252 1252   856 R     0.9  0.0   0:00   3 top
 2624 root      24   0 13920 7396  1572 S     0.5  0.1   6:25   1 java
16771 postgres  15   0  263M 263M  261M S     0.5  6.5   0:06   0 postmaster
   26 root      15   0     0    0     0 SW    0.3  0.0   3:24   1 kjournald
 2114 root      15   0   276  268   216 S     0.1  0.0   2:48   2 irqbalance
    1 root      15   0   108   76    56 S     0.0  0.0   0:07   3 init
    2 root      RT   0     0    0     0 SW    0.0  0.0   0:00   0 migration/0
    3 root      RT   0     0    0     0 SW    0.0  0.0   0:00   1 migration/1
    4 root      RT   0     0    0     0 SW    0.0  0.0   0:00   2 migration/2
    5 root      RT   0     0    0     0 SW    0.0  0.0   0:00   3 migration/3
    6 root      15   0     0    0     0 SW    0.0  0.0   0:03   1 keventd

[root@data3 root]# cat < /proc/meminfo
        total:    used:    free:  shared: buffers:  cached:
Mem:  4223713280 4203782144 19931136        0 37982208 3684573184
Swap: 20859301888 65757184 20793544704
MemTotal:      4124720 kB
MemFree:         19464 kB
MemShared:           0 kB
Buffers:         37092 kB
Cached:        3570800 kB
SwapCached:      27416 kB
Active:        3215984 kB
ActiveAnon:     245576 kB
ActiveCache:   2970408 kB
Inact_dirty:    330796 kB
Inact_laundry:  164256 kB
Inact_clean:    160968 kB
Inact_target:   774400 kB
HighTotal:     3276736 kB
HighFree:         1024 kB
LowTotal:       847984 kB
LowFree:         18440 kB
SwapTotal:    20370412 kB
SwapFree:     20306196 kB

[root@data3 root]# cat < /proc/sys/kernel/shmmax
4000000000[root@data3 root]# cat < /proc/sys/kernel/shmall
134217728

max_connections = 165
shared_buffers = 32768
sort_mem = 20480
vacuum_mem = 16384
effective_cache_size = 256900

I still in doubt whether this figture is optimized and putting more ram will
help the system throughtput.

Any idea please . My organization is one oof the big hospital in Thailand
Thanks
Amrit
Thailand




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-02 09:23 ` Mark Kirkwood <markir@coretech.co.nz>
  1 sibling, 0 replies; 38+ messages in thread

From: Mark Kirkwood @ 2005-01-02 09:23 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

amrit@health2.moph.go.th wrote:

>I try to adjust my server for a couple of weeks with some sucess but it still
>slow when the server has stress in the moring from many connection . I used
>postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4 Gb.
>Since 1 1/2 yr. when I started to use the database server after optimizing the
>postgresql.conf everything went fine until a couple of weeks ago , my database
>grew up to 3.5 Gb and there were more than 160 concurent connections.
>The server seemed to be slower in the rush hour peroid than before . There
>is some swap process too. My top and meminfo are shown here below:
>  
>
You might just be running low on ram - your sort_mem setting means that
160 connections need about 3.1G. Add to that the 256M for your
shared_buffers and there may not be much left for the os to use
effectively (this could explain the fact that some swap is being used).

Is reducing sort_mem an option ?

regards

Mark




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-02 14:08 ` Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  1 sibling, 1 reply; 38+ messages in thread

From: Michael Adler @ 2005-01-02 14:08 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

On Sun, Jan 02, 2005 at 09:54:32AM +0700, amrit@health2.moph.go.th wrote:
> postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4 Gb.

You may want to try disabling hyperthreading, if you don't mind
rebooting. 

> grew up to 3.5 Gb and there were more than 160 concurent connections.

Looks like your growing dataset won't fit in your OS disk cache any
longer. Isolate your most problematic queries and check out their
query plans. I bet you have some sequential scans that used to read
from cache but now need to read the disk. An index may help you. 

More RAM wouldn't hurt. =)

 -Mike Adler



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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
@ 2005-01-02 16:28   ` amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 02:26     ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  0 siblings, 2 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-02 16:28 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Michael Adler <adler@pobox.com>

> > postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4
> Gb.
>
> You may want to try disabling hyperthreading, if you don't mind
> rebooting.

Can you give me an idea why should I use the SMP kernel instead of Bigmen kernel
[turn off the hyperthreading]? Will it be better to turn off ?

> > grew up to 3.5 Gb and there were more than 160 concurent connections.
>
> Looks like your growing dataset won't fit in your OS disk cache any
> longer. Isolate your most problematic queries and check out their
> query plans. I bet you have some sequential scans that used to read
> from cache but now need to read the disk. An index may help you.
>
> More RAM wouldn't hurt. =)

I think so that there may be some query load on our programe and I try to locate
it.
But if I reduce the config to :
max_connections = 160
shared_buffers =  2048	  [Total = 2.5 Gb.]
sort_mem  = 8192   [Total = 1280 Mb.]
vacuum_mem = 16384
effective_cache_size  = 128897 [= 1007 Mb. = 1 Gb.  ]
Will it be more suitable for my server than before?

Thanks for all comment.
Amrit
Thailand




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-02 16:56     ` Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  1 sibling, 1 reply; 38+ messages in thread

From: Dave Cramer @ 2005-01-02 16:56 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

The common wisdom of shared buffers is around 6-10% of available memory. 
Your proposal below is about 50% of memory.

I'm not sure what the original numbers actually meant, they are quite large.

also effective cache is the sum of kernel buffers + shared_buffers so it 
should be bigger than shared buffers.

Also turning hyperthreading off may help, it is unlikely it is doing any 
good unless you are running a relatively new (2.6.x) kernel.

I presume you are vacuuming on a regular basis?

amrit@health2.moph.go.th wrote:

>>>postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4
>>>      
>>>
>>Gb.
>>
>>You may want to try disabling hyperthreading, if you don't mind
>>rebooting.
>>    
>>
>
>Can you give me an idea why should I use the SMP kernel instead of Bigmen kernel
>[turn off the hyperthreading]? Will it be better to turn off ?
>
>  
>
>>>grew up to 3.5 Gb and there were more than 160 concurent connections.
>>>      
>>>
>>Looks like your growing dataset won't fit in your OS disk cache any
>>longer. Isolate your most problematic queries and check out their
>>query plans. I bet you have some sequential scans that used to read
>>from cache but now need to read the disk. An index may help you.
>>
>>More RAM wouldn't hurt. =)
>>    
>>
>
>I think so that there may be some query load on our programe and I try to locate
>it.
>But if I reduce the config to :
>max_connections = 160
>shared_buffers =  2048	  [Total = 2.5 Gb.]
>sort_mem  = 8192   [Total = 1280 Mb.]
>vacuum_mem = 16384
>effective_cache_size  = 128897 [= 1007 Mb. = 1 Gb.  ]
>Will it be more suitable for my server than before?
>
>Thanks for all comment.
>Amrit
>Thailand
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
@ 2005-01-03 01:54       ` amrit@health2.moph.go.th
  2005-01-03 08:32         ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
  2005-01-03 14:10         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  0 siblings, 2 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-03 01:54 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: pgsql-performance

> The common wisdom of shared buffers is around 6-10% of available memory.
> Your proposal below is about 50% of memory.
>
> I'm not sure what the original numbers actually meant, they are quite large.
>
I will try to reduce shared buffer to 1536 [1.87 Mb].

> also effective cache is the sum of kernel buffers + shared_buffers so it
> should be bigger than shared buffers.
also make the effective cache to 2097152 [2 Gb].
I will give you the result , because tomorrow [4/12/05] will be the official day
of my hospital [which have more than 1700 OPD patient/day].


> Also turning hyperthreading off may help, it is unlikely it is doing any
> good unless you are running a relatively new (2.6.x) kernel.
Why , could you give me the reason?

> I presume you are vacuuming on a regular basis?
Yes , vacuumdb daily.





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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-03 08:32         ` William Yu <wyu@talisys.com>
  2005-01-03 08:51           ` Re: Low Performance for big hospital server .. Mike Mascari <mascarm@mascari.com>
  2005-01-03 14:01           ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  1 sibling, 2 replies; 38+ messages in thread

From: William Yu @ 2005-01-03 08:32 UTC (permalink / raw)
  To: pgsql-performance

amrit@health2.moph.go.th wrote:
> I will try to reduce shared buffer to 1536 [1.87 Mb].

1536 is probaby too low. I've tested a bunch of different settings on my 
  8GB Opteron server and 10K seems to be the best setting.


>>also effective cache is the sum of kernel buffers + shared_buffers so it
>>should be bigger than shared buffers.
> 
> also make the effective cache to 2097152 [2 Gb].
> I will give you the result , because tomorrow [4/12/05] will be the official day
> of my hospital [which have more than 1700 OPD patient/day].

To figure out your effective cache size, run top and add free+cached.


>>Also turning hyperthreading off may help, it is unlikely it is doing any
>>good unless you are running a relatively new (2.6.x) kernel.
> 
> Why , could you give me the reason?

Pre 2.6, the kernel does not know the difference between logical and 
physical CPUs. Hence, in a dual processor system with hyperthreading, it 
actually sees 4 CPUs. And when assigning processes to CPUs, it may 
assign to 2 logical CPUs in the same physical CPU.


> 
> 
>>I presume you are vacuuming on a regular basis?
> 
> Yes , vacuumdb daily.

Do you vacuum table by table or the entire DB? I find over time, the 
system tables can get very bloated and cause a lot of slowdowns just due 
to schema queries/updates. You might want to try a VACUUM FULL ANALYZE 
just on the system tables.



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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 08:32         ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
@ 2005-01-03 08:51           ` Mike Mascari <mascarm@mascari.com>
  1 sibling, 0 replies; 38+ messages in thread

From: Mike Mascari @ 2005-01-03 08:51 UTC (permalink / raw)
  To: William Yu <wyu@talisys.com>; +Cc: pgsql-performance

William Yu wrote:
> amrit@health2.moph.go.th wrote:
>> Yes , vacuumdb daily.
> 
> Do you vacuum table by table or the entire DB? I find over time, the 
> system tables can get very bloated and cause a lot of slowdowns just due 
> to schema queries/updates. You might want to try a VACUUM FULL ANALYZE 
> just on the system tables.

A REINDEX of the system tables in stand-alone mode might also be in 
order, even for a 7.4.x database:

http://www.postgresql.org/docs/7.4/interactive/sql-reindex.html

If a dump-reload-analyze cycle yields significant performance 
improvements then we know it's due to dead-tuple bloat - either heap 
tuples or index tuples.

Mike Mascari




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 08:32         ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
@ 2005-01-03 14:01           ` Dave Cramer <pg@fastcrypt.com>
  2005-01-03 19:57             ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
  1 sibling, 1 reply; 38+ messages in thread

From: Dave Cramer @ 2005-01-03 14:01 UTC (permalink / raw)
  To: William Yu <wyu@talisys.com>; +Cc: pgsql-performance



William Yu wrote:

> amrit@health2.moph.go.th wrote:
>
>> I will try to reduce shared buffer to 1536 [1.87 Mb].
>
>
> 1536 is probaby too low. I've tested a bunch of different settings on 
> my  8GB Opteron server and 10K seems to be the best setting.

Be careful here, he is not using opterons which can access physical 
memory above 4G efficiently. Also he only has 4G the 6-10% rule still 
applies

>
>
>>> also effective cache is the sum of kernel buffers + shared_buffers 
>>> so it
>>> should be bigger than shared buffers.
>>
>>
>> also make the effective cache to 2097152 [2 Gb].
>> I will give you the result , because tomorrow [4/12/05] will be the 
>> official day
>> of my hospital [which have more than 1700 OPD patient/day].
>
>
> To figure out your effective cache size, run top and add free+cached.

My understanding is that effective cache is the sum of shared buffers, 
plus kernel buffers, not sure what free + cached gives you?

>
>
>>> Also turning hyperthreading off may help, it is unlikely it is doing 
>>> any
>>> good unless you are running a relatively new (2.6.x) kernel.
>>
>>
>> Why , could you give me the reason?
>
>
> Pre 2.6, the kernel does not know the difference between logical and 
> physical CPUs. Hence, in a dual processor system with hyperthreading, 
> it actually sees 4 CPUs. And when assigning processes to CPUs, it may 
> assign to 2 logical CPUs in the same physical CPU.

Right, the pre 2.6 kernels don't really know how to handle hyperthreaded 
CPU's

>
>
>>
>>
>>> I presume you are vacuuming on a regular basis?
>>
>>
>> Yes , vacuumdb daily.
>
>
> Do you vacuum table by table or the entire DB? I find over time, the 
> system tables can get very bloated and cause a lot of slowdowns just 
> due to schema queries/updates. You might want to try a VACUUM FULL 
> ANALYZE just on the system tables.

You may want to try this but regular vacuum analyze should work fine as 
long as you have the free space map settings correct. Also be aware that 
pre-7.4.x the free space map is not populated on startup so you should 
do a vacuum analyze right after startup.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 08:32         ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
  2005-01-03 14:01           ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
@ 2005-01-03 19:57             ` William Yu <wyu@talisys.com>
  2005-01-04 00:58               ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  0 siblings, 1 reply; 38+ messages in thread

From: William Yu @ 2005-01-03 19:57 UTC (permalink / raw)
  To: pgsql-performance

Dave Cramer wrote:

> 
> 
> William Yu wrote:
> 
>> amrit@health2.moph.go.th wrote:
>>
>>> I will try to reduce shared buffer to 1536 [1.87 Mb].
>>
>>
>>
>> 1536 is probaby too low. I've tested a bunch of different settings on 
>> my  8GB Opteron server and 10K seems to be the best setting.
> 
> 
> Be careful here, he is not using opterons which can access physical 
> memory above 4G efficiently. Also he only has 4G the 6-10% rule still 
> applies

10% of 4GB is 400MB. 10K buffers is 80MB. Easily less than the 6-10% rule.


>> To figure out your effective cache size, run top and add free+cached.
> 
> 
> My understanding is that effective cache is the sum of shared buffers, 
> plus kernel buffers, not sure what free + cached gives you?

Not true. Effective cache size is the free memory available that the OS 
can use for caching for Postgres. In a system that runs nothing but 
Postgres, it's free + cached.



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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 08:32         ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
  2005-01-03 14:01           ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 19:57             ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
@ 2005-01-04 00:58               ` Dave Cramer <pg@fastcrypt.com>
  0 siblings, 0 replies; 38+ messages in thread

From: Dave Cramer @ 2005-01-04 00:58 UTC (permalink / raw)
  To: William Yu <wyu@talisys.com>; +Cc: pgsql-performance



William Yu wrote:

> Dave Cramer wrote:
>
>>
>>
>> William Yu wrote:
>>
>>> amrit@health2.moph.go.th wrote:
>>>
>>>> I will try to reduce shared buffer to 1536 [1.87 Mb].
>>>
>>>
>>>
>>>
>>> 1536 is probaby too low. I've tested a bunch of different settings 
>>> on my  8GB Opteron server and 10K seems to be the best setting.
>>
>>
>>
>> Be careful here, he is not using opterons which can access physical 
>> memory above 4G efficiently. Also he only has 4G the 6-10% rule still 
>> applies
>
>
> 10% of 4GB is 400MB. 10K buffers is 80MB. Easily less than the 6-10% 
> rule.
>
Correct, I didn't actually do the math, I refrain from giving actual 
numbers as every system is different.

>
>>> To figure out your effective cache size, run top and add free+cached.
>>
>>
>>
>> My understanding is that effective cache is the sum of shared 
>> buffers, plus kernel buffers, not sure what free + cached gives you?
>
>
> Not true. Effective cache size is the free memory available that the 
> OS can use for caching for Postgres. In a system that runs nothing but 
> Postgres, it's free + cached.

You still need to add in the shared buffers as they are part of the 
"effective cache"

Dave

>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-03 14:10         ` Dave Cramer <pg@fastcrypt.com>
  2005-01-03 15:40           ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  1 sibling, 1 reply; 38+ messages in thread

From: Dave Cramer @ 2005-01-03 14:10 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

Amrit,

I realize you may be stuck with 7.3.x but you should be aware that 7.4 
is considerably faster, and 8.0 appears to be even faster yet.

I would seriously consider upgrading, if at all possible.

A few more hints.

Random page cost is quite conservative if you have reasonably fast disks.
Speaking of fast disks, not all disks are created equal, some RAID 
drives are quite slow (Bonnie++ is your friend here)

Sort memory can be set on a per query basis, I'd consider lowering it 
quite low and only increasing it when necessary.

Which brings us to how to find out when it is necessary.
Turn logging on and turn on log_pid, and log_duration, then you will 
need to sort through the logs to find the slow queries.

There are some special cases where postgresql can be quite slow, and 
minor adjustments to the query can improve it significantly

For instance pre-8.0 select * from foo where id = '1'; where id is a 
int8 will never use an index even if it exists.


Regards,

Dave


amrit@health2.moph.go.th wrote:

>>The common wisdom of shared buffers is around 6-10% of available memory.
>>Your proposal below is about 50% of memory.
>>
>>I'm not sure what the original numbers actually meant, they are quite large.
>>
>>    
>>
>I will try to reduce shared buffer to 1536 [1.87 Mb].
>
>  
>
>>also effective cache is the sum of kernel buffers + shared_buffers so it
>>should be bigger than shared buffers.
>>    
>>
>also make the effective cache to 2097152 [2 Gb].
>I will give you the result , because tomorrow [4/12/05] will be the official day
>of my hospital [which have more than 1700 OPD patient/day].
>
>
>  
>
>>Also turning hyperthreading off may help, it is unlikely it is doing any
>>good unless you are running a relatively new (2.6.x) kernel.
>>    
>>
>Why , could you give me the reason?
>
>  
>
>>I presume you are vacuuming on a regular basis?
>>    
>>
>Yes , vacuumdb daily.
>
>
>
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 14:10         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
@ 2005-01-03 15:40           ` amrit@health2.moph.go.th
  2005-01-03 18:51             ` Re: Low Performance for big hospital server .. Robert Treat <xzilla@users.sourceforge.net>
  2005-01-04 00:57             ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  0 siblings, 2 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-03 15:40 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: pgsql-performance

> I realize you may be stuck with 7.3.x but you should be aware that 7.4
> is considerably faster, and 8.0 appears to be even faster yet.

There are a little bit incompatibility between 7.3 -8 , so rather difficult to
change.

> I would seriously consider upgrading, if at all possible.
>
> A few more hints.
>
> Random page cost is quite conservative if you have reasonably fast disks.
> Speaking of fast disks, not all disks are created equal, some RAID
> drives are quite slow (Bonnie++ is your friend here)
>
> Sort memory can be set on a per query basis, I'd consider lowering it
> quite low and only increasing it when necessary.
>
> Which brings us to how to find out when it is necessary.
> Turn logging on and turn on log_pid, and log_duration, then you will
> need to sort through the logs to find the slow queries.

In standard RH 9.0 , if I enable both of the log [pid , duration] , where could
I look for the result of the log, and would it make the system to be slower?


Amrit
Thailand




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 14:10         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 15:40           ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-03 18:51             ` Robert Treat <xzilla@users.sourceforge.net>
  1 sibling, 0 replies; 38+ messages in thread

From: Robert Treat @ 2005-01-03 18:51 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

On Monday 03 January 2005 10:40, amrit@health2.moph.go.th wrote:
> > I realize you may be stuck with 7.3.x but you should be aware that 7.4
> > is considerably faster, and 8.0 appears to be even faster yet.
>
> There are a little bit incompatibility between 7.3 -8 , so rather difficult
> to change.
>

Sure, but even moving to 7.4 would be a bonus, especially if you use a lot of 
select * from tab where id in (select ... ) type queries, and the 
incompataibility is less as well. 

> > I would seriously consider upgrading, if at all possible.
> >
> > A few more hints.
> >

One thing I didn't see mentioned that should have been was to watch for index 
bloat, which was a real problem on 7.3 machines.  You can determine which 
indexes are bloated by studying vacuum output or by comparing index size on 
disk to table size on disk.  

Another thing I didn't see mentioned was to your free space map settings.  
Make sure these are large enough to hold your data... max_fsm_relations 
should be larger then the total # of tables you have in your system (check 
the archives for the exact query needed) and max_fsm_pages needs to be big 
enough to hold all of the pages you use in a day... this is hard to calculate 
in 7.3, but if you look at your vacuum output and add the number of pages 
cleaned up for all tables, this could give you a good number to work with. It 
would certainly tell you if your setting is too small. 

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 16:56     ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 01:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 14:10         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-03 15:40           ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-04 00:57             ` Dave Cramer <pg@fastcrypt.com>
  1 sibling, 0 replies; 38+ messages in thread

From: Dave Cramer @ 2005-01-04 00:57 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance



amrit@health2.moph.go.th wrote:

>>I realize you may be stuck with 7.3.x but you should be aware that 7.4
>>is considerably faster, and 8.0 appears to be even faster yet.
>>    
>>
>
>There are a little bit incompatibility between 7.3 -8 , so rather difficult to
>change.
>
>  
>
>>I would seriously consider upgrading, if at all possible.
>>
>>A few more hints.
>>
>>Random page cost is quite conservative if you have reasonably fast disks.
>>Speaking of fast disks, not all disks are created equal, some RAID
>>drives are quite slow (Bonnie++ is your friend here)
>>
>>Sort memory can be set on a per query basis, I'd consider lowering it
>>quite low and only increasing it when necessary.
>>
>>Which brings us to how to find out when it is necessary.
>>Turn logging on and turn on log_pid, and log_duration, then you will
>>need to sort through the logs to find the slow queries.
>>    
>>
>
>In standard RH 9.0 , if I enable both of the log [pid , duration] , where could
>I look for the result of the log, and would it make the system to be slower?
>  
>
On a redhat system logging is more or less disabled if you used the rpm

you can set syslog=2 in the postgresql.conf and then you will get the 
logs in messages.log
Yes, it will make it slower, but you have to find out which queries are 
slow.

Dave

>
>Amrit
>Thailand
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-03 02:26     ` Mark Kirkwood <markir@coretech.co.nz>
  2005-01-03 04:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  1 sibling, 1 reply; 38+ messages in thread

From: Mark Kirkwood @ 2005-01-03 02:26 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance@postgresql.org, Michael Adler <adler@pobox.com>

amrit@health2.moph.go.th wrote:

>
>max_connections = 160
>shared_buffers =  2048	  [Total = 2.5 Gb.]
>sort_mem  = 8192   [Total = 1280 Mb.]
>vacuum_mem = 16384
>effective_cache_size  = 128897 [= 1007 Mb. = 1 Gb.  ]
>Will it be more suitable for my server than before?
>
>
>  
>
I would keep shared_buffers in the 10000->20000 range, as this is
allocated *once* into shared memory, so only uses 80->160 Mb in *total*.

The lower sort_mem will help reduce memory pressure (as this is
allocated for every backend connection) and this will help performance -
*unless* you have lots of queries that need to sort large datasets. If
so, then these will hammer your i/o subsystem, possibly canceling any
gain from freeing up more memory. So there is a need to understand what
sort of workload you have!

best wishes

Mark




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 02:26     ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
@ 2005-01-03 04:54       ` amrit@health2.moph.go.th
  2005-01-03 06:19         ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  0 siblings, 1 reply; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-03 04:54 UTC (permalink / raw)
  To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: pgsql-performance

> >max_connections = 160
> >shared_buffers =  2048	  [Total = 2.5 Gb.]
> >sort_mem  = 8192   [Total = 1280 Mb.]
> >vacuum_mem = 16384
> >effective_cache_size  = 128897 [= 1007 Mb. = 1 Gb.  ]
> >Will it be more suitable for my server than before?
> >
> >
> >
> >
> I would keep shared_buffers in the 10000->20000 range, as this is
> allocated *once* into shared memory, so only uses 80->160 Mb in *total*.

You mean that if I increase the share buffer to arround 12000 [160 comnnections
] , this will not affect the mem. usage ?

> The lower sort_mem will help reduce memory pressure (as this is
> allocated for every backend connection) and this will help performance -
> *unless* you have lots of queries that need to sort large datasets. If
> so, then these will hammer your i/o subsystem, possibly canceling any
> gain from freeing up more memory. So there is a need to understand what
> sort of workload you have!

Will the increasing in effective cache size to arround 200000 make a little bit
improvement ? Do you think so?

Any comment please , thanks.
Amrit
Thailand




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 02:26     ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  2005-01-03 04:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-03 06:19         ` Mark Kirkwood <markir@coretech.co.nz>
  2005-01-03 08:18           ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  0 siblings, 1 reply; 38+ messages in thread

From: Mark Kirkwood @ 2005-01-03 06:19 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

amrit@health2.moph.go.th wrote:

>>>max_connections = 160
>>>shared_buffers =  2048	  [Total = 2.5 Gb.]
>>>sort_mem  = 8192   [Total = 1280 Mb.]
>>>vacuum_mem = 16384
>>>effective_cache_size  = 128897 [= 1007 Mb. = 1 Gb.  ]
>>>Will it be more suitable for my server than before?
>>>
>>>
>>>
>>>
>>>      
>>>
>>I would keep shared_buffers in the 10000->20000 range, as this is
>>allocated *once* into shared memory, so only uses 80->160 Mb in *total*.
>>    
>>
>
>You mean that if I increase the share buffer to arround 12000 [160 comnnections
>] , this will not affect the mem. usage ?
>
>  
>
shared_buffers = 12000 will use 12000*8192 bytes (i.e about 96Mb). It is
shared, so no matter how many connections you have it will only use 96M.


>>The lower sort_mem will help reduce memory pressure (as this is
>>allocated for every backend connection) and this will help performance -
>>*unless* you have lots of queries that need to sort large datasets. If
>>so, then these will hammer your i/o subsystem, possibly canceling any
>>gain from freeing up more memory. So there is a need to understand what
>>sort of workload you have!
>>    
>>
>
>Will the increasing in effective cache size to arround 200000 make a little bit
>improvement ? Do you think so?
>
>  
>
I would leave it at the figure you proposed (128897), and monitor your
performance.
(you can always increase it later and see what the effect is).

regards

Mark




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 02:26     ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  2005-01-03 04:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 06:19         ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
@ 2005-01-03 08:18           ` amrit@health2.moph.go.th
  2005-01-03 10:08             ` Re: Low Performance for big hospital server .. Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  0 siblings, 1 reply; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-03 08:18 UTC (permalink / raw)
  To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: pgsql-performance

> shared_buffers = 12000 will use 12000*8192 bytes (i.e about 96Mb). It is
> shared, so no matter how many connections you have it will only use 96M.

Now I use the figure of 27853

> >
> >Will the increasing in effective cache size to arround 200000 make a little
> bit
> >improvement ? Do you think so?
> >
Decrease the sort mem too much [8196] make the performance much slower so I use
sort_mem = 16384
and leave effective cache to the same value , the result is quite better but I
should wait for tomorrow morning [official hour]  to see the end result.

> >
> I would leave it at the figure you proposed (128897), and monitor your
> performance.
> (you can always increase it later and see what the effect is).
Yes , I use this figure.

If the result still poor , putting more ram "6-8Gb" [also putting more money
too] will solve the problem ?
Thanks ,
Amrit
Thailand




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

* Re: Low Performance for big hospital server ..
  2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-02 14:08 ` Re: Low Performance for big hospital server .. Michael Adler <adler@pobox.com>
  2005-01-02 16:28   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 02:26     ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  2005-01-03 04:54       ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-03 06:19         ` Re: Low Performance for big hospital server .. Mark Kirkwood <markir@coretech.co.nz>
  2005-01-03 08:18           ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-03 10:08             ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  0 siblings, 0 replies; 38+ messages in thread

From: Pierre-Frédéric Caillaud @ 2005-01-03 10:08 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance



> Decrease the sort mem too much [8196] make the performance much slower  
> so I use
> sort_mem = 16384
> and leave effective cache to the same value , the result is quite better  
> but I
> should wait for tomorrow morning [official hour]  to see the end result.

	You could also profile your queries to see where those big sorts come  
from, and maybe add some indexes to try to replace sorts by  
index-scans-in-order, which use no temporary memory. Can you give an  
example of your queries which make use of big sorts like this ?



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

* Re: Low Performance for big hospital server ..
@ 2005-01-03 09:32 Gregory S. Williamson <gsw@globexplorer.com>
  0 siblings, 0 replies; 38+ messages in thread

From: Gregory S. Williamson @ 2005-01-03 09:32 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; Mark Kirkwood <markir@coretech.co.nz>; +Cc: pgsql-performance

Amrit --

>-----Original Message-----
>From:	amrit@health2.moph.go.th [mailto:amrit@health2.moph.go.th]
>Sent:	Mon 1/3/2005 12:18 AM
>To:	Mark Kirkwood
>Cc:	PGsql-performance
>Subject:	Re: [PERFORM] Low Performance for big hospital server ..
>> shared_buffers = 12000 will use 12000*8192 bytes (i.e about 96Mb). It is
>> shared, so no matter how many connections you have it will only use 96M.
>
>Now I use the figure of 27853
>
>> >
>> >Will the increasing in effective cache size to arround 200000 make a >little
>> bit
>> >improvement ? Do you think so?
>> >
>Decrease the sort mem too much [8196] make the performance much slower so I >use
>sort_mem = 16384
>and leave effective cache to the same value , the result is quite better but >I
>should wait for tomorrow morning [official hour]  to see the end result.
>
>> >
>> I would leave it at the figure you proposed (128897), and monitor your
>> performance.
>> (you can always increase it later and see what the effect is).
>Yes , I use this figure.
>
>If the result still poor , putting more ram "6-8Gb" [also putting more money
>too] will solve the problem ?

Adding RAM will almost always help, at least for a while. Our small runitme servers have 2 gigs of RAM; the larger ones have 4 gigs; I do anticipate the need to add RAM as we add users.

If you have evaluated the queries that are running and verified that they are using indexes properly, etc., and tuned the other parameters for your system and its disks, adding memory helps because it increases the chance that data is already in memory, thus saving the time to fetch it from disk. Studying performance under load with top, vmstat, etc. and detailed analysis of queries can often trade some human time for the money that extra hardware would cost. Sometimes easier to do than getting downtime for a critical server, as well.

If you don't have a reliable way of reproducing real loads on a test system, it is best to change things cautiously, and observe the system under load; if you change too many things (ideally only 1 at a time but often that is not possible) you mau actually defeat a good change with a bad one; at the least,m you may not know which change was the most important one if you make several at once.

Best of luck,

Greg Williamson
DBA
GlobeXplorer LLC
>Thanks ,
>Amrit
>Thailand


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly






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

* Re: Low Performance for big hospital server ..
@ 2005-01-03 14:55 Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 38+ messages in thread

From: Merlin Moncure @ 2005-01-03 14:55 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

amrit wrote:
> I try to adjust my server for a couple of weeks with some sucess but
it
> still
> slow when the server has stress in the moring from many connection . I
> used
> postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of
4
> Gb.
> Since 1 1/2 yr. when I started to use the database server after
optimizing
> the
> postgresql.conf everything went fine until a couple of weeks ago , my
> database
> grew up to 3.5 Gb and there were more than 160 concurent connections.
> The server seemed to be slower in the rush hour peroid than before .
There
> is some swap process too. My top and meminfo are shown here below:

well, you've hit the 'wall'...your system seems to be more or less at
the limit of what 32 bit technology can deliver.  If upgrade to Opteron
and 64 bit is out of the question, here are a couple of new tactics you
can try.  Optimizing postgresql.conf can help, but only so much.  

Optimize queries:
One big often looked performance gainer is to use functional indexes to
access data from a table.  This can save space by making the index
smaller and more efficient.  This wins on cache and speed at the price
of some flexibility.  

Optimize datums:  replace numeric(4) with int2, numeric(6) with int4,
etc.  This will save a little space on the tuple which will ease up on
the cache a bit.  Use constraints where necessary to preserve data
integrity.

Materialized views:  These can provide an enormous win if you can deal
incorporate them into your application.  With normal views, multiple
backends can share a query plan.  With mat-views, backends can share
both the plan and its execution.

Merlin




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

* Re: Low Performance for big hospital server ..
@ 2005-01-04 09:31 amrit@health2.moph.go.th
  2005-01-04 11:13 ` Re: Low Performance for big hospital server .. Gavin Sherry <swm@alcove.com.au>
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  0 siblings, 2 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-04 09:31 UTC (permalink / raw)
  To: pgsql-performance; +Cc: @; @; @

Today is the first official day of this weeks and the system run better in
serveral points but there are still some points that need to be corrected. Some
queries or some tables are very slow. I think the queries inside the programe
need to be rewrite.
Now I put the sort mem to a little bit bigger:
sort mem = 16384       increase  the sort mem makes no effect on the slow point
eventhough there is little connnection.
shared_buffers = 27853
effective cache = 120000

I will put more ram but someone said RH 9.0 had poor recognition on the Ram
above 4 Gb?
Should I close the hyperthreading ? Would it make any differnce between open and
close the hyperthreading?
Thanks for any comment
Amrit
Thailand



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-04 11:13 ` Gavin Sherry <swm@alcove.com.au>
  2005-01-05 05:07   ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  1 sibling, 1 reply; 38+ messages in thread

From: Gavin Sherry @ 2005-01-04 11:13 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: pgsql-performance

On Tue, 4 Jan 2005 amrit@health2.moph.go.th wrote:

> Today is the first official day of this weeks and the system run better in
> serveral points but there are still some points that need to be corrected. Some
> queries or some tables are very slow. I think the queries inside the programe
> need to be rewrite.
> Now I put the sort mem to a little bit bigger:
> sort mem = 16384       increase  the sort mem makes no effect on the slow point
> eventhough there is little connnection.
> shared_buffers = 27853
> effective cache = 120000

Even though others have said otherwise, I've had good results from setting
sort_mem higher -- even if that is per query.

>
> I will put more ram but someone said RH 9.0 had poor recognition on the Ram
> above 4 Gb?

I think they were refering to 32 bit architectures, not distributions as
such.

> Should I close the hyperthreading ? Would it make any differnce between open and
> close the hyperthreading?
> Thanks for any comment

In my experience, the largest performance increases come from intensive
analysis and optimisation of queries. Look at the output of EXPLAIN
ANALYZE for the queries your application is generating and see if they can
be tuned in anyway. More often than not, they can.

Feel free to ask for assistence on irc at irc.freenode.net #postgresql.
People there help optimise queries all day ;-).

> Amrit
> Thailand

Gavin



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-04 11:13 ` Re: Low Performance for big hospital server .. Gavin Sherry <swm@alcove.com.au>
@ 2005-01-05 05:07   ` amrit@health2.moph.go.th
  0 siblings, 0 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-05 05:07 UTC (permalink / raw)
  To: Gavin Sherry <swm@alcove.com.au>; +Cc: pgsql-performance

> > I will put more ram but someone said RH 9.0 had poor recognition on the Ram
> > above 4 Gb?
>
> I think they were refering to 32 bit architectures, not distributions as
> such.

Sorry for wrong reason , then should I increase more RAM than 4 Gb. on 32 bit
Arche.?

> > Should I close the hyperthreading ? Would it make any differnce between
> open and
> > close the hyperthreading?
> > Thanks for any comment
>
> In my experience, the largest performance increases come from intensive
> analysis and optimisation of queries. Look at the output of EXPLAIN
> ANALYZE for the queries your application is generating and see if they can
> be tuned in anyway. More often than not, they can.

So what you mean is that the result is the same whether  close or open
hyperthreading ?
Will it be any harm if I open it ?
The main point shiuld be adjustment the query , right.

> Feel free to ask for assistence on irc at irc.freenode.net #postgresql.
> People there help optimise queries all day ;-).

How could I contact with those people ;=> which url ?
Thanks again.
Amrit
Thailand



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-05 15:35 ` amrit@health2.moph.go.th
  2005-01-05 18:21   ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-06 03:31   ` Re: Low Performance for big hospital server .. William Yu <wyu@talisys.com>
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  1 sibling, 3 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-05 15:35 UTC (permalink / raw)
  To: Frank Wiles <frank@wiles.org>; pgsql-performance


> > Today is the first official day of this weeks and the system run
> > better in serveral points but there are still some points that need to
> > be corrected. Some queries or some tables are very slow. I think the
> > queries inside the programe need to be rewrite.
> > Now I put the sort mem to a little bit bigger:
> > sort mem = 16384       increase  the sort mem makes no effect on the
> > slow point eventhough there is little connnection.
> > shared_buffers = 27853
> > effective cache = 120000

>   If I were you I would upgrade from RH 9 to Fedora Core 2 or 3 after
>   some initial testing.  You'll see a huge improvement of speed on the
>   system as a whole.  I would try turning hyperthreading off also.


Now I turn hyperthreading off and readjust the conf . I found the bulb query
that was :
update one flag of the table [8 million records which I think not too much]
.When I turned this query off everything went fine.
I don't know whether update the data is much slower than insert [Postgresql
7.3.2] and how could we improve the update method?
Thanks for many helps.
Amrit
Thailand

NB. I would like to give my appreciation to all of the volunteers from many
countries who combat with big disaster [Tsunamies] in my country [Thailand].



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-05 18:21   ` Dave Cramer <pg@fastcrypt.com>
  2 siblings, 0 replies; 38+ messages in thread

From: Dave Cramer @ 2005-01-05 18:21 UTC (permalink / raw)
  To: amrit@health2.moph.go.th; +Cc: Frank Wiles <frank@wiles.org>; pgsql-performance

Amrit,

can you post

explain <your slow update query>

so we can see what it does ?

Dave

amrit@health2.moph.go.th wrote:

>>>Today is the first official day of this weeks and the system run
>>>better in serveral points but there are still some points that need to
>>>be corrected. Some queries or some tables are very slow. I think the
>>>queries inside the programe need to be rewrite.
>>>Now I put the sort mem to a little bit bigger:
>>>sort mem = 16384       increase  the sort mem makes no effect on the
>>>slow point eventhough there is little connnection.
>>>shared_buffers = 27853
>>>effective cache = 120000
>>>      
>>>
>
>  
>
>>  If I were you I would upgrade from RH 9 to Fedora Core 2 or 3 after
>>  some initial testing.  You'll see a huge improvement of speed on the
>>  system as a whole.  I would try turning hyperthreading off also.
>>    
>>
>
>
>Now I turn hyperthreading off and readjust the conf . I found the bulb query
>that was :
>update one flag of the table [8 million records which I think not too much]
>.When I turned this query off everything went fine.
>I don't know whether update the data is much slower than insert [Postgresql
>7.3.2] and how could we improve the update method?
>Thanks for many helps.
>Amrit
>Thailand
>
>NB. I would like to give my appreciation to all of the volunteers from many
>countries who combat with big disaster [Tsunamies] in my country [Thailand].
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-06 03:31   ` William Yu <wyu@talisys.com>
  2 siblings, 0 replies; 38+ messages in thread

From: William Yu @ 2005-01-06 03:31 UTC (permalink / raw)
  To: pgsql-performance

amrit@health2.moph.go.th wrote:
> Now I turn hyperthreading off and readjust the conf . I found the bulb query
> that was :
> update one flag of the table [8 million records which I think not too much]
> .When I turned this query off everything went fine.
> I don't know whether update the data is much slower than insert [Postgresql
> 7.3.2] and how could we improve the update method?

UPDATE is expensive. Under a MVCC setup, it's roughtly the equivalent of 
DELETE + INSERT new record (ie, old record deprecated, new version of 
record. Updating 8 million records would be very I/O intensive and 
probably flushes your OS cache so all other queries hit disk versus 
superfast memory. And if this operation is run multiple times during the 
day, you may end up with a lot of dead tuples in the table which makes 
querying it deadly slow.

If it's a dead tuples issue, you probably have to increase your 
freespace map and vacuum analyze that specific table more often. If it's 
an I/O hit issue, a lazy updating procedure would help if the operation 
is not time critical (eg. load the record keys that need updating and 
loop through the records with a time delay.)



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
@ 2005-01-06 12:15   ` Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 16:34     ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
  2 siblings, 2 replies; 38+ messages in thread

From: Dawid Kuroczko @ 2005-01-06 12:15 UTC (permalink / raw)
  To: pgsql-performance

On Wed,  5 Jan 2005 22:35:42 +0700, amrit@health2.moph.go.th
<amrit@health2.moph.go.th> wrote:
> Now I turn hyperthreading off and readjust the conf . I found the bulb query
> that was :
> update one flag of the table [8 million records which I think not too much]

Ahh, the huge update.  Below are my "hints" I've
found while trying to optimize such updates.

First of all, does this update really changes this 'flag'?
Say, you have update:
UPDATE foo SET flag = 4 WHERE [blah];
are you sure, that flag always is different than 4?
If not, then add:
UPDATE foo SET flag = 4 WHERE flag <> 4 AND [blah];
This makes sure only tuples which actually need the change will
receive it.  [ IIRC mySQL does this, while PgSQL will always perform
UPDATE, regardless if it changes or not ];

Divide the update, if possible.  This way query uses
less memory and you may call VACUUM inbetween
updates.  To do this, first SELECT INTO TEMPORARY
table the list of rows to update (their ids or something),
and then loop through it to update the values.

I guess the problem with huge updates is that
until the update is finished, the new tuples are
not visible, so the old cannot be freed...

   Regards,
      Dawid



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
@ 2005-01-06 16:34     ` amrit@health2.moph.go.th
  1 sibling, 0 replies; 38+ messages in thread

From: amrit@health2.moph.go.th @ 2005-01-06 16:34 UTC (permalink / raw)
  To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: pgsql-performance


> Ahh, the huge update.  Below are my "hints" I've
> found while trying to optimize such updates.
>
> First of all, does this update really changes this 'flag'?
> Say, you have update:
> UPDATE foo SET flag = 4 WHERE [blah];
> are you sure, that flag always is different than 4?
> If not, then add:
> UPDATE foo SET flag = 4 WHERE flag <> 4 AND [blah];
> This makes sure only tuples which actually need the change will
> receive it.  [ IIRC mySQL does this, while PgSQL will always perform
> UPDATE, regardless if it changes or not ];
>
> Divide the update, if possible.  This way query uses
> less memory and you may call VACUUM inbetween
> updates.  To do this, first SELECT INTO TEMPORARY
> table the list of rows to update (their ids or something),
> and then loop through it to update the values.
>
> I guess the problem with huge updates is that
> until the update is finished, the new tuples are
> not visible, so the old cannot be freed...

Yes, very good point I must try this and I will give you the result , thanks a
lot.
Amrit
Thailand




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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
@ 2005-01-06 17:06     ` Josh Berkus <josh@agliodbs.com>
  2005-01-06 17:12       ` Re: Low Performance for big hospital server .. Frank Wiles <frank@wiles.org>
  1 sibling, 1 reply; 38+ messages in thread

From: Josh Berkus @ 2005-01-06 17:06 UTC (permalink / raw)
  To: pgsql-performance

Dawid,

> Ahh, the huge update.  Below are my "hints" I've
> found while trying to optimize such updates.
> Divide the update, if possible.  This way query uses
> less memory and you may call VACUUM inbetween
> updates.  To do this, first SELECT INTO TEMPORARY
> table the list of rows to update (their ids or something),
> and then loop through it to update the values.

There are other ways to deal as well -- one by normalizing the database.   
Often, I find that massive updates like this are caused by a denormalized 
database.

For example, Lyris stores its "mailing numbers" only as repeated numbers in 
the recipients table.   When a mailing is complete, Lyris updates all of the 
recipients .... up to 750,000 rows in the case of my client ... to indicate 
the completion of the mailing (it's actually a little more complicated than 
that, but the essential problem is the example)

It would be far better for Lyris to use a seperate mailings table, with a 
status in that table ... which would then require only *one* update row to 
indicate completion, instead of 750,000.   

I can't tell you how many times I've seen this sort of thing.   And the 
developers always tell me "Well, we denormalized for performance reasons ... 
"

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
@ 2005-01-06 17:12       ` Frank Wiles <frank@wiles.org>
  2005-01-06 17:35         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-06 19:47         ` Re: Low Performance for big hospital server .. Greg Stark <gsstark@mit.edu>
  0 siblings, 2 replies; 38+ messages in thread

From: Frank Wiles @ 2005-01-06 17:12 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance

On Thu, 6 Jan 2005 09:06:55 -0800
Josh Berkus <josh@agliodbs.com> wrote:

> I can't tell you how many times I've seen this sort of thing.   And
> the developers always tell me "Well, we denormalized for performance
> reasons ... "

  Now that's rich.  I don't think I've ever seen a database perform
  worse after it was normalized.  In fact, I can't even think of a
  situation where it could! 

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------




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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
  2005-01-06 17:12       ` Re: Low Performance for big hospital server .. Frank Wiles <frank@wiles.org>
@ 2005-01-06 17:35         ` Dave Cramer <pg@fastcrypt.com>
  2005-01-06 17:51           ` Re: Low Performance for big hospital server .. Rod Taylor <pg@rbt.ca>
  1 sibling, 1 reply; 38+ messages in thread

From: Dave Cramer @ 2005-01-06 17:35 UTC (permalink / raw)
  To: Frank Wiles <frank@wiles.org>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance

Reading can be worse for a normalized db, which is likely what the 
developers were concerned about.

One always have to be careful to measure the right thing.

Dave

Frank Wiles wrote:

>On Thu, 6 Jan 2005 09:06:55 -0800
>Josh Berkus <josh@agliodbs.com> wrote:
>
>  
>
>>I can't tell you how many times I've seen this sort of thing.   And
>>the developers always tell me "Well, we denormalized for performance
>>reasons ... "
>>    
>>
>
>  Now that's rich.  I don't think I've ever seen a database perform
>  worse after it was normalized.  In fact, I can't even think of a
>  situation where it could! 
>
> ---------------------------------
>   Frank Wiles <frank@wiles.org>
>   http://www.wiles.org
> ---------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336	
ICQ#14675561




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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
  2005-01-06 17:12       ` Re: Low Performance for big hospital server .. Frank Wiles <frank@wiles.org>
  2005-01-06 17:35         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
@ 2005-01-06 17:51           ` Rod Taylor <pg@rbt.ca>
  2005-01-06 19:09             ` Re: Low Performance for big hospital server .. Yann Michel <yann-postgresql@spline.de>
  0 siblings, 1 reply; 38+ messages in thread

From: Rod Taylor @ 2005-01-06 17:51 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: Frank Wiles <frank@wiles.org>; Josh Berkus <josh@agliodbs.com>; pgsql-performance

On Thu, 2005-01-06 at 12:35 -0500, Dave Cramer wrote:
> Reading can be worse for a normalized db, which is likely what the 
> developers were concerned about.

To a point. Once you have enough data that you start running out of
space in memory then normalization starts to rapidly gain ground again
because it's often smaller in size and won't hit the disk as much.

Moral of the story is don't tune with a smaller database than you expect
to have.

> Frank Wiles wrote:
> 
> >On Thu, 6 Jan 2005 09:06:55 -0800
> >Josh Berkus <josh@agliodbs.com> wrote:
> >
> >  
> >
> >>I can't tell you how many times I've seen this sort of thing.   And
> >>the developers always tell me "Well, we denormalized for performance
> >>reasons ... "
> >>    
> >>
> >
> >  Now that's rich.  I don't think I've ever seen a database perform
> >  worse after it was normalized.  In fact, I can't even think of a
> >  situation where it could! 
> >
> > ---------------------------------
> >   Frank Wiles <frank@wiles.org>
> >   http://www.wiles.org
> > ---------------------------------
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> >
> >  
> >
> 
-- 




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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
  2005-01-06 17:12       ` Re: Low Performance for big hospital server .. Frank Wiles <frank@wiles.org>
  2005-01-06 17:35         ` Re: Low Performance for big hospital server .. Dave Cramer <pg@fastcrypt.com>
  2005-01-06 17:51           ` Re: Low Performance for big hospital server .. Rod Taylor <pg@rbt.ca>
@ 2005-01-06 19:09             ` Yann Michel <yann-postgresql@spline.de>
  0 siblings, 0 replies; 38+ messages in thread

From: Yann Michel @ 2005-01-06 19:09 UTC (permalink / raw)
  To: Rod Taylor <pg@rbt.ca>; +Cc: pg@fastcrypt.com, Frank Wiles <frank@wiles.org>; Josh Berkus <josh@agliodbs.com>; pgsql-performance

Hi 

On Thu, Jan 06, 2005 at 12:51:14PM -0500, Rod Taylor wrote:
> On Thu, 2005-01-06 at 12:35 -0500, Dave Cramer wrote:
> > Reading can be worse for a normalized db, which is likely what the 
> > developers were concerned about.
> 
> To a point. Once you have enough data that you start running out of
> space in memory then normalization starts to rapidly gain ground again
> because it's often smaller in size and won't hit the disk as much.

Well, in datawarehousing applications you'll often denormalize your
entities due to most of the time the access method is a (more or less)
simple select. 

Regards,
Yann



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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
  2005-01-06 17:12       ` Re: Low Performance for big hospital server .. Frank Wiles <frank@wiles.org>
@ 2005-01-06 19:47         ` Greg Stark <gsstark@mit.edu>
  2005-01-06 20:21           ` Re: Low Performance for big hospital server .. Joshua D. Drake <jd@commandprompt.com>
  1 sibling, 1 reply; 38+ messages in thread

From: Greg Stark @ 2005-01-06 19:47 UTC (permalink / raw)
  To: Frank Wiles <frank@wiles.org>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance


Frank Wiles <frank@wiles.org> writes:

>   Now that's rich.  I don't think I've ever seen a database perform
>   worse after it was normalized.  In fact, I can't even think of a
>   situation where it could! 

Just remember. All generalisations are false.

-- 
greg




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

* Re: Low Performance for big hospital server ..
  2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-05 15:35 ` Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
  2005-01-06 12:15   ` Re: Low Performance for big hospital server .. Dawid Kuroczko <qnex42@gmail.com>
  2005-01-06 17:06     ` Re: Low Performance for big hospital server .. Josh Berkus <josh@agliodbs.com>
  2005-01-06 17:12       ` Re: Low Performance for big hospital server .. Frank Wiles <frank@wiles.org>
  2005-01-06 19:47         ` Re: Low Performance for big hospital server .. Greg Stark <gsstark@mit.edu>
@ 2005-01-06 20:21           ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 38+ messages in thread

From: Joshua D. Drake @ 2005-01-06 20:21 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Frank Wiles <frank@wiles.org>; Josh Berkus <josh@agliodbs.com>; pgsql-performance

Greg Stark wrote:
> Frank Wiles <frank@wiles.org> writes:
> 
> 
>>  Now that's rich.  I don't think I've ever seen a database perform
>>  worse after it was normalized.  In fact, I can't even think of a
>>  situation where it could! 
> 
> 
> Just remember. All generalisations are false.

In general, I would agree.

Sincerely,

Joshua D. Drake



> 


-- 
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachments:

jd.vcf (text/x-vcard, 638B) download begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard
^ permalink raw reply [nested|flat] 38+ messages in thread

* Re: Low Performance for big hospital server ..
@ 2005-01-06 18:33 Richard_D_Levine@raytheon.com
  0 siblings, 0 replies; 38+ messages in thread

From: Richard_D_Levine@raytheon.com @ 2005-01-06 18:33 UTC (permalink / raw)
  To: Frank Wiles <frank@wiles.org>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance; pgsql-performance-owner@postgresql.org

In my younger days I denormalized a database for performance reasons and
have been paid for it dearly with increased maintenance costs.  Adding
enhanced capabilities and new functionality will render denormalization
worse than useless quickly.  --Rick


                                                                                                                                                 
                      Frank Wiles                                                                                                                
                      <frank@wiles.org>                  To:       Josh Berkus <josh@agliodbs.com>                                               
                      Sent by:                           cc:       pgsql-performance@postgresql.org                                              
                      pgsql-performance-owner@pos        Subject:  Re: [PERFORM] Low Performance for big hospital server ..                      
                      tgresql.org                                                                                                                
                                                                                                                                                 
                                                                                                                                                 
                      01/06/2005 12:12 PM                                                                                                        
                                                                                                                                                 
                                                                                                                                                 




On Thu, 6 Jan 2005 09:06:55 -0800
Josh Berkus <josh@agliodbs.com> wrote:

> I can't tell you how many times I've seen this sort of thing.   And
> the developers always tell me "Well, we denormalized for performance
> reasons ... "

  Now that's rich.  I don't think I've ever seen a database perform
  worse after it was normalized.  In fact, I can't even think of a
  situation where it could!

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly






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


end of thread, other threads:[~2005-01-06 20:21 UTC | newest]

Thread overview: 38+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-02 02:54 Low Performance for big hospital server .. amrit@health2.moph.go.th
2005-01-02 09:23 ` Mark Kirkwood <markir@coretech.co.nz>
2005-01-02 14:08 ` Michael Adler <adler@pobox.com>
2005-01-02 16:28   ` amrit@health2.moph.go.th
2005-01-02 16:56     ` Dave Cramer <pg@fastcrypt.com>
2005-01-03 01:54       ` amrit@health2.moph.go.th
2005-01-03 08:32         ` William Yu <wyu@talisys.com>
2005-01-03 08:51           ` Mike Mascari <mascarm@mascari.com>
2005-01-03 14:01           ` Dave Cramer <pg@fastcrypt.com>
2005-01-03 19:57             ` William Yu <wyu@talisys.com>
2005-01-04 00:58               ` Dave Cramer <pg@fastcrypt.com>
2005-01-03 14:10         ` Dave Cramer <pg@fastcrypt.com>
2005-01-03 15:40           ` amrit@health2.moph.go.th
2005-01-03 18:51             ` Robert Treat <xzilla@users.sourceforge.net>
2005-01-04 00:57             ` Dave Cramer <pg@fastcrypt.com>
2005-01-03 02:26     ` Mark Kirkwood <markir@coretech.co.nz>
2005-01-03 04:54       ` amrit@health2.moph.go.th
2005-01-03 06:19         ` Mark Kirkwood <markir@coretech.co.nz>
2005-01-03 08:18           ` amrit@health2.moph.go.th
2005-01-03 10:08             ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2005-01-03 09:32 Re: Low Performance for big hospital server .. Gregory S. Williamson <gsw@globexplorer.com>
2005-01-03 14:55 Re: Low Performance for big hospital server .. Merlin Moncure <merlin.moncure@rcsonline.com>
2005-01-04 09:31 Re: Low Performance for big hospital server .. amrit@health2.moph.go.th
2005-01-04 11:13 ` Gavin Sherry <swm@alcove.com.au>
2005-01-05 05:07   ` amrit@health2.moph.go.th
2005-01-05 15:35 ` amrit@health2.moph.go.th
2005-01-05 18:21   ` Dave Cramer <pg@fastcrypt.com>
2005-01-06 03:31   ` William Yu <wyu@talisys.com>
2005-01-06 12:15   ` Dawid Kuroczko <qnex42@gmail.com>
2005-01-06 16:34     ` amrit@health2.moph.go.th
2005-01-06 17:06     ` Josh Berkus <josh@agliodbs.com>
2005-01-06 17:12       ` Frank Wiles <frank@wiles.org>
2005-01-06 17:35         ` Dave Cramer <pg@fastcrypt.com>
2005-01-06 17:51           ` Rod Taylor <pg@rbt.ca>
2005-01-06 19:09             ` Yann Michel <yann-postgresql@spline.de>
2005-01-06 19:47         ` Greg Stark <gsstark@mit.edu>
2005-01-06 20:21           ` Joshua D. Drake <jd@commandprompt.com>
2005-01-06 18:33 Re: Low Performance for big hospital server .. Richard_D_Levine@raytheon.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