Database / Uncategorized

MySQL Metrics

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.

https://poormansprofiler.org/

MySQL does not need SQL

https://www.olindata.com/en/blog/2014/08/analysing-slow-mysql-queries-pt-query-digest

Profiling your slow queries using pt-query-digest and some love from Percona Server

https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

http://www.php-trivandrum.org/open-php-myprofiler/

http://kedar.nitty-witty.com/blog/calculte-mysql-memory-usage-quick-stored-proc

MySQL Server Memory Usage

http://mysql.rjweb.org/doc.php/memory

http://20bits.com/article/10-tips-for-optimizing-mysql-queries-that-dont-suck

Leave a Reply

Your email address will not be published. Required fields are marked *