Re: postgresql recommendation memory

Sergey i will try to monitor my pgsql activity for several days.

Scott about pooling connection. Yesterday i start read about spring
implementation of jdbc our app use dbcp implementation:
http://commons.apache.org/proper/commons-dbcp/configuration.html
So i have this parameter in config
<property name="maxActive" value="15" />
<property name="maxIdle" value="1" />
<property name="maxWait" value="10000" />
<property name="validationQuery" value="SELECT 1" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="60" />
And i have several app that initialize and use this driver. Then i
calculate all of this initializing datasource - result is 380 active
connections. I simple add all maxActive node directive from all app dbcp
config xml. But as i write earlier this is about 100 concurrent connection
when i do 'select count(1) from pg_stat_activity'. I think that inexpedient
to install pgbouncer in front off db, or may be somebody in this list
have some experience with pgbouncer and dbcp? Why i don't want to use
pgbouncer:
1. I should use session mode because transaction doesn't support prepared
statement.
2. If i use session mode, i will have the same number max connection to DB,
because dbcp open connection to pgbouncer pgbouncer to DB and nobody close
this connection, only dbcp first, if i understand all correct. So if
overload happen i will have the same 380 heavyweight connection to DB and
all breaks down?

I think that i should correctly configure my dbcp pool config xml file.

2013/11/12 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>

> On Thu, Nov 7, 2013 at 2:13 AM, Евгений Селявка <evg(dot)selyavka(at)gmail(dot)com>wrote:
>
>> All my sar statistics
>>
> ...
>
>> sar -u ALL
>> 11:40:02 AM CPU %usr %nice %sys %iowait
>> %steal %irq %soft %guest %idle
>> 01:15:01 PM all 8.57 0.00 1.52 1.46
>> 0.00 0.00 0.05 0.00 88.40
>> 01:20:01 PM all 8.50 0.00 1.53 1.61
>> 0.00 0.00 0.05 0.00 88.31
>> 01:25:01 PM all 9.00 0.00 1.78 1.27
>> 0.00 0.00 0.06 0.00 87.89
>> 01:30:01 PM all 9.58 0.00 1.63 1.71
>> 0.00 0.00 0.06 0.00 87.01
>> 01:35:01 PM all 8.75 0.00 1.47 1.57
>> 0.00 0.00 0.06 0.00 88.15
>>
>
>
> Did a freeze-up occur in there someplace? Otherwise, that is not not so
> useful.
>
> You should try to decrease the sar interval to 1 min if you can. The
> extra overhead is negligible and the extra information can be very
> valuable. I'd also have something like "vmstat 5" running and capture
> that. Although perhaps one of the options to sar other than -u capture
> that same information, I know little of the other sar options.
>
> Cheers,
>
> Jeff
>

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