CODEWITHSUNDEEP
phpmysqlperformancemonitor
Bert Maurau
Bert Maurau

Reputation: 979

PHP get amount of MySQL queries per hour

Short: Is there a way to get the amount of queries that were executed within a certain timespan (via PHP) in an efficient way?

Full: I'm currently running an API for a frontend web application that will be used by a great amount of users.

I use my own custom framework that uses models to do all the data magic and they execute mostly INSERTs and SELECTs. One function of a model can execute 5 to 10 queries on a request and another function can maybe execute 50 or more per request.

Currently, I don't have a way to check if I'm "killing" my server by executing (for example) 500 queries every second.

I also don't want to have surprises when the amount of users increases to 200, 500, 1000, .. within the first week and maybe 10.000 by the end of the month.

I want to pull some sort of statistics, per hour, so that I have an idea about an average and that I can maybe work on performance and efficiency before everything fails. Merge some queries into one "bigger" one or stuff like that.

Posts I've read suggested to just keep a counter within my code, but that would require more queries, just to have a number. The preferred way would be to add a selector within my hourly statistics script that returns me the amount of queries that have been executed for the x-amount of processed requests.

To conclude. Are there any other options to keep track of this amount?

Extra. Should I be worried and concerned about the amount of queries? They are all small ones, just for fast execution without bottlenecks or heavy calculations and I'm currently quite impressed by how blazingly fast everything is running!

Extra extra. It's on our own VPS server, so I have full access and I'm not limited to "basic" functions or commands or anything like that.

Upvotes: 0

Views: 670

Answers (1)

Rick James
Rick James

Reputation: 142296

Short Answer: Use the slowlog.

Full Answer:

At the start and end of the time period, perform

SELECT VARIABLE_VALUE AS Questions
     FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Questions';

Then take the difference.

If the timing is not precise, also get ... WHERE VARIABLE_NAME = 'Uptime' in order to get the time (to the second)

But the problem... 500 very fast queries may not be as problematic as 5 very slow and complex queries. I suggest that elapsed time might be a better metric for deciding whether to kill someone.

And... Killing the process may lead to a puzzling situation wherein the naughty statement remains in "Killing" State for a long time. (See SHOW PROCESSLIST.) The reason why this may happen is that the statement needs to be undone to preserve the integrity of the data. An example is a single UPDATE statement that modifies all rows of a million-row table.

If you do a Kill in such a situation, it is probably best to let it finish.

In a different direction, if you have, say, a one-row UPDATE that does not use an index, but needs a table scan, then the query will take a long time and possible be more burden on the system than "500 queries". The 'cure' is likely to be adding an INDEX.

What to do about all this? Use the slowlog. Set long_query_time to some small value. The default is 10 (seconds); this is almost useless. Change it to 1 or even something smaller. Then keep an eye on the slowlog. I find it to be the best way to watch out for the system getting out of hand and to tell you what to work on fixing. More discussion: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Note that the best metric in the slowlog is neither the number of times a query is run, nor how long it runs, but the product of the two. This is the default for pt-query-digest. For mysqlslowdump, adding -s t gets the results sorted in that order.

Upvotes: 1

Related Questions