innobackupex is stuck on copying .frm, .MRG, .MYD, .MYI etc.

[Bar Ziony]

Hi,

I'm using MySQL server 5.1 with the newest xtrabackup debian package
from Percona's repo.

I'm trying to backup to a remote server by using this command:
innobackupex-1.5.1 --password=mypass --stream=tar /tmp/mysql_backup/ --
slave-info | nc mysql-02 9999

And on the remote destination:
nc -l -p 9999 | tar xfi - -C /var/backups/mysql

I have a InnoDB big db (40GB), and a 200MB MyISAM db.

At the first couple of minutes I get a lot of ">> log scanned up to
(86835313840)" lines, and I can see the .ibd files created and getting
larger on the destination. about 10MBps...
After a couple of minutes, innobackupex locks all the tables and tries
to copy FRM, MYD, MID files:

110720 19:48:47  innobackupex-1.5.1: Starting mysql with options:  --
password='mypass' --unbuffered --
110720 19:48:47  innobackupex-1.5.1: Connected to database with mysql
child process (pid=6165)
>> log scanned up to (86835996968)
110720 19:48:51  innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (86836058050)
>> log scanned up to (86836099692)
>> log scanned up to (86836199676)
110720 19:49:04  innobackupex-1.5.1: All tables locked and flushed to
disk
110720 19:49:04  innobackupex-1.5.1: Starting to
backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files
in
innobackupex-1.5.1: subdirectories of '/var/lib/mysql'
innobackupex-1.5.1: Backing up files '/var/lib/mysql/openx/*.
{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (172 files)


but nothing happens! no FRM file is being copied, it's like it's
stuck... but the .ibd files keep growing (a big one, 26GB is getting
filled), and more and more "log scanned up to..." lines are shown.
In all that time, the entire server is with a read lock, and my app is
completely down with this long read lock.

I can't backup to a local location because I don't have enough space
on the local host.

Appreciate the help,
Thanks,
Bar.

[Ryan Lowe]

(Posted on behalf of Bar, who is having issues posting)

[Bar Ziony]

[Jervin R]

Bar,

How big is the main tablespace? (ibdata*) Is the destination server
not getting filled during the backup?

How about using SSH instead of netcat i.e.

innobackupex --slave-info --stream=tar ./ | ssh user@backuphost -e
"(cd /var/backups/mysql && tar xivf -)"

[Bar Ziony]

Hi Jervin.
The main tablespace is 137MB (ibdata1), I don't know what's in it.

Why using SSH over netcat would help?

Thanks,
Bar.

[Jervin R]

Hello Bar,

I simply wanted to eliminate the transfer method as the source of the
problem.

Try using the v option on tar on the remote end to see if nothing is
really being transferred.

Which *.ibd file kept growing during the backup? Does this tablespace
consistenly growing on every attempt?

[Bar Ziony]

Hi Jervin,

The .ibd file that keeps growing is results.ibd, it's a 26GB file. it
gets to 4GB until I stop (cause I don't want the long locking to
continue...)

I just tried with the -v on the tar on the remote side, and it seems
like when innobackupex hits results.ibd (after 30 seconds of running,
copying some smaller .ibd files and backup-my.cnf first), it is stuck
on it until it ends (it does copy the file, cause I see it growing on
the remote end, and the network throughput is 10MBps...), so probably
the .frm and other files are "queued" behind that huge file.

Can that be ? Is that the design of xtrabackup & innobackupex?
I think a better way might be waiting until all the .ibd files are
copied, or just do the locking before the .ibd files are copied...

Thanks!
Bar.

[Jervin R]

Bar,

I checked the innobackupex source and it seems that the xtrabackup is
called on a separate thread while backup of the other files
(frm,myi,myd) is also called. Someone may correct me, but I believe
since the netcat transfer is single threaded, it will finish what came
first, that is the xtrabackup process to finish before it could
transfer the other files in the pipe. So, you will have to wait fo the
26G file to finish before the other files are copied. 10MBps should be
really fast (even if 10Mbps).

Jervin

[Bar Ziony]

Jervin,

Thanks for your help.

SSH will also have this problem ?
Can I somehow delay the 26GB file until the locking is done, frm/myi/
myd is copied and tables are unlocked?

Or any other solution other than "wait until that huge file will
finish" ?

Thanks a lot for your insight, I "feel" that this is the right
answer :)

Bar.

[Jervin R]

Bar,

Yes, unfortunately I expect this to be the same with SSH.

You can use the innobackupex --no-lock option, however this will not
guarantee consistency of the backup especially since you are using --
slave-info, I presume you intend to save this backup for another slave
or fo future use of another slave.

Another thing I can think of is to hack innobackupex and disable
forking of the xtrabackup process so that the global LOCK is not
acquired until after the ibd files are copied. I think you can also
open a feature request for this.

Hope this helps.

[Bar Ziony]

Hi Jervin,

Is this how xtrabackup & innobackupex really work ? Anyone with
big .ibd files just wait until they are finished? what if you have a
200GB DB ?

Thanks,
Bar.

[Vadim Tkachenko]

Bar,

There should be some misunderstanding.
Innobackupex does not lock for copying .ibd files.

Lock is applied only to copy MyISAM and frm files.

> --
> You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
>
>

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-888-401-3403,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Join us at Percona Live London!
http://www.percona.com/live/london-2011/

[Bar Ziony]

Vadim,

Thank you for taking the time to answer.

I know that Innobackupex doesn't lock for copying .ibd files.

The issue is that when I start innobackupex, I can see that it starts
to copy .ibd files, and no locking is done. It gets to a very big .ibd
file (26GB), and continues to copy it.
Then, it says it is going to lock all tables in order to copy frm/myd/
myi and some more, and it says it started copying those files in /var/
lib/mysql/my_db/*.{frm,myd,myi....}.

But when I look at the destination of the backup - those files aren't
created! Only the big .ibd file keeps growing. and in the mean while
all the tables are locked and my application is down. So I have to
stop the backup. I once waited for 20 minutes, but the lock was still
not lifted.

Jervin - I now tried to use a local destination with gzip, and it
seems like this is still happening. the lock isn't being released!

All my DBs are InnoDB, besides a 200MB MyISAM DB.

I'm stuck on this issue for 3 days now... I don't really know how to
approach it further.

Thanks,
Bar.

> > For more options, visit this group athttp://groups.google.com/group/percona-discussion?hl=en.

[Vadim Tkachenko]

Bar,

I am not sure why is that, it requires debugging.

I can recommend you to try --no-lock --safe-slave-backup
options, in this case, you will have still reliable --slave-info.

But you need to make sure there is no changes into MyISAM during backup.

> For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

[Bar Ziony]

Vadim,

Thank you for the insight on --no-lock and --safe-slave-backup. some questions:

1) How .frm files are copied than ? And how does --safe-slave-backup works without locking? How do you get the binlog number and position without entering a to race condition (because there's no locking) ?

2) The 'mysql' db is MyISAM , right? How can I avoid changes to it ?

3) Can I one time copy All InnoDB databases with --no-lock and --safe-slave-backup and then after that copy all the MyISAM databases (really small, fast backup) with regular --slave-info (and locking), and somehow still have a backup that can be the initial slave data?

Thanks,

Bar.

[Bar Ziony]

Vadim,

Did you get my last message (with the questions :)) ?

Thanks!
Bar.

> Vadim...
>
> read more »

[Vadim Tkachenko]

Bar,

On Fri, Jul 22, 2011 at 1:05 PM, Bar Ziony <bar...@gmail.com> wrote:
> Vadim,

> Thank you for the insight on --no-lock and --safe-slave-backup. some
> questions:
> 1) How .frm files are copied than ? And how does --safe-slave-backup works
> without locking? How do you get the binlog number and position without
> entering a to race condition (because there's no locking) ?

This assumes do you not make changes to .frm during copy.
That is you do not run DDL statements.

> 2) The 'mysql' db is MyISAM , right? How can I avoid changes to it ?

I assume you have control of your environment. Changes to mysql
are usually some priveleges or system changes, which does
not happen without DBA.
However if you have no control - then you probably should not use --no-lock

> 3) Can I one time copy All InnoDB databases with --no-lock and
> --safe-slave-backup and then after that copy all the MyISAM databases
> (really small, fast backup) with regular --slave-info (and locking), and
> somehow still have a backup that can be the initial slave data?

I do not think so. Most likely you will end up with database in
non-synchronous condition.

[Bar Ziony]

Hi Vadim,

I tried with --safe-slave-backup --no-lock --slave_info (all my tables
are now innodb, besides mysql DB, but I'm not issuing any DDL
statements).
I get this error: nnobackupex: Error: mysql child process has died:
ERROR 1200 (HY000) at line 5: The server is not configured as slave;
fix in config file or with CHANGE MASTER TO

Please note that my server is not a slave. I'm trying to create a
backup from my single server , in order to create a 2nd server as a
slave.

Anyway - I don't think that the locking is the issue!

The problem is much weirder:
I tried backing up a tar stream locally (I finally managed to free
some space), and innobackupex says the backup finished successfully!
However, the backup file is very small (after decompressing, the tar
file is 2.6GB, while my DB size is ~40GB), and "tar xvfi backup.tar"
results in an unexpected EOF error.

Why InnoDB starts backing up FRM/MRG/MYD/MID before the .ibd files
finished copying ? And why it says the backup was successful when it
only backed up ~%6 of the DB?

Any help would be greatly appreciated.

Thanks,
Bar.

> ...
>
> read more »

[Jervin R]

Bar,

If this is not a slave, then you can skip --safe-slave-backup and --
slave-info.

Did you untar from the source server or the target server? Perhaps
their checksum are different and inconsistencies during transfer are
introduced, try comparing the tarball's md5sums.

Additionally, can you confirm if tar4ibd is installed on your system
(`which tar4ibd` or `locate tar4ibd`).

Lastly, for the sake of eliminating all possibilities, have you tried
backing up locally without streaming?

> ...
>
> read more »

[Bar Ziony]

Hi Jervin,

It's not a slave, but I need the data from --slave-info (the binlog
number and position) in order to make this backup the initial data
transfer for a new slave (I currently have only one server, want to
make it the master with a slave). How can I else get the consistent
binlog number and position of that backup, without --slave-info ?

I did try backing up locally the last time, no streaming (only to
gzip, locally). The problem is what I described with the unexpected
EOF tar file...
For some reason innobackupex thinks the backup is complete, when it's
really only in the start (this happens after innobackupex outputs that
it's going to backup FRM/MYD/MID) files...

Yes, tar4ibd is installed (/usr/bin/tar4ibd).

Any suggestions?

Thanks,
Bar.

> > > >> >> >> > > > > > what's in it....
>
> read more »

[Jervin R]

Bar,

This may be simpler or harder than it looks without working on your
server first hand.

On the other hand, I was suggesting a local backup without streaming
of compressing the backup.

i.e.

innobackupex --user=user --password=pass /path/to/backup/dir

During the backup the binlog coordinates will be printed along with
the output like below:

innobackupex: Backup created in directory '/home/viiin/sandboxes/
msb_5_1_57p/backups/full/2011-08-11_01-00-43'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001',
position 106
110811 01:15:53 innobackupex: completed OK!

The file xtrabackup_binlog_info contains the same information as well.

> ...
>
> read more »

[Bar Ziony]

Hi everyone,

I found and solved the issue - it was a problem with '--stream=tar'.
Apparently, when you use streaming, the lock never ends and the
backup is stuck.
When I used regular backup (innobackupex /var/backups/mysql/),
everything worked great, however I had a problem with space on the
local host. I installed a NFS server on the other host, mounted an
export and backed up to it (innobackupex /mnt/backups/), it worked
great. A bit of a stupid hack, but at least it works! :)

Do you want me to open a bug about --stream=tar not working on my
environment or something?

Thanks for your help! :)
Bar.

> > > > > >> >> >> > > Jervin...
>
> read more »