Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: query analysis (reset)

The Optimiser Conundrum
+2 Vote Up -0Vote Down

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

  [Read more...]
Profiling your slow queries using pt-query-digest and some love from Percona Server
+2 Vote Up -0Vote Down

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.

Optimizing SQL Performance – The Art of Elimination
+3 Vote Up -0Vote Down

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
  [Read more...]
Query analysis plugin for 5.5!
+1 Vote Up -0Vote Down

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 (http://www.mysql.com/products/enterprise/monitor.html" target="_blank) 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

  [Read more...]
SQL Analysis with MySQL Proxy – Part 2
+2 Vote Up -0Vote Down

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

  [Read more...]
SQL query analysis with MySQL Proxy
+3 Vote Up -2Vote Down

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,

  [Read more...]
UC2009: How I Used Query Analysis to Speed up My Applications
+3 Vote Up -0Vote Down

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.

Showing entries 1 to 7

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.