Re: [Bacula-users] restore performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I added the indices back and performance dramatically increased. I assume my tables needed a repair and that that was the initial problem (maybe caused by the upgrade I had recently done from 2.2.8 to 5.0.3). While restores essentially hung before I dropped the indices and dropped to maybe 30 seconds to build a ~15GB directory tree, after adding them back, it's now nearly instantaneous. On 01/15/2013 11:03 AM, Novosielski, Ryan wrote: I would say it depends on your read of that article and either way whether or not you believe it. I'm not quite sure what to think. I believe I will re-add the indices I dropped and see what happens. - Original Message - From: Uwe Schuerkamp [mailto:uwe.schuerk...@nionex.net] Sent: Tuesday, January 15, 2013 10:32 AM To: Novosielski, Ryan Cc: John Drescher dresche...@gmail.com; bacula-users Bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] restore performance On Mon, Jan 14, 2013 at 09:01:32AM -0500, Novosielski, Ryan wrote: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Actually, after re-reading that, I'm a little confused. I don't know whether it says to drop all indexes except for primary and then drop the listed indices or drop everything except for primary and except for the listed indices. In any case, this solved the problem for me. I wonder if a repair would have also and maybe that's why? I have the following indexes defined on the File table: MariaDB [bacula] show indexes in File ; +---++--+--+-+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---++--+--+-+---+-+--++--++-+---+ | File | 0 | PRIMARY |1 | FileId | A | 947714927 | NULL | NULL | | BTREE | | | | File | 1 | JobId|1 | JobId | A |2513 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |1 | JobId | A |2513 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |2 | PathId | A |30571449 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |3 | FilenameId | A | 947714927 | NULL | NULL | | BTREE | | | +---++--+--+-+---+-+--++--++-+---+ 5 rows in set (0.00 sec) MariaDB [bacula] select count(*) from File ; +---+ | count(*) | +---+ | 947749966 | +---+ 1 row in set (0.00 sec) So it seems I'm more or less in the clear regarding our index setup, or am I missing something? Cheers thanks in advance, Uwe -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users - -- - _ _ _ _ ___ _ _ _ |Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Sr. Systems Programmer |$| |__| | | |__/ | \| _| |novos...@umdnj.edu - 973/972.0922 (2-0922) \__/ Univ. of Med. and Dent.|IST/EI-Academic Svcs. - ADMC 450, Newark -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iEYEARECAAYFAlD/ZFUACgkQmb+gadEcsb7QjACgh2I9brotUCzV17wIqaUfEaX7 4D8AoNNw+SvElefUPWV1t175yl7RTPDt =RwzM -END PGP SIGNATURE- -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
On Mon, Jan 14, 2013 at 09:01:32AM -0500, Novosielski, Ryan wrote: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Actually, after re-reading that, I'm a little confused. I don't know whether it says to drop all indexes except for primary and then drop the listed indices or drop everything except for primary and except for the listed indices. In any case, this solved the problem for me. I wonder if a repair would have also and maybe that's why? I have the following indexes defined on the File table: MariaDB [bacula] show indexes in File ; +---++--+--+-+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---++--+--+-+---+-+--++--++-+---+ | File | 0 | PRIMARY |1 | FileId | A | 947714927 | NULL | NULL | | BTREE | | | | File | 1 | JobId|1 | JobId | A |2513 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |1 | JobId | A |2513 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |2 | PathId | A |30571449 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |3 | FilenameId | A | 947714927 | NULL | NULL | | BTREE | | | +---++--+--+-+---+-+--++--++-+---+ 5 rows in set (0.00 sec) MariaDB [bacula] select count(*) from File ; +---+ | count(*) | +---+ | 947749966 | +---+ 1 row in set (0.00 sec) So it seems I'm more or less in the clear regarding our index setup, or am I missing something? Cheers thanks in advance, Uwe -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
I would say it depends on your read of that article and either way whether or not you believe it. I'm not quite sure what to think. I believe I will re-add the indices I dropped and see what happens. - Original Message - From: Uwe Schuerkamp [mailto:uwe.schuerk...@nionex.net] Sent: Tuesday, January 15, 2013 10:32 AM To: Novosielski, Ryan Cc: John Drescher dresche...@gmail.com; bacula-users Bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] restore performance On Mon, Jan 14, 2013 at 09:01:32AM -0500, Novosielski, Ryan wrote: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Actually, after re-reading that, I'm a little confused. I don't know whether it says to drop all indexes except for primary and then drop the listed indices or drop everything except for primary and except for the listed indices. In any case, this solved the problem for me. I wonder if a repair would have also and maybe that's why? I have the following indexes defined on the File table: MariaDB [bacula] show indexes in File ; +---++--+--+-+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---++--+--+-+---+-+--++--++-+---+ | File | 0 | PRIMARY |1 | FileId | A | 947714927 | NULL | NULL | | BTREE | | | | File | 1 | JobId|1 | JobId | A |2513 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |1 | JobId | A |2513 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |2 | PathId | A |30571449 | NULL | NULL | | BTREE | | | | File | 1 | JobId_2 |3 | FilenameId | A | 947714927 | NULL | NULL | | BTREE | | | +---++--+--+-+---+-+--++--++-+---+ 5 rows in set (0.00 sec) MariaDB [bacula] select count(*) from File ; +---+ | count(*) | +---+ | 947749966 | +---+ 1 row in set (0.00 sec) So it seems I'm more or less in the clear regarding our index setup, or am I missing something? Cheers thanks in advance, Uwe -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
On Mon, Jan 14, 2013 at 11:38:04AM +, Antony Mayi wrote: Hi guys, I seem to have major performance problem with the director/catalog when trying restoration. Using MySQL, the catalog has about 200MB, mysql is using innodb with large enough buffer pool to keep all data in memory. there is no iowait. when I attempt to do a restore using bconsole I get: snippet Select FileSet resource (1-2): 2 +---+---+---++-++ | JobId | Level | JobFiles | JobBytes | StartTime | VolumeName | +---+---+---++-++ | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1212000-full.vol03 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol00 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol01 | | 352 | I | 1,142 | 12,445,396 | 2013-01-07 23:42:58 | Offsite-1212502-incr.vol00 | | 363 | I | 1,127 | 12,098,952 | 2013-01-08 23:38:43 | Offsite-1212503-incr.vol00 | | 374 | I | 1,779 | 10,246,830 | 2013-01-09 23:44:54 | Offsite-1212504-incr.vol00 | | 385 | I | 1,520 | 16,334,830 | 2013-01-10 23:27:38 | Offsite-1212494-incr.vol00 | | 396 | I | 1,882 | 12,371,311 | 2013-01-11 23:27:28 | Offsite-1212495-incr.vol00 | +---+---+---++-++ To select the JobIds, you have the following choices: ... 3: Enter list of comma separated JobIds to select ... Select item: (1-13): 3 Enter JobId(s), comma separated, to restore: 341 You have selected the following JobId: 341 Building directory tree for JobId(s) 341 ... /snippet this building directory tree runs already 2 hours. mysql is taking 100% CPU (on single core of 4 available - Intel Xeon 2.4GHz). it is running following query: SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS FilenameId, LStat, MD5 FROM Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM ( SELECT JobTDate, PathId, FilenameId FROM File JOIN Job USING (JobId) WHERE File.JobId IN (341) UNION ALL SELECT JobTDate, PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (341) ) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (341)) OR Job.JobId IN (341)) AND T1.JobTDate = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) WHERE FileIndex 0 ORDER BY Temp.JobId, FileIndex ASC what can I do to make the restore function useful (ie. working in reasonable time)? I don't see many options in boosting the HW. thanks, Antony. Have you tried repair table File and checked for broken indices the like? All the best, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
I just ran into this. After a Google search, I turned up an article that says the indices that used to make Bacula run faster now cause a performance problem with recent versions of Bacula and recent versions of MySQL (it's on the Bacula wiki, the address for which I don't have handy). I removed all of the indices I had on the file table and restores only take a few minutes now. From: Antony Mayi [mailto:antonym...@yahoo.com] Sent: Monday, January 14, 2013 06:38 AM To: bacula-users@lists.sourceforge.net bacula-users@lists.sourceforge.net Subject: [Bacula-users] restore performance Hi guys, I seem to have major performance problem with the director/catalog when trying restoration. Using MySQL, the catalog has about 200MB, mysql is using innodb with large enough buffer pool to keep all data in memory. there is no iowait. when I attempt to do a restore using bconsole I get: snippet Select FileSet resource (1-2): 2 +---+---+---++-++ | JobId | Level | JobFiles | JobBytes | StartTime | VolumeName | +---+---+---++-++ | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1212000-full.vol03 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol00 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol01 | | 352 | I | 1,142 | 12,445,396 | 2013-01-07 23:42:58 | Offsite-1212502-incr.vol00 | | 363 | I | 1,127 | 12,098,952 | 2013-01-08 23:38:43 | Offsite-1212503-incr.vol00 | | 374 | I | 1,779 | 10,246,830 | 2013-01-09 23:44:54 | Offsite-1212504-incr.vol00 | | 385 | I | 1,520 | 16,334,830 | 2013-01-10 23:27:38 | Offsite-1212494-incr.vol00 | | 396 | I | 1,882 | 12,371,311 | 2013-01-11 23:27:28 | Offsite-1212495-incr.vol00 | +---+---+---++-++ To select the JobIds, you have the following choices: ... 3: Enter list of comma separated JobIds to select ... Select item: (1-13): 3 Enter JobId(s), comma separated, to restore: 341 You have selected the following JobId: 341 Building directory tree for JobId(s) 341 ... /snippet this building directory tree runs already 2 hours. mysql is taking 100% CPU (on single core of 4 available - Intel Xeon 2.4GHz). it is running following query: SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS FilenameId, LStat, MD5 FROM Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM ( SELECT JobTDate, PathId, FilenameId FROM File JOIN Job USING (JobId) WHERE File.JobId IN (341) UNION ALL SELECT JobTDate, PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON(BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (341) ) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (341)) OR Job.JobId IN (341)) AND T1.JobTDate = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) WHERE FileIndex 0 ORDER BY Temp.JobId, FileIndex ASC what can I do to make the restore function useful (ie. working in reasonable time)? I don't see many options in boosting the HW. thanks, Antony. -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
thanks Uwe, good hint. I run the repair on all tables and restore works like a charm now! thanks a lot, Antony. From: Uwe Schuerkamp uwe.schuerk...@nionex.net To: Antony Mayi antonym...@yahoo.com Cc: bacula-users@lists.sourceforge.net bacula-users@lists.sourceforge.net Sent: Monday, 14 January 2013, 12:45 Subject: Re: [Bacula-users] restore performance On Mon, Jan 14, 2013 at 11:38:04AM +, Antony Mayi wrote: Hi guys, I seem to have major performance problem with the director/catalog when trying restoration. Using MySQL, the catalog has about 200MB, mysql is using innodb with large enough buffer pool to keep all data in memory. there is no iowait. when I attempt to do a restore using bconsole I get: snippet Select FileSet resource (1-2): 2 +---+---+---++-++ | JobId | Level | JobFiles | JobBytes | StartTime | VolumeName | +---+---+---++-++ | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1212000-full.vol03 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol00 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol01 | | 352 | I | 1,142 | 12,445,396 | 2013-01-07 23:42:58 | Offsite-1212502-incr.vol00 | | 363 | I | 1,127 | 12,098,952 | 2013-01-08 23:38:43 | Offsite-1212503-incr.vol00 | | 374 | I | 1,779 | 10,246,830 | 2013-01-09 23:44:54 | Offsite-1212504-incr.vol00 | | 385 | I | 1,520 | 16,334,830 | 2013-01-10 23:27:38 | Offsite-1212494-incr.vol00 | | 396 | I | 1,882 | 12,371,311 | 2013-01-11 23:27:28 | Offsite-1212495-incr.vol00 | +---+---+---++-++ To select the JobIds, you have the following choices: ... 3: Enter list of comma separated JobIds to select ... Select item: (1-13): 3 Enter JobId(s), comma separated, to restore: 341 You have selected the following JobId: 341 Building directory tree for JobId(s) 341 ... /snippet this building directory tree runs already 2 hours. mysql is taking 100% CPU (on single core of 4 available - Intel Xeon 2.4GHz). it is running following query: SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS FilenameId, LStat, MD5 FROM Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM ( SELECT JobTDate, PathId, FilenameId FROM File JOIN Job USING (JobId) WHERE File.JobId IN (341) UNION ALL SELECT JobTDate, PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (341) ) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (341)) OR Job.JobId IN (341)) AND T1.JobTDate = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) WHERE FileIndex 0 ORDER BY Temp.JobId, FileIndex ASC what can I do to make the restore function useful (ie. working in reasonable time)? I don't see many options in boosting the HW. thanks, Antony. Have you tried repair table File and checked for broken indices the like? All the best, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
I just ran into this. After a Google search, I turned up an article that says the indices that used to make Bacula run faster now cause a performance problem with recent versions of Bacula and recent versions of MySQL (it's on the Bacula wiki, the address for which I don't have handy). I removed all of the indices I had on the file table and restores only take a few minutes now. Even though you have a tiny database I would still look into tuning your mysql settings. A lot of distributions ship with a ridiculous default configuration that assumes your PC has 64MB of ram or similar. John -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
On Mon, Jan 14, 2013 at 01:10:59PM +, Antony Mayi wrote: thanks Uwe, good hint. I run the repair on all tables and restore works like a charm now! thanks a lot, Antony. You're welcome, I've fallen into the same trap before ;) Cheers, Uwe From: Uwe Schuerkamp uwe.schuerk...@nionex.net To: Antony Mayi antonym...@yahoo.com Cc: bacula-users@lists.sourceforge.net bacula-users@lists.sourceforge.net Sent: Monday, 14 January 2013, 12:45 Subject: Re: [Bacula-users] restore performance On Mon, Jan 14, 2013 at 11:38:04AM +, Antony Mayi wrote: Hi guys, I seem to have major performance problem with the director/catalog when trying restoration. Using MySQL, the catalog has about 200MB, mysql is using innodb with large enough buffer pool to keep all data in memory. there is no iowait. when I attempt to do a restore using bconsole I get: snippet Select FileSet resource (1-2): 2 +---+---+---++-++ | JobId | Level | JobFiles | JobBytes | StartTime | VolumeName | +---+---+---++-++ | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1212000-full.vol03 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol00 | | 341 | F | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol01 | | 352 | I | 1,142 | 12,445,396 | 2013-01-07 23:42:58 | Offsite-1212502-incr.vol00 | | 363 | I | 1,127 | 12,098,952 | 2013-01-08 23:38:43 | Offsite-1212503-incr.vol00 | | 374 | I | 1,779 | 10,246,830 | 2013-01-09 23:44:54 | Offsite-1212504-incr.vol00 | | 385 | I | 1,520 | 16,334,830 | 2013-01-10 23:27:38 | Offsite-1212494-incr.vol00 | | 396 | I | 1,882 | 12,371,311 | 2013-01-11 23:27:28 | Offsite-1212495-incr.vol00 | +---+---+---++-++ To select the JobIds, you have the following choices: ... 3: Enter list of comma separated JobIds to select ... Select item: (1-13): 3 Enter JobId(s), comma separated, to restore: 341 You have selected the following JobId: 341 Building directory tree for JobId(s) 341 ... /snippet this building directory tree runs already 2 hours. mysql is taking 100% CPU (on single core of 4 available - Intel Xeon 2.4GHz). it is running following query: SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS FilenameId, LStat, MD5 FROM Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM ( SELECT JobTDate, PathId, FilenameId FROM File JOIN Job USING (JobId) WHERE File.JobId IN (341) UNION ALL SELECT JobTDate, PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (341) ) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (341)) OR Job.JobId IN (341)) AND T1.JobTDate = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) WHERE FileIndex 0 ORDER BY Temp.JobId, FileIndex ASC what can I do to make the restore function useful (ie. working in reasonable time)? I don't see many options in boosting the HW. thanks, Antony. Have you tried repair table File and checked for broken indices the like? All the best, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
On Mon, Jan 14, 2013 at 08:24:07AM -0500, John Drescher wrote: I just ran into this. After a Google search, I turned up an article that says the indices that used to make Bacula run faster now cause a performance problem with recent versions of Bacula and recent versions of MySQL (it's on the Bacula wiki, the address for which I don't have handy). I removed all of the indices I had on the file table and restores only take a few minutes now. Even though you have a tiny database I would still look into tuning your mysql settings. A lot of distributions ship with a ridiculous default configuration that assumes your PC has 64MB of ram or similar. John I'd like to have the above point clarified by the people in the know: Are File table indices recommended with bacula versions 5.0 or not? Can the original poster provide a link to the article mentioned above? Thanks, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
On Mon, Jan 14, 2013 at 8:32 AM, Uwe Schuerkamp uwe.schuerk...@nionex.net wrote: On Mon, Jan 14, 2013 at 08:24:07AM -0500, John Drescher wrote: I just ran into this. After a Google search, I turned up an article that says the indices that used to make Bacula run faster now cause a performance problem with recent versions of Bacula and recent versions of MySQL (it's on the Bacula wiki, the address for which I don't have handy). I removed all of the indices I had on the file table and restores only take a few minutes now. Even though you have a tiny database I would still look into tuning your mysql settings. A lot of distributions ship with a ridiculous default configuration that assumes your PC has 64MB of ram or similar. John I'd like to have the above point clarified by the people in the know: Are File table indices recommended with bacula versions 5.0 or not? Can the original poster provide a link to the article mentioned above? I did not mean file table indices (I am not sure about these - I use postgresql with bacula). However I meant tuning the database parameters to allow mysql server to use more memory. I have had to do that for other mysql usage. John -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
On Mon, Jan 14, 2013 at 08:44:06AM -0500, John Drescher wrote: I did not mean file table indices (I am not sure about these - I use postgresql with bacula). However I meant tuning the database parameters to allow mysql server to use more memory. I have had to do that for other mysql usage. John Sure, on our db server (18GB RAM, latest MariaDB 64bit) I have allocated about 8G for key buffers, but the idea that File table indices slow bacula down in recent versions needs clarification ;-) I was hoping Ryan could link to the article in question. All the best, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/14/2013 08:52 AM, Uwe Schuerkamp wrote: On Mon, Jan 14, 2013 at 08:44:06AM -0500, John Drescher wrote: I did not mean file table indices (I am not sure about these - I use postgresql with bacula). However I meant tuning the database parameters to allow mysql server to use more memory. I have had to do that for other mysql usage. John Sure, on our db server (18GB RAM, latest MariaDB 64bit) I have allocated about 8G for key buffers, but the idea that File table indices slow bacula down in recent versions needs clarification ;-) I was hoping Ryan could link to the article in question. According to this anyhow: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog - -- - _ _ _ _ ___ _ _ _ |Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Sr. Systems Programmer |$| |__| | | |__/ | \| _| |novos...@umdnj.edu - 973/972.0922 (2-0922) \__/ Univ. of Med. and Dent.|IST/EI-Academic Svcs. - ADMC 450, Newark -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iEYEARECAAYFAlD0DvAACgkQmb+gadEcsb7I1wCgwyT7xz6uRG7LodjMDUMDgDxY 8DYAn0DoZ3xGyv8ZXJZTqHvc79Z5m27o =kLIG -END PGP SIGNATURE- -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/14/2013 08:59 AM, Novosielski, Ryan wrote: On 01/14/2013 08:52 AM, Uwe Schuerkamp wrote: On Mon, Jan 14, 2013 at 08:44:06AM -0500, John Drescher wrote: I did not mean file table indices (I am not sure about these - I use postgresql with bacula). However I meant tuning the database parameters to allow mysql server to use more memory. I have had to do that for other mysql usage. John Sure, on our db server (18GB RAM, latest MariaDB 64bit) I have allocated about 8G for key buffers, but the idea that File table indices slow bacula down in recent versions needs clarification ;-) I was hoping Ryan could link to the article in question. According to this anyhow: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Actually, after re-reading that, I'm a little confused. I don't know whether it says to drop all indexes except for primary and then drop the listed indices or drop everything except for primary and except for the listed indices. In any case, this solved the problem for me. I wonder if a repair would have also and maybe that's why? - -- - _ _ _ _ ___ _ _ _ |Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Sr. Systems Programmer |$| |__| | | |__/ | \| _| |novos...@umdnj.edu - 973/972.0922 (2-0922) \__/ Univ. of Med. and Dent.|IST/EI-Academic Svcs. - ADMC 450, Newark -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iEYEARECAAYFAlD0D7oACgkQmb+gadEcsb4ttgCg4qEBugxxEnadoBZK7bkraZwv ox0AoI+m8TMdoqhP9Yaz5h3Ba5Nbhkk4 =+mF6 -END PGP SIGNATURE- -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] restore performance
Le 2013-01-14 14:32, Uwe Schuerkamp a écrit : On Mon, Jan 14, 2013 at 08:24:07AM -0500, John Drescher wrote: I just ran into this. After a Google search, I turned up an article that says the indices that used to make Bacula run faster now cause a performance problem with recent versions of Bacula and recent versions of MySQL (it's on the Bacula wiki, the address for which I don't have handy). I removed all of the indices I had on the file table and restores only take a few minutes now. Even though you have a tiny database I would still look into tuning your mysql settings. A lot of distributions ship with a ridiculous default configuration that assumes your PC has 64MB of ram or similar. John I'd like to have the above point clarified by the people in the know: Are File table indices recommended with bacula versions 5.0 or not? Can the original poster provide a link to the article mentioned above? Thanks, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE Co. KGaA Hello, Did you have a look on that? http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_catalog dbcheck creates 2 indexes. If interrupted, these two indexes can have a very big impact on times to perform actions related to these tables (backup AND restores) Your catalog is quite small, anyway, did you schedule to run a dbckeck periodically? Best regards. Jerome Blion. -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122412 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 21.09.2011 14:58, schrieb Gavin McCullagh: Then a couple of months ago I noticed a new detail in the wiki about _removing_ bogus indexes. It turns out that by adding extra indexes you can slow down MySQL SELECT queries (I think someone said it doesn't always choose the optimal/correct index). http://wiki.bacula.org/doku.php?id=faq The FAQ first (in #why_does_dbcheck_take_forever_to_run) advises to creade indexes on File.PathId and File.FilenameId and later on (in #restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog) to drop them if they exist. Since Bacula version 5, dbcheck(8) warns if these indexes do not exist and offers to create temporary indexes for them, although the latter FAQ entry appears to warn against allowing that, too. Perhaps someone could clarify a bit how to decide which indexes to create or drop when? Thanks, Tilman -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.16 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org/ iEYEARECAAYFAk6RkKcACgkQ780oymN0g8Ot/gCeLi/IwY1jbCB3h50SosAphq0p DGUAniaOwMa+nRpylSVmOnAE45XA5q21 =IB2p -END PGP SIGNATURE- -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
On Wed, 21 Sep 2011, Erik P. Olsen wrote: I am running a very smooth bacula 5.0.3 on Fedora 14. Everything seems to be OK except restores which are incredibly slow. How can I debug it to see what's wrong? Start off by telling us what part of the restore process is slow: - building the file tree for selection - the actual restore of files to disk - something else Gavin -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Hi, On Wed, 21 Sep 2011, Gavin McCullagh wrote: On Wed, 21 Sep 2011, Erik P. Olsen wrote: I am running a very smooth bacula 5.0.3 on Fedora 14. Everything seems to be OK except restores which are incredibly slow. How can I debug it to see what's wrong? Start off by telling us what part of the restore process is slow: - building the file tree for selection - the actual restore of files to disk - something else A few minutes after replying I noticed that you asked this in more detail back in February and said that building the tree was the slow part. I have suffered similar problems with MySQL explored in the thread linked below. By logging slow queries I managed to identify a single query which was the primary cause. http://adsm.org/lists/html/Bacula-users/2010-11/msg00112.html http://adsm.org/lists/html/Bacula-users/2010-11/msg00187.html Then a couple of months ago I noticed a new detail in the wiki about _removing_ bogus indexes. It turns out that by adding extra indexes you can slow down MySQL SELECT queries (I think someone said it doesn't always choose the optimal/correct index). http://wiki.bacula.org/doku.php?id=faq http://adsm.org/lists/html/Bacula-users/2011-08/msg4.html http://adsm.org/lists/html/Bacula-users/2011-08/msg7.html It would be worth taking a look at that. Gavin -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Em 21-09-2011 09:29, Gavin McCullagh escreveu: On Wed, 21 Sep 2011, Erik P. Olsen wrote: I am running a very smooth bacula 5.0.3 on Fedora 14. Everything seems to be OK except restores which are incredibly slow. How can I debug it to see what's wrong? - building the file tree for selection +1 on this. I am running bacula 5.0.1-1ubuntu1 on a Ubuntu 10.04.3 LTS server. Database is about 150GB big and any restore takes 2 or 3 minutes to build the tree. -- *Marcio Merlone* attachment: marcio_merlone.vcf-- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Hi, On Wed, 21 Sep 2011, Marcio Merlone wrote: Em 21-09-2011 09:29, Gavin McCullagh escreveu: On Wed, 21 Sep 2011, Erik P. Olsen wrote: I am running a very smooth bacula 5.0.3 on Fedora 14. Everything seems to be OK except restores which are incredibly slow. How can I debug it to see what's wrong? - building the file tree for selection +1 on this. I am running bacula 5.0.1-1ubuntu1 on a Ubuntu 10.04.3 LTS server. Database is about 150GB big and any restore takes 2 or 3 minutes to build the tree. A 150GB database. That's pretty large. How many clients have you? Which database are you using (MySQL, Postgresql)? If you haven't seen it already, this might be useful: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Gavin -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Em 21-09-2011 10:29, Gavin McCullagh escreveu: A 150GB database. That's pretty large. How many clients have you? About a dozen clients - some inactive but still with valid backup - File Retention = 6 months, Job Retention = 1 year. Most clients have few or no changes, but my storage server has 2,825,101 files on a full backup and almost 100.000 on an incremental. Which database are you using (MySQL, Postgresql)? MySQL. If you haven't seen it already, this might be useful: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Yes, I was there when you mail arrived. It made think about going to Postgres. Is there any upgrade path or helper to go from mysql to postgres? I am now optimizing File table and will then run dbcheck. Best regards, -- *Marcio Merlone* -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Hi, On Wed, 21 Sep 2011, Marcio Merlone wrote: Em 21-09-2011 10:29, Gavin McCullagh escreveu: A 150GB database. That's pretty large. How many clients have you? About a dozen clients - some inactive but still with valid backup - File Retention = 6 months, Job Retention = 1 year. Most clients have few or no changes, but my storage server has 2,825,101 files on a full backup and almost 100.000 on an incremental. It's definitely the database that is 150GB, yeah? We have 40 clients, some of which have 5 million files and similar retention times, but our database is 11GB. Which database are you using (MySQL, Postgresql)? MySQL. Ditto. If you haven't seen it already, this might be useful: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Yes, I was there when you mail arrived. It made think about going to Postgres. Is there any upgrade path or helper to go from mysql to postgres? I am now optimizing File table and will then run dbcheck. Check that you have the right indexes (and no extra ones). The removal of a couple of wrong indexes made a massive difference for us. Bacula does seem to be better optimised toward Postgresql, but I'm not convinced you can't get MySQL to work. Gavin -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
On 21/09/11 15:56, Marcio Merlone wrote: Em 21-09-2011 10:29, Gavin McCullagh escreveu: A 150GB database. That's pretty large. How many clients have you? About a dozen clients - some inactive but still with valid backup - File Retention = 6 months, Job Retention = 1 year. Most clients have few or no changes, but my storage server has 2,825,101 files on a full backup and almost 100.000 on an incremental. Which database are you using (MySQL, Postgresql)? MySQL. If you haven't seen it already, this might be useful: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Yes, I was there when you mail arrived. It made think about going to Postgres. Is there any upgrade path or helper to go from mysql to postgres? I am now optimizing File table and will then run dbcheck. I forgot to mention that my problem is with building the file tree. So converting to postresql may be the best thing to do. Will the following scenario work? 1. Backup the catalogue 2. Remove mysql database 3. Build postresql database 4. Restore the catalogue Or is there more to it than this? -- Erik -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
On Wed, 21 Sep 2011, Erik P. Olsen wrote: I forgot to mention that my problem is with building the file tree. So converting to postresql may be the best thing to do. Will the following scenario work? 1. Backup the catalogue 2. Remove mysql database 3. Build postresql database 4. Restore the catalogue Or is there more to it than this? There is more to it than that. You need to stop bacula, dump the mysql database, load it into postgresql, install the postgresql version of bacula-sd and bacula-dir and get that up and running. If your database is large this may take some time. It's not entirely trivial to load a mysql dump into postgresql though. http://mtu.net/~jpschewe/blog/2010/06/migrating-bacula-from-mysql-to-postgresql/ http://workaround.org/node/258 http://www.backupcentral.com/phpBB2/two-way-mirrors-of-external-mailing-lists-3/bacula-25/process-to-convert-mysql-to-postgres-85413/ Gavin -- Gavin McCullagh Senior System Administrator IT Services Griffith College South Circular Road Dublin 8 Ireland Tel: +353 1 4163365 http://www.gcd.ie http://www.gcd.ie/brochure.pdf http://www.gcd.ie/opendays http://www.gcd.ie/ebrochure This E-mail is from Griffith College. The E-mail and any files transmitted with it are confidential and may be privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the addressee you are prohibited from disclosing its content, copying it or distributing it otherwise than to the addressee. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and delete the e-mail from your computer. Bellerophon Ltd, trades as Griffith College (registered in Ireland No. 60469) with its registered address as Griffith College Campus, South Circular Road, Dublin 8, Ireland. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Eric As Gavin pointed out, a 150GB database is huuuge for only a dozen client. Unless you have billions of files on each client there is no reason your catalog is that large. Are you sure you correctly applied job and file retention on your catalog? Also are you sure you catalog is not full of orphaned records? Before migrating to postgres (which is a good choice for big catalogs), I would look at the catalog to see if all retention period are correctly applied. Best regards. Le 21/09/2011 16:51, Erik P. Olsen a crit: On 21/09/11 15:56, Marcio Merlone wrote: Em 21-09-2011 10:29, Gavin McCullagh escreveu: A 150GB database. That's pretty large. How many clients have you? About a dozen clients - some inactive but still with valid backup - File Retention = 6 months, Job Retention = 1 year. Most clients have few or no changes, but my storage server has 2,825,101 files on a full backup and almost 100.000 on an incremental. Which database are you using (MySQL, Postgresql)? MySQL. If you haven't seen it already, this might be useful: http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog Yes, I was there when you mail arrived. It made think about going to Postgres. Is there any upgrade path or helper to go from mysql to postgres? I am now optimizing File table and will then run dbcheck. I forgot to mention that my problem is with building the file tree. So converting to postresql may be the best thing to do. Will the following scenario work? 1. Backup the catalogue 2. Remove mysql database 3. Build postresql database 4. Restore the catalogue Or is there more to it than this? -- Alexandre Chapellon Ingnierie des systmes open sources et rseaux. Follow me on twitter: @alxgomz attachment: a_chapellon.vcf-- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance
Em 21-09-2011 13:33, Alexandre Chapellon escreveu: As Gavin pointed out, a 150GB database is huuuge for only a dozen client. Unless you have billions of files on each client there is no reason your catalog is that large. Are you sure you correctly applied job and file retention on your catalog? Also are you sure you catalog is not full of orphaned records? Before migrating to postgres (which is a good choice for big catalogs), I would look at the catalog to see if all retention period are correctly applied. I am running dbcheck to see how many rabbits come out of the bushes. File table is only 6.6GB and Log is 105GB. What's that Log table for? It only have blobs... Also, in a near future I will change bacula-dir to another server, which will be a good opportunity to start a blank catalog and go with Postgres, without any migration, just keeping the old one sitting there until the end of retention period. -- *Marcio Merlone* -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Restore performance using concurrent backups
Hello,
On 3/30/2006 9:38 AM, Christoph Litauer wrote:
Hi,
I just searched the archives but didn't find hints. Sorry if this
question has been asked before.
I am using bacula-1.36.3 for the backup of about 12 linux clients. At
the moment I just use an external hardware raid as (File-)backup device:
Device {
Name = FileStorage
Media Type = File
Archive Device = /storage
LabelMedia = yes;
Random Access = Yes;
AutomaticMount = yes;
RemovableMedia = no;
AlwaysOpen = no;
Maximum Volume Size = 2147483648 # 2GB
}
The backups are done in parallel with a maximum of 20 concurrent jobs.
The most recent backup of one of the clients (about 1,6 GB of data)
consists of a full, 1 differential and 2 incrememtal backups, located on
16 backup files (FileStorage).
Running the restore I get a Rate of about 500 KB/sec (restoring to a
local filesystem on the storage server). The raid device is able to
deliver 12MB/sec. The local filesystem is able to write about 10MB/sec,
My conclusion for that degraded performance is that even if I use
FileStorage as a random access media, the restore process recovers the
data by sequentially reading all the 16 backup files searching for the
right files. I would have expected a rate of nearly native disk speed
because the backup files could be seeked.
As far as I know, there are some problems using seeking instead of
reading and interpreting which lead to Kern disabling volume seeking for
file volumes, so your conclusion is right.
A solution would be to not perform concurrent backups. But then the
backup window gets significantly wider (especially for the incrementals).
Any solutions, hints? Would my problem disappear if I upgrade to 1.38.x?
Perhaps, I don't know if that peculiar problem is resolved by now. You
might read through the release notes of the 1.38 versions.
Possible solution, assuming you've got enough disk space: Use spooling.
It's - theoretically - nonsense doing this with file volumes, but might
reslove the performance problems you see. Try to put the spool space
onto different physical disks and a different filesystem than the real
storage space!
Arno
Thanks a lot in advance. If you need more of my configuration files, no
problem, please tell me.
--
IT-Service Lehmann[EMAIL PROTECTED]
Arno Lehmann http://www.its-lehmann.de
---
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnkkid=110944bid=241720dat=121642
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
