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.
Sure, I'd boost this.
> (*) wal_buffers = 8MB # pgtune wizard 2010-06-25
> (64kb , via default)
If you have the disk space for the 30 segments, I wouldn't reduce
> checkpoint_segments = 16 # pgtune wizard 2010-06-25
> (30 , specified)
it.
This one is perhaps the most sensitive to workload. Anywhere
> shared_buffers = 7680MB # pgtune wizard 2010-06-25
> (4096 MB , specified)
between 1GB and 8GB might be best for you. Greg Smith has some
great advice on how to tune this for your workload.
That's probably a good number to get to, but you have to reduce the
> (*) 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.
number of actual connections before you set the limit that low.
If any of these stay in that state for more than a minute or two,
> lot of our connections are in <IDLE> in transaction state
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
