Diamond Notes

This is a “reprint” of a post I did yesterday.  For some reason Planetmysql didn’t pick it up and I wanted to make sure people knew about the new version of querysniffer.

We updated querysniffer just a bit. A user pointed out that the sniffer doesn’t actually start recording data until a “use” statement is issued. Ian added a flag so you can change this behavior if you want.

There is a memory leak when the sniffer runs for an exteneded period of time. We are working on that. Should have something soon. The new sniffer files are available at http://www.paragon-cs.com/queryprogs/querysniffer.tar.gz

We updated querysniffer just a bit.  A user pointed out that the sniffer doesn’t actually start recording data until a “use” statement is issued.  Ian added a flag so you can change this behavior if you want.

There is a memory leak when the sniffer runs for an exteneded period of time.  We are working on that. Should have something soon.  The new sniffer files are available at http://www.paragon-cs.com/queryprogs/querysniffer.tar.gz

Hey everyone. A kind user pointed out a timing error in the querysniffer code. Ian fixed it and I fiddled around and managed to upload the new release to the server. If you are using querysniffer you should update to the new version as you are getting incorrect timing results currently.

Downloads are available at http://www.paragon-cs.com/queryprogs

Earlier today I posted a video and slides of a presentation that I did at work on the Query Processing Programs (QPP).  It is fairly short, only 16 minutes or so including questions but is a nice overview of the toolkit.  The only “video editing” tools I have access to are MS Movie Maker so it is a WMV file.  Sorry if that is a problem.

Also I put up a podcast that I participated in back in August on WebDevRadio

All are available at http://www.paragon-cs.com/presentations.

We made a minor update to the QPP toolkit today (”ver. 1.01″). I uploaded the new files to the website. They can be found at http://www.paragon-cs.com/queryprogs. In addition, I have beginnings of a tutorial located at http://www.paragon-cs.com/queryprogs/tutorial.html. It will make this a bit easier to understand.

A couple of people have asked how the QPP toolkit is different than other programs. Please give me a minute to try and explain. QPP can be used to capture queries being executed on a production server at any time. In addition, it will take the captured queries and insert the text of those queries plus timing and host information into a database which you can then use to determine where your servers are spending time executing queries. Finally, you can take that same data that you gathered from a production server and use to generate benchmarking and load tests against a test server. If set up properly these tests can be repeated as many times as needed.

Hope that helps!!

Hey everyone. I just finished uploaded the latest versions of the three QPP programs. They are all working well and I consider them “version 1.0″. I will be updating the qpp pages as soon as possible with a tutorial on how all these programs work together.

The basic idea is that you use the querysniffer program to gather production data from your servers as needed without stopping/starting the MySQL server. Queryparse is actually used to control querysniffer and push the gathered data into a database from which you can use simple php/html pages to view the statistics.

Querybench is used to benchmark/stress-test a test server with production data (gathered, once again, with querysniffer). You can even set it up so that the database can be “rolled back” to its initial state after a run.

All in all, I am very pleased. I have fulfilled all the goals I set out to achieve when I began this little journey a few months ago. It will be very useful in my work and I hope others find it useful as well.

I would like to publicly thank Ian Kilgore who helped far above and beyond anything required. He is one of the system administrators where I work and is the genius programmer behind this stuff. I just had the idea and kind of directed things along. The only “programming” I have done is working on the documentation a bit and developing the web pages for the display of the statistics.

Feedback can be sent to “bmurphy” at “paragon-cs.com”.

Now what do I with all this free time I have left over *cough*?

I just wanted to take a minute and let people know that we are continuing to work internally on the query processing tools. The querysniffer tool is feature complete for now (and I can’t imagine what else you might need with it). It grabs the MySQL data from the network and injects it into a database table storing the query, the time stored, the total execution time of the query and the host it was captured from. In addition, you can tell it to not grab the data modifying statements (inserts, updates and deletes). Also, it can replace varibles (”WHERE username=”Jack”) with something like this: WHERE username={string}. Why is this useful? So you can group the queries together. Even though one query might be searching for Jack and another query might be searching for Jill, they are both the same query. Can’t think of any other options, but I probably missed one or two.

After all that work, it becomes simple to write a web interface of php or perl to display statistics. I already have one set up internally displaying the most executed queries and the average execution and total execution time fo a query. While I will release this as part of the package, this will be something that each DBA will end up spending some time tweaking to fit their own needs I am sure.

I haven’t really started working on the benchmarking part of the toolkit yet. I have an idea of implementing a unionfs(??) so that I can run the queries against a database and then roll the database back without to much difficulty. That way I can run INSERTs, UPDATEs and DELETEs without worrying about.

We have some internal paperwork to take care of before these new additions can be released but I am confident it won’t be long until that is taken care of. As soon as it is done we will release the new code to the world.

Just wanted to let everyone know that we haven’t quit working on querysniffer. We have added several new options to allow (among other things) logging the IP address of the initiating client and tracking sessions (including time-stamp information).

It is becoming much more useful for our other query processing tools..querybench and queryparser. I set up a page for all the tools in addition to Ian’s homepage at http://www.paragon-cs.com/queryprogs.

Reading the new Logbuffer #61 that was just released I came across this new feature of Oracle 11g — what amounts to application replay. Think DVR for your database.

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-replay.html

Now sing to the tune of “I’m Dreaming of a White Christmas” –

I’m dreaming of an MySQL replay

Just like the other replays I know

Where the data is captured

and the testing is easy

and the DBA’s get to go (home early that is)

…..

So now you know why I work on databases and don’t write songs.

Maybe one day the QPP’s (query processing tools) will grow up and do something like that. If I can just figure out how they roll the database back after making changes to it. I mean..can you imagine capturing 400 megs of data and playing it back against a test database .. all the changes that are made to the DB and then being able to roll it back to a start checkpoint easily?

That would be awesome. Any ideas how you could set a checkpoint and the easily and quickly drop all changes made after that checkpoint when you run restore?