Re: postgresql recommendation memory

[Евгений Селявка <evg.selyavka@xxxxxxxxx>]

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.janes@xxxxxxxxx>

On Thu, Nov 7, 2013 at 2:13 AM, Евгений Селявка <evg.selyavka@xxxxxxxxx> 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

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