Who is Keyser Soze?
Some recall:
"...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 …
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]OK, you found the problem SQL statement that was affecting your server’s performance, now where did it originate?
The new MySQL Enterprise Plugins for Connector/J and Connector/NET send query statistics, including the source location for each query, directly to the MySQL Enterprise Monitor.
Figure 1 is a screenshot of new source location feature.
Figure 1. Source Location
Figure 2 shows the standard query statistics, which are collected in the query analyzer. In both cases, the statistics are gathered by the MySQL Connector and the Plugin, not MySQL proxy.
Figure 2. Query Analyzer
If you’re a MySQL Enterprise customer, you can …
[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 …
MySQL Enterprise Monitor is a tool to watch and analyze multiple
MySQL environments from a single web based dashboard. More
information is available on the MySQL homepage. Each MySQL instance is monitored
by a small agent that connects to the MySQL instance and reads
statistics that is sent to the MySQL Enterprise Monitor (MEM)
Server.That setup is very easy. But if the MySQL server is in a
cluster failover configuration, there are some things to consider
when installing the MEM agent:
What do you want?
Do you want to have two entries in the MEM dashboard for both
physical servers?This is good because: …
The goal is to have only one entry in the Enterprise Monitor Dashboard that shows the status of the MySQL instance, no matter on which physical server in runs. There are two ways to achieve this:
- You can install the agent on both physical nodes
- You can install the agent on a shared storage.
In either case you have to make sure, that only one agent runs at
a time. You have to integrate the agent into your cluster
framework. I will not describe how this works, as it is highly
dependant on your cluster framework.
The following description assumes, that you will install the
agent on both physical nodes.
- Install the agent but DO NOT START the agent yet.
- Edit the
[agent-installdir]/etc/mysql-monitor-agent.ini
In the [mysql-proxy] section add the following line:
agent-host-id=[logical hostname] - …
To install the MEM agent in a way that both physical servers are listed in the MEM dashboard, you have to install the agent on both physical nodes. But: Do not start the agent after the installation!There are three different IDs in MEM: agent-uuid, mysql-uuid and host-id. Usually they are generated automatically and you will never notice these IDs. For more information about the meaning of the different IDs look at this very good explanation from Jonathon Coombes.The agent stores the uuid and the hostid in a MySQL table called mysql.inventory. After a failover the other agent on the new node will notice "wrong" hostid and uuid entries in the inventory table. The agent will stop and ask you to TRUNCATE mysql.inventory. But with this procedure MEM creates a new instance, so all old data is lost. Not good for a failover environment.So in case of a …
[Read more]A lot of people are into the whole cloud computing scenario these days. However, no one has talked about offering DBA-like services in the cloud, all automated, so that startups don’t have to get their own DBAs.
Enter FathomDB. They are poised to offer databases as a service (maybe they’ll charge per database - so you can in theory run both WordPress and Mediawiki, if you prefix wp_ and mw_ in your table creation, for example). They are using MySQL. They’ve also taken the worry of running a database out - they will backup, they will setup (so you don’t have to issue GRANT commands :P), and they will also monitor your databases for you.
But what really takes the cake? The fact that they will also offer performance advisors. This totally reminds me of the MySQL Enterprise Monitor (aka …
[Read more]
There is a big event coming: Cebit!And
with that I took the task to implement a demo for MySQL Enterprise Monitor to show at the Sun booth. So I need a machine for the enterprise
monitor and some machines to run MySQL databases. After all I
need something to monitor. So the setup will be an Ultra 24 desktop machine installed with
OpenSolaris 2008.11 . For every machine I will
install a zone to run a separate database and one zone to run the
enterprise monitor.
So here is my first round of experiences: Installing a template
container on OpenSolaris and cloning …
We are quickly approaching opening up the MySQL Query Analyzer
for general beta and I wanted to pass along an open invite to the
following related and informational events.
On 8/13, I will be doing a micro level presentation on MySQL
Enterprise. Please attend and learn more about the database
software, monitoring and advisor services and support solutions
that make up a subscription. I plan to do a demo of the
Enterprise Monitor and the new Query Analyzer; that alone makes
attending worth the price of admission (in this case 45 minutes
of your time!). Learn more and register here.
On 8/20, I will be doing a presentation on the new Query
Analyzer. This will be a technical discussion around how DBAs
monitor for bad queries now and how the Query Analyzer makes the
job much easier. This will be a good time to learn about getting
in …