Fwd: How to avoid SET application_name = '' - Mailing list pgsql-jdbc
| From | Dave Cramer |
|---|---|
| Subject | Fwd: How to avoid SET application_name = '' |
| Date | |
| Msg-id | CADK3HHLKxOG=nQK3qme+fcR4gpbdYKu+h0f9w8NAxJxaBid0tA@mail.gmail.com Whole thread Raw |
| In response to | How to avoid SET application_name = '' (Fabio Caiut <caiut@hotmail.com>) |
| List | pgsql-jdbc |
+list
---------- Forwarded message ----------
From: Fabio Caiut <caiut@hotmail.com>
Date: 25 November 2015 at 12:18
Subject: RE: [JDBC] How to avoid SET application_name = ''
To: Dave Cramer <pg@fastcrypt.com>
From: Fabio Caiut <caiut@hotmail.com>
Date: 25 November 2015 at 12:18
Subject: RE: [JDBC] How to avoid SET application_name = ''
To: Dave Cramer <pg@fastcrypt.com>
Hi Dave,
From: pg@fastcrypt.com
Date: Wed, 25 Nov 2015 11:56:26 -0500
Subject: Re: [JDBC] How to avoid SET application_name = ''
To: caiut@hotmail.com
CC: pgsql-jdbc@postgresql.org
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.
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@fastcrypt.com
Date: Wed, 25 Nov 2015 11:56:26 -0500
Subject: Re: [JDBC] How to avoid SET application_name = ''
To: caiut@hotmail.com
CC: pgsql-jdbc@postgresql.org
SET application name should be ridiculously fast. What are the units ?
On 25 November 2015 at 10:20, Fabio Caiut <caiut@hotmail.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
pgsql-jdbc by date: