Re: How to avoid SET application_name = ''

Fabio Caiut schrieb am 25.11.2015 um 16:20:
> 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.

Is it possible that this is configured as a "connection test" statement with your connection pool in Glassfish?
Including something like "test-on-borrow" (or test-on-return) so that this would send each time a
connection is taken out of the pool (or returned).

I don't think the driver sends this "on its own" without any trigger from the application.
The statement is sent if Connection.setClientInfo() is called, so maybe that called from somewhere in your application or the connection pool.