If you can’t measure it, you can’t improve it.
So the biggest thing when it comes to measuring is from where do you start. Here we lay down a few metrics which would help us in setting up things to measure the MySQL performance. This would be a building block and not a comprehensive list of metrics to measure MySQL performance.
Performance is captured in terms of response times, the quicker the response time the better is the performance, though improving performance could orient towards the resources but we would not be delving into resources to get a better performance, but to profile as to where the could be the areas where performance could be improved.
Ensure to have the mysql slow query log enabled, check the variable by issuing the command
SHOW VARIABLES LIKE ‘%slow_query_log%’;
SET GLOBAL slow_query_log = ‘ON’;
Validate whether slow query log is enabled by issuing the same command, it should be ON now.
Also look up as to where the slow queries are logged by issuing the command
SHOW VARIABLES LIKE ‘%slow_query_log_file%’;
Just in case if you want to alter as to where the query log needs to be saved, issue the command
SET GLOBAL slow_query_log_file = ‘/var/logs/mysql-slow.log’;
With this we are set to start off with query-digest which would profile information for slow queries. This would help us dig deep into areas where the time is being spent in terms of execution and waiting while the query is getting executed.
user@Sys-201:~$ pt-query-digest –limit=100% /var/lib/mysql/Sys-201-slow.log > ptqd1.out
Just in case if you dont get to see any information in the file which is specified, probably you need to use the root login.