On 3/16/13 11:43 AM, "Phil Stracchino" <alaric AT metrocast DOT net> wrote:
>On 03/16/13 06:18, Jérôme Blion wrote:
>> Le 16/03/2013 10:54, Uwe Schuerkamp a écrit :
>>> My question: Is there some way to optimize the catalog dump to make
>>> the import faster, like maybe omitting indices and re-creating them
>>> manually once the import has completed? Seeing the Path table also has
>>> 19GB, its import probably won't have finished before our Sun goes
>>> Nova. ;)
>>>
>> Hello,
>>
>> You have several ways to speed it up.
>> First:
>> - use --disable-keys when dumping
>> - use other tools to do the backup / restore : You can try:
>> * mydumper : Each table will be a different dump. you will
>>recreate
>> the database using multiple threads in parallel.
>> * mylvmbackup : you will restore a snapshot of the filesystem, the
>> speed will be the highest you can have. (the size of the backup will be
>> much bigger)
>
>Snapshot-based MySQL backup schemes work with varying results depending
>on the underlying OS and filesystem. Using ZFS snapshots, for example,
>in Solaris 10/11 or presumably FreeBSD, a snapshot backup scheme works
>very well. We have experimented at my company with snapshot backup
>schemes using Linux LVM, and frankly, they really don't work well at all
>by comparison. LVM snapshots are too slow and require too much reserved
>disk space to make the technique viable on a large DB.
>
>
>--
> Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
> alaric AT caerllewys DOT net alaric AT metrocast DOT net phil AT
> co.ordinate DOT org
> Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
> It's not the years, it's the mileage.
>
>--------------------------------------------------------------------------
>----
I'm sorry that you've not had good results with LVM snapshots. It has
been my experience that they work very well. We'd used the mylvmbackup
very successfully for a mixed engine mysql database that had reached
greater than 70G. Mysqldump would back it up, but after 3 days, it
couldn't complete a recovery. As a production tool, mysqldump was not
usable. Alternatively, the LVM snapshot reduces the down time of the
production database to less than a minute while the db is quiesced for the
snapshot and returned to operation. The snapshots get written to tape and
all is well. Recovery of the database was without issue, performed in
less than 2 hours from tape to full function.
We also took the script and adopted it for a Postgresql database that was
nearly 1TB, again without issue.
Yes, the amount of space needed to perform snapshots is equivalent to the
size of the databases themselves, but the cost is somewhat small in
comparison when we have disk drives that are 1-2 TB or more. Until we
used the snapshots, our production databases would be offline while a much
slower and less reliable backup method was used.
Patti Clark
Linux System Administrator
Research and Development Systems Support Oak Ridge National Laboratory
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
