MySQL on Netapp

One of the databases we need to support is a MySQL database, installed on a windows server. The DBA team collectively has about 3 days of experience with MySQL. The system is technically production, but it is used only by internal users. It is a rather large database (200G) and gets tons of traffic because the application does lots of automatic data collection and reports. In addition it is an ultra sensitive application, which will crash if we stop the database for more than a minute.

But the worse thing is that the developers can’t keep their hands off the database. So every day contains a new surprise – new version, new storage system, the data files move to a new directory, all our backup scripts were deleted, etc etc.

I placed the MySQL data files on a Netapp LUN (connected to windows via iScsi) and I wrote a very nice script that locked tables, flushed them to disk, took netapp snapshot and unlocked the tables. I was rather proud of this script because it was really a good use of Netapp and allowed us to take backup without upsetting our sensitive application.

Well, few weeks ago we noticed that snapshots stopped on the relevant volume. Farther investigation revealed that the backup scripts are gone. Well, I’m used to that and I immediately uploaded a new copy of the scripts.

Only today I noticed that at some point our developers changed MySQL storage from MyISAM to InnoDB. InnoDB tables are not affected by lock and flush commands, which means that we have no valid backups.

I looked into InnoDB backup possibilities a bit and there seem to be two solutions, both of them are not really compatible with Netapp snapshot capabilities, which mean that they will be really really slow and take up too much storage. I’m still checking our options with Netapp support, but we may need to resort to cold backups.

In the meantime my opinion of MySQL just got a bit more negative. It appears to give DBAs (or developers) two choices for storage:
1. MyISAM – which is quick and can be easily backed up, but doesn’t support transactions, ACID or point-in-time recovery, so it can barely be called a real DB.
2. InnoDB – which can be called a real DB, except that it is very difficult to back up.

I love Open Source, but I prefer my databases to be Oracle.

Advertisements

2 Comments on “MySQL on Netapp”

  1. Ivan says:

    Hi Chen,

    I think you should carefully check what kind of functionality MyISAM and InnoDB provide. First of all, a change from MyISAM to InnoDB demands some analysis, since the behaviour of the 2 storage engines is completely different. Secondly, if you are looking for something similar to Oracle, InnoDB is definitely the closest thing to Oracle in terms of locking, flushing, isolation etc.

    If you need incremental backup and point in time recovery, the best thing to do is to set up the binary log – in general, it does not add too much overhead and it can do the trick. You can also implement MySQL replication and apply full backups on the slave server, leaving the master online 24/7.

    Another point: although it’s quite slow, with innodb you can apply online hot backups, whilst you can’t do it with MyISAM.

    Hope it helps.

    /iz

  2. prodlife says:

    Thanks Ivan,

    Indeed a change from MyISAM to InnoDB requires some analysis. Or at the very least – informing the DBA. Unfortunately our development team did neither.

    I’ll check the issue of hot backups with InnoDB – it sounds promising.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s