1
I’m performing some performance tests in my BD using Mysql, and I got the following question.
Is there any way to predict how long it will take to execute a query in Mysql after starting its execution?
I checked that in Client Connections I can get the state of the query, but not how long it will take (predicted).
this? https://dev.mysql.com/doc/refman/8.0/en/show-profiles.html
– Wees Smith
I believe we cherish the time is not possible. The execution itself depends on other factors that may change after the start of the execution, such as the availability of resources, the processing capacity and I/O rate of the server itself where the bank service is running... a thing shovel. I don’t think there’s a complete way to determine that time. Particularly, I think it’s fair. Better to have no information than to have wrong or inconsistent information.
– Diego Rafael Souza
The problem with Show profiles Wees is that it only shows the duration of the query, and not a duration forecast Talt duration is only presented after completion
– Clayton Tosatti
Yes. He does, and afterward to make it tells how long it took to make.
– Diego Rafael Souza
I agree with your opinion Diego, the problem and that it shows not even a basic estimate you know, for example: I have to bring a thousand lines and already charged 500, having this we could have some notion of the time, even if it is not accurate would already be a north
– Clayton Tosatti
Even this is complicated. Because it won’t sweep all the table records and go picking one by one. He draws up a plan to select the records and the action of picking them up is one shot only (perhaps more than one in case of paging and/or partitioning). He doesn’t know how many records will come, before they actually come - after that perhaps there is a need to apply other filters as should be the case with subselects and the like...
– Diego Rafael Souza
Unless you are talking about an absurdly large amount re records and it takes a considerable time to be recovered (more than 2 minutes, for example), this is almost not worth it. Monitoring this type of processing to give an estimated termination time in general will take longer than processing and return of the query itself.
– Diego Rafael Souza
Dude, you want to predict the weather? Make an average, because predict believe that is not possible, but through an average, for example, calculates the execution time of 100 different querys, you find the time of each value of each query and makes a media for each record, so you can predict, approximately, a search for 1000 results, for example.
– Wees Smith
The queries that I’m looking for this estimate really take time, are tables with JOIN’s and some have a few million records. I believe in this case maybe the best option is that same Wees check with a small sample and I make an estimate the part to verify this issue
– Clayton Tosatti
I don’t know how much influence this would have, but a COUNT command is usually much faster to execute. In this case if there was a way for the BD to execute this total counter and the measure that runs the main query will show how much is missing, for example: 1/100, 2/100.... 100/100 would already be enough to estimate also
– Clayton Tosatti
It is not possible to predict the execution time, even based on previous statistics, average, or something like that.. Imagine someone makes one lock in the table or try to create an index, run a backup, anything at runtime will stick to the forecast, then it will be a thing without credibility, equal to that "estimated time" when copies files in the Windows, who never works out :(
– Ricardo Pontual