Обсуждение: Too many SET TimeZone and Application_name queries
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.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
TimeZone
----------
UTC
Thanks in advance,
Regards, Amarendra
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/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
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:
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