Thread: Too many SET TimeZone and Application_name queries

Too many SET TimeZone and Application_name queries

From

Amarendra Konda

Date:

11 October 2019, 14:49:10

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 ?

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

Re: Too many SET TimeZone and Application_name queries

From

Adrian Klaver

Date:

11 October 2019, 17:03:27

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

Re: Too many SET TimeZone and Application_name queries

From

Amarendra Konda

Date:

14 October 2019, 08:24:02

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/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

Re: Too many SET TimeZone and Application_name queries

From

Adrian Klaver

Date:

14 October 2019, 17:15:48

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

Re: Too many SET TimeZone and Application_name queries

From

Jeff Janes

Date:

20 October 2019, 21:22:29

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

Re: Too many SET TimeZone and Application_name queries

From

Amarendra Konda

Date:

22 October 2019, 11:57:20

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:

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