Pythian Group

Yesterday, The Pythian Group issued a press release about my book, Pythian’s partnership with Sun, and our new “MySQL Adoption Accelerator Package”. I am not a marketing guru, but I can tell you what we the package means in terms of new work that the MySQL teams have been doing.

Basically, the MySQL Adoption Accelerator Package combines customized training with a comprehensive audit of systems. The name “Adoption Accelerator” makes it sound like it’s only for new applications that are almost ready to go live. What the program actually does is have us evaluate your systems, and intensively train you in the areas you want and need. The program is designed to suit all your needs, whether it’s teaching you about one topic (say, query optimization) or an entire range of topics, from Architecture to ZFS (special issues with running MySQL on ZFS, that is, but that did not fit a cute “from A-Z” model…).

Whether you have already adopted MySQL or are thinking of converting from Oracle, DB2, Microsoft SQL Server or even sqlite, this new package may be what you need.

And now, the full text of the press release, for the curious:

‘MySQL Administrator’s Bible’ Hits the Bookstands: Pythian Launches MySQL Accelerator Adoption Package

The Pythian Group, the leading provider of remote database services, is pleased to announce that the much-anticipated MySQL Administrator’s Bible, written by employee Sheeri K. Cabral, is now available.
Read the rest of this entry . . .

Simple auditing, i.e., knowing what changed recently, can save you tons of time while troubleshooting.

I know that, in the ideal world:

• Everything is supposed to be done through configuration management.
• Everything is documented and all changes are tracked through a VCS.
• Every DDL or set global is trapped via MySQL Proxy and logged.

But there are always ways to bypass the gatekeepers. Changes can go in unnoticed. An hour or so later, your database performance suddenly changes for the worse, and you get that phone call.

First you check if anything caused an actual error. You look around at a few log files and nothing shows up. The next thing you ask yourself is, did someone change anything in the last little while. Of course, everybody says no. After a few hours of digging, comparing schemas, diff-ing old and current config files, you actually find what has changed, put it back the way it was and everything is back to normal. You ask the question again, did anybody change the global variable from X to Y, and someone finally admits it. But they thought that it was not relevant since they did that change on the weekend and the system performance didn’t go down the toilet until Monday morning around 9:00am. Sound familiar?

We have all gone through this many times, and it doesn’t matter what process you have in place — something always slips through the cracks.

I came up with some simple stored procedure that will compare two data sets and keep track of the changes historically. It’s loosely based on slowly-changing dimension type 2 in the data warehouse world (google Ralph Kimball if you want to get all the gory details). This method tracks only changes, so you should be able to keep historical rows forever, unless you are constantly dropping and creating whatever you are tracking.

Say you want to keep track of my.cnf or global variables changes. This one of the simplest tables to track, the information_schema — it has only two columns. Others, such as tables or routines, would make the SQL to compare a little more involved.

First you need a table to track this with. I called mine historical_global_variable. It has the two columns from the original table plus three additional ones:

Read the rest of this entry . . .