Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)

Dear List,

Today has been good since morning. Although it is a lean day
for us but the indications are nice. I thank everyone who shared
the concern. I think the most significant change has been to reduce
shared_buffers from 10G to 4G , this has lead to reduced memory
usage and some breathing space to the OS.

Although i am yet to incorporate the suggestions from pgtune but
i think the issue of max_connection needs to be addressed first.

I am investigating application issues and about the mechanism that
puts many backend to '<IDLE> in transaction ' mode for significant
times. I thank Tom for the script he sent. Once that resolves i shall
check pooling as suggested by Kevin, then eventually max_connections
can be reduced. I shall also check pgpool and pgbouncer if they are
helpful in this regard.

I observed that the number of simultaneous connection today (lean day)
hovers between 1 to 10 , occasionally shooting to 15 but never more than
20 i would say.

I am happy that i/o waits are negligible and cpu is idling also for a while.

procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy
id wa st
22 0 18468 954120 13460 28491772 0 0 568 1558 13645 18355 62 10
27 2 0
16 0 18468 208100 13476 28469084 0 0 580 671 14039 17055 67 13
19 1 0
10 2 18812 329032 13400 28356972 0 46 301 1768 13848 17884 68 10
20 1 0
16 2 18812 366596 13416 28361620 0 0 325 535 13957 16649 72 11
16 1 0
50 1 18812 657048 13432 28366548 0 0 416 937 13823 16667 62 9
28 1 0
6 1 18812 361040 13452 28371908 0 0 323 522 14352 16789 74 12
14 0 0
33 0 18812 162760 12604 28210152 0 0 664 1544 14701 16315 66 11
22 2 0
5 0 18812 212028 10764 27921800 0 0 552 648 14567 17737 67 10
21 1 0
6 0 18796 279920 10548 27890388 3 0 359 562 12635 15976 60 9
30 1 0
8 0 18796 438820 10564 27894440 0 0 289 2144 12234 15770 57 8
34 1 0
5 0 18796 531800 10580 27901700 0 0 514 394 12169 16005 59 8
32 1 0
17 0 18796 645868 10596 27890704 0 0 423 948 13369 16554 67 10
23 1 0
9 1 18796 1076540 10612 27898604 0 0 598 403 12703 17363 71 10
18 1 0
8 0 18796 1666508 10628 27904748 0 0 430 1123 13314 17421 57 9
32 1 0
9 1 18776 1541444 10644 27913092 1 0 653 954 13194 16822 75 11
12 1 0
8 0 18776 1526728 10660 27921380 0 0 692 788 13073 16987 74 9
15 1 0
8 0 18776 1482304 10676 27933176 0 0 966 2029 13017 16651 76 12
11 1 0
21 0 18776 1683260 10700 27937492 0 0 298 663 13110 15796 67 10
23 1 0
18 0 18776 2087664 10716 27943512 0 0 406 622 12399 17072 62 9
28 1 0

With 300 connections, I think that either of these could lead you to
> experience intermittent bursts of extreme swapping. I'd drop it to
> somewhere in the 16MB to 32MB range until I had a connection pool
> configured such that it was actually keeping the number of active
> connections much lower.
>
> > (*) wal_buffers = 8MB # pgtune wizard 2010-06-25
> > (64kb , via default)
>
> Sure, I'd boost this.
>
> > checkpoint_segments = 16 # pgtune wizard 2010-06-25
> > (30 , specified)
>
> If you have the disk space for the 30 segments, I wouldn't reduce
> it.
>
> > shared_buffers = 7680MB # pgtune wizard 2010-06-25
> > (4096 MB , specified)
>
> This one is perhaps the most sensitive to workload. Anywhere
> between 1GB and 8GB might be best for you. Greg Smith has some
> great advice on how to tune this for your workload.
>
> > (*) max_connections = 80 # pgtune wizard 2010-06-25
> > (300 , ;-) specified)
> >
> > when i reduce max_connections i start getting errors, i will see
> > again concurrent connections during business hours.
>
> That's probably a good number to get to, but you have to reduce the
> number of actual connections before you set the limit that low.
>
> > lot of our connections are in <IDLE> in transaction state
>
> If any of these stay in that state for more than a minute or two,
> you need to address that if you want to get your connection count
> under control. If any of them persist for hours or days, you need
> to fix it to avoid bloat which can kill performance.
>
> -Kevin
>