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

The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Message from discussion innobackupex is stuck on copying .frm, .MRG, .MYD, .MYI etc.
MIME-Version: 1.0
Date: Fri, 22 Jul 2011 12:14:13 -0700 (PDT)
In-Reply-To: <CAKL74PMmh7Xsqa_h1R7vdOcgbQ+kW34m8=dL7T7RdvJy+8593w@mail.gmail.com>
References: <d644982c-c9a3-4e8e-acca-6e88b401bc22@s17g2000yqs.googlegroups.com>
 <7604bc7a-a252-45fe-864b-2456f090a90e@m18g2000vbl.googlegroups.com>
 <4082a2d0-314d-4cf8-8d4b-a73777081ec7@r18g2000vbs.googlegroups.com>
 <8a641a2b-c0cf-4a6b-9f67-9001501410f8@p14g2000yqj.googlegroups.com>
 <8c313a07-0a43-41ec-9ac8-2fdb5d7cb140@p14g2000yqj.googlegroups.com>
 <cdadbc3f-30ff-4c6e-b1d8-298af95298c3@m8g2000yqo.googlegroups.com>
 <e8cf3240-4d4f-4be9-9918-9bde1bac5f53@g16g2000yqg.googlegroups.com>
 <50676a8a-499e-46a4-a4ec-77b53c69b6ac@v12g2000vby.googlegroups.com>
 <71f156f1-ca50-4bce-8b86-f24f9b6d2d3f@h14g2000yqd.googlegroups.com> <CAKL74PMmh7Xsqa_h1R7vdOcgbQ+kW34m8=dL7T7RdvJy+8593w@mail.gmail.com>
User-Agent: G2/1.0
Message-ID: <b5921a8c-ab75-440b-8593-66295f1a51b5@r18g2000vbs.googlegroups.com>
Subject: Re: innobackupex is stuck on copying .frm, .MRG, .MYD, .MYI etc.
From: Bar Ziony <bar...@gmail.com>
To: Percona Discussion <percona-discussion@googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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=A0pm, 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=A0am, 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=A0am, 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=A0am, 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 believ=
e
> >> > > 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 shoul=
d be
> >> > > really fast (even if 10Mbps).
>
> >> > > Jervin
>
> >> > > On Jul 21, 4:26=A0pm, Bar Ziony <bar...@gmail.com> wrote:
>
> >> > > > Hi Jervin,
>
> >> > > > The .ibd file that keeps growing is results.ibd, it's a 26GB fil=
e. 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 s=
eems
> >> > > > like when innobackupex hits results.ibd (after 30 seconds of run=
ning,
> >> > > > 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 growi=
ng on
> >> > > > the remote end, and the network throughput is 10MBps...), so pro=
bably
> >> > > > 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 a=
re
> >> > > > copied, or just do the locking before the .ibd files are copied.=
..
>
> >> > > > Thanks!
> >> > > > Bar.
>
> >> > > > On Jul 21, 7:23=A0am, 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 noth=
ing is
> >> > > > > really being transferred.
>
> >> > > > > Which *.ibd file kept growing during the backup? Does this tab=
lespace
> >> > > > > consistenly growing on every attempt?
>
> >> > > > > On Jul 21, 11:19=A0am, 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=A0am, Jervin R <jervin.r...@percona.com> wro=
te:
>
> >> > > > > > > Bar,
>
> >> > > > > > > How big is the main tablespace? (ibdata*) Is the destinati=
on server
> >> > > > > > > not getting filled during the backup?
>
> >> > > > > > > How about using SSH instead of netcat i.e.
>
> >> > > > > > > innobackupex --slave-info --stream=3Dtar ./ | ssh user@bac=
kuphost -e
> >> > > > > > > "(cd /var/backups/mysql && tar xivf -)"
>
> >> > > > > > > On Jul 21, 4:04=A0am, Bar Ziony <bar...@gmail.com> wrote:
>
> >> > > > > > > > Hi,
>
> >> > > > > > > > I'm using MySQL server 5.1 with the newest xtrabackup de=
bian package
> >> > > > > > > > from Percona's repo.
>
> >> > > > > > > > I'm trying to backup to a remote server by using this co=
mmand:
> >> > > > > > > > innobackupex-1.5.1 --password=3Dmypass --stream=3Dtar /t=
mp/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 sc=
anned up to
> >> > > > > > > > (86835313840)" lines, and I can see the .ibd files creat=
ed and getting
> >> > > > > > > > larger on the destination. about 10MBps...
> >> > > > > > > > After a couple of minutes, innobackupex locks all the ta=
bles and tries
> >> > > > > > > > to copy FRM, MYD, MID files:
>
> >> > > > > > > > 110720 19:48:47 =A0innobackupex-1.5.1: Starting mysql wi=
th options: =A0--
> >> > > > > > > > password=3D'mypass' --unbuffered --
> >> > > > > > > > 110720 19:48:47 =A0innobackupex-1.5.1: Connected to data=
base with mysql
> >> > > > > > > > child process (pid=3D6165)>> log scanned up to (86835996=
968)
>
> >> > > > > > > > 110720 19:48:51 =A0innobackupex-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 =A0innobackupex-1.5.1: All tables locked=
 and flushed to
> >> > > > > > > > disk
> >> > > > > > > > 110720 19:49:04 =A0innobackupex-1.5.1: Starting to
> >> > > > > > > > backup .frm, .MRG, .MYD, .MYI,
> >> > > > > > > > innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV a=
nd .opt files
> >> > > > > > > > in
> >> > > > > > > > innobackupex-1.5.1: subdirectories of '/var/lib/mysql'
> >> > > > > > > > innobackupex-1.5.1: Backing up files '/var/lib/mysql/ope=
nx/*.
> >> > > > > > > > {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 l=
ike it's
> >> > > > > > > > stuck... but the .ibd files keep growing (a big one, 26G=
B 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 Grou=
ps "Percona Discussion" group.
> > To post to this group, send email to percona-discuss...@googlegroups.co=
m.
> > To unsubscribe from this group, send email to percona-discussion+unsubs=
cribe@googlegroups.com.
> > For more options, visit this group athttp://groups.google.com/group/per=
cona-discussion?hl=3Den.
>
> --
> Vadim Tkachenko, CTO, Percona Inc.
> Phone +1-888-401-3403,=A0 Skype: vadimtk153
> Schedule meeting:http://tungle.me/VadimTkachenko
>
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
Account Options
