Re: [Bacula-users] Have we reached bacula's limits?
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@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@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@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@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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 ala...@caerllewys.net ala...@metrocast.net p...@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
On Tue, Jan 24, 2012 at 12:00 PM, Marcello Romani mrom...@ottotecnica.com wrote: 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 :-) I believe it meant that with bacula prior to 5.2.X you had to compile in your db choice (sqlite, postgresql,mysql) and could not change that choice at runtime. Now with 5.2 You can mix and match and have more than 1 catalog. John -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
On 24/01/12 17:08, John Drescher wrote: I believe it meant that with bacula prior to 5.2.X you had to compile in your db choice (sqlite, postgresql,mysql) and could not change that choice at runtime. Now with 5.2 You can mix and match and have more than 1 catalog. 1: You can only define one database server in bacula-dir 2: You can have as many catalogs on that server as you want. 3: No matter which database engine you choose, as the table sizes increase it will perform badly unless tuned properly. Different engines suit different loads but until the size and load factor of bacula's requirements gets _large_ the differences are largely academic, so people should go with what they're comfortable with. 4: Changing between mysql/postgres is irritating but as long as dumps are done in proper compatibility mode, relatively painless (I have around 350 million files in my databases. Tuned Postgres vs tuned Mysql has measurable performance/memory differences. With only 20 million files onboard the differences are negligable) There are advantages and disadvantages to a monolithic catalog. This is a matter of personal taste. 5: Splitting up a monolithic catalog set takes a fair amount of patience. FWIW I have multiple catalog sets: a: Servers and support gear (daily machine backups) b: Fileserver cluster1 backups (100Tb or so) c: Fileserver 2 backups (~200Tb) d: Desktop hardware This is mainly done for political and dump/restore size reasons. There's very little performance/memory difference between running them all monolithically or separate.) 6: Poolsets are per-catalog. 1 pool cannot service multiple catalogs. (the data about what's on which tape is per-catalog) 7: Bacula scales to limits which would blow most people's minds - well beyond most COTS (commercial off the shelf) software and past every other OSS package I'm aware 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Have we reached bacula's limits?
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
Re: [Bacula-users] Have we reached bacula's limits?
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 ala...@caerllewys.net ala...@metrocast.net p...@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Have we reached bacula's limits?
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
