Fwd: How to avoid SET application_name = ''

+list

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

---------- Forwarded message ----------
From: Fabio Caiut <caiut(at)hotmail(dot)com>
Date: 25 November 2015 at 12:18
Subject: RE: [JDBC] How to avoid SET application_name = ''
To: Dave Cramer <pg(at)fastcrypt(dot)com>

Hi Dave,

One call is really ridiculously fast. ( avg_time = 0.15 ms )

55782793 calls, in more or less 2 hours real time collecting,
take 8212550.69 milisseconds

8212550.69 milisseconds
8212 seconds
136 minutes
2,2 hours

The problem is the number of calls.

We have already seen problems here of Glassfish + default Pool manager (I
don't know which one) + pg jdbc resulting in absurd open connection rate in
moments of high load.
I don't know if it is a related case.

I was just thinking that a simple solution (of my point of view, of course)
could be

IF appname = null OR appname = '' DO NOTHING

It seems that is sent appname='' a lot of times: in open connection, in
reset connection, in release connection, after a transaction, before a
transaction ...
I'm not sure when, because in PostgreSQL we just log slow queries (more
than 0,5s) ... so these commands don't appear in our common logs.
We get it with pg_stat_statements extension.

Caiut

------------------------------
From: pg(at)fastcrypt(dot)com
Date: Wed, 25 Nov 2015 11:56:26 -0500
Subject: Re: [JDBC] How to avoid SET application_name = ''
To: caiut(at)hotmail(dot)com
CC: pgsql-jdbc(at)postgresql(dot)org

SET application name should be ridiculously fast. What are the units ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 25 November 2015 at 10:20, Fabio Caiut <caiut(at)hotmail(dot)com> wrote:

Hello,

I'm trying to understand the "SET application_name" command sent by the
driver.

We have a database server with high concurrency, ~25K TPS, and looking the
top queries (with pg_stat_statements view) I get it's the query taking more
time.

The command is very fast but the number of calls is huge.

TOP 10 Queries (only ~2 hours collecting)

calls | total_time | avg_time | rows | avg_rows | hit_percent |

----------+------------+----------+----------+----------+-------------+------------------------------
55782793 | 8212550.69 | 0.15 | 0 | 0.00 | | SET
application_name = ''
4425751 | 322475.14 | 0.07 | 138606 | 0.00 | 98.98 | /*
load one-to-many ...
4097384 | 963780.17 | 0.24 | 84514 | 0.00 | 99.77 | /*
load ...
3908144 | 366598.92 | 0.09 | 3834578 | 0.00 | 100.00 | /*
load ...
2596097 | 480030.01 | 0.18 | 2596097 | 1.00 | 100.00 | /*
SELECT count(*) FROM ...
2365353 | 290965.73 | 0.12 | 2365335 | 0.00 | 99.99 | /*
load ...
2081011 | 127373.91 | 0.06 | 379289 | 0.00 | 99.59 | /*
load collection ...
1303931 | 145544.50 | 0.11 | 1303925 | 0.00 | 95.94 | /*
load ...
1274982 | 577683.73 | 0.45 | 1274982 | 1.00 | 100.00 | /*
Select count(*) from ...
1203475 | 1522327.87 | 1.26 | 4405775 | 3.00 | 89.44 | /*
load ...

It takes ~8K seconds, more than 2 hours, it means that this command can use
more than one core all the time! (if I'm understanding right)

I'm thinking as the application name is empty maybe this command could be
avoid.
I have asked our Glassfish people to "unmark" this option if possible but
they told me they don't know how to do it, where to do it.

I'm not sure if the quantity of calls is a problem in application, in
glassfish or in driver. But, if it's possible tune to avoid it, I would
like that.

Any ideas?

Thanks!

Caiut