-1

Lately, my company have decided to migrate our Spring application to Microsoft's Azure.
Azure gave us the possibility to monitor our DB (MySQL) queries and their runtime using appinsight.

A clarification - we investigate the traffic between our app (Azure cloud) and our DB (on-prem MySQL).

Upon investigation of the logs (telemetry logs) we have found that before most of the queries, a single query is being ran, the query:

SELECT ?;

This query takes up to 300 ms each time, then after that, the "real" query gets ran, which takes anywhere between few micro seconds to few hundreds of milliseconds.
We are trying to understand this lone query and where does it come from, to see if we can improve SQL queries runtime.

So far what we have done:

  1. I have altered my local logback.xml so that MySQL queries gets logged in a log file.
  2. I have integrated my local Spring instance to use p6spy so that I am able to see the queries that gets sent to the DB.

Conclusions I have came to:

  • With p6spy I observed a lone SELECT 1 query that being ran before each "real" query is being ran.
    I have investigated our configurations and saw that this is the "validationQuery" query (server.xml), but that should not take that long as the SELECT ? does.
  • I have read about the possibility that this query pre-loads the data from the DB to populate queried parameters. But that explanation seems too far fetched, and again, the run time is what bothering me.

Have you encountered anything similar? What can I do next to find the origin of that SELECT ? query.

Any help will be great.

Yaroslav Miloslavsky
  • 1
  • 3
  • 1
    I suspect the `SELECT 1` and `SELECT ?` are the same. Ideally you shouldn't need a `validationQuery`. Any modern JDBC (JDBC 4 and up) providate a special method to validate connections no need for a dedicated query. The `?` is a placeholder for a value, which is what is being send as the statement to prepare, parameters are send differently so the server can re-use a prepared statement. – M. Deinum Jul 31 '23 at 12:44

2 Answers2

1

Since you have confirmed that you are using spring.datasource.validationQuery=SELECT 1 property. You need to have a look at the other properties in your datasource configuration. The properties like 'test-on-borrow' and 'validation-interval' does make a difference. In your situation the validation query is being executed every time before connecting to the database

Raju Rudru
  • 1,102
  • 12
  • 19
  • 1
    I see lots of configurations like that that yes, for example "validationQueryTimeout" and "testWhileIdle", will try to tamper with these options, thanks! – Yaroslav Miloslavsky Aug 01 '23 at 08:58
0

This actually check the network latency, before any query.

A quick check before starting any performance benchmarking run is to determine the network latency between the client and database using a simple SELECT 1 query

see https://learn.microsoft.com/en-us/azure/mysql/single-server/concept-performance-best-practices

The problem of distributed webcontent is, that information can be stored anywhere and so it ican be votal for quick responces, you would check for the best server.

nbk
  • 45,398
  • 8
  • 30
  • 47