Re: heavy swapping, not sure why - Mailing list pgsql-general

On Mon, Aug 29, 2011 at 2:51 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Aug 29, 2011 at 3:38 PM, Lonni J Friedman <netllama@gmail.com> wrote:
>> On Mon, Aug 29, 2011 at 2:24 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>> On Mon, Aug 29, 2011 at 2:57 PM, Lonni J Friedman <netllama@gmail.com> wrote:
>>>> On Mon, Aug 29, 2011 at 1:46 PM, Alan Hodgson <ahodgson@simkin.ca> wrote:
>>>>> On August 29, 2011 01:36:07 PM Lonni J Friedman wrote:
>>>>>> I have several Linux-x68_64 based dedicated PostgreSQL servers where
>>>>>> I'm experiencing significant swap usage growth over time.
>>>>>
>>>>> It's the Linux kernel that does it, not PostgreSQL. Set vm.swappiness=0
>>>>> (usually in /etc/sysctl.conf) and put that into effect.
>>>>
>>>> I understand that the kernel determines what is swapped out, however
>>>> postgres is what is using nearly all the RAM, and then overflowing
>>>> into swap.  I guess I should have noted that this isn't a case of a
>>>> significant amount of RAM not being used, and swapping occurring
>>>> anyway.  Most of the RAM is already consumed when the heavy swapping
>>>> is happening.  So, I'd be surprised if setting vm.swappiness=0 will
>>>> make a significant difference, however I can certainly try.
>>>
>>> You haven't shown us how you determined this, it would be nice to see
>>> some copy and paste of things like top, free, or whatever.   How much
>>> free AND cache is left over when the machine starts to run out of
>>> memory etc.
>>
>> Sorry, I was looking at the output from 'free' (plus we have some
>> generic monitoring tools which generate pretty graphs that also
>> illustrate the problem).  I restarted postgres this morning, so
>> everything is in a good state right now:
>>             total       used       free     shared    buffers     cached
>> Mem:         56481      55486        995          0         15      53298
>> -/+ buffers/cache:       2172      54309
>> Swap:         1099         18       1081
>>
>>
>>             total       used       free     shared    buffers     cached
>> Mem:        121177     111603       9573          0          0     101007
>> -/+ buffers/cache:      10596     110581
>> Swap:         1498         10       1488
>>
>> Based on past results, it'll be about two weeks before a few hundred
>> MB of swap is in use, and perf is noticeably poor.
>
> That's all a few hundred Megs?  That shouldn't make any real
> difference.  Now a few dozen gigs that would make a difference.  Use
> top or htop or some other method that shows you the VIRT RES and SHR
> memory usage of the processes.

Yes, a few hundred MB of swap, and its definitely making a huge
difference.  Upon restarting postgres, its all freed up, and then perf
is good again.  Also, this box only has 1GB of swap total, so its
never going to get up a few dozen GB.

Anyway, here's some of top output for systemA right now:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5582 postgres  20   0 13.5g 8.9g 8.9g R 97.7 16.2   2:51.61 postmaster
 6554 postgres  20   0 13.5g 1.9g 1.9g D 63.8  3.4   1:50.50
postmaster
 6052 postgres  20   0 13.5g 1.3g 1.2g S 22.6  2.3   0:26.33
postmaster
 2751 postgres  20   0 13.5g 1.6g 1.6g S 21.6  2.8   0:52.32 postmaster
31221 postgres  20   0 13.5g 2.0g 2.0g S 10.0  3.6   1:19.05
postmaster
 1721 postgres  20   0 13.5g 6.7g 6.7g S  3.0 12.2   2:19.21
postmaster
 6050 postgres  20   0 13.5g 879m 867m S  8.3  1.6   0:06.89 postmaster

I can certainly grab more in a few days once swap usage has started to
creep up a bit.

>
>>  Although it will
>> creep up over time, so even in a day or 2, it will be worse than right
>> now.
>>
>> I could post the pretty graph somewhere (or send it to the list, if
>> you'd prefer) if you want to see something right now (filesize is less
>> than 40KB).
>>
>>>
>>> Your settings for shared_memory are HUGE.  I run a machine witih 128G
>>> of ram and my shared_memory is 8G and that's quite large.  No testing
>>> anyone has done has shown anything over 10G being useful.
>>
>> Do you mean shared_buffers?
>
> Yeah

OK, I'll reduce it to 10GB and see if there's any noticable change in
performance.  thanks