[MDEV-250] SLOW QUERY LOG - Group queries and check total slow query Created: 2012-05-04  Updated: 2015-01-23  Resolved: 2015-01-23

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Fix Version/s: 10.1.2

Type: Task Priority: Trivial
Reporter: roberto spadim Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None


 Description   

Hi again guys
slow log is a very very good tool to know what´s running slow and change 'bad' queries
the problem is the slow query time isn´t a fixed time in real life, it must be a dinamic value
for example i know that i have some queries that they can´t be done more than 1 second, well what i´m talking about?

first... a per query estimate time tweak like:
/*estimate time=5ms*/ SELECT * FROM any_table WHERE some where that must run before 5ms

second... a per group of queries time tweak like:
1) /*group query time=1s*/
2) do one query
3) do another query
4) do another query
5) do another query
6) /*end group of query slow query estimate time*/

if 2+3+4+5+6 total time is bigger than group query time, this should be logged

that´s very nice and very useful since a group of queries = a php or another web application page information/action and...
only one query = a simple query information request or a update/delete/create/insert data append/change

with this two types of query could allow a very very powerfull tool to undestand where my database is slowing down, it´s on only one query? or a group of queries that didn´t done the task in a good time?

check that doesnt matter about cliente time to retrieve query results, just server query process time...

 Comments   
Comment by Sergei Golubchik [ 2012-05-16 ]

Thank you for a feature suggestion.
Although I believe we won't have resources to implement it anytime soon, we'll keep it in Jira, in case we want to do it later.

Syntax-wise, I think it'd better to avoid magic comments, and use a new session variable, like

set session group_query_time=5; – timeout in seconds
queries... queries.... queries...
set session group_query_time=0;

Comment by roberto spadim [ 2012-05-16 ]

well it's really better but it's nice to see how ideas grow fast here =]
no problems you sugestion in my opnion is very nice
again, no problem about time, i'm doing this today in my app, but in some cases it's better putting it with database log, since some applications servers can be in anothers server differnte from database server

thanks sergei!

Comment by roberto spadim [ 2012-06-28 ]

hi =) any news?

Comment by Sergei Golubchik [ 2015-01-23 ]

This is pretty much done in 10.1.

I believe you can do it like this:

SET STATEMENT long_query_time=1
 
BEGIN NOT ATOMIC
 
  do one query
 
  do another query
 
  do another query
 
  do another query
 
END

Generated at Thu Feb 08 06:27:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.