Horton

Thread

  1. Low Performance for big hospital server ..

    amrit@health2.moph.go.th — 2005-01-02T02:54:32Z

    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

  2. Re: Low Performance for big hospital server ..

    Mark Kirkwood <markir@coretech.co.nz> — 2005-01-02T09:23:45Z

    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

  3. Re: Low Performance for big hospital server ..

    Michael Adler <adler@pobox.com> — 2005-01-02T14:08:28Z

    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

  4. Re: Low Performance for big hospital server ..

    amrit@health2.moph.go.th — 2005-01-02T16:28:13Z

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

  5. Re: Low Performance for big hospital server ..

    Dave Cramer <pg@fastcrypt.com> — 2005-01-02T16:56:33Z

    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

  6. Re: Low Performance for big hospital server ..

    amrit@health2.moph.go.th — 2005-01-03T01:54:03Z

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

  7. Re: Low Performance for big hospital server ..

    Mark Kirkwood <markir@coretech.co.nz> — 2005-01-03T02:26:10Z

    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

  8. Re: Low Performance for big hospital server ..

    amrit@health2.moph.go.th — 2005-01-03T04:54:10Z

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

  9. Re: Low Performance for big hospital server ..

    Mark Kirkwood <markir@coretech.co.nz> — 2005-01-03T06:19:50Z

    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

  10. Re: Low Performance for big hospital server ..

    amrit@health2.moph.go.th — 2005-01-03T08:18:56Z

    > 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

  11. Re: Low Performance for big hospital server ..

    William Yu <wyu@talisys.com> — 2005-01-03T08:32:10Z

    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.

  12. Re: Low Performance for big hospital server ..

    Mike Mascari <mascarm@mascari.com> — 2005-01-03T08:51:42Z

    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

  13. Re: Low Performance for big hospital server ..

    PFC <lists@boutiquenumerique.com> — 2005-01-03T10:08:32Z

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

  14. Re: Low Performance for big hospital server ..

    Dave Cramer <pg@fastcrypt.com> — 2005-01-03T14:01:38Z

    
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

  15. Re: Low Performance for big hospital server ..

    Dave Cramer <pg@fastcrypt.com> — 2005-01-03T14:10:37Z

    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

  16. Re: Low Performance for big hospital server ..

    amrit@health2.moph.go.th — 2005-01-03T15:40:05Z

    > 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

  17. Re: Low Performance for big hospital server ..

    Robert Treat <xzilla@users.sourceforge.net> — 2005-01-03T18:51:55Z

    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

  18. Re: Low Performance for big hospital server ..

    William Yu <wyu@talisys.com> — 2005-01-03T19:57:50Z

    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.

  19. Re: Low Performance for big hospital server ..

    Dave Cramer <pg@fastcrypt.com> — 2005-01-04T00:57:20Z

    
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

  20. Re: Low Performance for big hospital server ..

    Dave Cramer <pg@fastcrypt.com> — 2005-01-04T00:58:44Z

    
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