Querysniffer updated
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?
2 Comments so far
Leave a reply
LVM Snapshots. You use a snapshot from production to build the test db and just restore the snapshot again when you are done.
This of course requires the space to store the gzipped tarball of the snapshot.
Now if you had a storage engine which tracked all changes of the row over a certain time, you could just tell the db to go back to time (or log pos).
Oracle gets there by using SCN for each block, and you telling it that a specific SCN is the one you want to flashback to.
It would be nice if something like the Oracle rollback was built into a MySQL storage engine.
I suppose you could do what you are talking about with LVM, but a better way that I have come across is using EVMS. It was developed by IBM and is basically a shell over the filesystems and LVM. It does allow for snapshots and rollbacks and once it is set up is much easier (and faster) than using LVM and then copying off a tarball like you are talking. Google EVMS. It has not been in active development for a while, but seems to be quite stable. Since I only use it on testing servers I am not worried about it being supported.