Re: How to avoid SET application_name = ''

Daniel,

Thanks for the taking the time to do this.

Dave Cramer

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

On 26 November 2015 at 02:27, Daniel Blanch Bataller <
dblanch(at)hoplasoftware(dot)com> wrote:

> Hi everyone, this is my first post, so forgive me If I’m not respecting
> any protocol rule I haven’t read.
>
> I’ve run a small test, the driver doesn’t do that by it’s own on every
> statement if it’s inside a pool and correctly configured, i’m using bdcp.
> See by yourself:
>
> public DbcpExperiment() throws Exception {
> initializePool();
> }
>
> protected void initializePool() {
> basicDataSource = new BasicDataSource();
> basicDataSource.setDriverClassName("org.postgresql.Driver");
> basicDataSource.setUsername("test");
> basicDataSource.setPassword("test");
> basicDataSource.setUrl("jdbc:postgresql://
> 127.0.0.1:5432/test?ApplicationName=DbcpExperiment");
> basicDataSource.setInitialSize(1);
> basicDataSource.setMaxIdle(1);
> basicDataSource.setMaxActive(1);
> basicDataSource.setDefaultAutoCommit(false);
> }
>
> public void testApplicationName() throws Exception {
>
>
> Connection connection = null;
>
>
> for (int i = 0; i < 1000; i++) {
> connection = basicDataSource.getConnection();
> PreparedStatement preparedStatement = connection.prepareStatement("SELECT
> 1");
> preparedStatement.executeQuery();
> connection.commit();
> connection.close();
> System.out.println(i);
> }
>
>
> }
>
> test=# select calls, total_time, query FROM pg_stat_statements ORDER BY
> calls DESC;
> calls | total_time | query
> -------+------------------+-----------------------------------------
> 1000 | 4.85599999999995 | SELECT ?
> 1000 | 0.446 | BEGIN
> 1000 | 0.468 | COMMIT
> 2 | 0.057 | SET application_name = 'DbcpExperiment'
> 2 | 0.049 | SET extra_float_digits = 3
> 1 | 0.232 | SELECT pg_stat_statements_reset();
> (6 rows)
>
> test=#
>
>
> Pom snippet:
> ==========
>
> <dependencies>
> <dependency>
> <groupId>commons-dbcp</groupId>
> <artifactId>commons-dbcp</artifactId>
> <version>1.4</version>
> </dependency>
> <dependency>
> <groupId>junit</groupId>
> <artifactId>junit</artifactId>
> <version>3.8.1</version>
> <scope>test</scope>
> </dependency>
> <dependency>
> <groupId>org.postgresql</groupId>
> <artifactId>postgresql</artifactId>
> <version>9.4-1201-jdbc4</version>
> </dependency>
> </dependencies>
>
>
> Regards
>
> - Daniel
>
>
>
> El 25 nov 2015, a las 19:25, Thomas Kellerer <spam_eater(at)gmx(dot)net>
> escribió:
>
> 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.
>
>
>
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>