The pt-query-digest is my go-to tool in order to perform slow query analysis, improve query performance and thus overall MySQL performance. In this blog post, I’m going to highlight the…
The post How to filter MySQL slow queries using pt-query-digest first appeared on Change Is Inevitable.
We’ve been helping a long-term client who runs some fairly complex queries (covering lots of tables and logic on a respectably big but mainly volatile dataset). We tend to look first at query structure and table design, as fixing problems there tends to have the most impact. This contrary to just tossing more hardware at the problem, which is just expensive.
As subqueries are used (and necessary in this case), MariaDB 5.3 was already a great help with its subquery optimisations. Once again thanks, Monty and the Monty Program optimiser team (Igor, Sergey, Timour, and possibly others) – all former colleagues and they’re absolutely awesome. Together, they know the MySQL optimiser like no other.
Because the queries are generated indirectly from an exposed API (just for paying clients, but still), the load is more unpredictable than having merely a local front-end. Maintaining spare capacity with slaves addresses this, but …
[Read more]This guide will get you up and running with how to identify the bottleneck queries using the excellent tool pt-query-digest. You will learn how to use and analyze the output returned by pt-query-digest. You will also learn some differences between slow query logging in various MySQL versions. Later on in the post I will also show you how to make use of the extra diagnostic data available with Percona Server.
The post Profiling your slow queries using pt-query-digest and some love from Percona Server appeared first on ovais.tariq.
The most efficient performance optimization of a SQL statement is to eliminate it. Cary Millsap’s recent Kaleidoscope presentation again highlighted that improving performance is function of code path. Removing code will improve performance.
You may think that it could be hard to eliminate SQL, however when you know every SQL statement that is executed in your code path obvious improvements may be possible. In the sequence SQL was implemented sometimes easy observations can lead to great gains. Let me provide some actual client examples that were discovered by using the MySQL General Log.
Example 1
5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query …[Read more]
A colleague pointed me at More fun with the MySQL Audit Plugin API which looks very interesting. Analysis of the queries going on inside a msyqld has been something that has been wanted for some time. Until now it’s only been possible with external addons such as MySQL Enterprise Monitor which do a good job. However, really the place for this functionality is inside the db server itself. If 5.5 m3 provides the hooks to do this that’s great news and while Anders’ first implementation may be simple, this can surely be extended in many ways.
The things I would like to see added to this plugin are the following many of which are safeguards to ensure you can use functionality on a system like this in production without …
[Read more]As I outlined in Part 1 MySQL Proxy can be one tool for performing SQL analysis. The impact with any monitoring is the art of monitoring will affect the results, in this case the performance. I don’t recommend enabling this level of detailed monitoring in production, these techniques are designed for development, testing, and possibly stress testing.
This leads to the question, how do I monitor SQL in production? The simple answer to this question is, Sampling. Take a representative sample of your production system. The implementation of this depends on many factors including your programming technology stack, and your MySQL topology.
If for example you are using PHP, then defining MySQL proxy on a production system, and executing firewall rules to redirect incoming 3306 traffic to 4040 for a period of time, e.g. 2 …
[Read more]Long before there was the official Query Analyzer (QUAN), a component of MySQL Enterprise, SQL analysis was possible using MySQL Proxy.
The following is an introduction to logging and query analysis with MySQL Proxy.
Get MySQL Proxy
You need to first download MySQL Proxy. In this example I am using the Linux RHEL5 64bit OS and Version 0.7.2
$ wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.7.2-linux-rhel5-x86-64bit.tar.gz/from/http://mirror.trouble-free.net/mysql_mirror/ $ tar xvfz mysql-proxy-0.7.2-linux-rhel5-x86-64bit.tar.gz $ ln -s mysql-proxy-0.7.2-linux-rhel5-x86-64bit mysql-proxy $ export PATH=`pwd`/mysql-proxy/sbin:$PATH $ mysql-proxy --help-all
Pre-requisites
MySQL Proxy uses TCP/IP, so it is important you connect via the actual …
[Read more]The presentation slides for my Query Analysis talk here at the users conference are now available:
How I Used Query Analysis to Speed up My Applications
I’ll sort out the scripts and bits and pieces I use once I get back to the office next week, for those people waiting for that material.