Too many SET TimeZone and Application_name queries

On 10/11/19 4:49 AM, Amarendra Konda wrote:

Hi,

In our test environment, it was observed that there are too many queries
were getting fired to the database server, even though they are not part
of the SQL query execution.

And the number of queries that were coming to server are very high. Can
you please suggest on how to avoid these queries to the database server ?

My guess is your application server/framework is setting the below.
What are you using for above?

2019-10-10 13:37:25 UTC:172.31.77.194(36920):
user1@new_unity_green1:[2549]:LOG:  duration: 0.081 ms  statement: *SET
application_name='PostgreSQL JDBC Driver';*
2019-10-10 13:37:25 UTC:172.31.69.112(45682):
user1@new_unity_green0:[3545]:LOG:  duration: 0.036 ms  statement: *SET
TimeZone='UTC';*
2019-10-10 13:37:25
UTC:172.31.77.194(36902):user1@new_unity_green1:[2112]:LOG:  duration:
0.177 ms  statement: *SET TimeZone='Etc/UTC';SET
application_name='PostgreSQL JDBC Driver';*

*_Environment_*

* PGBouncer 1.9
* JDBC Driver 42.2.8
* Java 1.8
* PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit

Application Server, pgBouncer and database server are all configured
with UTC only.

=>show timezone;
 TimeZone
----------
 UTC

Thanks in advance,

Regards, Amarendra

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Thanks a lot for the response.

We are using JDBC Driver 42.2.8 along with the Tomcat Server on Java 8. As
part of application code, We are *not* setting timezone (or) application
names. One observation was, application was querying columns of the
datatype "timestamp without time zone" .

Regards, Amarendra

On Fri, Oct 11, 2019 at 7:33 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/13/19 10:24 PM, Amarendra Konda wrote:

Hi Adrian,

Thanks a lot for the response.

We are using JDBC Driver 42.2.8 along with the Tomcat Server on Java 8.
As part of application code, We are *_not_* setting timezone (or)
application names. One observation was, application was querying columns
of the datatype "timestamp without time zone" .

Well something is explicitly setting the TimeZone. Per this:

https://stackoverflow.com/questions/18447995/postgresql-9-2-jdbc-driver-uses-client-time-zone

I would start with the JDBC driver. You might also try the Postgres JDBC
list:

https://www.postgresql.org/list/pgsql-jdbc/

Re: application_name. I do not see SET for this when I connect using
application_name as part of connection string:

psql "host=localhost dbname=postgres user=postgres
application_name=psql_client"

[unknown]-[unknown]-2019-10-14 07:06:35.508 PDT-0LOG: connection
received: host=::1 port=46246
[unknown]-postgres-2019-10-14 07:06:35.530 PDT-0LOG: connection
authorized: user=postgres database=postgres SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256,
compression=off)

So I believe this is being explicitly SET by something. Since
'PostgreSQL JDBC Driver' is the Postgres JDBC driver name I would start
there.

Regards, Amarendra

On Fri, Oct 11, 2019 at 7:33 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/11/19 4:49 AM, Amarendra Konda wrote:

Hi,

In our test environment, it was observed that there are too many

queries

were getting fired to the database server, even though they are

not part

of the SQL query execution.

And the number of queries that were coming to server are very

high. Can

you please suggest on how to avoid these queries to the database

server ?

My guess is your application server/framework is setting the below.
What are you using for above?

2019-10-10 13:37:25 UTC:172.31.77.194(36920):
user1@new_unity_green1:[2549]:LOG:  duration: 0.081 ms

 statement: *SET

application_name='PostgreSQL JDBC Driver';*
2019-10-10 13:37:25 UTC:172.31.69.112(45682):
user1@new_unity_green0:[3545]:LOG:  duration: 0.036 ms

 statement: *SET

TimeZone='UTC';*
2019-10-10 13:37:25
UTC:172.31.77.194(36902):user1@new_unity_green1:[2112]:LOG:

 duration:

0.177 ms  statement: *SET TimeZone='Etc/UTC';SET
application_name='PostgreSQL JDBC Driver';*

*_Environment_*

   * PGBouncer 1.9
   * JDBC Driver 42.2.8
   * Java 1.8
   * PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc

(GCC) 4.8.3

     20140911 (Red Hat 4.8.3-9), 64-bit

Application Server, pgBouncer and database server are all configured
with UTC only.

=>show timezone;
   TimeZone
----------
   UTC

Thanks in advance,

Regards, Amarendra

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Oct 11, 2019 at 7:49 AM Amarendra Konda <amar.vijaya@gmail.com>
wrote:

Hi,

In our test environment, it was observed that there are too many queries
were getting fired to the database server,

What does "too many" mean here? Is it just more than you like to see in
your log file, or is there some objective problem?

These look like housekeeping queries which are executed by a connection
pooler each time a connection is checked out of the pool (or perhaps
checked back in). However, they don't seem to be the housekeeping queries
which pgbouncer itself uses. I don't think that JDBC automatically issues
them either, although that might depend on your configuration. So I think
that leaves Tomcat as the most likely culprit. Tomcat does offer a
connection pool. Are you using it?

Cheers,

Jeff

Hi Adrian,

Thanks a lot for the right pointer. Setting -Duser.timezone=UTC has solved
the problem. Now, we don't see any more queries related to *SET TimeZone.*
Thanks again for your time and valuable suggestion.

@Jeff : These queries were sent by the JDBC Driver latest changes, nothing
to do with the Tomcat server. On test server, We were seeing around 45 K +
queries with very minimal load.

Regards, Amarendra

On Mon, Oct 14, 2019 at 7:45 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote: