Diamond Notes

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

As I wrote in my last post, there is an issue that bit me using XFS data partitions and trying to take a normal LVM snapshot of them while running MySQL. As part of the upgrade from (MySQL) 4.1 to 5.0 I dumped the entire dataset and imported it into a new server running 5.0. This server had an XFS data partitioning as previous testing had shown that it is faster than reiserfs for our database servers. This same server had our application “pointed” to it (after stopping the application and shutting down the old replication so there was no database activity). In essence it became the new master. I then had to reconfigure the old master with the MySQL 5.0 binaries, delete the data in the data partition and re-sync it with a copy of the data from the new master.

This is a fairly standard procedure and is done using the lvcreate command to create a snapshot of the data partition and then mounting the new snapshot and rsyncing it to the old master’s data partition. I have done this dozens of times in the past without ever having a problem — but this was the first time with an XFS filesystem in use on the data partition on the running server.

This time I copied over the data and tried to start up the new server. It didn’t work. Innodb complained bitterly and then the server just died. I don’t have the exact error but it didn’t start. This was actually the first of the set of servers I had to upgrade, so I went on and did the same procedure on all the other servers. Each one worked exactly as expected. However, I had resierfs on each of the other data partitions.

It was about six in the morning by this time. I began doing the upgrades at 10:00 pm so I suppose I wasn’t exactly sharp at this point. I performed the entire procedure again with exactly the same results. At this point I had to stop as our traffic was beginning to build up and the snapshot/rsync process creates too much load on the servers to do it during normal hours.

I googled around. People suggested that with XFS partitions you use the xfs_freeze command to halt all disk activity and clear out the XFS transaction logs. It seems that LVM doesn’t do this and it was what was causing the inconsistent view of the data. Basically you did this:

xfs_freeze -f

lvcreate -s ……..

xfs_freeze -u

So you freeze the partition, create the snapshot and then unfreeze it so activity continue. However, googling some more, I found that this doesn’t work with LVM2 (the current stable version of LVM).  Something has changed with LVM in the newer version and now when you issue the “xfs_freeze -f” comand and than the lvcreate command — lvcreate actually hangs. It won’t create the snapshot until you run the “xfs_freeze -u” command which “unfreezes” the filesystem.

I don’t know what changed with LVM but I really wish that this wasn’t the case. To get the copy of the data partition that would actually work I had to briefly shut down the MySQL server and then run the lvcreate command.  Then, after the snapshot was created, I could restart the server.  Of course the server was down for about 30 seconds.  But, because there was no XFS filesystem activity going on when the snapshot was made, it worked perfectly.  However this means I have to revert filesystems back to reiserfs until this issue with LVM is resolved.  I suspect it will be quite some time

December the 17th we upgraded the majority of our servers that had been running version 4.1.22 of MySQL to 5.0.45. This was not a small undertaking and is something that I had been planning for about five months. It had been initially slated to take place in September, but due to the release of a new product (and the subsequent lack of time on the part of the developers) it was delayed until December.

I wanted to lay out some thoughts for those who will be upgrading in the future. Hopefully you might miss a few of the potholes that we ran across in our little journey to 5.0.

It was imperative that our customers be affected as little as possible. We went to great lengths to minimize the downtime of the site. We also did everything in our power to make sure that bugs didn’t creep into the process and cause problems.

**space savings: one benefit to upgrading is that 5.0 uses a more compact format for innodb tables. We gained about 15 - 20% on each server in space which was a nice bonus.

**dump your data using mysqldump (or better yet — the Ma’atkit parallel dump). Don’t try and just upgrade the mysql binaries. Not only will you loose out on the innodb space savings there are enough subtle differences in the format of storage that you will end up with problems down the road. Invest the time to do it right. Dump your data.

** mysql replicates from 4.1 to 5.0 — but not the other way. Once we promoted our slaves to masters we were running without backups until we upgraded the slaves to 5.0 and re-synced the data from the new masters.

**go through the changelogs. Every time MySQL releases a new version of the server they also release a changelog of bug fixes/errata for the server. These are your friend. I copied each one of the changelogs between 4.1.22 and 5.0.45 into one gigantic log file and highlighted areas that might cause problems. It didn’t catch every problem we ran across..but most of them.

As a starting point here are the major problems we ran across:

1. Rounding is calculated differently. This will affect financial applications particularly. With the new version, ROUND may return different results depending on whether you’re rounding a DECIMAL or a DOUBLE type.
2. When doing joins, subqueries, etc., the new version complains about duplicate column names:

• A subquery is not allowed to have a duplicate column name. The solution is to specify the columns you want for at least one of the tables.

• When doing joins with the USING clause, duplicate columns will cause an error.

3. With DECIMAL columns, the new version will cut off numbers that were stored in the old version. An example I found was in the the table_name.column_name column. It’s specified as DECIMAL(6,2). The old version allowed numbers such as 12000.00, but the new version will cut those off and store 9999.99. The solution is to modify the column definition before data is migrated: “alter table table_name modify rate decimal(10,2) default NULL”

4. UPDATE works differently.

**make sure you have backups of every bit of data… including your binary logs.

**make sure your backups continue to run after you shuffle around servers. This is something I should have planned ahead a little. In the shuffle I didn’t think about how our backups run off our slaves — but those slaves got promoted to master. No backups ran off those masters (I shut the backups off) but I could have done it with a little more forethought and probably only miss one backup (while our servers were down during the actual upgrade).

**in any organization larger than a mom ‘n pop shop you need to coordinate with other people. In my case I had to work with our systems team (who brought the application down, made some necessary configuration changes and deployed new code for the app and then brought it back up) and also our developers (in resolving problems with the code and its interaction with the new databases). I couldn’t have done it without them. I despise meetings but they are necessary to coordinate things. Make sure everyone is on-board!!

Overall I would rate our experience a 9 out of 10. It went exactly as I planned up until the point where I discovered that XFS data partitions don’t create consistent snapshots with LVM2 for copying over to another server and creating a slave..but that is a topic for another post.

Today I got an email in my inbox from Visibones. Visibones (http://www.visibone.com/sql/) has just released and updated version of the MySQL Chart and cards. This is a really cool product that isn’t very many “bones” (sorry..couldn’t resist). The smaller charts (17″ x 24.1″) are $20 and the larger charts (24″x31.1″) are $30. One chart has statements and one has functions on it. They include 84 statements and 240 functions and operators. I am going to be ordering one for myself. Take the time to look at the sight. I don’t know about you, but there is no way I can remember every statement (and syntax).

We are having the great opportunity of hosting Jay Pipes tomorrow at our Raleigh MUG. If you want to go and haven’t signed up yet you can go here to sign up:

http://mysql.meetup.com/cities/us/nc/raleigh/

Hope to see you there!! Meeting starts at 7:00.

Sorry everyone.  Turns out that my ftp program cut out about half way through the upload of the Q & I tuning presentation last week.  A kind reader pointed it out.  I checked this time..its all there!!!

So I have question.  Something I was thinking about.  I check and repair tables whenever I get a chance, but I am curious what other people do.  How do you manage your checks on production servers?

How do you do it for the least impact on the server?  Whats up!!

I am a command-line guy.  I have been for a long time.  Almost everything I need to do with MySQL I can do from the command line.  This is a good thing.  For one thing, it makes it easy to script things.   However, that being said, there is at least one area that I need a GUI.  That is for creating charts of schema.  For some reason it makes it easy for the developers if they can see a pretty picture of the the database ;)  I don’t understand but  …. that is what they want.

I have tried out several programs.  I don’t like be negative, so let’s just say that some of them had short-comings.  The most recent program I checked out, which has been around for quite some time,  has a decent schema design section.  However, it CAN’T PRINT!!  I even sent an email to support and they answered and said no..but that is coming in the “near future”.  Hmmm….seems pretty important to me.

Fortunately, when I evaluated the newest version of the Workbench program from MySQL AB, I didn’t have these problems.  Enough ink has been spilled about the differences between the free version of the Workbench and the Standard Edition (SE).  I have looked at both.  I will happily pay the price of the license (I believe it’s going to be $100 or less although I can’t put my hands on an exact price right now) for the ability to perform everything easily.  It isn’t too expensive considering how it makes things easier.  And how much is your time worth?  If you are getting paid to work on MySQL servers this should not even be a question.  And yes, I have imported table schema from one of my databases and could even print it out.

Evaluate it for yourself.  Downloads are available from here: http://dev.mysql.com/downloads/workbench/5.0.html

If you wish to participate in the SE beta program send an email to “workbench” @ “mysql.com”.

I posted the video to a presentation I did in September at our local MySQL Users Group.  It is about 50 minutes long if I recall.  Video and pdf of the slides are available here:

http://www.paragon-cs.com/presentations

Enjoy!!

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.