An article I wrote was posted to the Facebook Engineering blog, about the automation system I worked on at Facebook for MySQL Database Provisioning.
It covers, in fairly intimate detail, a system called "Windex" that we use to provision and re-provision our MySQL databases at Facebook. This system basically provisioned the new Facebook Datacenter in Luleå, Sweden, with very little human effort, saving us loads of time.
So, if you're curious about some of what it is that has been taking up all my time for the last year and some, or if you're just always curious about how Facebook is doing things, go check it out.
Friday, July 12, 2013
Tuesday, April 17, 2012
Support for XtraBackup 2.0 in XtraBackup Manager coming soon...
Hi Folks,
Just a quick note to let you know that I am planning to add support to XtraBackup Manager to work with XtraBackup 2.0 series releases fairly soon.
Using the XtraBackup 2.0 series will mean that XtraBackup Manager will no longer need to stage the incremental backups to a location on the remote host before copying them back to the XtraBackup Manager storage.
This can be a remarkable efficiency saving for systems with a lot of page changes between backups.
I will also be trying to address some of the feedback/requests that I have received in the Google Code Issues section.
Please check out the project in Google Code here, if you have not already. Feedback and contributions are welcomed!
http://code.google.com/p/xtrabackup-manager/
Cheers,
Lachlan
Just a quick note to let you know that I am planning to add support to XtraBackup Manager to work with XtraBackup 2.0 series releases fairly soon.
Using the XtraBackup 2.0 series will mean that XtraBackup Manager will no longer need to stage the incremental backups to a location on the remote host before copying them back to the XtraBackup Manager storage.
This can be a remarkable efficiency saving for systems with a lot of page changes between backups.
I will also be trying to address some of the feedback/requests that I have received in the Google Code Issues section.
Please check out the project in Google Code here, if you have not already. Feedback and contributions are welcomed!
http://code.google.com/p/xtrabackup-manager/
Cheers,
Lachlan
Monday, April 9, 2012
Talking At MySQL Conference
Hi Everyone!
Just a reminder to all of those who are attending the MySQL Conference in Santa Clara this week that I'll be presenting a session all about XtraBackup Manager.
My session will be entitled "Introducing XtraBackup Manager" and happens on 11 April 15:30-16:20 @ Ballroom D.
If you are interested in learning more about XtraBackup Manager, or would just like to come support me - I look forward to seeing you there!
Cheers,
Lachlan
Just a reminder to all of those who are attending the MySQL Conference in Santa Clara this week that I'll be presenting a session all about XtraBackup Manager.
My session will be entitled "Introducing XtraBackup Manager" and happens on 11 April 15:30-16:20 @ Ballroom D.
If you are interested in learning more about XtraBackup Manager, or would just like to come support me - I look forward to seeing you there!
Cheers,
Lachlan
Tuesday, February 7, 2012
XtraBackup Manager - Job Control, Better Debugging and some little fixes...
Hi Everyone,
Just a quick note to let you know that I've just finished up adding some new features to XtraBackup Manager.
You can now get better visibility into what is going on inside XtraBackup Manager with the "xbm status" command.
It will allow you to see which backup jobs are running and also those which may be waiting to start, due to the maximum number of concurrent backup tasks already running.
It looks/works as follows:
Note: I have to thank a tiny little BSD-licensed project I found on Google Code called PHP text table for saving me the need to reinvent the wheel in providing this very mysql command-line client-styled table output.
In addition to seeing which jobs are running/queued, if there is a backup job you would like to abort for some reason, then you can now simply use the "xbm kill" command with a Job ID taken from the "xbm status" output:
The backup job itself will log an event at the ERROR level, like:
I'm still not 100% on whether an aborted backup message should be considered an "Error" level event or an "Info" level event. My thinking is that I'd prefer to know if a job was aborted, so I figure putting it at the ERROR level will ensure it is always logged.
Now speaking quickly of the log levels -- it is now useful to increase your logging level in config.php from INFO to DEBUG.
You will see the exact commands used for running backups by XtraBackup Manager, which can be useful when troubleshooting XBM-related issues.
It will enable logging like the below -- Note: The password is _actually_ masked when writing the command to the log. You're welcome ;-)
Aside from the above, some other small fixes were made, including ensuring that all temporary files created on the database host that you're backing up are created in the defined "staging_tmpdir" -- This is a parameter that is set at the host level in XtraBackup Manager.
eg. shell> xbm host edit hostname staging_tmpdir /path/to/use
I encourage you to check out the XtraBackup Manager Project and open issues with any problems you encounter or other feedback.
Cheers,
Lachlan
Just a quick note to let you know that I've just finished up adding some new features to XtraBackup Manager.
You can now get better visibility into what is going on inside XtraBackup Manager with the "xbm status" command.
It will allow you to see which backup jobs are running and also those which may be waiting to start, due to the maximum number of concurrent backup tasks already running.
It looks/works as follows:
[xbm@localhost ~]$ xbm status XtraBackup Manager v0.8 - Copyright 2011-2012 Marin Software Currently Running Backups: +--------+-----------+-------------+---------------------+-------------------+------+ | Job ID | Host | Backup Name | Start Time | Status | PID | +--------+-----------+-------------+---------------------+-------------------+------+ | 14 | localhost | test-backup | 2012-02-07 14:19:19 | Performing Backup | 2525 | +--------+-----------+-------------+---------------------+-------------------+------+
Note: I have to thank a tiny little BSD-licensed project I found on Google Code called PHP text table for saving me the need to reinvent the wheel in providing this very mysql command-line client-styled table output.
In addition to seeing which jobs are running/queued, if there is a backup job you would like to abort for some reason, then you can now simply use the "xbm kill" command with a Job ID taken from the "xbm status" output:
[xbm@localhost ~]$ xbm kill 14 XtraBackup Manager v0.8 - Copyright 2011-2012 Marin Software Action: Backup Job ID 14 was killed.
The backup job itself will log an event at the ERROR level, like:
2012-02-07 14:19:30 -0800 [ERROR] : [ The backup job was killed by an administrator. Aborting... ] 2012-02-07 14:19:30 -0800 [INFO] : [ Cleaning up files... ] 2012-02-07 14:19:30 -0800 [INFO] : [ Released lock on port 10000. ] 2012-02-07 14:19:31 -0800 [ERROR] : [ Exiting after the backup job was killed... ]
I'm still not 100% on whether an aborted backup message should be considered an "Error" level event or an "Info" level event. My thinking is that I'd prefer to know if a job was aborted, so I figure putting it at the ERROR level will ensure it is always logged.
Now speaking quickly of the log levels -- it is now useful to increase your logging level in config.php from INFO to DEBUG.
You will see the exact commands used for running backups by XtraBackup Manager, which can be useful when troubleshooting XBM-related issues.
It will enable logging like the below -- Note: The password is _actually_ masked when writing the command to the log. You're welcome ;-)
2012-02-07 14:19:19 -0800 [INFO] : [ Staging an INCREMENTAL xtrabackup snapshot of /var/lib/mysql via ssh: mysql@localhost to /tmp/xbm-3592510/deltas... ] 2012-02-07 14:19:19 -0800 [DEBUG] : [ Attempting to run the incremental backup with command: ssh -o StrictHostKeyChecking=no -p 22 mysql@localhost 'cd /tmp/xbm-3592510 ; innobackupex --ibbackup=xtrabackup --slave-info --incremental-lsn=2325647 /tmp/xbm-3592510/deltas --user=root --safe-slave-backup --password=XXXXXXX --no-timestamp --incremental --throttle=0 1>&2 ' ]
Aside from the above, some other small fixes were made, including ensuring that all temporary files created on the database host that you're backing up are created in the defined "staging_tmpdir" -- This is a parameter that is set at the host level in XtraBackup Manager.
eg. shell> xbm host edit hostname staging_tmpdir /path/to/use
I encourage you to check out the XtraBackup Manager Project and open issues with any problems you encounter or other feedback.
Cheers,
Lachlan
Tuesday, January 24, 2012
I'm speaking at the MySQL Conference And Expo 2012!
Hello Everyone,I'm very pleased to announce that my submission to talk at the Mysql Conference And Expo 2012 has been accepted! I'll be giving a talk entitled "Introducing XtraBackup Manager", which, as the title suggests, will serve as an introduction to XtraBackup Manager.
I'll be covering what it is, how it works and its features as well as reserving some time for Q+A.
If you are interested in learning more about this tool and plan to attend the conference, this will be a great way to get started!
I hope to see some of you there in April!
For more info on the conference, click here.
Cheers,
Lachlan
Thursday, January 5, 2012
XtraBackup Manager Pre-Release v0.8 - Try it out today!
I'm happy to announce XtraBackup Manager Pre-Release v0.8!
Now that XtraBackup 1.6.4 is released and I have completed some of my final show-stopper bug fixes, I feel that XtraBackup Manager is now in a state ready for more general consumption.
I have yet to package up tarballs, but the Quick Start Guide in the Project Wiki contains all the steps you should need to get up and running from the svn trunk.
There is also some great detailed documentation, including diagrams of all of the different Backup Strategies here.
So please, check out the Project and take it for a spin -- if you have problems or questions, join the discussion on the XtraBackup Manager Google Group!
Thanks and Happy 2012!!
Note: Release notes for XtraBackup v0.8 can be found here.
Lachlan
Friday, December 2, 2011
XtraBackup Manager - XtraBackup Throttling
Hello again!
This week I have been spending some time adding support for throttling to XtraBackup Manager as it has been considered a pre-requisite for us using the tool against our production databases.
In order to add support for throttling, the first thing I did was to look into what kind of means are available to throttle.
It seems there are two methods, both of which are mentioned in Percona's docs or blogs.
#1. Use the --throttle=N parameter. You can give this to innobackupex or to xtrabackup directly. According to the documentation this will limit xtrabackup to use N IOPs/sec when running in --backup mode.
For local machine backups this means N total read/write IOPS/sec and for incrementals this simply means N read IOPS/sec. When using streaming mode --throttle does not take effect (see #2).
#2. Use a nifty tool called "pv" (Pipe Viewer). It has a few features, but most notably it can be use as a simple rate limiter in your pipeline. An example:
shell> cat myFile | pv -q -L10m > myFileCopy
The above will limit the speed at which the file is "cat" into myFileCopy to 10 megabytes a second. Assuming of course the IO subsystem can reach at least that speed.
The best application for pv is to place it somewhere in the pipeline of your streaming backups to limit the rate at which things can flow through the pipeline.
Eg.
shell> innobackupex --stream | pv -q -L10m | nc targetHost 10000
The above will stream through pv and limit the maximum throughput to 10 megabytes/second.
So now understanding what rate limiting methods are available, I needed to consider in what ways XtraBackup Manager uses XtraBackup and the best way to implement the throttling.
I know that:
a) XtraBackup Manager always uses streaming mode when it takes a full backup, so the only option to use there is #2, pv.
b) When performing an incremental backup, XtraBackup Manager will always have xtrabackup stage the deltas locally, before using netcat (nc) to shuttle the data back over the network to the backup host for storage. In this case, limiting using pv is not really useful, because xtrabackup is going to chew up as much IO as it can while calculating the deltas, so we need to opt for the --throttle option on xtrabackup.
So once I understood that I'll need to actually implement throttling in two ways in XtraBackup Manager, I thought about how I would present it to the user for configuration.
I personally find it a bit annoying and confusing that I have to think in two units of measurement for different situations, so I wanted to see if I could insulate the user from that.
My aim was to see if I could present the user with a single configurable for the throttle on a backup task. After all, you don't care what type of backup is going on, you just want to say "Don't use more IO than this much…".
So in order to achieve this, I needed to understand the relationship between the two options as well as the characteristics of IO in both cases.
From my understanding, if you are taking a full backup, you are simply streaming each file sequentially - so we are talking about sequential reads here.
If we are talking about incrementals, we basically give xtrabackup a log sequence number and say "check all the pages and copy ones with a log sequence number above the one we gave" -- so we're finding the pages that have been changed since the given log sequence number.
In this case, it should also be a sequential read, as we're scanning pages end to end, and just checking the log sequence number.
So in both cases it seems we're talking about sequential reads.
When using pv, we're already dealing in a term that is easy to understand and fairly non-subjective. A rate limit in megabytes/sec of sequential read is straight forward.
Now when we're dealing with the --throttle option and thinking in IOPS we have some more to think about. Firstly, how big is an IOP?
Since I'm no good at reading C source code, I opted for the black box method of investigation and simply took an idle database server and started running xtrabackup against it with various --throttle values, while watching iostat on the data mount.
Here are some results:
Throttle value vs Observed disk throughput MB/sec
1:3 MB/sec
2:4 MB/sec
3:5 MB/sec
4:6 MB/sec
5:7 MB/sec
Interestingly the pattern I observe is: throughput = N+2
My best interpretation after even attempting a little digging into xtrabackup.c is that on this idle system we are limiting xtrabackup to 1 x 1MB IOP per second to scan the InnoDB data files, plus we burn 2MB per second to scan/copy the InnoDB log so that it can be applied later.
Now the catch 22 in this whole thing is that I'm observing this on an idle system, so this 2MB per second of log IO would increase if there is more log activity -- surely on a busy system you would need to read more than 2MB of logs every second to keep up.
The catch part? If I actually make the system busy, I can no longer determine where all the different IO in iostat is coming from, so I can't determine how much IO xtrabackup is now using. I'm sure there is a better way to instrument that per process, but unfortunately it extends beyond my personal skill set right now.
In blogging this, I'm hoping someone reading this can help with ideas or clarification...
So coming back to how I should implement the throttling -- I'm fairly sure that IOPS are 1MB in xtrabackup and pv also allows me to throttle in MB/sec, so I should be able to give one simple "throttle" configurable to the XtraBackup Manager user and tell them it limits in MB/sec.
The question then becomes, should I adjust the value I pass to --throttle for XtraBackup to account for this "at least 2MB used for log scanning"?
I decided I wanted to try to be clever and go ahead and adjust it -- so the value passed to XtraBackup for --throttle is now adjusted -2. If the adjustment gives a throttle value less than 1, it is simply given as 1.
None of this is set in stone -- I'm still testing and experimenting, but I'm curious to know your thoughts.
Can anyone shed light on what xtrabackup is doing ?
Should I bother adjusting this value or not ?
Cheers,
Lachlan
This week I have been spending some time adding support for throttling to XtraBackup Manager as it has been considered a pre-requisite for us using the tool against our production databases.
In order to add support for throttling, the first thing I did was to look into what kind of means are available to throttle.
It seems there are two methods, both of which are mentioned in Percona's docs or blogs.
#1. Use the --throttle=N parameter. You can give this to innobackupex or to xtrabackup directly. According to the documentation this will limit xtrabackup to use N IOPs/sec when running in --backup mode.
For local machine backups this means N total read/write IOPS/sec and for incrementals this simply means N read IOPS/sec. When using streaming mode --throttle does not take effect (see #2).
#2. Use a nifty tool called "pv" (Pipe Viewer). It has a few features, but most notably it can be use as a simple rate limiter in your pipeline. An example:
shell> cat myFile | pv -q -L10m > myFileCopy
The above will limit the speed at which the file is "cat" into myFileCopy to 10 megabytes a second. Assuming of course the IO subsystem can reach at least that speed.
The best application for pv is to place it somewhere in the pipeline of your streaming backups to limit the rate at which things can flow through the pipeline.
Eg.
shell> innobackupex --stream
The above will stream through pv and limit the maximum throughput to 10 megabytes/second.
So now understanding what rate limiting methods are available, I needed to consider in what ways XtraBackup Manager uses XtraBackup and the best way to implement the throttling.
I know that:
a) XtraBackup Manager always uses streaming mode when it takes a full backup, so the only option to use there is #2, pv.
b) When performing an incremental backup, XtraBackup Manager will always have xtrabackup stage the deltas locally, before using netcat (nc) to shuttle the data back over the network to the backup host for storage. In this case, limiting using pv is not really useful, because xtrabackup is going to chew up as much IO as it can while calculating the deltas, so we need to opt for the --throttle option on xtrabackup.
So once I understood that I'll need to actually implement throttling in two ways in XtraBackup Manager, I thought about how I would present it to the user for configuration.
I personally find it a bit annoying and confusing that I have to think in two units of measurement for different situations, so I wanted to see if I could insulate the user from that.
My aim was to see if I could present the user with a single configurable for the throttle on a backup task. After all, you don't care what type of backup is going on, you just want to say "Don't use more IO than this much…".
So in order to achieve this, I needed to understand the relationship between the two options as well as the characteristics of IO in both cases.
From my understanding, if you are taking a full backup, you are simply streaming each file sequentially - so we are talking about sequential reads here.
If we are talking about incrementals, we basically give xtrabackup a log sequence number and say "check all the pages and copy ones with a log sequence number above the one we gave" -- so we're finding the pages that have been changed since the given log sequence number.
In this case, it should also be a sequential read, as we're scanning pages end to end, and just checking the log sequence number.
So in both cases it seems we're talking about sequential reads.
When using pv, we're already dealing in a term that is easy to understand and fairly non-subjective. A rate limit in megabytes/sec of sequential read is straight forward.
Now when we're dealing with the --throttle option and thinking in IOPS we have some more to think about. Firstly, how big is an IOP?
Since I'm no good at reading C source code, I opted for the black box method of investigation and simply took an idle database server and started running xtrabackup against it with various --throttle values, while watching iostat on the data mount.
Here are some results:
Throttle value vs Observed disk throughput MB/sec
1:3 MB/sec
2:4 MB/sec
3:5 MB/sec
4:6 MB/sec
5:7 MB/sec
Interestingly the pattern I observe is: throughput = N+2
My best interpretation after even attempting a little digging into xtrabackup.c is that on this idle system we are limiting xtrabackup to 1 x 1MB IOP per second to scan the InnoDB data files, plus we burn 2MB per second to scan/copy the InnoDB log so that it can be applied later.
Now the catch 22 in this whole thing is that I'm observing this on an idle system, so this 2MB per second of log IO would increase if there is more log activity -- surely on a busy system you would need to read more than 2MB of logs every second to keep up.
The catch part? If I actually make the system busy, I can no longer determine where all the different IO in iostat is coming from, so I can't determine how much IO xtrabackup is now using. I'm sure there is a better way to instrument that per process, but unfortunately it extends beyond my personal skill set right now.
In blogging this, I'm hoping someone reading this can help with ideas or clarification...
So coming back to how I should implement the throttling -- I'm fairly sure that IOPS are 1MB in xtrabackup and pv also allows me to throttle in MB/sec, so I should be able to give one simple "throttle" configurable to the XtraBackup Manager user and tell them it limits in MB/sec.
The question then becomes, should I adjust the value I pass to --throttle for XtraBackup to account for this "at least 2MB used for log scanning"?
I decided I wanted to try to be clever and go ahead and adjust it -- so the value passed to XtraBackup for --throttle is now adjusted -2. If the adjustment gives a throttle value less than 1, it is simply given as 1.
None of this is set in stone -- I'm still testing and experimenting, but I'm curious to know your thoughts.
Can anyone shed light on what xtrabackup is doing ?
Should I bother adjusting this value or not ?
Cheers,
Lachlan
Tuesday, November 22, 2011
XtraBackup Manager - Exciting progress!
Hi Folks,
Just another quick update.
I've been working really hard these past couple of weeks on getting what I'm hoping is some great documentation happening for XtraBackup Manager.
There is still more work to be done, but I'm really pleased with how it's coming along.
Stay tuned… awesome things are coming :)
Meanwhile, to those of you who celebrate Thanksgiving this week -- have a safe and happy holiday, however you choose to spend it!
Cheers,
Lachlan
Just another quick update.
I've been working really hard these past couple of weeks on getting what I'm hoping is some great documentation happening for XtraBackup Manager.
There is still more work to be done, but I'm really pleased with how it's coming along.
Stay tuned… awesome things are coming :)
Meanwhile, to those of you who celebrate Thanksgiving this week -- have a safe and happy holiday, however you choose to spend it!
Cheers,
Lachlan
Tuesday, November 8, 2011
XtraBackup Manager - Coming soon!...
Howdy everyone!
I'm very happy to announce that very soon XtraBackup Manager will be released in an initial alpha capacity.
The command-line configuration and management interface is very close to completion and I'll be working on some documentation soon too.
This alpha will serve as a way to get some early adopters testing the tool as well as help collate feedback on necessary features that I may have missed including so far.
Stay tuned!…
The first completely FREE/OSS management software for XtraBackup will be available soon!
Cheers,
Lachlan
I'm very happy to announce that very soon XtraBackup Manager will be released in an initial alpha capacity.
The command-line configuration and management interface is very close to completion and I'll be working on some documentation soon too.
This alpha will serve as a way to get some early adopters testing the tool as well as help collate feedback on necessary features that I may have missed including so far.
Stay tuned!…
The first completely FREE/OSS management software for XtraBackup will be available soon!
Cheers,
Lachlan
Friday, September 23, 2011
XtraBackup Manager - What have I been up to!?!
Howdy all,
Just a quick update in the world of XtraBackup Manager development. In the last couple of weeks I have not been doing a great deal of work on XtraBackup Manager itself, but rather doing a lot of testing of XtraBackup Manager and implicitly XtraBackup along with it.
I hit upon some bugs that were basically roadblocks in the way that we intend to use XtraBackup and I'm sure issues that other folks will run into eventually once adoption of XtraBackup increases even more...
I have been working with Percona and SkySQL Support, as well as dabbling in some of the code myself to get fixes for these issues.
The main issues we hit were:
* tar4ibd crashes on certain InnoDB data files (unable to use streaming backups at all) - This was a regression in pre-release build of xtrabackup-1.6.3, For now "fixed" by using an older tar4ibd binary from stable 1.6.2 release.
* innobackupex would not capture MySQL slave position unless using FLUSH TABLES WITH READ LOCK and performing a full backup. Now slave position can be captured in incrementals or full backups without locks, provided that --safe-slave-backup is specified.
* Tables getting both DROP/CREATE or TRUNCATE during backup can cause assertion failure - SkySQL contributed a fix via Monty Program which I am ready to test now.
* Xtrabackup apply-log can crash when attempting to create temporary tables if the temp dir does not exist - Should be fixed very soon in xtrabackup-1.6.3 release.
When I decided to embark on the project for XtraBackup Manager, I was happy to think that finally I'll be able to give something back to the community in the tool that I make. It seems what I didn't consider was that in being such a heavy integrator with XtraBackup that I'd also be contributing some good QA and perhaps improvements to XtraBackup itself.
As an aside, I also found a couple of little issues in Shlomi's online alter table in the OpenArk toolkit and submitted patches for that -- so I've felt very contributive lately.
So what lies ahead?
More testing of XtraBackup and XtraBackup Manager as well as finishing off the command-line configurator.
Then we will be preparing to start eating my dog food and run XBM in production!
That's it for now...
Have a great weekend all!
Cheers,
Lachlan
Just a quick update in the world of XtraBackup Manager development. In the last couple of weeks I have not been doing a great deal of work on XtraBackup Manager itself, but rather doing a lot of testing of XtraBackup Manager and implicitly XtraBackup along with it.
I hit upon some bugs that were basically roadblocks in the way that we intend to use XtraBackup and I'm sure issues that other folks will run into eventually once adoption of XtraBackup increases even more...
I have been working with Percona and SkySQL Support, as well as dabbling in some of the code myself to get fixes for these issues.
The main issues we hit were:
* tar4ibd crashes on certain InnoDB data files (unable to use streaming backups at all) - This was a regression in pre-release build of xtrabackup-1.6.3, For now "fixed" by using an older tar4ibd binary from stable 1.6.2 release.
* innobackupex would not capture MySQL slave position unless using FLUSH TABLES WITH READ LOCK and performing a full backup. Now slave position can be captured in incrementals or full backups without locks, provided that --safe-slave-backup is specified.
* Tables getting both DROP/CREATE or TRUNCATE during backup can cause assertion failure - SkySQL contributed a fix via Monty Program which I am ready to test now.
* Xtrabackup apply-log can crash when attempting to create temporary tables if the temp dir does not exist - Should be fixed very soon in xtrabackup-1.6.3 release.
When I decided to embark on the project for XtraBackup Manager, I was happy to think that finally I'll be able to give something back to the community in the tool that I make. It seems what I didn't consider was that in being such a heavy integrator with XtraBackup that I'd also be contributing some good QA and perhaps improvements to XtraBackup itself.
As an aside, I also found a couple of little issues in Shlomi's online alter table in the OpenArk toolkit and submitted patches for that -- so I've felt very contributive lately.
So what lies ahead?
More testing of XtraBackup and XtraBackup Manager as well as finishing off the command-line configurator.
Then we will be preparing to start eating my dog food and run XBM in production!
That's it for now...
Have a great weekend all!
Cheers,
Lachlan
Thursday, September 1, 2011
XtraBackup Manager - Command-line Configurator Preview!
Over the past two weeks I have been working on XtraBackup Manager as much as I can and I'm pleased to say that the command-line configurator is coming along very nicely!
There is now a generic "xbm" command that will be the way to manage hosts, storage volumes and backup schedules as well as doing restores, etc.
So far I have built the volume and host management in and will begin work on the backup schedules next!
Once this command-line interface is complete, I'll work to document it on the project wiki on Google Code and it should be ready for public consumption.
Here is a little preview of how it looks in action - forgive the ugly wrapping…
There is now a generic "xbm" command that will be the way to manage hosts, storage volumes and backup schedules as well as doing restores, etc.
So far I have built the volume and host management in and will begin work on the backup schedules next!
Once this command-line interface is complete, I'll work to document it on the project wiki on Google Code and it should be ready for public consumption.
Here is a little preview of how it looks in action - forgive the ugly wrapping…
xbm@mslvlxbp01:~/xtrabackup-manager$ ./xbm XtraBackup Manager v0.5 - Copyright 2011 Marin Software Error: Context missing. Usage: xbm <context> <action> <args> ... Contexts and actions may be one of the following: volume [add|list|edit|delete] <args> -- Manage Backup Volumes host [add|list|edit|delete] <args> -- Manage Hosts to Backup backup [add|list|edit|delete] <args> -- Manage Scheduled Backup Tasks snapshot [list|delete] -- Manage Backup Snapshots restore [local|remote] <args> -- Restore Backups You may specify only a context, or a context and action to get help on its relevant arguments. xbm@mslvlxbp01:~/xtrabackup-manager$ ./xbm volumes XtraBackup Manager v0.5 - Copyright 2011 Marin Software Error: Action missing. Usage: xbm volumes <action> <args> ... Actions may be one of the following: add <name> <path> -- Add a New Backup Volume list -- List available Backup Volumes edit <name> <parameter> <value> -- Edit a Backup Volume to set <parameter> to <value> delete <name> -- Delete a Backup Volume You may specify an action without parameters to get help on its relevant arguments. xbm@mslvlxbp01:~/xtrabackup-manager$ ./xbm volumes list XtraBackup Manager v0.5 - Copyright 2011 Marin Software -- Listing all Backup Volumes -- Name: Storage Array 1 Path: /backup/xbm Name: Test /backup Path: /backup xbm@mslvlxbp01:~/xtrabackup-manager$ ./xbm volumes add MyVolume /tmp XtraBackup Manager v0.5 - Copyright 2011 Marin Software Action: New volume created with name/path: MyVolume -- /tmp
Friday, August 26, 2011
XtraBackup Manager - A couple of little features...
Just a quick check in… I just added a couple of things that I have found necessary as I'm testing out XtraBackup Manager.
You can now configure globally whether or not to automatically cleanup failed backups. Previously, XBM would always cleanup after itself on a failure.
I am finding that when things fail, that I would like a chance to investigate and troubleshoot why and perhaps open an XtraBackup bug or try some experimentation to see what might get around the problem.
I have been finding it particularly frustrating when waiting 9 hours for a multi-terabyte system to backup and then have some failure occur right at the end -- with the previous auto-cleanup I was left with nothing to troubleshoot with! Now I can turn off the cleanup and have a chance to do some forensics myself.
At the moment this is a quick and dirty feature -- it is only configurable at the global level, not per backup host.
The option is cleanup_on_failure and is found in/includes/config.php
The other feature that I added was the ability to configure how much memory XtraBackup Manager will tell XtraBackup to use when applying logs or merging incremental snapshots onto a full backup.
The default being used so far is 1G - so be careful about how much memory you have and consider how many possible concurrent backups you have configured.
This option is xtrabackup_use_memory and is also found in the config.php file as well as being a global feature only, not configurable per host.
These are not big features by any means, but they are certainly helpful for me and hopefully for others in the future.
That's it for today!
Lachlan
You can now configure globally whether or not to automatically cleanup failed backups. Previously, XBM would always cleanup after itself on a failure.
I am finding that when things fail, that I would like a chance to investigate and troubleshoot why and perhaps open an XtraBackup bug or try some experimentation to see what might get around the problem.
I have been finding it particularly frustrating when waiting 9 hours for a multi-terabyte system to backup and then have some failure occur right at the end -- with the previous auto-cleanup I was left with nothing to troubleshoot with! Now I can turn off the cleanup and have a chance to do some forensics myself.
At the moment this is a quick and dirty feature -- it is only configurable at the global level, not per backup host.
The option is cleanup_on_failure and is found in
The other feature that I added was the ability to configure how much memory XtraBackup Manager will tell XtraBackup to use when applying logs or merging incremental snapshots onto a full backup.
The default being used so far is 1G - so be careful about how much memory you have and consider how many possible concurrent backups you have configured.
This option is xtrabackup_use_memory and is also found in the config.php file as well as being a global feature only, not configurable per host.
These are not big features by any means, but they are certainly helpful for me and hopefully for others in the future.
That's it for today!
Lachlan
Tuesday, August 23, 2011
XtraBackup Manager - Movement on the home front...
It has been a while since I have posted any updates on the XtraBackup Manager front and I apologise for that. Between taking some time off for vacation (how dare I!?) and various different tasks at work snagging my focus away from XBM, I really haven't had much time to work on it.
(Un)fortunately last week we encountered a DB failure that would have been much faster and less painful to recover from had we had XtraBackup Manager finished and in place. While it was a pretty rough week for us DBAs working on addressing the failure, the silver lining is that we now have a concrete example to point to for the importance of the XtraBackup Manager project.
The silver lining in the long story cut short is that I now have the support I need to focus most of my time on XBM again.
So what have I been working on?
I have added support for materialzed backups to the "Continuous Incremental" backup strategy.
I have proceeded with actually running XBM against a few sample hosts with various schedules/settings to see what issues I may encounter.
I have posted a rough design outline in the Google Code wiki for the command-line interface for configuration and started on coding it.
My plan is to follow a similar design to the way the "zfs" command works on (Open)Solaris/Nexenta.
You can see the design doc here:
Once the CLI configurator is done, I'll proceed with some heavy documentation. After that point XBM should be pretty much ready for mass consumption in an evaluation capacity.
I have learned a lot about PHP and OO in the process of developing XBM, which has been fun, but I know code wise it isn't as elegant as it could be.
As I said when I started the project, I am not really a developer, so the internals of XBM probably aren't the cleanest code ever, but I'm doing my best while focussing on actually forging ahead to get it functional rather than getting too bogged down in how well the internals adhere to best OO design practise.
I'm hoping to get some more folks trying it out once the configurator and docs are up to snuff.
Stay tuned!
Lachlan
Thursday, July 7, 2011
NFS Slowness Weirdness
We recently deployed a new NFS filer running on top of Nexenta using ZFS and noticed that some of our systems were having performance issues. Writes to the NFS system on most hosts were snappy, but reads from NFS were capping out at around 3M per second on a Gig-E network interface on a handful of hosts.
Systems that were identically configured in every way - kernel version, nfs package version, hardware, mount options, etc. were behaving differently. One would read and write at around 100M yet another would cap out at 3M.
Our systems guys did some troubleshooting and diagnosis on the network and could not find any issue there. So we went ahead and tested an scp from the NFS server to a problematic host.
The scp would run closer to 80M per second, so it seems that the problem was NFS itself.
We checked and double checked our configs, settings, sysctl.conf, versions and could not find anything different between a host where throughput was fine and one where it was horribly slow.
In the end we decided to umount all the NFS mounts, remove the "nfs" kernel module (rmmod) and reload it with modprobe and then remount the NFS mounts.
Lo and behold the throughput was back up to around 100M per second. This approach to fix the problem worked on all the problematic hosts we have tried it on so far.
Still,.. we are left scratching our head as to the real cause of the issue here as we basically have "jiggled the cable" or given NFS the "three fingered salute", if you will.
So though we now have a (rather intrusive) fix, we still don't know how to prevent the issue, if or when it will happen again, etc.
Has anyone out there seen anything similar to this before? Any ideas on what could be the issue?
Hmmm...
Lachlan
Systems that were identically configured in every way - kernel version, nfs package version, hardware, mount options, etc. were behaving differently. One would read and write at around 100M yet another would cap out at 3M.
Our systems guys did some troubleshooting and diagnosis on the network and could not find any issue there. So we went ahead and tested an scp from the NFS server to a problematic host.
The scp would run closer to 80M per second, so it seems that the problem was NFS itself.
We checked and double checked our configs, settings, sysctl.conf, versions and could not find anything different between a host where throughput was fine and one where it was horribly slow.
In the end we decided to umount all the NFS mounts, remove the "nfs" kernel module (rmmod) and reload it with modprobe and then remount the NFS mounts.
Lo and behold the throughput was back up to around 100M per second. This approach to fix the problem worked on all the problematic hosts we have tried it on so far.
Still,.. we are left scratching our head as to the real cause of the issue here as we basically have "jiggled the cable" or given NFS the "three fingered salute", if you will.
So though we now have a (rather intrusive) fix, we still don't know how to prevent the issue, if or when it will happen again, etc.
Has anyone out there seen anything similar to this before? Any ideas on what could be the issue?
Hmmm...
Lachlan
Thursday, June 23, 2011
XtraBackup Manager - Backup Strategies and Materialized Snapshots
Hi Folks,
I have now committed the changes for the new Backup Strategies feature to trunk! In addition, I'm pretty much finished on implementing the majority of the Materialized Snapshot feature/option.
So let me talk a little bit about those features...
Enabling the "maintain_materialized_copy" feature for a backup will mean that while XBM takes FULL backups and INCREMENTAL backups and keeps them separately, it will maintain an additional directory that contains a complete backup with the latest deltas applied to it.
We only keep a materialized copy of the latest backup, not for each and every possible restore point as that would take up more space than most people can afford ( or at least more than we can afford ).
One benefit here is that if some problem should occur applying the latest set of deltas, you do not risk completely voiding your backup, you can always restore from the seed and deltas that are stored separately, up until the snapshot before the problem, and then perhaps use binary logs to roll forward from there.
Using materialized snapshots also means that you are constantly testing the process of actually applying your deltas, so if something was wrong with that step, you will learn about it quickly, not later on when you are desperately trying to restore from your backups.
Another great thing about materialized snapshots is that there is no waiting around for multiple sets of deltas to apply in order to restore your latest backup. Simply copy the materialized snapshot to the restore location and fire up MySQL -- InnoDB will of course take the usual time to do final crash recovery steps, but it should be much faster to get back up and running.
Now a little on Backup Strategies. There are three major strategies available and I'll talk a little on each below.
Full Backup Only
This is fairly cut and dry. XtraBackup Manager only takes full backups. You can configure how many is the maximum number of these snapshots to keep. After each backup is complete, the retention policy will be applied and any number of backups beyond the maximum will be deleted, counting from the latest to oldest. There is no need or option for materialized snapshots, since in this case all backups are always fully materialized.
Continuous Incremental
Take a full backup (aka seed) first and then after that only take incremental backups. The seed and deltas are all stored separately. Again you can configure the maximum number of snapshots to maintain (retention policy), however, in this case, we apply the oldest set of deltas onto the seed and repeat that process until we have no more than the maximum number of snapshots configured. The retention policy is always applied after a successful backup.
Rotating Groups
This is the most complex backup strategy, but it allows a great deal of flexibility. The concept here is that we consider a backup group as a full backup with corresponding sets of deltas. You may configure the number of groups you keep, as well as when new groups should be created in a variety of ways.
The benefit of keeping more than one group, is that should one seed or set of deltas be bad or broken in any way, you have another option -- in addition, you may more frequently take full backups, which means that the number of sets of deltas to be applied to reach a particular restore point will be less.
When using rotating groups, you must select a rotation method - there are two options: DAY_OF_WEEK and AFTER_SNAPSHOT_COUNT.
With the snapshot count rotation method, the first backup will be a FULL backup, after which incremental backups are taken until a total number of backups equals the number you configure. The next backup after that will be a full backup in a new group and the backups after that will be incrementals, based on the newly taken full backup. This cycle just repeats, however, retention is controlled based on the maximum number of groups to maintain. Once a new group is created beyond the maximum allowed, the oldest group will be removed until there are no more than the maximum.
With day of week, you simply select which day(s) of week you would like your FULL backups to be taken on -- XBM will "rotate" on the first snapshot taken for that day. "Rotate" essentially means it will create a new group with its own full backup and then proceed to take deltas until a "rotate_day_of_week" is encountered again. You can also configure a maximum number of deltas allowed, in case for some reason the backup is never running on the day of the week that it should. In that case it will not backup - You may configure if you consider that a fatal error that should be alerted, or if it should just silently do nothing/skip that backup.
The benefit of using day of week over snapshot count is that it allows you to firmly control which days your full backups should happen on.
Eg. If I deploy backups on a new host and I configure to take full backups on Sunday. I might kick off the first backup on a Wednesday -- in this case because it is the first backup ever for the host, it will take a full backup and then continue to take deltas until Sunday, when it will take a full backup again and then continue to rotate every Sunday from then on.
Again for day of week rotation, retention is controlled based on the maximum number of groups to maintain. Once a new group is created beyond the maximum allowed, the oldest group will be removed until there are no more than the maximum.
Now with all of these complex behaviours and options to configure and close to zero up-to-date documentation, I am about the only person who could make use of XBM, so the next steps are a better configuration tool/interface and documentation.
In addition, I'm also planning to add support for backing up the MySQL binary logs.
Once again, if you're interested in contributing in any way or just checking out the project, it is hosted on Google Code here:
http://code.google.com/p/xtrabackup-manager/
Comments and feedback are welcome!
Cheers,
Lachlan
I have now committed the changes for the new Backup Strategies feature to trunk! In addition, I'm pretty much finished on implementing the majority of the Materialized Snapshot feature/option.
So let me talk a little bit about those features...
Enabling the "maintain_materialized_copy" feature for a backup will mean that while XBM takes FULL backups and INCREMENTAL backups and keeps them separately, it will maintain an additional directory that contains a complete backup with the latest deltas applied to it.
We only keep a materialized copy of the latest backup, not for each and every possible restore point as that would take up more space than most people can afford ( or at least more than we can afford ).
One benefit here is that if some problem should occur applying the latest set of deltas, you do not risk completely voiding your backup, you can always restore from the seed and deltas that are stored separately, up until the snapshot before the problem, and then perhaps use binary logs to roll forward from there.
Using materialized snapshots also means that you are constantly testing the process of actually applying your deltas, so if something was wrong with that step, you will learn about it quickly, not later on when you are desperately trying to restore from your backups.
Another great thing about materialized snapshots is that there is no waiting around for multiple sets of deltas to apply in order to restore your latest backup. Simply copy the materialized snapshot to the restore location and fire up MySQL -- InnoDB will of course take the usual time to do final crash recovery steps, but it should be much faster to get back up and running.
Now a little on Backup Strategies. There are three major strategies available and I'll talk a little on each below.
Full Backup Only
This is fairly cut and dry. XtraBackup Manager only takes full backups. You can configure how many is the maximum number of these snapshots to keep. After each backup is complete, the retention policy will be applied and any number of backups beyond the maximum will be deleted, counting from the latest to oldest. There is no need or option for materialized snapshots, since in this case all backups are always fully materialized.
Continuous Incremental
Take a full backup (aka seed) first and then after that only take incremental backups. The seed and deltas are all stored separately. Again you can configure the maximum number of snapshots to maintain (retention policy), however, in this case, we apply the oldest set of deltas onto the seed and repeat that process until we have no more than the maximum number of snapshots configured. The retention policy is always applied after a successful backup.
Rotating Groups
This is the most complex backup strategy, but it allows a great deal of flexibility. The concept here is that we consider a backup group as a full backup with corresponding sets of deltas. You may configure the number of groups you keep, as well as when new groups should be created in a variety of ways.
The benefit of keeping more than one group, is that should one seed or set of deltas be bad or broken in any way, you have another option -- in addition, you may more frequently take full backups, which means that the number of sets of deltas to be applied to reach a particular restore point will be less.
When using rotating groups, you must select a rotation method - there are two options: DAY_OF_WEEK and AFTER_SNAPSHOT_COUNT.
With the snapshot count rotation method, the first backup will be a FULL backup, after which incremental backups are taken until a total number of backups equals the number you configure. The next backup after that will be a full backup in a new group and the backups after that will be incrementals, based on the newly taken full backup. This cycle just repeats, however, retention is controlled based on the maximum number of groups to maintain. Once a new group is created beyond the maximum allowed, the oldest group will be removed until there are no more than the maximum.
With day of week, you simply select which day(s) of week you would like your FULL backups to be taken on -- XBM will "rotate" on the first snapshot taken for that day. "Rotate" essentially means it will create a new group with its own full backup and then proceed to take deltas until a "rotate_day_of_week" is encountered again. You can also configure a maximum number of deltas allowed, in case for some reason the backup is never running on the day of the week that it should. In that case it will not backup - You may configure if you consider that a fatal error that should be alerted, or if it should just silently do nothing/skip that backup.
The benefit of using day of week over snapshot count is that it allows you to firmly control which days your full backups should happen on.
Eg. If I deploy backups on a new host and I configure to take full backups on Sunday. I might kick off the first backup on a Wednesday -- in this case because it is the first backup ever for the host, it will take a full backup and then continue to take deltas until Sunday, when it will take a full backup again and then continue to rotate every Sunday from then on.
Again for day of week rotation, retention is controlled based on the maximum number of groups to maintain. Once a new group is created beyond the maximum allowed, the oldest group will be removed until there are no more than the maximum.
Now with all of these complex behaviours and options to configure and close to zero up-to-date documentation, I am about the only person who could make use of XBM, so the next steps are a better configuration tool/interface and documentation.
In addition, I'm also planning to add support for backing up the MySQL binary logs.
Once again, if you're interested in contributing in any way or just checking out the project, it is hosted on Google Code here:
http://code.google.com/p/xtrabackup-manager/
Comments and feedback are welcome!
Cheers,
Lachlan
Monday, June 13, 2011
XtraBackup Manager - Wheels in motion!
Hi Folks,
I just realised that it has now been just a little over a month since I have posted anything regarding XtraBackup Manager!
Fear not friends, I have been working on the most significant changes and additions in XBM yet -- the addition of backup strategies.
With backup strategies you can get better control over when you want to take full backups and when you wish to take incremental backups.
I'll be making a more detailed post once I finish and push the code, but here is a little sneak preview of the kind of things it will do:
* Take full backups only, maintaining up to the last X backups.
* Take a full backup and then incrementals only, maintaining up to the last X backups
* Maintain N sets of backups, where each set has a full backup, followed by X incrementals.
* Rotate backup sets based on different rules like day of the week or after N successful backups.
* Choose which days of the week you want to take your full backups on and take incrementals on the rest!
* Control the number of sets of backups to keep -- old ones will be deleted.
* Keep a separate materialized copy of the most recent backup you took - No waiting to apply days of diffs in the event you need to restore!
The internal refactoring to support these features has made XBM much easier to build on, which is great news for those who may wish to either create their own patches or request features be added.
That's it for now, but keep your eyes peeled for more updates as I push towards a first official release!
Lachlan
I just realised that it has now been just a little over a month since I have posted anything regarding XtraBackup Manager!
Fear not friends, I have been working on the most significant changes and additions in XBM yet -- the addition of backup strategies.
With backup strategies you can get better control over when you want to take full backups and when you wish to take incremental backups.
I'll be making a more detailed post once I finish and push the code, but here is a little sneak preview of the kind of things it will do:
* Take full backups only, maintaining up to the last X backups.
* Take a full backup and then incrementals only, maintaining up to the last X backups
* Maintain N sets of backups, where each set has a full backup, followed by X incrementals.
* Rotate backup sets based on different rules like day of the week or after N successful backups.
* Choose which days of the week you want to take your full backups on and take incrementals on the rest!
* Control the number of sets of backups to keep -- old ones will be deleted.
* Keep a separate materialized copy of the most recent backup you took - No waiting to apply days of diffs in the event you need to restore!
The internal refactoring to support these features has made XBM much easier to build on, which is great news for those who may wish to either create their own patches or request features be added.
That's it for now, but keep your eyes peeled for more updates as I push towards a first official release!
Lachlan
Friday, May 6, 2011
XtraBackup Manager - Email Alerts and More Nexenta/ZFS Testing
Just a quick note - I have added support for email alerts on failed backups into XtraBackup Manager.
Now if something goes awry, XtraBackup Manager can optionally send some detailed information to the email address(es) of your choice!
This should allow you to easily hook to SMS gateways, NOC alert lists, etc.
Additionally, we have been doing some preliminary testing of XtraBackup Manager on a Nexenta machine with 8 Xeon CPUs using ZFS with lzjb compression.
We are getting around 3.8x compression and the Gigabit NICs seem to be the bottleneck in the speed here. Both CPU and disk utilisation look very low.
Our plan is to use a 10 Gigabit NIC in the Nexenta Backup host and stack it with storage - it should make for a very cost effective and space efficient backup host.
Meanwhile, development work continues towards a 1.0 !
Happy Friday!
Cheers,
Lachlan
Now if something goes awry, XtraBackup Manager can optionally send some detailed information to the email address(es) of your choice!
This should allow you to easily hook to SMS gateways, NOC alert lists, etc.
Additionally, we have been doing some preliminary testing of XtraBackup Manager on a Nexenta machine with 8 Xeon CPUs using ZFS with lzjb compression.
We are getting around 3.8x compression and the Gigabit NICs seem to be the bottleneck in the speed here. Both CPU and disk utilisation look very low.
Our plan is to use a 10 Gigabit NIC in the Nexenta Backup host and stack it with storage - it should make for a very cost effective and space efficient backup host.
Meanwhile, development work continues towards a 1.0 !
Happy Friday!
Cheers,
Lachlan
Wednesday, May 4, 2011
XtraBackup Manager - Support for Nexenta (OpenSolaris)!
Hi Folks,
This is just a quick update to let you know that, after much cursing and frustration with my lack of Solaris experience, I have managed to make XtraBackup Manager work on Nexenta (NCP3).
So why would you care?
The answer is because Nexenta is OpenSolaris based and therefore has support for ZFS. I think ZFS is an awesome filesystem to combine with XtraBackup Manager, because you can benefit from transparent compression at the filesystem level.
This means you can store a whole lot more on less disk and you don't have to deal with compressing and uncompressing your backups all the time.
Sure, you could always use Nexenta Community or Enterprise appliances as a NFS mounted filer, but why would you want to stream all of your backup data into one Linux based server to run XtraBackup Manager just so that it all goes out an interface via NFS to the real storage?
This way you can run XtraBackup Manager right on the file server and save yourself network bandwidth and rackspace at the same time. Huzzah!
So far the feature set and documentation remain basic, but I will be continuing development as we want to get this tested so that we can use it ourselves in Production ASAP.
Once again, if you are interested in checking out the project you can find more at:
http://code.google.com/p/xtrabackup-manager
Feedback and comments are welcomed!
Lachlan
This is just a quick update to let you know that, after much cursing and frustration with my lack of Solaris experience, I have managed to make XtraBackup Manager work on Nexenta (NCP3).
So why would you care?
The answer is because Nexenta is OpenSolaris based and therefore has support for ZFS. I think ZFS is an awesome filesystem to combine with XtraBackup Manager, because you can benefit from transparent compression at the filesystem level.
This means you can store a whole lot more on less disk and you don't have to deal with compressing and uncompressing your backups all the time.
Sure, you could always use Nexenta Community or Enterprise appliances as a NFS mounted filer, but why would you want to stream all of your backup data into one Linux based server to run XtraBackup Manager just so that it all goes out an interface via NFS to the real storage?
This way you can run XtraBackup Manager right on the file server and save yourself network bandwidth and rackspace at the same time. Huzzah!
So far the feature set and documentation remain basic, but I will be continuing development as we want to get this tested so that we can use it ourselves in Production ASAP.
Once again, if you are interested in checking out the project you can find more at:
http://code.google.com/p/xtrabackup-manager
Feedback and comments are welcomed!
Lachlan
Tuesday, April 19, 2011
When is an in-memory operation a BAD idea?
Recently I've learned a little more about how MySQL uses implicit in-memory temp tables that I felt it would be worth sharing.
A little background that perhaps many of you may wish to skip...
MySQL when handling many kinds of queries will implicitly create a temp table. This table will start off in-memory and if it exceeds a certain size (controlled by tmp_table_size and max_heap_table_size) it will be converted to an on-disk table in the location(s) defined by the tmpdir server variable.
This was not new to me and it may not be new to you, but I urge you to read on for the interesting part...
When MySQL creates this temporary table in memory, it will use fixed width rows. I assume this is done because in many cases it is easier/faster to allocate and manage memory this way, rather than measuring the size needed for each row in the temp table and then allocating the memory needed, MySQL just allocates <max_row_size> for each row and it's done.
What this means is that the maximum possible space that could be consumed by any one row is the amount of space allocated and consumed for all rows.
Consider, if you will, a VARCHAR(1024) field using the UTF8 character set. Given that a UTF8 character can be represented by up to three bytes (in MySQL), it means that the maximum theoretical size for storage of 1024 such characters becomes 3072 bytes (3K).
Suddenly your generous and forward-thinking schema design becomes your enemy. If such a field only contains simple words like "cat" and "dog" you will need 3K of memory to be allocated in your in-memory temp table regardless.
As you can imagine, a few such fields existing in your implicit temp table, combined with a high number of rows can cause the space needed for this to spiral out of control very quickly!
Now, to add insult to injury, when MySQL decides that your enormous implicit temp table is too big for memory, based on tmp_table_size / max_heap_table_size, it maintains the very same fixed width row format as it copies the table to disk and continues appending rows to it there.
In practise, I have seen this cause 2.3G of data balloon out to 43G -- this is an increase by a factor of over 18!
So how to avoid it?
It really depends on the situation, but I would suggest that if you know a query is going to need such a temp table that you split the query into multiple steps and employ the use of a pivot table.
The pivot table would be an on-disk MyISAM table (TEMPORARY or not - your choice) that you use to explicitly perform the work done by MySQL when performing the implicit temp table step. The benefit here is that when you define this table, you can use variable-width fields and only consume the space needed.
Depending on your system and environment, you could be a little sneaky and even consider defining your MySQL tmpdir as tmpfs (memory) -- this way you get the benefit of the speed of memory as well as only allocating the space you need for each row, rather than the maximum theoretical size.
In the case that I found, it makes a lot more sense to just materialize the temp table efficiently on disk than to be exposed to the risk that a fixed-width table could run amok.
Hopefully this is useful to some of you out there!
Cheers,
Lachlan
Note: Edited per Don McArthur for correctness. utf8 in MySQL only supports the Basic Multilingual Plane subset of utf8, meaning that it may consume only up to 3 bytes per character, not 4 as in the full utf8 spec.
A little background that perhaps many of you may wish to skip...
MySQL when handling many kinds of queries will implicitly create a temp table. This table will start off in-memory and if it exceeds a certain size (controlled by tmp_table_size and max_heap_table_size) it will be converted to an on-disk table in the location(s) defined by the tmpdir server variable.
This was not new to me and it may not be new to you, but I urge you to read on for the interesting part...
When MySQL creates this temporary table in memory, it will use fixed width rows. I assume this is done because in many cases it is easier/faster to allocate and manage memory this way, rather than measuring the size needed for each row in the temp table and then allocating the memory needed, MySQL just allocates <max_row_size> for each row and it's done.
What this means is that the maximum possible space that could be consumed by any one row is the amount of space allocated and consumed for all rows.
Consider, if you will, a VARCHAR(1024) field using the UTF8 character set. Given that a UTF8 character can be represented by up to three bytes (in MySQL), it means that the maximum theoretical size for storage of 1024 such characters becomes 3072 bytes (3K).
Suddenly your generous and forward-thinking schema design becomes your enemy. If such a field only contains simple words like "cat" and "dog" you will need 3K of memory to be allocated in your in-memory temp table regardless.
As you can imagine, a few such fields existing in your implicit temp table, combined with a high number of rows can cause the space needed for this to spiral out of control very quickly!
Now, to add insult to injury, when MySQL decides that your enormous implicit temp table is too big for memory, based on tmp_table_size / max_heap_table_size, it maintains the very same fixed width row format as it copies the table to disk and continues appending rows to it there.
In practise, I have seen this cause 2.3G of data balloon out to 43G -- this is an increase by a factor of over 18!
So how to avoid it?
It really depends on the situation, but I would suggest that if you know a query is going to need such a temp table that you split the query into multiple steps and employ the use of a pivot table.
The pivot table would be an on-disk MyISAM table (TEMPORARY or not - your choice) that you use to explicitly perform the work done by MySQL when performing the implicit temp table step. The benefit here is that when you define this table, you can use variable-width fields and only consume the space needed.
Depending on your system and environment, you could be a little sneaky and even consider defining your MySQL tmpdir as tmpfs (memory) -- this way you get the benefit of the speed of memory as well as only allocating the space you need for each row, rather than the maximum theoretical size.
In the case that I found, it makes a lot more sense to just materialize the temp table efficiently on disk than to be exposed to the risk that a fixed-width table could run amok.
Hopefully this is useful to some of you out there!
Cheers,
Lachlan
Note: Edited per Don McArthur for correctness. utf8 in MySQL only supports the Basic Multilingual Plane subset of utf8, meaning that it may consume only up to 3 bytes per character, not 4 as in the full utf8 spec.
Xtrabackup Manager - Updates and MySQL Conference Observations..
After talking to a number of people at the MySQL Conf last week, it seems there is a pretty high level of interest in a tool like Xtrabackup Manager. This is great news!
I also got a chance to discuss with some folks about what their needs might be and how they would use such a tool. Hopefully I can make sure that those needs are met as I'm developing things.
The other day I finally committed the xbm-conftool contribution. You can now manage the configuration of your hosts in your favourite CSV editor and then import it into the DB.
I have also now started work on making sure that Xtrabackup Manager will run on Nexenta. If you're not aware, Nexenta is a Solaris kernel based system with a Debian userland -- basically OpenSolaris with apt-get.
The main reason for this is that I really like the idea of using a ZFS based system to run as my backup host. It means I can have the filesystem do compression behind the scenes, which saves on disk usage, but I don't have to worry about it in the user space -- This makes it easier to manage backups because I don't have to worry about compressing and uncompressing stuff all the time. This simplifies operations like applying incremental deltas into full backups.
So far the main aspects of the Xtrabackup Manager code seem to "just work" on Nexenta which is promising, but more testing is needed. I've had to make a small change in the way flushing to the crontab is done, since it seems the crontab command in Nexenta does not support installing a file in the crontab of another user.
I've been side-lined with some other work tasks this week, but I'm hoping to get back to Xtrabackup Manager soon.
Lachlan
I also got a chance to discuss with some folks about what their needs might be and how they would use such a tool. Hopefully I can make sure that those needs are met as I'm developing things.
The other day I finally committed the xbm-conftool contribution. You can now manage the configuration of your hosts in your favourite CSV editor and then import it into the DB.
I have also now started work on making sure that Xtrabackup Manager will run on Nexenta. If you're not aware, Nexenta is a Solaris kernel based system with a Debian userland -- basically OpenSolaris with apt-get.
The main reason for this is that I really like the idea of using a ZFS based system to run as my backup host. It means I can have the filesystem do compression behind the scenes, which saves on disk usage, but I don't have to worry about it in the user space -- This makes it easier to manage backups because I don't have to worry about compressing and uncompressing stuff all the time. This simplifies operations like applying incremental deltas into full backups.
So far the main aspects of the Xtrabackup Manager code seem to "just work" on Nexenta which is promising, but more testing is needed. I've had to make a small change in the way flushing to the crontab is done, since it seems the crontab command in Nexenta does not support installing a file in the crontab of another user.
I've been side-lined with some other work tasks this week, but I'm hoping to get back to Xtrabackup Manager soon.
Lachlan
Subscribe to:
Posts (Atom)