PDA

View Full Version : Understanding how ZRM for MySQL works...



lmegliol
February 4th, 2009, 12:42 PM
I recently installed ZRM for MySQL and tried running a logical backup on a database that has both InnoDB and MyISAM tables. I assumed that the MyISAM tables would be locked for writing during the backup, but I wanted to confirm that. While the backup was running, I tried updating a record in the MyISAM table and was surprised when it worked. What am I not understanding about how ZRM for MySQL works?

I have LVM installed and I using that for raw backups, but I assumed that LVM doesn't come into play with logical backups. Does it?

Another question: If I remember correctly, my MySQL instance is setup to automatically purge binary logs. Are there settings that I need to make sure are consistent with ZRM so that ZRM doesn't try to perform an incremental backup only to find that the binary log is missing?

Finally, right now I am running ZRM on the same system that MySQL is running on. If I want to use a remote ZRM system to take raw backups using LVM snapshots, how does this work? Does ZRM take the snapshot, compress the files and store them on the MySQL server and then transfer them to the remote ZRM server? Or is the snapshot taken, the data transferred to the remote ZRM system straight from the snapshot, and then compressed on the ZRM system? Or is it something else? The answer to this has implications for system performance, network performance and required free extent in the LVM system.

Thanks in advance for any responses.

Leo

paddy
February 5th, 2009, 01:54 PM
I recently installed ZRM for MySQL and tried running a logical backup on a database that has both InnoDB and MyISAM tables. I assumed that the MyISAM tables would be locked for writing during the backup, but I wanted to confirm that. While the backup was running, I tried updating a record in the MyISAM table and was surprised when it worked. What am I not understanding about how ZRM for MySQL works?



MyISAM tables should have read locks during backup process. The locks are released after extraction of data is completed. It will not be locked during compression or post-backup processing. Please check /var/log/mysql-zrm/mysql-zrm.log to see the backup phases.



I have LVM installed and I using that for raw backups, but I assumed that LVM doesn't come into play with logical backups. Does it?

LVM is not relevant for logical backups



Another question: If I remember correctly, my MySQL instance is setup to automatically purge binary logs. Are there settings that I need to make sure are consistent with ZRM so that ZRM doesn't try to perform an incremental backup only to find that the binary log is missing?


Please disable automatic purge of binary logs. Instead purge it in post-backup plugin (which will work in your configuration). See http://www.zmanda.com/blogs/?p=28



Finally, right now I am running ZRM on the same system that MySQL is running on. If I want to use a remote ZRM system to take raw backups using LVM snapshots, how does this work? Does ZRM take the snapshot, compress the files and store them on the MySQL server and then transfer them to the remote ZRM server? Or is the snapshot taken, the data transferred to the remote ZRM system straight from the snapshot, and then compressed on the ZRM system? Or is it something else? The answer to this has implications for system performance, network performance and required free extent in the LVM system.


You have to configure socket or raw copy plugin. The backup compression configured in the mysql-zrm.conf happens on the ZRM server. Please note that the data is transferred over the network in compressed manner.

Paddy

lmegliol
February 6th, 2009, 08:03 AM
Thanks for the responses.


MyISAM tables should have read locks during backup process. The locks are released after extraction of data is completed. It will not be locked during compression or post-backup processing. Please check /var/log/mysql-zrm/mysql-zrm.log to see the backup phases.


Hmm. I can't explain my experiment then. I'll have to give it another try.

bmonkey
February 9th, 2009, 04:14 PM
Please note that the data is transferred over the network in compressed manner.


Is it possible to disable this?

I see CPU usage spike during this time with gzip using it all up. Is there some specific reason why it's compressing the files for transfer? It might be useful if you're paying for bandwidth between servers but if you're not it's just taking up CPU cycles away from the DB server.


Thanks

zmanda_jacob
February 9th, 2009, 04:27 PM
There are a few files depending on whether you are using SSH or Socket copy for your plugin type. The files you will want to modify are in /usr/share/mysql-zrm/plugins

If you are using the Socket Copy plugin then you will need to modify socket-server.pl and socket-copy.pl.

If you are using the SSH Copy plugin then you will need to modify the ssh-copy.pl.

I believe these changes only need to be done on the MySQL server end. The easiest way to identify the part of the file you need to change is to search for "--same-owner". It will show you the tar command that is being used and you can just remove the "z" from the command and it will turn off compression. Please make sure you change it in ALL occurances of the command or things will not work. For example:

socket-copy.pl: my $tarCmd = "$TAR --same-owner -phszC ";
socket-copy.pl: unless( open( TAR_H, "|$TAR --same-owner -xphszC $destDir 2>/dev/null" ) ){
socket-copy.pl: unless(open( TAR_H, "$TAR --same-owner -cphszC $_[0] $_[1] 2>/dev/null|" ) ){

Will need to be changed to:

socket-copy.pl: my $tarCmd = "$TAR --same-owner -phsC ";
socket-copy.pl: unless( open( TAR_H, "|$TAR --same-owner -xphsC $destDir 2>/dev/null" ) ){
socket-copy.pl: unless(open( TAR_H, "$TAR --same-owner -cphsC $_[0] $_[1] 2>/dev/null|" ) ){

bmonkey
February 21st, 2009, 07:44 PM
The changes you suggested did not quite work, although they were close, so I did my own digging around and found what works.

To disable compression for socket-copy plugin you need to edit these:


On client: socket-server.pl edit writeTarStream()
On server: socket-copy.pl edit readTarStream()


Doing this cuts down CPU usage on the client system quite a bit and cuts down the overall backup time in half.

Perhaps a future release could include an option to disable this in the configuration without having to edit the scripts manually. It would definitely be a useful feature to have build in.

paddy
February 22nd, 2009, 05:34 PM
The changes you suggested did not quite work, although they were close, so I did my own digging around and found what works.

To disable compression for socket-copy plugin you need to edit these:


On client: socket-server.pl edit writeTarStream()
On server: socket-copy.pl edit readTarStream()


Doing this cuts down CPU usage on the client system quite a bit and cuts down the overall backup time in half.

Perhaps a future release could include an option to disable this in the configuration without having to edit the scripts manually. It would definitely be a useful feature to have build in.



Thanks for the feedback. We will add this as default or as an option in the next release.

Paddy