Question: What do you use to capture and analyse MySQL processlist?

I have recently been evaluating MONyog and one of the key things that I was hoping that it would provide for me was an easy way to answer the all important question for troubleshooting...

"What was happening at the time?"

If I see some query that is normally fast took far too long or perhaps replication fell behind significantly for a while -- I will always ask myself "What was happening at the time?"

I'd check out stuff like SHOW FULL PROCESSLIST, SHOW ENGINE INNODB STATUS and some system level things like iostat and vmstat or mpstat, etc.

I saw that MONyog has the ability to do things like periodically sniff SHOW PROCESSLIST, or even use MySQL Proxy for query analysis purposes.

This seems to capture how often queries run, whether they used an index, how long they took, etc. but the data is not available to be seen in a time-based snapshot style format.

I know that MySQL Enterprise Monitor that Oracle have on offer as a part of the MySQL Enterprise offerings sort of has this kind of feature -- they have a spiffy way to click and drag a time portion of a graph and then be taken to Query Analyzer to see what was happening during the window.

This of course is still not quite a substitute for the full SHOW FULL PROCESSLIST output -- so I open the question to you, oh MySQL blog-o-sphere...

What do you use to capture Processlist and InnoDB Status info so that you can refer back to it?

Do you just use some cron and have it periodically write to a file? .. or is there some other nifty tool out there that I haven't heard of?

Lachlan

Xtrabackup Manager - License changed from GPLv3 to GPLv2

Hi Folks,

I changed the license on Xtrabackup Manager from GPLv3 to GPLv2 today. This should make it more compatible with the MySQL Ecosystem.

If anyone has any objections, advice, comments -- please let them fly :)

Meanwhile, testing and work continue...

Cheers,
Lachlan

Xtrabackup Manager - A new home and some more progress...

Hi Folks,

The Xtrabackup Manager project now has a new home on Google Code. Including tasks/to-dos, etc.

You can find the project here:

  http://code.google.com/p/xtrabackup-manager/

If you haven't heard about it yet, the project aims to provide a nice backup management wrapper to the popular "xtrabackup" tool from Percona.

At the moment you can set it up and it will automagically detect when to take a FULL backup (first) and then when to take incremental backups following that. It will even collapse your old incrementals into your FULL backup (seed) based on your snapshot retention policy.

So functionality is coming along nicely!...

I have also started work on some documentation -- it is still a work in progress, but shortly there will be enough there for the more experimental among you to try it out.

I'll make another post once that is ready.

Interesting in contributing? Please reach out to me and let me know. I'm interested in finding someone who may like to develop a Web Front-end for it in PHP.

Until then, I will keep plugging away as I find the time.

Cheers,
Lachlan

OSS Project hosting woes of a first-timer

Being a long time OSS Project user/supporter but not having setup or developed any of my own projects previously, I'm having to navigate unchartered territory with regards to how and where to setup my projects.

At first I looked at launchpad.net because I'd seen a bunch of other projects using it, however, when I discovered they didn't have many of the supporting features I wanted - mainly wiki, I decided to look elsewhere.

It was then I checked out good ol' sourceforge.net (apparently I'm still living in the past, because someone commented to me the other day something like "Oh.. you mean sourceforget?") So I guess even geeks have stuff that's "in" and stuff that's "out" ;-)

Anywho.. I liked what I saw on SF.net, it seemed fairly easy to setup and had a lot of features that seemed like they would be useful for a project. So I setup the project on sourceforge(t).net and continued my work.

About 2 revision commits in I started thinking the project was getting to a point that it could be used - so of course I needed to make some quick and dirty documentation about how to use and set it up.

It was then that I came to realise my mistake -- I was apparently so blinded by the various _other_ features on SF.net, that I neglected to notice that they don't have a wiki space provided. Sure, I could set one up in the web hosting space, but I want to spend my time on actually working on the project, not managing the infrastructure around it. (It's a nice way of saying I'm lazy..)

So now I'm thinking about Google Code and Github.

I went to Github and went to check out some of the project hosting pages. Honestly, I think I touched git a long time ago in a galaxy far far away and any information other than it's name has long since evacuated my brain. 

The first thing I did was try to browse through some of the existing projects -- How does it feel to browse? Can I find the things I would want to find easily?

The answer was "No." most of the time I couldn't tell if I was in a project or a branch or a fork or whatever and I struggled to find an easy link for wiki or documentation or even downloads. Github might be a great tool if I knew git and knew what I was doing, but if I imagine a DBA like myself coming to the page to find my tool and use it, they would likely feel an equal level of confusion. They don't necessarily want the source code or to contribute, they want to download and use it.

So for now Github is out.

Then came Google Code - It seemed like a breath of fresh air. I could see clear links to Source, Downloads, Wiki and Issues ( bugs ). Yes, this would do just fine. The problem then became that when I logged in with my gmail account, I saw that it was not my display name that came showing up against the project, but my gmail account ID. 

Ewww... 

Call me vain, but I like putting my real name against things that I do in the community. I don't want people to have to learn that "arzkl123@gmail.com" is actually Lachlan Mulcahy. (That's not my gmail by the way -- I'm attempting to avoid spam here). 

A quick google search and it seems this issue is something people have complained about since 2008 sometime. I guess it is not going to get changed anytime soon, so I'll just go any make myself a gmail account with my real name in it more visibly and use that.

It is a bit of a pain, but out of the available options, it seems like the best choice.

Now I'll hop off the soapbox and get back to work :)

Cheers,

Lachlan

Xtrabackup Manager - A toolset for making managing backups using xtrabackup simple!

Hi Folks,

Long time, no post -- I know, but I'll cut to the chase.

I have started work on a new GPL project entitled "Xtrabackup Manager". Just to be clear, the only relationship to Percona is that the toolset is designed to wrap around the Percona xtrabackup tool.

It is essentially a wrapper for xtrabackup that allows you to more easily manage and schedule your backups for multiple systems.

It is being written in PHP and the work-in-progress code is up on sourceforge. Currently it is not really near ready for general consumption as it is still under construction, but I'm trying to be a good OSS citizen and follow the "release early, release often" method.

A few things to note and disclaim:

#1. I am not the world's greatest developer, so please when you critique my code, be gentle ;-)
#2. It is written in PHP because it is the scripting language I am most familiar with. Not necessarily because it is the best tool for the job ( although I think it should do the job just fine ! )

Some info/features on what I have planned:

* Will run on Linux only to start with - so far have been testing on CentOS 5.5
* Setup any number of hosts and configure backup times using a cron expression (don't reinvent the wheel for scheduling)
* Give the tool a Linux user of it's own, it will hijack the crontab for scheduling
* Uses SSH trust as a means of running backups
* Uses tar stream and netcat for pulling backups over the network into the backup host
* Configure how many snapshots you wish to retain - utilizes full backup and incremental backup feature of the xtrabackup too for this. Automatically merges snapshots together as you roll forward with more snapshots.
* Support for multiple storage volumes -- all incrementals and seed must live on the same volume.
* Rich logging: Mutliple log levels, DEBUG/INFO/ERROR - Global system log and per host log files.
* Email alerting / reporting - Get alerted when backups fail. Get reports of what backups ran/when, etc.
* Requires a small MySQL instance for metadata, and management storage.
* Command-line and DB interface for configuration to start with.

Want to get involved? I'm looking for anyone who may be interested in developing a web front-end to the tool. I'm a decent hand with PHP, but I have not developed anything web related in quite some time.

Leave a blog comment or reach out to me on lmulcahy (at) marinsoftware (dot) com if you are interested!

Right now I am developing this for use in-house at the company I work for, so my focus is towards getting something working that we can begin using here.

I'm of course hoping that this will be a chance to give something back to the MySQL community and that others can benefit.

Please let me know your thoughts/feedback.

Lachlan