Question 220000 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – A weird query observed in Azure appinsight

Lately, my company have decided to migrate our Spring application to Microsoft’s Azure.
Azure gave us the possibility to monitor out SQL (MySQL) queries and their runtime using appinsight.
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.

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search