> On Fri, Jul 22, 2011 at 10:18 PM, Vadim Tkachenko <va...@percona.com> wrote:

>> 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.

>> On Fri, Jul 22, 2011 at 12:14 PM, Bar Ziony <bar...@gmail.com> wrote:
>> > 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.

>> > On Jul 22, 9:56 pm, Vadim Tkachenko <va...@percona.com> wrote:
>> >> Bar,

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

>> >> Lock is applied only to copy MyISAM and frm files.

>> >> On Fri, Jul 22, 2011 at 11:02 AM, Bar Ziony <bar...@gmail.com> wrote:
>> >> > 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.

>> >> > On Jul 22, 7:35 am, Jervin R <jervin.r...@percona.com> wrote:
>> >> >> 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.

>> >> >> On Jul 22, 10:30 am, Bar Ziony <bar...@gmail.com> wrote:

>> >> >> > 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.

>> >> >> > On Jul 22, 5:26 am, Jervin R <jervin.r...@percona.com> wrote:

>> >> >> > > 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

>> >> >> > > On Jul 21, 4:26 pm, Bar Ziony <bar...@gmail.com> wrote:

>> >> >> > > > 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.

>> >> >> > > > On Jul 21, 7:23 am, Jervin R <jervin.r...@percona.com> wrote:

>> >> >> > > > > 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?

>> >> >> > > > > On Jul 21, 11:19 am, Bar Ziony <bar...@gmail.com> wrote:

>> >> >> > > > > > 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.

>> >> >> > > > > > On Jul 21, 5:11 am, Jervin R <jervin.r...@percona.com>
>> >> >> > > > > > wrote:

>> >> >> > > > > > > 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 -)"

>> >> >> > > > > > > On Jul 21, 4:04 am, Bar Ziony <bar...@gmail.com> wrote:

>> >> >> > > > > > > > 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.

>> >> > --
>> >> > You received this message because you are subscribed to the Google
>> >> > Groups "Percona Discussion" group.
>> >> > To post to this group, send email to
>> >> > percona-discussion@googlegroups.com.
>> >> > To unsubscribe from this group, send email to
>> >> > percona-discussion+unsubscribe@googlegroups.com.
>> >> > For more options, visit this group
>> >> > athttp://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/

>> > --
>> > You received this message because you are subscribed to the Google
>> > Groups "Percona Discussion" group.
>> > To post to this group, send email to
>> > percona-discussion@googlegroups.com.
>> > To unsubscribe from this group, send email to
>> > percona-discussion+unsubscribe@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/

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

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