AW: Question Bacula/MySQL/Large backups

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

AW: Question Bacula/MySQL/Large backups

Roland Arendes
Hi Ronan,

Did you add some indices manually to your mySQL db? Looks like the first
(concurrent) jobs run fast until the first finishes and updates the DB.

If the DB structure is still unmodified since Installation, this could be
the problem - but I'm not sure.

These changes can (should) be made after installation of 1.36.3:

alter table File add index (JobId, PathId, FilenameId);
alter table File add index (PathId);
alter table Path drop Index Path;
alter table Path add Index (Path(255));

Please report back if this solved your problems.

Bye
Roland

 

-----Ursprüngliche Nachricht-----
Von: Ronan Eckelberry [mailto:[hidden email]]
Gesendet: Donnerstag, 12. Mai 2005 07:43
An: Bacula List
Betreff: [Bacula-users] Question Bacula/MySQL/Large backups

Hi all,

        I run rather large backups, about 70+ servers, 1 Full a month, rest
daily incrementals.  All disk -> disk backups.  At the time of full backups
I can be pulling anywhere from 1.5-3TB of total data from the servers.

        Backups start out great, pulling a few megs a piece from about 8
boxes at a time.  The problem is after about 12 hours of running, MySQL
shoots up to 99% CPU, which is not really a problem because it is SMP, and
box is still responsive.  I check the processes on mysql and it is
responding to queries quickly still.  Backup speed however drops to about
20k/sec from each box.  It continues to run, but after awhile the director
stops responding altogether and all the bacula process show as sleeping.  I
am forced to restart bacula and lose the backups that were unfinished.

        Not sure if this is because of MySQL, which seems to be operating
fine, or maybe bacula, RAM, or what.  Below is what the server is.  I can
supply configs or whatever may be needed.  If anyone has any suggestions or
a similar setup that had the same type of problem and was resolved, it would
be appreciated.

Server:
Debian Woody
Kernel 2.4.26
P4 2.8 HT
1G RAM
2G Swap
Intel S875WP MB

Thanks for any help,

-Ronan



-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes Want to be the
first software developer in space?
Enter now for the Oracle Space Sweepstakes!
http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users


-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
<a href="http://ads.osdn.com/?ad_ids93&alloc_id281&op=click">http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: AW: Question Bacula/MySQL/Large backups

Peter Eriksson
Another things you want to change in a default MySQL installation
is the max size of the tables - especially if you're like me and
backup a *lot* of files then the "File" table will run into
a 2/4GB limit quite quickly...

For details, see:

   http://jeremy.zawodny.com/blog/archives/000796.html

- Peter

On Thu, 12 May 2005, Roland Arendes wrote:

> Hi Ronan,
>
> Did you add some indices manually to your mySQL db? Looks like the first
> (concurrent) jobs run fast until the first finishes and updates the DB.
>
> If the DB structure is still unmodified since Installation, this could be
> the problem - but I'm not sure.
>
> These changes can (should) be made after installation of 1.36.3:
>
> alter table File add index (JobId, PathId, FilenameId);
> alter table File add index (PathId);
> alter table Path drop Index Path;
> alter table Path add Index (Path(255));
>
> Please report back if this solved your problems.
>
> Bye
> Roland
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: Ronan Eckelberry [mailto:[hidden email]]
> Gesendet: Donnerstag, 12. Mai 2005 07:43
> An: Bacula List
> Betreff: [Bacula-users] Question Bacula/MySQL/Large backups
>
> Hi all,
>
> I run rather large backups, about 70+ servers, 1 Full a month, rest
> daily incrementals.  All disk -> disk backups.  At the time of full backups
> I can be pulling anywhere from 1.5-3TB of total data from the servers.
>
> Backups start out great, pulling a few megs a piece from about 8
> boxes at a time.  The problem is after about 12 hours of running, MySQL
> shoots up to 99% CPU, which is not really a problem because it is SMP, and
> box is still responsive.  I check the processes on mysql and it is
> responding to queries quickly still.  Backup speed however drops to about
> 20k/sec from each box.  It continues to run, but after awhile the director
> stops responding altogether and all the bacula process show as sleeping.  I
> am forced to restart bacula and lose the backups that were unfinished.
>
> Not sure if this is because of MySQL, which seems to be operating
> fine, or maybe bacula, RAM, or what.  Below is what the server is.  I can
> supply configs or whatever may be needed.  If anyone has any suggestions or
> a similar setup that had the same type of problem and was resolved, it would
> be appreciated.
>
> Server:
> Debian Woody
> Kernel 2.4.26
> P4 2.8 HT
> 1G RAM
> 2G Swap
> Intel S875WP MB
>
> Thanks for any help,
>
> -Ronan
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes Want to be the
> first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> Bacula-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> <a href="http://ads.osdn.com/?ad_ids93&alloc_id281&opÌk">http://ads.osdn.com/?ad_ids93&alloc_id281&opÌk
> _______________________________________________
> Bacula-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>

--
Peter Eriksson <[hidden email]>            Phone:    +46 13  28 2786
Computer Systems Manager/BOFH                Cell/GSM: +46 705 18 2786
Physics Department, Linköping University     Room:     Building F, F203
SE-581 83 Linköping, Sweden                  http://www.ifm.liu.se/~peter


-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
<a href="http://ads.osdn.com/?ad_ids93&alloc_id281&op=click">http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: AW: Question Bacula/MySQL/Large backups

Ronan Eckelberry
In reply to this post by Roland Arendes
My DB had those indexes already.  Here is what I have ATM:

------Snip------

mysql> show indexes from File \G
*************************** 1. row ***************************
       Table: File
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: FileId
   Collation: A
 Cardinality: 16088626
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: File
  Non_unique: 1
    Key_name: JobId
Seq_in_index: 1
 Column_name: JobId
   Collation: A
 Cardinality: 116
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: File
  Non_unique: 1
    Key_name: PathId
Seq_in_index: 1
 Column_name: PathId
   Collation: A
 Cardinality: 402215
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: File
  Non_unique: 1
    Key_name: FilenameId
Seq_in_index: 1
 Column_name: FilenameId
   Collation: A
 Cardinality: 2011078
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 5. row ***************************
       Table: File
  Non_unique: 1
    Key_name: JobId_2
Seq_in_index: 1
 Column_name: JobId
   Collation: A
 Cardinality: 116
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 6. row ***************************
       Table: File
  Non_unique: 1
    Key_name: JobId_2
Seq_in_index: 2
 Column_name: PathId
   Collation: A
 Cardinality: 1237586
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 7. row ***************************
       Table: File
  Non_unique: 1
    Key_name: JobId_2
Seq_in_index: 3
 Column_name: FilenameId
   Collation: A
 Cardinality: 16088626
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
7 rows in set (0.00 sec)

mysql> show indexes from Path \G
*************************** 1. row ***************************
       Table: Path
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: PathId
   Collation: A
 Cardinality: 399985
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: Path
  Non_unique: 1
    Key_name: Path
Seq_in_index: 1
 Column_name: Path
   Collation: A
 Cardinality: 399985
    Sub_part: 255
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)

--Snip---


-Ronan


On Thu, 2005-05-12 at 10:35 +0200, Roland Arendes wrote:

> Hi Ronan,
>
> Did you add some indices manually to your mySQL db? Looks like the first
> (concurrent) jobs run fast until the first finishes and updates the DB.
>
> If the DB structure is still unmodified since Installation, this could be
> the problem - but I'm not sure.
>
> These changes can (should) be made after installation of 1.36.3:
>
> alter table File add index (JobId, PathId, FilenameId);
> alter table File add index (PathId);
> alter table Path drop Index Path;
> alter table Path add Index (Path(255));
>
> Please report back if this solved your problems.
>
> Bye
> Roland
>
>  
>
> -----Ursprüngliche Nachricht-----
> Von: Ronan Eckelberry [mailto:[hidden email]]
> Gesendet: Donnerstag, 12. Mai 2005 07:43
> An: Bacula List
> Betreff: [Bacula-users] Question Bacula/MySQL/Large backups
>
> Hi all,
>
> I run rather large backups, about 70+ servers, 1 Full a month, rest
> daily incrementals.  All disk -> disk backups.  At the time of full backups
> I can be pulling anywhere from 1.5-3TB of total data from the servers.
>
> Backups start out great, pulling a few megs a piece from about 8
> boxes at a time.  The problem is after about 12 hours of running, MySQL
> shoots up to 99% CPU, which is not really a problem because it is SMP, and
> box is still responsive.  I check the processes on mysql and it is
> responding to queries quickly still.  Backup speed however drops to about
> 20k/sec from each box.  It continues to run, but after awhile the director
> stops responding altogether and all the bacula process show as sleeping.  I
> am forced to restart bacula and lose the backups that were unfinished.
>
> Not sure if this is because of MySQL, which seems to be operating
> fine, or maybe bacula, RAM, or what.  Below is what the server is.  I can
> supply configs or whatever may be needed.  If anyone has any suggestions or
> a similar setup that had the same type of problem and was resolved, it would
> be appreciated.
>
> Server:
> Debian Woody
> Kernel 2.4.26
> P4 2.8 HT
> 1G RAM
> 2G Swap
> Intel S875WP MB
>
> Thanks for any help,
>
> -Ronan
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes Want to be the
> first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> Bacula-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> <a href="http://ads.osdn.com/?ad_ids93&alloc_id281&op=click">http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> Bacula-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>



-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users