PlanetMysql.ru — информация о СУБД MySQL

The linux.conf.au organisers have given all miniconfs an additional few weeks to spruik for more proposal submissions, huzzah!

So if you didn’t submit a proposal because you weren’t sure whether you’d be able to attend LCA2010, you now have until October 23 to convince your boss to send you and get your proposal in.

PlanetMySQL Voting: Vote UP / Vote DOWN

The linux.conf.au organisers have given all miniconfs an additional few weeks to spruik for more proposal submissions, huzzah!

So if you didn’t submit a proposal because you weren’t sure whether you’d be able to attend LCA2010, you now have until October 23 to convince your boss to send you and get your proposal in.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL backup soon becomes an important matter when the database is used in production. The pain-point comes from the fact that while backuping the database is not available to respond to client requests anymore. With mysqldump - the standard tool for performing MySQL backups - and a large database the operation can go over many tenth of minutes if not hours. If I am running my business on line this is simply not acceptable.

The classical approach to workaround this problem is to take advantage of MySQL replication. I set up a master/slave configuration where the slave acts as copy of the master. Then, when needed, I run mysqldump on the slave without any service interruption on the master.

But ZFS snapshosts bring a new straightforward approach that avoids the pain and the complexity of a master/slave replication.

Snapshots are a key feature of ZFS that allows me to save a copy of any ZFS file system in less than a second. Yesterday, Sept 27th I first created a myDB ZFS filesystem for my database, and since I decided to backup my database everyday I also created a first snapshot. I am using InnoDB, so I made sure that both the data and the logs are located in the myDB filesystem. Today, Sept 28th, I created a second snapshot. Here are the commands:

Yesterday, September 27th

# zfs create pool/myDB
# zfs snapshot pool/myDB@Sept27.2009

Today, September 28th

# zfs snapshot pool/myDB@Sept28.2009

Note that as long as the dataset does not change, the snapshot does not use any disk space, so since there was no change to the database when I created the first snapshot zfs list reporst me a null USED space :

# zfs list -t snapshot
NAME                                     USED AVAIL REFER MOUNTPOINT
pool/myDB@Sept27.2009   0         -          667M   

The commands are simple, but what about the performance? If I run the commands just as is, the filesystem that I save takes 667M on disk. On my labtop I have much more data in my home directory. Let's see how long it takes me to snapshot this data.

# zfs list rpool/export/home/tmanfe
NAME                                      USED  AVAIL  REFER  MOUNTPOINT
rpool/export/home/tmanfe   47.8G   22.5G     28.1G     /export/home/tmanfe

# time  zfs snapshot rpool/export/home/tmanfe@28.2009
real    0m0.370s
user   0m0.004s
sys     0m0.009s

I am able to snapshot 47.9GBytes of data in 0.37seconds. Good enough. As long as I can stay bellow a second, this will certainly go unnoticed by the users. By the way, should my dataset increase, the snapshot time will stay the same.

Now, what do I need to do with MySQL?

For my data to be properly and consistently saved, I need to make sure that it gets written to disk. To do this, MySQL provides me with the command FLUSH and its TABLES WITH READ LOCK option. About this option the documentation says: "Closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executing UNLOCK TABLES. This is very convenient way to get backups if you have a file system such as Veritas that can take snapshots in time". Note that the documentation should be modified to add ZFS as another filesystem that supports snapshots.

Since I am using InnoDB I also need to turn autocommit off before running the FLUSH command otherwise every MySQL statement - such as FLUSH TABLES WITH READ LOCK - is wrapped into a transaction and at the end of each transaction all the table locks are released. In other words, my attempt to lock tables would be self-canceling, would have no effect. No such thing is required with MyISAM.

Then, I create the ZFS snapshot and I release the tables with the command UNLOCK TABLES. I am done with MySQL!

The snapshot I created is located on the same disks and server than my original data. To put a real backup system in place I need to transfer the snapshot to another backup server or storage system. Once again ZFS provides me with all I need. The zfs send command enables me to send a snapshot somewhere else. The only prerequisite is that ZFS must be available on my  backup server in order to take advantage of the snapshot that it receives.

Here is the exact syntax:

# zfs send -Ri pool/myDB@Sept27.2009 pool/myDB@Sept28.2009 | ssh backupHost “zfs receive -Fd pool”

With the -i option, only the difference between my two snapshots is sent to a system named backupHost. This option enables me to do incremental backups. Ahead of incremental backups I need to send a first complete snapshot to the backup host. I did it yesterday, when creating my very first  snapshot:

# zfs send -R pool/myDB@Sept27.2009 | ssh backupHost “zfs receive -Fd pool”

Note that the stream generated by zfs send replicates the filesystem, not the snapshot.

The stream is then funneled into an ssh command that connects to the backup host. Once connected, the zfs receive command is executed that takes the content of the stream - i.e. the filesytem - and copies it into a zfs pool that is local to the backup host.

I am done.

PlanetMySQL Voting: Vote UP / Vote DOWN

Save the date - October 14th, 3pm Paris & Berlin, 2pm London, 4pm Jerusalem  -  for this free live webinar where you'll have a chance to ask questions to our experts.

This webinar focuses on how ZFS, SSDs and the Open Storage line of products from Sun are changing the rules in the database storage industry. You will learn how to increase data security, scalability, and reduce the price/performance ratio with these technologies. This webinar includes ZFS best practises for databases backup and performance.

To register, click here.

PlanetMySQL Voting: Vote UP / Vote DOWN

Just in case you missed the live event, we have a recording of the ZFS + SSD for databases webcast Listen Now

You can also download the slides from Slide share. Download Slides

PlanetMySQL Voting: Vote UP / Vote DOWN

Over the last month I have been working on a ZFS Tuning for Databases presentation. I'll be presenting it live tomorrow 8/26/09 at 8 AM PST.

This based on a lot of work done at Sun as well as in the community. With the massive adoption of Solid State Devices (SSDs) (thank you iPOD) the storage market just got a whole lot more interesting. Incorporating SSDs into a ZFS pool is a breeze. This presentation is meant to help you get the best out of the ZFS + SSD combination for databases. We look into Postgres, MySQL and Oracle. I also provide a quick into into Sun's unified storage 7000 series systems.

If you are interested do Register Now

PlanetMySQL Voting: Vote UP / Vote DOWN