Skip site navigation (1) Skip section navigation (2)

Re: High load,

From: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>
To: Michael Kohl <michael(dot)kohl(at)tupalo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: High load,
Date: 2011-01-27 11:58:02
Message-ID: AANLkTin-6vhuHq18EwfEWeQyDoSBG+vGazbQurHEt6V-@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
2011/1/27 Michael Kohl <michael(dot)kohl(at)tupalo(dot)com>:
> Cédric, thanks a lot for your answer so far!
>
> On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
> <cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
>
>> you have swap used, IO on the swap partition ?
>
> Memory-wise we are fine.
>
>> can you paste the /proc/meminfo ?
>
> Sure:
>
> # cat /proc/meminfo
> MemTotal:       16461012 kB
> MemFree:          280440 kB
> Buffers:           60984 kB
> Cached:         13757080 kB
> SwapCached:         6112 kB
> Active:          7049744 kB
> Inactive:        7716308 kB
> Active(anon):    2743696 kB
> Inactive(anon):  2498056 kB
> Active(file):    4306048 kB
> Inactive(file):  5218252 kB
> Unevictable:           0 kB
> Mlocked:               0 kB
> SwapTotal:        999992 kB
> SwapFree:         989496 kB
> Dirty:              3500 kB
> Writeback:             0 kB
> AnonPages:        943752 kB
> Mapped:          4114916 kB
> Shmem:           4293312 kB
> Slab:             247036 kB
> SReclaimable:     212788 kB
> SUnreclaim:        34248 kB
> KernelStack:        3144 kB
> PageTables:       832768 kB
> NFS_Unstable:          0 kB
> Bounce:                0 kB
> WritebackTmp:          0 kB
> CommitLimit:     9230496 kB

the commitlimit looks to low, it is because your swap partition is small.

You need to either enlarge the swap partition, or change the
vm.overcommit_ratio if you want to be able to use more of your mermory
 sanely.
(
see kernel/Documentation/filesystems/proc.txt for the explanations on
the formula :
CommitLimit = ('vm.overcommit_ratio' * Physical RAM) + Swap
)

> Committed_AS:    5651528 kB

this is way under CommitLimit so you are good. (it is rare to be
limited by that anyway, and your perf issues are not relative to that)

> VmallocTotal:   34359738367 kB
> VmallocUsed:       51060 kB
> VmallocChunk:   34350787468 kB
> HardwareCorrupted:     0 kB
> HugePages_Total:       0
> HugePages_Free:        0
> HugePages_Rsvd:        0
> HugePages_Surp:        0
> Hugepagesize:       2048 kB
> DirectMap4k:        7936 kB
> DirectMap2M:    16760832 kB
>
>> Also turn on log_checkpoint if it is not already and check the
>> duration to write the data.
>
> Will do, thanks!
>
>> You didn't said the DB size (and size of active part of it), it would help here.
>
> => select pg_size_pretty(pg_database_size('xxx'));
>  pg_size_pretty
> ----------------
>  32 GB
> (1 row)
>
>> it is too much with 200 connections. you may experiment case where you
>> try to use more than the memory available.
>
> So far memory never really was a problem, but I'll keep these
> suggestions in mind.
>
>> 16MB should work well
>
> We already thought of increasing that, will do so now.
>
>>> effective_cache_size = 8192MB
>>
>> 12-14GB looks better
>
> Thank you, I was rather unsure on this on.
>
>> you use full_text_search ?
>
> Not anymore, probably a leftover.
>
>> do you monitor the 'locks' ? and the commit/rollbacks  ?
>
> No, but I'll look into doing that.

It may help to find what is the issue.

-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

In response to

pgsql-performance by date

Next:From: WaldomiroDate: 2011-01-27 12:10:01
Subject: Why I lost the last pg_xlog file?
Previous:From: Michael KohlDate: 2011-01-27 11:36:45
Subject: Re: High load,

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group