Who is Keyser Soze?
"...a rarely seen, nearly mythical kingpin... Most believe he is not real, but rather boogeyman story or name-drop to intimidate people."
Some people think of it as :
"Keyser Soze – DBA or developer who looks ordinary but has insanely good skills, hardly anybody knows about it."
What does any of this have to do with MySQL ?
Well for this blog post, "Keyser Soze" is the nearly mythical query that most believe is not real, but rather a "boogeyman sql query" that wastes resources in a MySQL Database. Typically, every application has at least one. Your developers blame the database for being slow and they have caught the ear of the CEO. It is time to resolve the boogeyman issue and find out. The difference is we want a better agent than Dave Kujan, so we have the …
Who is Keyser Soze?
Today we've released a few things for PHP and MySQL users: One is the first (and probably only) beta of the mysqlnd_ms plugin for MySQL Replication and Load Balancing support and the first GA version of a PHP plugin for the Query Analyzer of the MySQL Enterprise Monitor.
Ulf blogged a
lot about mysqlnd_ms, so I don't have to repeat him here.
what I want to talk about is the other one. So what is that
When running a PHP-based application with MySQL it is often quite interesting to see what actually happens on the database sever. Besides monitoring of the system load etc. it is often interesting to see what queries are actually executed and which of them are expensive. A part of MySQL Enterprise Monitor is the MySQL Query …[Read more]
Nowadays MySQL Databases are encapsulated into many mission critical software solutions. Lots of companies host one or many MySQL databases in their data center, sometimes even without knowing it except when the MySQL service is no more available. In order to increase this service availability it is mandatory to have a monitoring solution. Regardles of you are using MySQL Server, MySQL replication or cluster the Oracle/MySQL monitoring solution is called MySQL Enterprise Monitor.
Figure 1. Source Location
In a prior post ( Trace SQL From Database to Source Code ), I showed how to enable SQL trace capabilities for java/MySQL application to trace SQL statements from the database to the exact line of code from which the statement was executed (see Figure 1). In this post, I’ll enable SQL tracing in the sample C# application, which is included with the MySQL Connector/NET (MySQL’s ADO.NET provider ) install.
The following instructions assume that the MySQL Enterprise Agent and Monitor is already installed. The Monitor is available for support customers at …[Read more]
MySQL Enterprise Monitor offers a tool called "Query Analyzer" (QuAn). QuAn sits between any client app and the MySQL server and logs every query and its runtime statistics. A very cool tool for analyzing your SQL. More information is available here. If you identify a query, that needs some improvement, sometimes it is hard to identify the source of that query as well. With hundreds of different PHP scripts for example it is not easy to know, which one issued the query, that you want to modify. A good way to achieve this is adding C-style SQL comments. Let's look at an example: SELECT * FROM mytable /*main.php*/; Query Analyzer will strip that comment off before archiving the query. This is ok, because QuAn wants to consolidate all similar …
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
MySQL Proxy uses TCP/IP, so it is important you connect via the actual …[Read more]
There have been plenty of blog entries and writings about the MySQL Query Analyzer, for what I think are good reasons. Labeling it a potential Killer App, causing many MySQL users to become paying Sun customers, may be a daring thing. However, the Query Analyzer might very well have what it takes. The key benefit of it is that it identifies the source of performance bottlenecks. In that sense, one could perhaps instead call it a profiler, as it analyses the set of all queries going on, as opposed to an individual one. One person to whom I described it said “ah, so …[Read more]