Re: postgresql 9.1 out of memory

2013/10/31 Kevin Grittner <kgrittn(at)ymail(dot)com>

> Please resend with a copy to the list, rather than to just me.
>
> -Kevin
>
>
> ------------------------------
> *From:* Евгений Селявка <evg(dot)selyavka(at)gmail(dot)com>
> *To:* Kevin Grittner <kgrittn(at)ymail(dot)com>
> *Sent:* Thursday, October 31, 2013 3:53 AM
> *Subject:* Re: [ADMIN] postgresql 9.1 out of memory
>
> Kevin thank you very much, really i set work_mem to 128MB. I monitor
> activity on my db cluster and there is about 100 concurrent connections.
> I think that my trouble could be also related with incorrect kernel
> setting.
>
> vm.swappiness = 15
> vm.overcommit_memory = 2
> vm.overcommit_ratio = 90
>
> I change to
> vm.overcommit_memory = 0
> vm.swappiness=30
> vm.dirty_background_bytes = 67108864
> vm.dirty_bytes = 536870912
>
> But now is another problem when my fs cache grow up, and then clenup all
> db process freeaze. For example: duration of select 1 is about 1000-500 ms.
>
> sar -B
> 12:00:01 AM pgpgin/s pgpgout/s fault/s majflt/s pgfree/s pgscank/s
> pgscand/s pgsteal/s %vmeff
> ...
> 01:50:01 PM 7.20 227.76 37726.31 0.00 18532.07
> 0.00 0.00 0.00 0.00
> 02:00:01 PM 10.27 246.87 37005.50 0.00 19326.56
> 0.00 0.00 0.00 0.00
> 02:10:01 PM 9.03 295.57 36891.37 0.00 19254.30
> 0.00 0.00 0.00 0.00
> 02:20:01 PM 53.56 251.34 54926.00 0.23 28884.46 353.07
> 521.79 874.83 100.00
> 02:30:01 PM 56.51 254.79 42914.24 0.01 26866.86
> 0.00 0.00 0.00 0.00
> 02:40:01 PM 49.96 298.62 46861.55 0.00 19883.76
> 0.00 0.00 0.00 0.00
>
> sar -r
> 12:00:01 AM kbmemfree kbmemused %memused kbbuffers kbcached kbcommit
> %commit
> ...
> 01:50:01 PM 1317664 31559528 95.99 477160 28905420
> 10197236 27.58
> 02:00:01 PM 1248996 31628196 96.20 478000 28917704
> 10217184 27.63
> 02:10:01 PM 1244356 31632836 96.22 478844 28929684
> 10223964 27.65
> 02:20:01 PM 3295792 29581400 89.98 420304 26938616
> 10233616 27.68
> 02:30:01 PM 3277168 29600024 90.03 421732 26979684
> 10228140 27.66
> 02:40:01 PM 3260916 29616276 90.08 423204 27017212
> 10214488 27.63
>
> Could anybody help me with advice?
>
>
>
>
>
> 2013/10/30 Kevin Grittner <kgrittn(at)ymail(dot)com>
>
> Евгений Селявка <evg(dot)selyavka(at)gmail(dot)com> wrote:
>
> > Server HW:
>
> > 32GB RAM
>
> > Before server crash i have this parameters in config:
> >
> > max_connections = 350
>
> > work_mem = 2GB
>
> > After crash i change this parameters:
>
> > work_mem = 1GB
>
> A good "rule of thumb" is to allow for one work_mem allocation per
> connection for a reasonable estimation of peak memory usage for
> this purpose. (This may need to be adjusted based on workload, but
> it's a reasonable place to start.) So you have adjusted likely
> peaks down from 700GB to 350GB.
>
> I usually start with work_mem set to RAM * 25% / max_connections.
> In your case that works out to 23MB. If you are able to use a
> connection pooler to reducee max_connections, that will allow you
> to boost work_mem.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
> --
> С уважением Селявка Евгений
>
>
>

--
С уважением Селявка Евгений