Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server
On Sun, Dec 12, 2010 at 09:49:52PM -0800, savio rodriges wrote: > Hello, > > We are facing very HIGH memory utilization on postgreSQL server and need help. which number from all of what's below is making you worried? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server
Hello Savio, > top - 21:43:35 up 55 days, 8:07, 4 users, load average: 0.05, > 0.25, 0.17 Tasks: 257 total, 1 running, 256 sleeping, 0 stopped, > 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, > 0.0%hi, 0.0%si, 0.0%st Mem: 8165696k total, 7943160k used, > 222536k free, 282044k buffers Swap: 8385920k total, 112828k > used, 8273092k free, 4793732k cached this looks kind of normal behaviour. Your system uses almost your entire ram right, but if you look at buffers and cached in your top output about 5 GB of your 8GB are used there. Only the remaining 3GB are used by postgres or other applications. Every "new" linux I know will use non-used ram for caching your filesystem data for example to achieve 100% memory usage. This is "a good thing" (TM) as postgres will run better if there is caching on os level and the instance knows about this: > effective_cache_size = — This value tells PostgreSQL's > optimizer how much memory PostgreSQL has available for caching data > and helps in determing whether or not it use an index or not. The > larger the value increases the likely hood of using an index. This > should be set to the amount of memory allocated to shared_buffers > plus the amount of OS cache available. Often this is more than 50% of > the total system memory. So don't worry - everything is fine. You should pay attention that your system doesn't start swapping. Cheers, Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server
On Mon, Dec 13, 2010 at 4:49 PM, savio rodriges wrote: > Hello, > > We are facing very HIGH memory utilization on postgreSQL server and need > help. > > Mem: 8165696k total, 7943160k used, 222536k free, 282044k buffers > Swap: 8385920k total, 112828k used, 8273092k free, 4793732k cached > What makes you say memory usage is high? You have ~8gb of memory. Less than 3gb of that is being used by applications and the OS is using the "spare" 5gb for caching and buffers. Paul
Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server
On Mon, Dec 13, 2010 at 8:49 AM, savio rodriges wrote: > Hello, > > We are facing very HIGH memory utilization on postgreSQL server and need help. > > Below are details of PostgreSQL server, > > === > MemTotal: 8165696 kB > CpuTotal: 8 > === > /etc/sysctl.conf > > # Controls the maximum shared segment size, in bytes > kernel.shmmax = 68719476736 > > # Controls the maximum number of shared memory segments, in pages > kernel.shmall = 4294967296 > === > top - 21:43:35 up 55 days, 8:07, 4 users, load average: 0.05, 0.25, 0.17 > Tasks: 257 total, 1 running, 256 sleeping, 0 stopped, 0 zombie > Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st > Mem: 8165696k total, 7943160k used, 222536k free, 282044k buffers > Swap: 8385920k total, 112828k used, 8273092k free, 4793732k cached > === > -bash-3.2$ free -m > total used free shared buffers cached > Mem: 7974 7756 217 0 275 4681 > -/+ buffers/cache: 2799 5174 > Swap: 8189 110 8079 > > === > -bash-3.2$ cat /proc/meminfo > MemTotal: 8165696 kB > MemFree: 222576 kB > Buffers: 282136 kB > Cached: 4793748 kB > SwapCached: 21144 kB > Active: 3508524 kB > Inactive: 4004532 kB > HighTotal: 0 kB > HighFree: 0 kB > LowTotal: 8165696 kB > LowFree: 222576 kB > SwapTotal: 8385920 kB > SwapFree: 8273092 kB > Dirty: 548 kB > Writeback: 368 kB > AnonPages: 2415992 kB > Mapped: 730104 kB > Slab: 332440 kB > PageTables: 58632 kB > NFS_Unstable: 0 kB > Bounce: 0 kB > CommitLimit: 12468768 kB > Committed_AS: 4774104 kB > VmallocTotal: 34359738367 kB > VmallocUsed: 267392 kB > VmallocChunk: 34359470967 kB > HugePages_Total: 0 > HugePages_Free: 0 > HugePages_Rsvd: 0 > Hugepagesize: 2048 kB > === > processor : Total 8 > vendor_id : GenuineIntel > cpu family : 6 > model : 26 > model name : Intel(R) Xeon(R) CPU E5504 @ 2.00GHz > stepping : 5 > cpu MHz : 2000.118 > cache size : 4096 KB > physical id : 1 > siblings : 4 > core id : 3 > cpu cores : 4 > apicid : 22 > fpu : yes > fpu_exception : yes > cpuid level : 11 > wp : yes > flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca > cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp > lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr > sse4_1 sse4_2 popcnt lahf_lm > bogomips : 4000.10 > clflush size : 64 > cache_alignment : 64 > address sizes : 40 bits physical, 48 bits virtual > power management: [8] > === > -bash-3.2$ ipcs > > -- Shared Memory Segments > key shmid owner perms bytes nattch status > 0x0052e2c1 35454978 postgres 600 1142923264 28 > > -- Semaphore Arrays > key semid owner perms nsems > 0x0052e2c1 37650432 postgres 600 17 > 0x0052e2c2 37683201 postgres 600 17 > 0x0052e2c3 37715970 postgres 600 17 > 0x0052e2c4 37748739 postgres 600 17 > 0x0052e2c5 37781508 postgres 600 17 > 0x0052e2c6 37814277 postgres 600 17 > 0x0052e2c7 37847046 postgres 600 17 > > -- Message Queues > key msqid owner perms used-bytes messages > === > -bash-3.2$ ipcs -l > > -- Shared Memory Limits > max number of segments = 4096 > max seg size (kbytes) = 67108864 > max total shared memory (kbytes) = 17179869184 > min seg size (bytes) = 1 > > -- Semaphore Limits > max number of arrays = 128 > max semaphores per array = 250 > max semaphores system wide = 32000 > max ops per semop call = 32 > semaphore max value = 32767 > > -- Messages: Limits > max queues system wide = 16 > max size of message (bytes) = 65536 > default max size of queue (bytes) = 65536 > === > /var/lib/pgsql/data/postgresql.conf > --- > (Non-default values are as follows) > > maintenance_work_mem = 480MB > checkpoint_completion_targe
[GENERAL] Urgent -- High memory usage on PostgreSQL server
Hello, We are facing very HIGH memory utilization on postgreSQL server and need help. Below are details of PostgreSQL server, === MemTotal: 8165696 kB CpuTotal: 8 === /etc/sysctl.conf # Controls the maximum shared segment size, in bytes kernel.shmmax = 68719476736 # Controls the maximum number of shared memory segments, in pages kernel.shmall = 4294967296 === top - 21:43:35 up 55 days, 8:07, 4 users, load average: 0.05, 0.25, 0.17 Tasks: 257 total, 1 running, 256 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8165696k total, 7943160k used, 222536k free, 282044k buffers Swap: 8385920k total, 112828k used, 8273092k free, 4793732k cached === -bash-3.2$ free -m total used free sharedbuffers cached Mem: 7974 7756217 0275 4681 -/+ buffers/cache: 2799 5174 Swap: 8189110 8079 === -bash-3.2$ cat /proc/meminfo MemTotal: 8165696 kB MemFree:222576 kB Buffers:282136 kB Cached:4793748 kB SwapCached: 21144 kB Active:3508524 kB Inactive: 4004532 kB HighTotal: 0 kB HighFree:0 kB LowTotal: 8165696 kB LowFree:222576 kB SwapTotal: 8385920 kB SwapFree: 8273092 kB Dirty: 548 kB Writeback: 368 kB AnonPages: 2415992 kB Mapped: 730104 kB Slab: 332440 kB PageTables: 58632 kB NFS_Unstable:0 kB Bounce: 0 kB CommitLimit: 12468768 kB Committed_AS: 4774104 kB VmallocTotal: 34359738367 kB VmallocUsed:267392 kB VmallocChunk: 34359470967 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 Hugepagesize: 2048 kB === processor : Total 8 vendor_id : GenuineIntel cpu family : 6 model : 26 model name : Intel(R) Xeon(R) CPU E5504 @ 2.00GHz stepping: 5 cpu MHz : 2000.118 cache size : 4096 KB physical id : 1 siblings: 4 core id : 3 cpu cores : 4 apicid : 22 fpu : yes fpu_exception : yes cpuid level : 11 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm bogomips: 4000.10 clflush size: 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: [8] === -bash-3.2$ ipcs -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x0052e2c1 35454978 postgres 6001142923264 28 -- Semaphore Arrays keysemid owner perms nsems 0x0052e2c1 37650432 postgres 60017 0x0052e2c2 37683201 postgres 60017 0x0052e2c3 37715970 postgres 60017 0x0052e2c4 37748739 postgres 60017 0x0052e2c5 37781508 postgres 60017 0x0052e2c6 37814277 postgres 60017 0x0052e2c7 37847046 postgres 60017 -- Message Queues keymsqid owner perms used-bytes messages === -bash-3.2$ ipcs -l -- Shared Memory Limits max number of segments = 4096 max seg size (kbytes) = 67108864 max total shared memory (kbytes) = 17179869184 min seg size (bytes) = 1 -- Semaphore Limits max number of arrays = 128 max semaphores per array = 250 max semaphores system wide = 32000 max ops per semop call = 32 semaphore max value = 32767 -- Messages: Limits max queues system wide = 16 max size of message (bytes) = 65536 default max size of queue (bytes) = 65536 === /var/lib/pgsql/data/postgresql.conf --- (Non-default values are as follows) maintenance_work_mem = 480MB checkpoint_completion_target = 0.7 work_mem = 36MB wal_buffers = 4MB checkpoint_segments = 8 shared_buffers = 1024MB log_duration = on log_min_duration_statement = 1 effective_cache_size=2048MB === Any idea where is the issue and what needs to be changed ? Thank