Is there a standard approach to monitor the performance of an application and its database, which is still in design?

What I'm doing so far: I develop queries and tests directly in mysql workbench and monitor the msec used per query. On top of that I'm using mocha tests in node.js to test the database api and also try here to measure the typical impact on the database.

The standard approach I know is: do your design and one day, as the amount of data has grown, there is a bad surprise, that the database needs to be optimized. I'm asking for good practices to monitor performance from the beginning.

  • What would your approach be about resolving the "bad surprises" resulting from data growth? Perhaps some of the methods might be relevant in monitoring too. – Andriy M Aug 24 '17 at 15:22
  • One good thing about bad surprises: You may nail it down to some critical queries and trying to improve these only. – Myonara Aug 24 '17 at 15:31

My favorite monitoring tool is the slowlog, with long_query_time=1. And use pt-query-digest to summarize the results. Then work on improving the first few queries in the list. They will, by default, be ordered with the "worst" queries first -- either slow but infrequent or fast but frequent (or both).

Digging into why a particular query is slow (or frequent) may lead to a missing index, the need for a 'composite' index, reformulation of a query, redesign of the code, tuning, more RAM, or any of a dozen other things.

More here

  • Unfortunately the persona toolkit (pt-query-digest) cannot be installed on Windows 10. Now I'm concentrating on mysqldumpslow. – Myonara Aug 26 '17 at 6:17
  • To get mysqldumpslow up and running in Windows10, I'm using the cygwin64 bash shell skript with the following content: "/cygdrive/c/Program Files/MySQL/Mysql Server 5.7/bin/mysqldumpslow.pl" -s at "/cygdrive/c/ProgramData/MySQL/MySQL Server 5.7/Data/NAMEOFSQLSERVER-slow.log", so the name of server ond the path to mysqldumpslow and the slow log may vary dependant on the installation. – Myonara Aug 26 '17 at 6:48
  • Use -s t option on mysqldumpslow; this orders the queries "worst first". – Rick James Aug 26 '17 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.