Re: How to avoid SET application_name = ''

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
>