Friday, October 26, 2012

Keyser Soze in MySQL?

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 MySQL Query Analyzer and Enterprise Monitor.


First, we get them on the case:
./mysqlmonitor-2.3.12.2175-linux-x86_64-installer.bin

FYI: Requires MySQL Server version 5.1.43 or later with partitioning and InnoDB storage engine enabled is required.


The install is very straight forward and just follow the prompts.
You will be asked:

Database Installation

Please select which database configuration you wish to use

[1] I wish to use the bundled MySQL database
[2] I wish to use an existing MySQL database

It is up to you which database you decide. In my opinion, a monitor should not alter the environment of what is is monitoring. I would choose option 1. It would not hurt to still backup this database, just in case.


Once this is installed we start to look over our graphs and advisor...









Very mellow server load here.
 ( granted this is a demo)














The MySQL Query Analyzer can shed more light on curious queries. 
It will take a couple edits to the ../enterprise/agent/etc/mysql-monitor-agent.ini file.  I prefer the server method because it is quick and simple and catches all default interactions with the database.







So now if an event was to hit your database you are aware whenever it happens. You will be able to scroll back overnight for example to see not only what ran, but how it ran.  If someone decided to put a small query into a infinite loop by mistake, you will find it here.





Once you do see something you are curious about... (Maybe it is "Keyser Soze" or maybe it is a planned query)

You can then highlight the graph and then pull up all queries related to that time frame.








 You will then be allowed to see the explain (as long as it meets time thresholds)  as well as graphs of how the selected query was executed via your server.








If you find yourself not getting the explains like you expected make sure to check your configuration and adjust the threshold.












While this is just a demo, this tool is very useful for exploring trends in your data, as well as find the critical events. The advisers can help you fine tune the server per the data that has been executed on it.  The Enterprise monitor will allow you to do a deep dive into the system and explore all options as you look for the "Keyser Soze" in your application.






Of course don't forget, the monitor also delivers replication information. So you can keep track of topology, server type, position, errors, and log space.




While all this  might not make an interesting movie like the Usual Suspects did, it does show that while a DBA might have a list of usual suspects when it comes to a problem, they also can lean on the tools available for a wealth of additional information.

MySQL Enterprise Edition




More Reference urls:








Posted by at
Labels: , ,