Re: Hardware upgrade for a high-traffic database

> -----Original Message-----
> From: Rod Taylor [mailto:pg(at)rbt(dot)ca]
> Sent: Wednesday, August 11, 2004 5:46 PM
> To: Jason Coene
> Cc: 'Merlin Moncure'; Postgresql Performance
> Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database
>
> > I'm wondering why our PG server is using so little memory... The system
> has
> > 2GB of memory, though only around 200MB of it are used. Is there a PG
>
> This is the second time you've said this. Surely you're not implying
> there is 1.8GB Free Memory -- rather than 1.8GB in Buffers or Cache.

Hi Rod,

I was looking at top and vmstat - which always show under 300MB "Active".
We may hit 400MB at peak. Everything I see (though this isn't my area of
expertise) points to most of the memory simply being unused. Results below,
am I missing something?

Jason

>
> Send output of the below:
>
> sysctl vm

d01> sysctl vm
vm.vmtotal:
System wide totals computed every five seconds: (values in kilobytes)
===============================================
Processes: (RUNQ: 1 Disk Wait: 0 Page Wait: 0 Sleep: 149)
Virtual Memory: (Total: 2101614K, Active 440212K)
Real Memory: (Total: 2023532K Active 327032K)
Shared Virtual Memory: (Total: 14356K Active: 3788K)
Shared Real Memory: (Total: 4236K Active: 2456K)
Free Memory Pages: 88824K

vm.loadavg: { 0.46 0.41 0.42 }
vm.v_free_min: 3312
vm.v_free_target: 13997
vm.v_free_reserved: 749
vm.v_inactive_target: 20995
vm.v_cache_min: 13997
vm.v_cache_max: 27994
vm.v_pageout_free_min: 34
vm.pageout_algorithm: 0
vm.swap_enabled: 1
vm.swap_async_max: 4
vm.dmmax: 32
vm.nswapdev: 1
vm.swap_idle_threshold1: 2
vm.swap_idle_threshold2: 10
vm.v_free_severe: 2030
vm.stats.sys.v_swtch: 627853362
vm.stats.sys.v_trap: 3622664114
vm.stats.sys.v_syscall: 1638589210
vm.stats.sys.v_intr: 3250875036
vm.stats.sys.v_soft: 1930666043
vm.stats.vm.v_vm_faults: 3197534554
vm.stats.vm.v_cow_faults: 2999625102
vm.stats.vm.v_cow_optim: 10093309
vm.stats.vm.v_zfod: 3603956919
vm.stats.vm.v_ozfod: 3104475907
vm.stats.vm.v_swapin: 3353
vm.stats.vm.v_swapout: 3382
vm.stats.vm.v_swappgsin: 3792
vm.stats.vm.v_swappgsout: 7213
vm.stats.vm.v_vnodein: 14675
vm.stats.vm.v_vnodeout: 140671
vm.stats.vm.v_vnodepgsin: 24330
vm.stats.vm.v_vnodepgsout: 245840
vm.stats.vm.v_intrans: 3643
vm.stats.vm.v_reactivated: 35038
vm.stats.vm.v_pdwakeups: 26984
vm.stats.vm.v_pdpages: 335769007
vm.stats.vm.v_dfree: 8
vm.stats.vm.v_pfree: 1507856856
vm.stats.vm.v_tfree: 430723755
vm.stats.vm.v_page_size: 4096
vm.stats.vm.v_page_count: 512831
vm.stats.vm.v_free_reserved: 749
vm.stats.vm.v_free_target: 13997
vm.stats.vm.v_free_min: 3312
vm.stats.vm.v_free_count: 968
vm.stats.vm.v_wire_count: 62039
vm.stats.vm.v_active_count: 44233
vm.stats.vm.v_inactive_target: 20995
vm.stats.vm.v_inactive_count: 343621
vm.stats.vm.v_cache_count: 21237
vm.stats.vm.v_cache_min: 13997
vm.stats.vm.v_cache_max: 27994
vm.stats.vm.v_pageout_free_min: 34
vm.stats.vm.v_interrupt_free_min: 2
vm.stats.vm.v_forks: 45205536
vm.stats.vm.v_vforks: 74315
vm.stats.vm.v_rforks: 0
vm.stats.vm.v_kthreads: 2416
vm.stats.vm.v_forkpages: 1464383994
vm.stats.vm.v_vforkpages: 4259727
vm.stats.vm.v_rforkpages: 0
vm.stats.vm.v_kthreadpages: 0
vm.stats.misc.zero_page_count: 709
vm.stats.misc.cnt_prezero: -972664922
vm.max_proc_mmap: 34952
vm.msync_flush_flags: 3
vm.idlezero_enable: 1
vm.idlezero_maxrun: 16
vm.max_launder: 32
vm.pageout_stats_max: 13997
vm.pageout_full_stats_interval: 20
vm.pageout_stats_interval: 5
vm.pageout_stats_free_max: 5
vm.swap_idle_enabled: 0
vm.defer_swapspace_pageouts: 0
vm.disable_swapspace_pageouts: 0
vm.pageout_lock_miss: 0
vm.zone:
ITEM SIZE LIMIT USED FREE REQUESTS

FFS2 dinode: 256, 0, 30156, 4389, 20093512
FFS1 dinode: 128, 0, 0, 0, 0
FFS inode: 140, 0, 30156, 4340, 20093512
SWAPMETA: 276, 121576, 16, 264, 44599
ripcb: 180, 32780, 0, 132, 289
hostcache: 88, 15390, 6, 309, 741
syncache: 104, 15390, 0, 418, 44592418
tcptw: 56, 6603, 3, 1204, 224900
tcpcb: 368, 32769, 136, 4264, 44594153
inpcb: 180, 32780, 139, 4437, 44594153
udpcb: 180, 32780, 10, 144, 85953
unpcb: 140, 32788, 6, 246, 143982
socket: 240, 32768, 152, 4248, 44824378
KNOTE: 64, 0, 0, 434, 7561
PIPE: 172, 0, 8, 222, 352848
NFSNODE: 460, 0, 1596, 92, 2419
NFSMOUNT: 424, 0, 1, 17, 1
DIRHASH: 1024, 0, 238, 86, 287
L VFS Cache: 291, 0, 165, 160, 11956
S VFS Cache: 68, 0, 38283, 3430, 3795133
NAMEI: 1024, 0, 0, 240, 907013101
VNODEPOLL: 60, 0, 1, 131, 2
VNODE: 260, 0, 34104, 36, 34104
g_bio: 136, 0, 0, 5887, 551700514
VMSPACE: 236, 0, 152, 987, 45279840
UPCALL: 44, 0, 0, 0, 0
KSE: 64, 0, 1224, 202, 1224
KSEGRP: 120, 0, 1224, 109, 1224
THREAD: 312, 0, 1224, 84, 1224
PROC: 452, 0, 261, 963, 45282231
Files: 68, 0, 782, 5413, 719968279
4096: 4096, 0, 441, 1935, 90066743
2048: 2048, 0, 237, 423, 25077
1024: 1024, 0, 23, 157, 448114
512: 512, 0, 108, 140, 770519
256: 256, 0, 458, 1102, 70685682
128: 128, 0, 1904, 1041, 186085712
64: 64, 0, 5124, 13042, 1404464781
32: 32, 0, 1281, 1302, 839881182
16: 16, 0, 842, 1548, 1712031683
DP fakepg: 72, 0, 0, 0, 0
PV ENTRY: 28, 2166780, 157829, 769251, 56650653911
MAP ENTRY: 60, 0, 6716, 33280, 2270740046
KMAP ENTRY: 60, 65538, 24, 702, 152938
MAP: 160, 0, 9, 41, 2
VM OBJECT: 132, 0, 21596, 10654, 1136467083
128 Bucket: 524, 0, 3115, 0, 0
64 Bucket: 268, 0, 200, 10, 0
32 Bucket: 140, 0, 191, 5, 0
16 Bucket: 76, 0, 49, 3, 0
UMA Hash: 128, 0, 0, 31, 0
UMA Slabs: 34, 0, 3095, 95, 0
UMA Zones: 432, 0, 52, 2, 0

vm.kvm_size: 1069543424
vm.kvm_free: 364900352

>
> sysctl -a | grep buffers

d01 > sysctl -a | grep buffers
vfs.numdirtybuffers: 52
vfs.lodirtybuffers: 909
vfs.hidirtybuffers: 1819
vfs.numfreebuffers: 7146
vfs.lofreebuffers: 404
vfs.hifreebuffers: 808
>
> top | grep -E "(Mem|Swap):"
>

d01 > top | grep -E "(Mem|Swap):"
Mem: 173M Active, 1346M Inact, 242M Wired, 77M Cache, 112M Buf, 5784K Free
Swap: 4096M Total, 124K Used, 4096M Free