Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Creating indexes solve performance issues on your environment Evan? On Fri, Apr 16, 2010 at 7:21 PM, Evan Fraser evan.fra...@rms.com wrote: Thanks for this, it fixed my problem. Cheers, Evan. -Original Message- From: Graham Keeling [mailto:gra...@equiinet.com] Sent: 13 April 2010 14:10 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience On Tue, Apr 13, 2010 at 02:03:39PM +0100, Graham Keeling wrote: On Tue, Apr 13, 2010 at 02:42:15PM +0200, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards Hello, I had similar problems with virtual and accurate backups until I made sure that the indexes on my mysql database were the bacula defaults. In particular, I had these indexes on my File table: JobId JobId, PathId, FilenameId PathId FilenameId Once I had removed the PathId and FilenameId indexes, my queries changed from taking many hours to taking about a second. For clarity, I now have these indexes on my File table: JobId JobId, PathId, FilenameId To check these on your database: Log into mysql. use bacula; show indexes from File; If you have any extra indexes, you can drop them like this: drop index index name on File; e.g: drop index PathId on File; If you need to add one: create index index name on File (list of fields); e.g: create index JobId on File (JobId); -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users This message and any attachments contain information that may be RMS Inc. confidential and/or privileged. If you are not the intended recipient (or authorized to receive for the intended recipient), and have received this message in error, any use, disclosure or distribution is strictly prohibited. If you have received this message in error, please notify the sender immediately by replying to the e-mail and permanently deleting the message from your computer and/or storage system. -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Hi, I have the following indexes: mysql show indexes from File;+---++--+--+-+---+-+--++--++-+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+---++--+--+-+---+-+--++--++-+| File | 0 | PRIMARY |1 | FileId | A | 367094717 | NULL | NULL | | BTREE | || File | 1 | JobId|1 | JobId | A |6900 | NULL | NULL | | BTREE | || File | 1 | JobId| 2 | PathId | A |22943419 | NULL | NULL | | BTREE | || File | 1 | JobId|3 | FilenameId | A | 367094717 | NULL | NULL | | BTREE | |+---++--+--+-+---+-+--++--++-+ Are these the right ones? Thanks, Jan Jaap Date: Tue, 20 Apr 2010 14:53:54 +0200 From: ksantiste...@gmail.com To: evan.fra...@rms.com CC: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] bacula 5.0.1 and db issues - please,share your experience Creating indexes solve performance issues on your environment Evan? On Fri, Apr 16, 2010 at 7:21 PM, Evan Fraser evan.fra...@rms.com wrote: Thanks for this, it fixed my problem. Cheers, Evan. -Original Message- From: Graham Keeling [mailto:gra...@equiinet.com] Sent: 13 April 2010 14:10 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience On Tue, Apr 13, 2010 at 02:03:39PM +0100, Graham Keeling wrote: On Tue, Apr 13, 2010 at 02:42:15PM +0200, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards Hello, I had similar problems with virtual and accurate backups until I made sure that the indexes on my mysql database were the bacula defaults. In particular, I had these indexes on my File table: JobId JobId, PathId, FilenameId PathId FilenameId Once I had removed the PathId and FilenameId indexes, my queries changed from taking many hours to taking about a second. For clarity, I now have these indexes on my File table: JobId JobId, PathId, FilenameId To check these on your database: Log into mysql. use bacula; show indexes from File; If you have any extra indexes, you can drop them like this: drop index index name on File; e.g: drop index PathId on File; If you need to add one: create index index name on File (list of fields); e.g: create index JobId on File (JobId); -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users This message and any attachments contain information that may be RMS Inc. confidential and/or privileged. If you are not the intended recipient (or authorized to receive for the intended recipient), and have received this message in error, any use
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Thanks for this, it fixed my problem. Cheers, Evan. -Original Message- From: Graham Keeling [mailto:gra...@equiinet.com] Sent: 13 April 2010 14:10 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience On Tue, Apr 13, 2010 at 02:03:39PM +0100, Graham Keeling wrote: On Tue, Apr 13, 2010 at 02:42:15PM +0200, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards Hello, I had similar problems with virtual and accurate backups until I made sure that the indexes on my mysql database were the bacula defaults. In particular, I had these indexes on my File table: JobId JobId, PathId, FilenameId PathId FilenameId Once I had removed the PathId and FilenameId indexes, my queries changed from taking many hours to taking about a second. For clarity, I now have these indexes on my File table: JobId JobId, PathId, FilenameId To check these on your database: Log into mysql. use bacula; show indexes from File; If you have any extra indexes, you can drop them like this: drop index index name on File; e.g: drop index PathId on File; If you need to add one: create index index name on File (list of fields); e.g: create index JobId on File (JobId); -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users This message and any attachments contain information that may be RMS Inc. confidential and/or privileged. If you are not the intended recipient (or authorized to receive for the intended recipient), and have received this message in error, any use, disclosure or distribution is strictly prohibited. If you have received this message in error, please notify the sender immediately by replying to the e-mail and permanently deleting the message from your computer and/or storage system. -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Thanks for your answers. Stephen, do you use bconsole or bat? Perhaps the issue is only on bat. I recognize that i only use bweb and bat (on windows). Regarding your comments Stephen, my bacula server is smaller than yours, but my catalog size was 400 Mb (Baculas is working since 2 months ago). I don´t tune my database, but with 3.0.3 version thats wasn´t neccesary. Wich parameters are recommending to tune? This info i think that is very useful for people with the same issue like me... i see that some people says that the better way is creating new indexes(someone says that this is the worst option), others say to custom mysql parameters...but i can´t find any official info, and, at less in my case, i don´t have enough time(and knowledge) to testing bacula with some new indexes, or customizing mysql/postgre... I miss this offcial info... Regards On Tue, Apr 13, 2010 at 7:36 PM, Thomas Mueller tho...@chaschperli.chwrote: Am Tue, 13 Apr 2010 15:59:25 +0200 schrieb Koldo Santisteban: thanks for your answer. The first stage was with mysql 5.0.77, and works with bacula 3.0.3 without problems. I have used the same database and server with bacula 5.0.1. The bacula server + DB is a 3,5 Gb Ram with a Xeon processor. I have tested my environment installing postgree on the same server and with a empty db. I create full bacula server backup and then try to restore. I have detected that the restore process works fine usgin bweb and bacula 5.0.1, what is the difference between bat and bweb? noticed too, bat takes forever on building trees on restores. bconsole is _much_ faster. - Thomas -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
On Wed, Apr 14, 2010 at 03:29:29PM +0200, Koldo Santisteban wrote: Thanks for your answers. Stephen, do you use bconsole or bat? Perhaps the issue is only on bat. I recognize that i only use bweb and bat (on windows). For what it's worth, I used bconsole, and it was extremly slow (20+ hours) under 5.0.x, while it was relatively fast (less than 10 minutes) under 3.0.3 Regarding your comments Stephen, my bacula server is smaller than yours, but my catalog size was 400 Mb (Baculas is working since 2 months ago). I don´t tune my database, but with 3.0.3 version thats wasn´t neccesary. Wich parameters are recommending to tune? This info i think that is very useful for people with the same issue like me... i see that some people says that the better way is creating new indexes(someone says that this is the worst option), others say to custom mysql parameters...but i can´t find any official info, and, at less in my case, i don´t have enough time(and knowledge) to testing bacula with some new indexes, or customizing mysql/postgre... I miss this offcial info... I do not see how you could expect to get official info before there is official opinion formed ? There is a speed bug in bacula 5.0.x series only. It is reported, and you can track the bug status at http://bugs.bacula.org/view.php?id=1472 Once the exact problem and official resolution are known, then bug report will be closed and speed bug fixed (or official recommendations given in bug report at URL above). So you could either: 1) put up with slowness until the bug 1472 is resolved and then upgrade and/or follow official recommendations. Perhaps you can even help with it by providing additional information and/or running tests ? 2) downgrade to 3.0.3 which does not have this bug 3) spend some time reading the experiences of others and trying to adopt them to your case 4) give up -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
I mostly use bat for restores (i.e. building restore tree). I did nothing with my tables regarding indexing. I have whatever the bacula scripts create by defaults. In regards to tuning, I did play with changing the join and sort buffer sizes. I found a 'slight' increase in performance. By slight, I mean something like 4.5 vs 4.6 minutes for the same restore. Stephen On 4/14/10 6:29 AM, Koldo Santisteban wrote: Thanks for your answers. Stephen, do you use bconsole or bat? Perhaps the issue is only on bat. I recognize that i only use bweb and bat (on windows). Regarding your comments Stephen, my bacula server is smaller than yours, but my catalog size was 400 Mb (Baculas is working since 2 months ago). I don´t tune my database, but with 3.0.3 version thats wasn´t neccesary. Wich parameters are recommending to tune? This info i think that is very useful for people with the same issue like me... i see that some people says that the better way is creating new indexes(someone says that this is the worst option), others say to custom mysql parameters...but i can´t find any official info, and, at less in my case, i don´t have enough time(and knowledge) to testing bacula with some new indexes, or customizing mysql/postgre... I miss this offcial info... Regards On Tue, Apr 13, 2010 at 7:36 PM, Thomas Mueller tho...@chaschperli.ch mailto:tho...@chaschperli.ch wrote: Am Tue, 13 Apr 2010 15:59:25 +0200 schrieb Koldo Santisteban: thanks for your answer. The first stage was with mysql 5.0.77, and works with bacula 3.0.3 without problems. I have used the same database and server with bacula 5.0.1. The bacula server + DB is a 3,5 Gb Ram with a Xeon processor. I have tested my environment installing postgree on the same server and with a empty db. I create full bacula server backup and then try to restore. I have detected that the restore process works fine usgin bweb and bacula 5.0.1, what is the difference between bat and bweb? noticed too, bat takes forever on building trees on restores. bconsole is _much_ faster. - Thomas -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net mailto:Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Stephen Thompson Berkeley Seismological Laboratory step...@seismo.berkeley.edu215 McCone Hall # 4760 404.538.7077 (phone) University of California, Berkeley 510.643.5811 (fax) Berkeley, CA 94720-4760 -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Stephen, do you use bat or bconsole? Perhaps the issue os on bat console... El 13 de abr de 2010, 7:37 p.m., Thomas Mueller tho...@chaschperli.ch escribió: Am Tue, 13 Apr 2010 15:59:25 +0200 schrieb Koldo Santisteban: thanks for your answer. The first stage was with mysql 5.0.77, and works with bacula 3.0.3 ... noticed too, bat takes forever on building trees on restores. bconsole is _much_ faster. - Thomas -- Download Intel#174... -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Hello Stephan, Thanks for your feeback, sometime, few users have performance problem due to bad settings and can give bad advise or the impression that nothing can be done whereas it works well on ten of thousand of silent users. In this case the performance problem is due to the presence of bad and useless indexes on the database. Just use the default schema, don't add anything, and it will work like a charm (proper my.cnf could also help of course). With those extra indexes, it was looking to work with 3.0.3, but not with 5.0.x. anymore. IMHO, MySQL (and probably PostgreSQL) try to use them for the new query and performances are bad (yes indexes aren't always the solution to speedup database performance). When I see sites like yours that run very big jobs in few minutes, and others that should wait days the difference has big chance to come from configuration mistake and extra tuning... In this kind of situation, the best way to avoid problems is asking for profesionnal help. In your case, if you want to wait only a couple of seconds (instead of minutes), you can think to switch on PostgreSQL, but it doesn't look like to be a problem for you :-) Anyway if you give only a couple of bytes to run sort and join operations (default settings) or you add bad indexes, performance will be bad. Thanks for using Bacula, and feel free to fill a testimonial on www.bacula.org, your setup looks to be very interesting! Bye Stephen Thompson wrote: Heya, This response isn't advice, just a statement about how 5.0.1 is working out for me in a mysql environment. I've read a lot of concerns out there and like you say, there are issues, but no magic bullet, so I too am trying to understand the situation so as not to create problems for myself now or down the road. My catalog... mysql-5.0.77 (64bit) MyISAM 210Gb in size 1,412,297,215 records in File table note: database built with bacula 2x scripts, upgraded with 3x scripts, then again with 5x scripts (i.e. nothing customized along the way) My OS hardware for bacula DIR+SD server... Centos 5.4 (fully patched) 8Gb RAM 2Gb Swap 1Tb EXT3 filesystem on external fiber RAID5 array (dedicated to database, incl. temp files) 2 dual-core [AMD Opteron(tm) Processor 2220] CPUs My experience... I was worried when I migrated to 5.x because I had heard of slow mysql issues, but building the most recent restore tree for a 1.23Tb 6,578,204-files FileSet takes just under 5 minutes. That response time seems reasonable and acceptable to me. Hope this info helps at least define what's possible -- would be nice to see more stats about the bacula community in general. Good luck! thanks, Stephen On 04/13/2010 05:42 AM, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards -- View this message in context: http://old.nabble.com/bacula-5.0.1-and-db-issues---please%2C-share-your-experience-tp28229717p28247718.html Sent from the Bacula - Users mailing list archive at Nabble.com. -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] bacula 5.0.1 and db issues - please, share your experience
Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
On Tue, Apr 13, 2010 at 02:42:15PM +0200, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards Hello, I had similar problems with virtual and accurate backups until I made sure that the indexes on my mysql database were the bacula defaults. In particular, I had these indexes on my File table: JobId JobId, PathId, FilenameId PathId FilenameId Once I had removed the PathId and FilenameId indexes, my queries changed from taking many hours to taking about a second. To check these on your database: Log into mysql. use bacula; show indexes from File; If you have any extra indexes, you can drop them like this: drop index index name on File; e.g: drop index PathId on File; If you need to add one: create index index name on File (list of fields); e.g: create index JobId on File (JobId); -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
On Tue, Apr 13, 2010 at 02:03:39PM +0100, Graham Keeling wrote: On Tue, Apr 13, 2010 at 02:42:15PM +0200, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards Hello, I had similar problems with virtual and accurate backups until I made sure that the indexes on my mysql database were the bacula defaults. In particular, I had these indexes on my File table: JobId JobId, PathId, FilenameId PathId FilenameId Once I had removed the PathId and FilenameId indexes, my queries changed from taking many hours to taking about a second. For clarity, I now have these indexes on my File table: JobId JobId, PathId, FilenameId To check these on your database: Log into mysql. use bacula; show indexes from File; If you have any extra indexes, you can drop them like this: drop index index name on File; e.g: drop index PathId on File; If you need to add one: create index index name on File (list of fields); e.g: create index JobId on File (JobId); -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
On Tue, Apr 13, 2010 at 02:42:15PM +0200, Koldo Santisteban wrote: Hello I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this Could you share more info on your environment ? MySQL version (and specific settings like MyISAM/InnoDB buffers etc), hardware specs, number of records in various tables (count(*) in File, Job, Path, Filename...) http://wiki.bacula.org/doku.php?id=faq#jobs_with_accurate_filesets_take_forever_deprecated http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog If you look into: http://bugs.bacula.org/view.php?id=1472 it seems that the issue is only with bacula 5 series; bacula 3.0.3 and lower are *much* faster (less then 10 minutes instead of 20+ hours). The bug is being looked into currently. Apart from this bug, there is also a discussion on this list with subject VirtualFull mysql query blocks other jobs for a?long time which tackles wider SQL issues... issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Again, more data ? PostgreSQL version ? is it any faster (it should be about 600% faster on same datasets according to links above) or not ? Perhaps it works better, but, in my opnion, this is not serios on a production environment. I agree. Apart from downgrading (which could be quite ok for you, but it was not possible for us for other issues, like bug 1528) you could reduce your file retention (it helps greatly) Anyway, if you can, downgrade to 3.0.3; if you can't, look into bug 1472 and share your info. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. see the http://bugs.bacula.org/view.php?id=1472 for my info, and also for things that fix it (like undefining new_db_get_file_list in sql_get.c) -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards You need to tune your database. Is it on a fast raid 5, 10, or 6? How many files are in your dataset? Do you have 8GB of ram on your database server or more? How big is your postgres database? I have between 8 and 10 million files in my 30GB postgres database for bacula and restore times are nothing like this. My database server is 5 years old. Although it is on a different machine than the director. John -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula 5.0.1 and db issues - please, share your experience
thanks for your answer. The first stage was with mysql 5.0.77, and works with bacula 3.0.3 without problems. I have used the same database and server with bacula 5.0.1. The bacula server + DB is a 3,5 Gb Ram with a Xeon processor. I have tested my environment installing postgree on the same server and with a empty db. I create full bacula server backup and then try to restore. I have detected that the restore process works fine usgin bweb and bacula 5.0.1, what is the difference between bat and bweb? On Tue, Apr 13, 2010 at 3:10 PM, John Drescher dresche...@gmail.com wrote: I am working with bacula 5.0.1. On first stage i setup the server with bacula 5.0.1 and Mysql, but, when i need to restore i have found that the bulid tree process take 10-12 hours (or more). I have read all about this issues and i can see that no exists any magic solution. In order to solve it, i have migrate from mysql to postgre, but i can see the same symtopms. Perhaps it works better, but, in my opnion, this is not serios on a production environment. If it´s possible, i will appreciate it people share their experience with bacula last version and this kind of issues. A couple of month ago i finished to deploy bacula on my environment, but now, i am considering to go back all. Please any comment regarding this case is welcome. Regards You need to tune your database. Is it on a fast raid 5, 10, or 6? How many files are in your dataset? Do you have 8GB of ram on your database server or more? How big is your postgres database? I have between 8 and 10 million files in my 30GB postgres database for bacula and restore times are nothing like this. My database server is 5 years old. Although it is on a different machine than the director. John -- Download Intel#174; Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users