Re: High load,

On Thu, Jan 27, 2011 at 6:36 AM, Michael Kohl <michael(dot)kohl(at)tupalo(dot)com> wrote:
> 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
> Committed_AS:    5651528 kB
> 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)
>

Here I am still a big fan of setting
shared_buffers=8GB

for dbsize of 32GB that is a 25% in bufferpool ration
effective cache size then will be more like 8GB.

The only time this will hurt is you have more sequential access than
random which wont be populated in the shared_buffer but chances of
that being the problem is lowered with your random_page_cost set to
2.0 or lower.

Also I am a big fan of separating the WAL and data separately which
gives two advantages and monitoring the IO that way so you know where
your IO are coming from.. WAL or DATA and then further tuning can be
done according to what you see.

Also SSDs sometimes have trouble with varying sizes of WAL writes so
response times for WAL writes varies quite a bit and can confuse SSDs.

-Jignesh

>> 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.
>
> Thanks a lot for the feedback again,
> Michael
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>