Backup Central

Hi folks,

we're running four bacula installations, most of them on version 5.0.x
compiled from source on CentOS 5.x / 6.x 64bit servers. We're mostly
happy with the setup, backups are fast, reliable and generally do not
cause us a lot of headaches.

Today, a colleague asked me to restore some data from the last backup
of a client on our largest bacula install, namely (according to bweb)

DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB

MySQL isn't exactly huge, and restoring the data didn't look like too
much of a big deal at first:

+-------+-------+----------+----------------+---------------------+--------------+
| 9,582 | F | 527,265 | 55,999,595,573 | 2012-01-20 21:05:03 |
OFFLINE14_02 |
| 9,652 | I | 1,150 | 1,534,499,185 | 2012-01-21 18:34:56 |
OFFLINE15_01 |
+-------+-------+----------+----------------+---------------------+--------------+

So we're talking a mere 500,000 files (he only needed a single dir out
of the bunch).

4,5 hours later, Bacula is still sitting at the "Building Directory
Tree" message, without so much as a single "." or "+" hopefully
showing up in the terminal, indicating some kind of progress.

I've run mysqltuner on the db a couple of times as this isn't the
first time we've had problems during a restore, and it looks ok (to my
untrained, non-dba eyes anyway):

######################################################################
-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.52-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
-------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 31G (Tables: 33)
[!!] Total fragmented tables: 2

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 35s (57 q [1.629 qps], 12 conn, TX: 44K, RX: 3K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 12.0G global + 83.2M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.3G (137% of installed RAM)
[OK] Slow queries: 0% (0/57)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 11.9G/15.3G
[OK] Key buffer hit rate: 100.0% (6K cached / 2 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 23 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9 sorts)
[!!] Temporary tables created on disk: 34% (8 on disk / 23 total)
[OK] Thread cache hit rate: 91% (1 created / 12 connections)
[OK] Table cache hit rate: 85% (41 open / 48 opened)
[OK] Open file limit used: 1% (83/4K)
[OK] Table locks acquired immediately: 100% (38 immediate / 38 locks)
[!!] Connections aborted: 8%

-------- Recommendations
-----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be
inaccurate
Reduce your overall MySQL memory footprint for system stability
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 16M, or use smaller result sets)
tmp_table_size (> 61M)
max_heap_table_size (> 16M)

######################################################################

For the restore run mentioned above, I'm seeing a 40MB mysql tmp table
in /tmp updated every once in a while, and there's lots of write
activity to the partition that holds /tmp.

I'm now running a "repair table File" after cancelling the restore
job, but I guess there's something seriously wrong with the above
setup. The other bacula servers generally run on smaller machines, but
come up with a dir tree after five to ten minutes for a comparable job
which is acceptable, but 5 hours seems way off the mark.

the bacula db was created using bacula's own mysql init script, so I
assume all the indices where created (and more importantly, no extra
ones that might slow bacula down) correctly. Insert performance it
great during backups, we usually achieve around 30-50MB/sec sustained
for 3 to 4 jobs running in parallel.

Mem usage (as opposed to mysql tuner's warning) is ok during the
restore run, no swapping, 5GB of 18G total still used for buffer
cache.


Thanks in advance for any help / hints or thoughts,

Uwe

PS: Please let me know if I should provide more info on the setup what
would help in analyzing this problem.

--
NIONEX --- Ein Unternehmen der Bertelsmann AG



------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On 01/23/2012 10:28 AM, Uwe Schuerkamp wrote:
I've run mysqltuner on the db a couple of times as this isn't the
first time we've had problems during a restore, and it looks ok (to my
untrained, non-dba eyes anyway):

######################################################################
-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.52-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
-------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 31G (Tables: 33)
[!!] Total fragmented tables: 2

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 35s (57 q [1.629 qps], 12 conn, TX: 44K, RX: 3K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 12.0G global + 83.2M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.3G (137% of installed RAM)
[OK] Slow queries: 0% (0/57)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 11.9G/15.3G
[OK] Key buffer hit rate: 100.0% (6K cached / 2 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 23 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9 sorts)
[!!] Temporary tables created on disk: 34% (8 on disk / 23 total)
[OK] Thread cache hit rate: 91% (1 created / 12 connections)
[OK] Table cache hit rate: 85% (41 open / 48 opened)
[OK] Open file limit used: 1% (83/4K)
[OK] Table locks acquired immediately: 100% (38 immediate / 38 locks)
[!!] Connections aborted: 8%

-------- Recommendations
-----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be
inaccurate
Reduce your overall MySQL memory footprint for system stability
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 16M, or use smaller result sets)
tmp_table_size (> 61M)
max_heap_table_size (> 16M)

######################################################################

For the restore run mentioned above, I'm seeing a 40MB mysql tmp table
in /tmp updated every once in a while, and there's lots of write
activity to the partition that holds /tmp.


If max_heap_table_size is 16M, then in-memory temporary tables are
limited to 16M too. Maximum in-memory temporary table size is the
smaller of tmp_table-size and max_heap_table_size. You only ever have a
single DB connection; why are you allowing 151 connections?

Cut max_connections to 10, increase tmp_table_size and
max_heap_table_size to 64M or even 128M, increase table_cache to 64,
disable the query cache because you're going to have few if any
frequently-repeated queries, update to MySQL 5.5, and seriously,
seriously consider converting to InnoDB. It is a MUCH higher
performance storage engine than MyISAM. Remember that MyISAM was
designed to yield *acceptable* performance in shared installations on
machines with less than 32MB of RAM.


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

1: Make sure you have enough ram in your mysql box (ie, several 10s of Gb)

2: Make sure you tune mysql properly. Most of the supplied config
examples are for sub-1Gb memory configuration.

3: Make sure you have the _correct_ indexes built. this is in the bacula
knowledgebase.

4: For systems with 10s of millions of files - Seriously consider moving
to postgres. MySQL is a memory hog.



On 23/01/12 15:28, Uwe Schuerkamp wrote:
Hi folks,

we're running four bacula installations, most of them on version 5.0.x
compiled from source on CentOS 5.x / 6.x 64bit servers. We're mostly
happy with the setup, backups are fast, reliable and generally do not
cause us a lot of headaches.

Today, a colleague asked me to restore some data from the last backup
of a client on our largest bacula install, namely (according to bweb)

DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB

MySQL isn't exactly huge, and restoring the data didn't look like too
much of a big deal at first:

+-------+-------+----------+----------------+---------------------+--------------+
| 9,582 | F | 527,265 | 55,999,595,573 | 2012-01-20 21:05:03 |
OFFLINE14_02 |
| 9,652 | I | 1,150 | 1,534,499,185 | 2012-01-21 18:34:56 |
OFFLINE15_01 |
+-------+-------+----------+----------------+---------------------+--------------+

So we're talking a mere 500,000 files (he only needed a single dir out
of the bunch).

4,5 hours later, Bacula is still sitting at the "Building Directory
Tree" message, without so much as a single "." or "+" hopefully
showing up in the terminal, indicating some kind of progress.

I've run mysqltuner on the db a couple of times as this isn't the
first time we've had problems during a restore, and it looks ok (to my
untrained, non-dba eyes anyway):

######################################################################
-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.52-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
-------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 31G (Tables: 33)
[!!] Total fragmented tables: 2

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 35s (57 q [1.629 qps], 12 conn, TX: 44K, RX: 3K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 12.0G global + 83.2M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.3G (137% of installed RAM)
[OK] Slow queries: 0% (0/57)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 11.9G/15.3G
[OK] Key buffer hit rate: 100.0% (6K cached / 2 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 23 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9 sorts)
[!!] Temporary tables created on disk: 34% (8 on disk / 23 total)
[OK] Thread cache hit rate: 91% (1 created / 12 connections)
[OK] Table cache hit rate: 85% (41 open / 48 opened)
[OK] Open file limit used: 1% (83/4K)
[OK] Table locks acquired immediately: 100% (38 immediate / 38 locks)
[!!] Connections aborted: 8%

-------- Recommendations
-----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be
inaccurate
Reduce your overall MySQL memory footprint for system stability
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 16M, or use smaller result sets)
tmp_table_size (> 61M)
max_heap_table_size (> 16M)

######################################################################

For the restore run mentioned above, I'm seeing a 40MB mysql tmp table
in /tmp updated every once in a while, and there's lots of write
activity to the partition that holds /tmp.

I'm now running a "repair table File" after cancelling the restore
job, but I guess there's something seriously wrong with the above
setup. The other bacula servers generally run on smaller machines, but
come up with a dir tree after five to ten minutes for a comparable job
which is acceptable, but 5 hours seems way off the mark.

the bacula db was created using bacula's own mysql init script, so I
assume all the indices where created (and more importantly, no extra
ones that might slow bacula down) correctly. Insert performance it
great during backups, we usually achieve around 30-50MB/sec sustained
for 3 to 4 jobs running in parallel.

Mem usage (as opposed to mysql tuner's warning) is ok during the
restore run, no swapping, 5GB of 18G total still used for buffer
cache.


Thanks in advance for any help / hints or thoughts,

Uwe

PS: Please let me know if I should provide more info on the setup what
would help in analyzing this problem.





------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On Mon, Jan 23, 2012 at 11:13:30AM -0500, Phil Stracchino wrote:

If max_heap_table_size is 16M, then in-memory temporary tables are
limited to 16M too. Maximum in-memory temporary table size is the
smaller of tmp_table-size and max_heap_table_size. You only ever have a
single DB connection; why are you allowing 151 connections?

Cut max_connections to 10, increase tmp_table_size and
max_heap_table_size to 64M or even 128M, increase table_cache to 64,
disable the query cache because you're going to have few if any
frequently-repeated queries, update to MySQL 5.5, and seriously,
seriously consider converting to InnoDB. It is a MUCH higher
performance storage engine than MyISAM. Remember that MyISAM was
designed to yield *acceptable* performance in shared installations on
machines with less than 32MB of RAM.


Hi Phil,

thanks much for your tuning hints, I'll give them a go and will report
back on how things work out.

Cheers, Uwe

--
uwe.schuerkamp < at > nionex.net fon: [+49] 5242.91 - 4740, fax:-69 72
Hauptsitz: Avenwedder Str. 55, D-33311 Gütersloh, Germany
Registergericht Gütersloh HRB 4196, Geschäftsführer: H. Gosewehr, D. Suda
NIONEX --- Ein Unternehmen der Bertelsmann AG



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

El 23/01/12 16:28, Uwe Schuerkamp escribió:
DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB
Hi Uwe,

I am having the same problem, backups are fast, but restores takes too
long creating directory tree with bat. I have a lot of files to backup
per client. I am using mysql with innodb engine, my File table is about
17GB on disk.

My numbers:

BytesPerJobAvg: 6539156346
ClientCount: 31
FileCount: 113286836
FileRetentionAvg:
FilenameCount: 29713190
FilesPerJobAvg: 184213
JobRetentionAvg:
PathCount: 6671143
TotalBytes: 1588763249151
TotalFiles: 44919364

First, I considered to create more bacula servers to separate clients on
diferent databases, but now I am testing a configuration with one
catalog per client. With this config, each client goes in separate db,
It's more difficult to administer and setup it but I guess is the best
way to scale the platform. Has anyone tried this config?

Sorry for my bad english.

Xabier


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Il 24/01/2012 10:05, Xabier Elkano ha scritto:
El 23/01/12 16:28, Uwe Schuerkamp escribió:
DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB
Hi Uwe,

I am having the same problem, backups are fast, but restores takes too
long creating directory tree with bat. I have a lot of files to backup
per client. I am using mysql with innodb engine, my File table is about
17GB on disk.

My numbers:

BytesPerJobAvg: 6539156346
ClientCount: 31
FileCount: 113286836
FileRetentionAvg:
FilenameCount: 29713190
FilesPerJobAvg: 184213
JobRetentionAvg:
PathCount: 6671143
TotalBytes: 1588763249151
TotalFiles: 44919364

First, I considered to create more bacula servers to separate clients on
diferent databases, but now I am testing a configuration with one
catalog per client. With this config, each client goes in separate db,
It's more difficult to administer and setup it but I guess is the best
way to scale the platform. Has anyone tried this config?

Sorry for my bad english.

Xabier


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

You mean 31 catalogs ?!

--
Marcello Romani

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

El 24/01/12 10:49, Marcello Romani escribió:
Il 24/01/2012 10:05, Xabier Elkano ha scritto:
El 23/01/12 16:28, Uwe Schuerkamp escribió:
DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB
Hi Uwe,

I am having the same problem, backups are fast, but restores takes too
long creating directory tree with bat. I have a lot of files to backup
per client. I am using mysql with innodb engine, my File table is about
17GB on disk.

My numbers:

BytesPerJobAvg: 6539156346
ClientCount: 31
FileCount: 113286836
FileRetentionAvg:
FilenameCount: 29713190
FilesPerJobAvg: 184213
JobRetentionAvg:
PathCount: 6671143
TotalBytes: 1588763249151
TotalFiles: 44919364

First, I considered to create more bacula servers to separate clients on
diferent databases, but now I am testing a configuration with one
catalog per client. With this config, each client goes in separate db,
It's more difficult to administer and setup it but I guess is the best
way to scale the platform. Has anyone tried this config?

Sorry for my bad english.

Xabier


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
You mean 31 catalogs ?!


Yes, now, I am only testing with two catalogs and it's working Ok, what
are the downsides using this config?
According to the documentation bacula supports it:

" The Catalog Resource defines what catalog to use for the current job.
Currently, Bacula can only handle a single database server (SQLite,
MySQL, PostgreSQL) that is defined when configuring*Bacula*. However,
there may be as many Catalogs (databases) defined as you wish. For
example, you may want each Client to have its own Catalog database, or
you may want backup jobs to use one database and verify or restore jobs
to use another database."



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Il 24/01/2012 11:18, Xabier Elkano ha scritto:
El 24/01/12 10:49, Marcello Romani escribió:
Il 24/01/2012 10:05, Xabier Elkano ha scritto:
El 23/01/12 16:28, Uwe Schuerkamp escribió:
DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB
Hi Uwe,

I am having the same problem, backups are fast, but restores takes too
long creating directory tree with bat. I have a lot of files to backup
per client. I am using mysql with innodb engine, my File table is about
17GB on disk.

My numbers:

BytesPerJobAvg: 6539156346
ClientCount: 31
FileCount: 113286836
FileRetentionAvg:
FilenameCount: 29713190
FilesPerJobAvg: 184213
JobRetentionAvg:
PathCount: 6671143
TotalBytes: 1588763249151
TotalFiles: 44919364

First, I considered to create more bacula servers to separate clients on
diferent databases, but now I am testing a configuration with one
catalog per client. With this config, each client goes in separate db,
It's more difficult to administer and setup it but I guess is the best
way to scale the platform. Has anyone tried this config?

Sorry for my bad english.

Xabier


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
You mean 31 catalogs ?!


Yes, now, I am only testing with two catalogs and it's working Ok, what
are the downsides using this config?
According to the documentation bacula supports it:

" The Catalog Resource defines what catalog to use for the current job.
Currently, Bacula can only handle a single database server (SQLite,
MySQL, PostgreSQL) that is defined when configuring*Bacula*. However,
there may be as many Catalogs (databases) defined as you wish. For
example, you may want each Client to have its own Catalog database, or
you may want backup jobs to use one database and verify or restore jobs
to use another database."



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different db
servers. You'd probably have a higher ROI by upgrading the DBMS hardware
and/or migrating to postgres and/or throwing some (consultancy) money at
tuning.

Just my 2 cents.

--
Marcello Romani

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On Tue, 24 Jan 2012 11:47:51 +0100
Marcello Romani <mromani < at > ottotecnica.com> wrote:

[...]
You mean 31 catalogs ?!
Yes, now, I am only testing with two catalogs and it's working Ok,
what are the downsides using this config?
According to the documentation bacula supports it:

" The Catalog Resource defines what catalog to use for the current
job. Currently, Bacula can only handle a single database server
(SQLite, MySQL, PostgreSQL) that is defined when
configuring*Bacula*. However, there may be as many Catalogs
(databases) defined as you wish. For example, you may want each
Client to have its own Catalog database, or you may want backup
jobs to use one database and verify or restore jobs to use another
database."
[...]
I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different
db servers. You'd probably have a higher ROI by upgrading the DBMS
hardware and/or migrating to postgres and/or throwing some
(consultancy) money at tuning.

Just my 2 cents.
More catalogs also means more stuff to back up and keep track of.

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

El 24/01/12 11:47, Marcello Romani escribió:
Il 24/01/2012 11:18, Xabier Elkano ha scritto:
El 24/01/12 10:49, Marcello Romani escribió:
Il 24/01/2012 10:05, Xabier Elkano ha scritto:
El 23/01/12 16:28, Uwe Schuerkamp escribió:
DB Size:
Total clients: 107 Total bytes stored: 34.41 TB
Total files: 47495362 Database size: 31.64 GB
Hi Uwe,

I am having the same problem, backups are fast, but restores takes too
long creating directory tree with bat. I have a lot of files to backup
per client. I am using mysql with innodb engine, my File table is about
17GB on disk.

My numbers:

BytesPerJobAvg: 6539156346
ClientCount: 31
FileCount: 113286836
FileRetentionAvg:
FilenameCount: 29713190
FilesPerJobAvg: 184213
JobRetentionAvg:
PathCount: 6671143
TotalBytes: 1588763249151
TotalFiles: 44919364

First, I considered to create more bacula servers to separate clients on
diferent databases, but now I am testing a configuration with one
catalog per client. With this config, each client goes in separate db,
It's more difficult to administer and setup it but I guess is the best
way to scale the platform. Has anyone tried this config?

Sorry for my bad english.

Xabier


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
You mean 31 catalogs ?!

Yes, now, I am only testing with two catalogs and it's working Ok, what
are the downsides using this config?
According to the documentation bacula supports it:

" The Catalog Resource defines what catalog to use for the current job.
Currently, Bacula can only handle a single database server (SQLite,
MySQL, PostgreSQL) that is defined when configuring*Bacula*. However,
there may be as many Catalogs (databases) defined as you wish. For
example, you may want each Client to have its own Catalog database, or
you may want backup jobs to use one database and verify or restore jobs
to use another database."



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different db
servers. You'd probably have a higher ROI by upgrading the DBMS hardware
and/or migrating to postgres and/or throwing some (consultancy) money at
tuning.

Just my 2 cents.
Why not? If I want I can put each catalog on different db servers, each
catalog has its own db config. But this is not the idea, I want to put
all catalogs on the same db server but trying to keep tables as small as
possible to reduce IOs on db server, because is the server bottleneck
now. I can upgrade my server hardware, putting more memory or cpu, but
my problem is on disks handling these table sizes.



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On 01/24/2012 06:21 AM, Xabier Elkano wrote:
El 24/01/12 11:47, Marcello Romani escribió:
I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different db
servers. You'd probably have a higher ROI by upgrading the DBMS hardware
and/or migrating to postgres and/or throwing some (consultancy) money at
tuning.

Just my 2 cents.
Why not? If I want I can put each catalog on different db servers, each
catalog has its own db config. But this is not the idea, I want to put
all catalogs on the same db server but trying to keep tables as small as
possible to reduce IOs on db server, because is the server bottleneck
now. I can upgrade my server hardware, putting more memory or cpu, but
my problem is on disks handling these table sizes.

What my company does is run multiple Directors each with its own catalog
DB. The catalog DBs share the same DB server though (running PostgreSQL).


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Hi folks,

thanks to your hints and ideas the restore times have been reduced
from 3-4 hours to about five minutes (building the directory tree,
that is).

Lesson learnt: Before complaining loudly to the list, make sure your
db is in good health by administering a generous dosage of "repair
table" statements in mysql even if the database itself reports that
all is just fine and dandy. Turned out the File table had some errors
which seem to have gone undetected. 8-P

I also used the opportunity to kick out mysql for good and replace it
with MariaDB 5.2 which helped improve dir build times even further,
even without having to tweak any special my.cnf settings or exporting
/ importing to InnoDB or some other storage backen. I had wanted to do
this ever since listening to the recent interview with Monty Widenius
on "FLOSS Weekly".

All the best & thanks again for your help,

Uwe


--
NIONEX --- Ein Unternehmen der Bertelsmann AG



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Il 24/01/2012 12:21, Xabier Elkano ha scritto:

[snip]

I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different db
servers. You'd probably have a higher ROI by upgrading the DBMS hardware
and/or migrating to postgres and/or throwing some (consultancy) money at
tuning.

Just my 2 cents.

Why not? If I want I can put each catalog on different db servers, each
catalog has its own db config. But this is not the idea, I want to put
all catalogs on the same db server but trying to keep tables as small as
possible to reduce IOs on db server, because is the server bottleneck
now. I can upgrade my server hardware, putting more memory or cpu, but
my problem is on disks handling these table sizes.


For the record:

"Currently, Bacula can only handle a single database server"

therefore you can't put different catalogs on different db servers.

Also:

"In the current implementation, there is only a single Director
resource, but the final design will contain multiple Directors to
maintain index and media database redundancy."

so now bacula is limited to a single director which connects to a single
database server.

So it seems the only way to spread the load of a huge db onto multiple
servers is to exploit the load balancing and replication feature of the
db server.

These 2 cents of mine are based on my understanding of the docs

--
Marcello Romani

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

El 24/01/12 17:02, Marcello Romani escribió:
Il 24/01/2012 12:21, Xabier Elkano ha scritto:

[snip]

I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different db
servers. You'd probably have a higher ROI by upgrading the DBMS hardware
and/or migrating to postgres and/or throwing some (consultancy) money at
tuning.

Just my 2 cents.
Why not? If I want I can put each catalog on different db servers, each
catalog has its own db config. But this is not the idea, I want to put
all catalogs on the same db server but trying to keep tables as small as
possible to reduce IOs on db server, because is the server bottleneck
now. I can upgrade my server hardware, putting more memory or cpu, but
my problem is on disks handling these table sizes.

May be, I've not explained it very well

Now, I can't grow my bacula installation without installing another
director to distribute the clients, because using only one catalog, its
database is too big and restores are becoming impossibles to do.

So, I am currently testing two catalogs (with two databases) in same
director and it is working fine (but, yes implies more stuff to do).
For the record:

"Currently, Bacula can only handle a single database server"

I think that this sentence is only explaining that if you have compiled
bacula to use mysql, you can only use mysql as db server (and not
postgresql), but you can use as many mysql servers as you want to
storage your catalogs.


therefore you can't put different catalogs on different db servers.

Also:

"In the current implementation, there is only a single Director
resource, but the final design will contain multiple Directors to
maintain index and media database redundancy."

so now bacula is limited to a single director which connects to a single
database server.

So it seems the only way to spread the load of a huge db onto multiple
servers is to exploit the load balancing and replication feature of the
db server.

These 2 cents of mine are based on my understanding of the docs



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Il 24/01/2012 17:43, Xabier Elkano ha scritto:
El 24/01/12 17:02, Marcello Romani escribió:
Il 24/01/2012 12:21, Xabier Elkano ha scritto:

[snip]

I'm just thinking out loud, but I don't see how having a catalog for
each client can help you scale, since you can't put them on different db
servers. You'd probably have a higher ROI by upgrading the DBMS hardware
and/or migrating to postgres and/or throwing some (consultancy) money at
tuning.

Just my 2 cents.
Why not? If I want I can put each catalog on different db servers, each
catalog has its own db config. But this is not the idea, I want to put
all catalogs on the same db server but trying to keep tables as small as
possible to reduce IOs on db server, because is the server bottleneck
now. I can upgrade my server hardware, putting more memory or cpu, but
my problem is on disks handling these table sizes.

May be, I've not explained it very well

Now, I can't grow my bacula installation without installing another
director to distribute the clients, because using only one catalog, its
database is too big and restores are becoming impossibles to do.

So, I am currently testing two catalogs (with two databases) in same
director and it is working fine (but, yes implies more stuff to do).
For the record:

"Currently, Bacula can only handle a single database server"

I think that this sentence is only explaining that if you have compiled
bacula to use mysql, you can only use mysql as db server (and not
postgresql), but you can use as many mysql servers as you want to
storage your catalogs.


So what is the correct meaning of that sentence ? Only tests will tell

--
Marcello Romani

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users