>What modification did you do at your my.conf ?
key_buffer_size = 16M
max_allowed_packet = 1M
thread_stack = 256K
thread_cache_size = 8
innodb_buffer_pool_size = 512M
table_open_cache = 10000
open-files-limit = 50000
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
>The performance issues are only during inserting or even during selecting?
Yes but not so huge, selecting takes about 10-20 minutes.
>Have you got concurrent jobs?
Yes, 3 concurrent jobs but only one is inserting at a time (because of DB locking).
>What's your hardware configuration? I'm particularly interested in hard
>drives bus, rpm and raid settings.
- SATA controller: Intel Corporation 82801JI (ICH10 Family) SATA AHCI Controller
- 2x WD VelociRaptor 150 GB, 10 000 rpm
- linux SW RAID 1
- 8 GB RAM
>
>
>
>
>2013/8/16 Kern Sibbald <kern@...>
>
>> Oh, I must have missed the part about 7 million files. In that case,
>> you will need a well tuned DB, and personally, I would use Postgres.
>> 5 hours seems to me much too long for the insert. I am sure that
>> DB tuning will make a big difference -- maybe as much as a factor
>> of 10.
>>
>> Kern
>>
>> On 08/16/2013 03:01 PM, azurIt wrote:
>> > Hi,
>> >
>> > i'm not using unmodified MySQL configuration. My Bacula database has
>> about 21 GB and i'm mainly having problems with inserting into File table
>> after the virtual full backup of our e-mail server. Job has more then 7 000
>> 000 of files and insert takes about 5 hours. Just to explain why i'm
>> searching how to speed up things without HW upgrades.
>> >
>> > azur
>> >
>> >
>> >
>> >
>> > ______________________________________________________________
>> >> Hello,
>> >>
>> >> Thanks for your question. I have asked my database expert who
>> >> says the same as I do but more in detail. I will include his response
>> >> below.
>> >>
>> >> Bottom line: we have spent a long time determining the best indexes
>> >> for Bacula, which are the ones we release in the code, so we do not
>> >> recommend making any changes. That said, we have not tested removing
>> >> the "extra" index for several years and it may be that database engines
>> >> have evolved. The problem with changing the Bacula default index setup
>> >> is that it may perform well in the beginning, but seemingly simple
>> >> changes can have big surprises in little used queries that are
>> >> seldom used, but by removing or changing
>> >> an index you can make a difference of a factor of 1000, which means that
>> >> an unusual query that runs rather quickly can become a real bottleneck.
>> >>
>> >> So change indexes at your own risk.
>> >>
>> >> Probably you are using an untunned MySQL with the default conf (my.cnf)
>> >> file rather than one of the examples for larger databases.
>> >> By proper tunning you can significantly improve the performance.
>> >> If you have a big database say bigger
>> >> than 1 or 2 GB, our experience is that you will get *much* better
>> >> performance
>> >> with PostgreSQL, providing you tune the conf parameters correctly (out
>> >> of the
>> >> box, the Postgres conf is a real bummer).
>> >>
>> >> Best regards,
>> >> Kern
>> >>
>> >> === email from someone who knows SQL better than I do ====
>> >>
>> >> The single index on JobId is compact, the other is very large, I guess
>> the
>> >> scanning time is not exactly the same (i.e. the single index on JobId
>> can
>> >> be much faster)
>> >>
>> >> Yes, Postgresql can use composed index and may not require it, but for
>> >> MySQL, I have some doubt if it can be used all the time (maybe recent
>> >> versions). It is hard to read their execution plan to be sure how
>> >> they use it.
>> >>
>> >> This user could do some tests, it's rather simple to drop
>> >> the index and run a large restore on a big database (probably not
>> >> interesting if the File table contains less than 300,000,000 records).
>> >>
>> >> If we have a bit of time one day, it might be good to test this
>> >> modification on large and "recent" databases.
>> >>
>> >> Anyway, if this user is using MySQL AND has performance problems
>> >> during insert, he probably didn't modify the default my.cnf.
>> >>
>> >> ===================== end included email ==============================
>> >>
>> >>
>> >>
>> >> On 08/16/2013 01:30 PM, azurIt wrote:
>> >>> Hi,
>> >>>
>> >>> i'm having some MySQL performance difficulties so i started to search
>> what can i do better. My table 'File' had these indexes created:
>> >>> CREATE INDEX file_jobid_idx on File (JobId);
>> >>> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId);
>> >>>
>> >>> Which looks correct according to documentation:
>> >>> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html
>> >>>
>> >>> BUT! The first index apperas to be unneeded as it's part of the second
>> index. According to MySQL documentation, 'any leftmost prefix of the index
>> can be used by the optimizer to find row':
>> >>> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
>> >>>
>> >>> I suggest to remove it.
>> >>>
>> >>> The same applies also for PostgreSQL:
>> >>>
>> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html
>> >>>
>> >>> azur
>> >>>
>> >>>
>> ------------------------------------------------------------------------------
>> >>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
>> >>> It's a free troubleshooting tool designed for production.
>> >>> Get down to code-level detail for bottlenecks, with <2% overhead.
>> >>> Download for free and get started troubleshooting in minutes.
>> >>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>> >>> _______________________________________________
>> >>> Bacula-devel mailing list
>> >>> Bacula-devel@...
>> >>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>> >>>
>> >>
>> >
>> ------------------------------------------------------------------------------
>> > Get 100% visibility into Java/.NET code with AppDynamics Lite!
>> > It's a free troubleshooting tool designed for production.
>> > Get down to code-level detail for bottlenecks, with <2% overhead.
>> > Download for free and get started troubleshooting in minutes.
>> >
>> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Bacula-devel mailing list
>> > Bacula-devel@...
>> > https://lists.sourceforge.net/lists/listinfo/bacula-devel
>> >
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
>> It's a free troubleshooting tool designed for production.
>> Get down to code-level detail for bottlenecks, with <2% overhead.
>> Download for free and get started troubleshooting in minutes.
>> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Bacula-devel mailing list
>> Bacula-devel@...
>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>>
>
>
>
>--
>
> Please consider the environment before printing this email
>

My own database is 2G. But in testing Bacula I tested
backups of 10 Million files, which is a relatively large number
for a single backup, but really big backups range from
20 Million to 40 Million files. My personal database was
MySQL for something like 12 years, but for the last
couple I have been using Postgres, and I do most of
my testing on Postgres DBs.
Making Postgres perform really well is a science and an art as
for best performance it needs really fast disks, but I leave those
discussions for the experts. I just program Bacula -- I am not
even very good at even answering support questions since I
do not work daily on support.
The Enterprise version has a lot of improvements and tuning for large
databases that we see our customers use, and these will over time
filter back to the community version.
Regards,
Kern
On 08/16/2013 07:28 PM, stefano scotti wrote:
> That is a really large database :)
>
> What modification did you do at your my.conf ?
> The performance issues are only during inserting or even during selecting?
> Have you got concurrent jobs?
> What's your hardware configuration? I'm particularly interested in
> hard drives bus, rpm and raid settings.
>
>
>
>
> 2013/8/16 Kern Sibbald <kern@... <mailto:kern@...>>
>
> Oh, I must have missed the part about 7 million files. In that case,
> you will need a well tuned DB, and personally, I would use Postgres.
> 5 hours seems to me much too long for the insert. I am sure that
> DB tuning will make a big difference -- maybe as much as a factor
> of 10.
>
> Kern
>
> On 08/16/2013 03:01 PM, azurIt wrote:
> > Hi,
> >
> > i'm not using unmodified MySQL configuration. My Bacula database
> has about 21 GB and i'm mainly having problems with inserting into
> File table after the virtual full backup of our e-mail server. Job
> has more then 7 000 000 of files and insert takes about 5 hours.
> Just to explain why i'm searching how to speed up things without
> HW upgrades.
> >
> > azur
> >
> >
> >
> >
> > ______________________________________________________________
> >> Hello,
> >>
> >> Thanks for your question. I have asked my database expert who
> >> says the same as I do but more in detail. I will include his
> response
> >> below.
> >>
> >> Bottom line: we have spent a long time determining the best indexes
> >> for Bacula, which are the ones we release in the code, so we do not
> >> recommend making any changes. That said, we have not tested
> removing
> >> the "extra" index for several years and it may be that database
> engines
> >> have evolved. The problem with changing the Bacula default
> index setup
> >> is that it may perform well in the beginning, but seemingly simple
> >> changes can have big surprises in little used queries that are
> >> seldom used, but by removing or changing
> >> an index you can make a difference of a factor of 1000, which
> means that
> >> an unusual query that runs rather quickly can become a real
> bottleneck.
> >>
> >> So change indexes at your own risk.
> >>
> >> Probably you are using an untunned MySQL with the default conf
> (my.cnf)
> >> file rather than one of the examples for larger databases.
> >> By proper tunning you can significantly improve the performance.
> >> If you have a big database say bigger
> >> than 1 or 2 GB, our experience is that you will get *much* better
> >> performance
> >> with PostgreSQL, providing you tune the conf parameters
> correctly (out
> >> of the
> >> box, the Postgres conf is a real bummer).
> >>
> >> Best regards,
> >> Kern
> >>
> >> === email from someone who knows SQL better than I do ====
> >>
> >> The single index on JobId is compact, the other is very large,
> I guess the
> >> scanning time is not exactly the same (i.e. the single index on
> JobId can
> >> be much faster)
> >>
> >> Yes, Postgresql can use composed index and may not require it,
> but for
> >> MySQL, I have some doubt if it can be used all the time (maybe
> recent
> >> versions). It is hard to read their execution plan to be sure how
> >> they use it.
> >>
> >> This user could do some tests, it's rather simple to drop
> >> the index and run a large restore on a big database (probably not
> >> interesting if the File table contains less than 300,000,000
> records).
> >>
> >> If we have a bit of time one day, it might be good to test this
> >> modification on large and "recent" databases.
> >>
> >> Anyway, if this user is using MySQL AND has performance problems
> >> during insert, he probably didn't modify the default my.cnf.
> >>
> >> ===================== end included email
> ==============================
> >>
> >>
> >>
> >> On 08/16/2013 01:30 PM, azurIt wrote:
> >>> Hi,
> >>>
> >>> i'm having some MySQL performance difficulties so i started to
> search what can i do better. My table 'File' had these indexes
> created:
> >>> CREATE INDEX file_jobid_idx on File (JobId);
> >>> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId);
> >>>
> >>> Which looks correct according to documentation:
> >>>
> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html
> >>>
> >>> BUT! The first index apperas to be unneeded as it's part of
> the second index. According to MySQL documentation, 'any leftmost
> prefix of the index can be used by the optimizer to find row':
> >>>
> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
> >>>
> >>> I suggest to remove it.
> >>>
> >>> The same applies also for PostgreSQL:
> >>>
> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html
> >>>
> >>> azur
> >>>
> >>>
> ------------------------------------------------------------------------------
> >>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> >>> It's a free troubleshooting tool designed for production.
> >>> Get down to code-level detail for bottlenecks, with <2% overhead.
> >>> Download for free and get started troubleshooting in minutes.
> >>>
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> >>> _______________________________________________
> >>> Bacula-devel mailing list
> >>> Bacula-devel@...
> <mailto:Bacula-devel@...>
> >>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
> >>>
> >>
> >
> ------------------------------------------------------------------------------
> > Get 100% visibility into Java/.NET code with AppDynamics Lite!
> > It's a free troubleshooting tool designed for production.
> > Get down to code-level detail for bottlenecks, with <2% overhead.
> > Download for free and get started troubleshooting in minutes.
> >
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Bacula-devel mailing list
> > Bacula-devel@...
> <mailto:Bacula-devel@...>
> > https://lists.sourceforge.net/lists/listinfo/bacula-devel
> >
>
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> _______________________________________________
> Bacula-devel mailing list
> Bacula-devel@...
> <mailto:Bacula-devel@...>
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>
>
>
>
> --
> Please consider the environment before printing this email
>
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>
>
> _______________________________________________
> Bacula-devel mailing list
> Bacula-devel@...
> https://lists.sourceforge.net/lists/listinfo/bacula-devel

That is a really large database :)
What modification did you do at your my.conf ?
The performance issues are only during inserting or even during selecting?
Have you got concurrent jobs?
What's your hardware configuration? I'm particularly interested in hard
drives bus, rpm and raid settings.
2013/8/16 Kern Sibbald <kern@...>
> Oh, I must have missed the part about 7 million files. In that case,
> you will need a well tuned DB, and personally, I would use Postgres.
> 5 hours seems to me much too long for the insert. I am sure that
> DB tuning will make a big difference -- maybe as much as a factor
> of 10.
>
> Kern
>
> On 08/16/2013 03:01 PM, azurIt wrote:
> > Hi,
> >
> > i'm not using unmodified MySQL configuration. My Bacula database has
> about 21 GB and i'm mainly having problems with inserting into File table
> after the virtual full backup of our e-mail server. Job has more then 7 000
> 000 of files and insert takes about 5 hours. Just to explain why i'm
> searching how to speed up things without HW upgrades.
> >
> > azur
> >
> >
> >
> >
> > ______________________________________________________________
> >> Hello,
> >>
> >> Thanks for your question. I have asked my database expert who
> >> says the same as I do but more in detail. I will include his response
> >> below.
> >>
> >> Bottom line: we have spent a long time determining the best indexes
> >> for Bacula, which are the ones we release in the code, so we do not
> >> recommend making any changes. That said, we have not tested removing
> >> the "extra" index for several years and it may be that database engines
> >> have evolved. The problem with changing the Bacula default index setup
> >> is that it may perform well in the beginning, but seemingly simple
> >> changes can have big surprises in little used queries that are
> >> seldom used, but by removing or changing
> >> an index you can make a difference of a factor of 1000, which means that
> >> an unusual query that runs rather quickly can become a real bottleneck.
> >>
> >> So change indexes at your own risk.
> >>
> >> Probably you are using an untunned MySQL with the default conf (my.cnf)
> >> file rather than one of the examples for larger databases.
> >> By proper tunning you can significantly improve the performance.
> >> If you have a big database say bigger
> >> than 1 or 2 GB, our experience is that you will get *much* better
> >> performance
> >> with PostgreSQL, providing you tune the conf parameters correctly (out
> >> of the
> >> box, the Postgres conf is a real bummer).
> >>
> >> Best regards,
> >> Kern
> >>
> >> === email from someone who knows SQL better than I do ====
> >>
> >> The single index on JobId is compact, the other is very large, I guess
> the
> >> scanning time is not exactly the same (i.e. the single index on JobId
> can
> >> be much faster)
> >>
> >> Yes, Postgresql can use composed index and may not require it, but for
> >> MySQL, I have some doubt if it can be used all the time (maybe recent
> >> versions). It is hard to read their execution plan to be sure how
> >> they use it.
> >>
> >> This user could do some tests, it's rather simple to drop
> >> the index and run a large restore on a big database (probably not
> >> interesting if the File table contains less than 300,000,000 records).
> >>
> >> If we have a bit of time one day, it might be good to test this
> >> modification on large and "recent" databases.
> >>
> >> Anyway, if this user is using MySQL AND has performance problems
> >> during insert, he probably didn't modify the default my.cnf.
> >>
> >> ===================== end included email ==============================
> >>
> >>
> >>
> >> On 08/16/2013 01:30 PM, azurIt wrote:
> >>> Hi,
> >>>
> >>> i'm having some MySQL performance difficulties so i started to search
> what can i do better. My table 'File' had these indexes created:
> >>> CREATE INDEX file_jobid_idx on File (JobId);
> >>> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId);
> >>>
> >>> Which looks correct according to documentation:
> >>> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html
> >>>
> >>> BUT! The first index apperas to be unneeded as it's part of the second
> index. According to MySQL documentation, 'any leftmost prefix of the index
> can be used by the optimizer to find row':
> >>> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
> >>>
> >>> I suggest to remove it.
> >>>
> >>> The same applies also for PostgreSQL:
> >>>
> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html
> >>>
> >>> azur
> >>>
> >>>
> ------------------------------------------------------------------------------
> >>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> >>> It's a free troubleshooting tool designed for production.
> >>> Get down to code-level detail for bottlenecks, with <2% overhead.
> >>> Download for free and get started troubleshooting in minutes.
> >>>
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> >>> _______________________________________________
> >>> Bacula-devel mailing list
> >>> Bacula-devel@...
> >>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
> >>>
> >>
> >
> ------------------------------------------------------------------------------
> > Get 100% visibility into Java/.NET code with AppDynamics Lite!
> > It's a free troubleshooting tool designed for production.
> > Get down to code-level detail for bottlenecks, with <2% overhead.
> > Download for free and get started troubleshooting in minutes.
> >
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Bacula-devel mailing list
> > Bacula-devel@...
> > https://lists.sourceforge.net/lists/listinfo/bacula-devel
> >
>
>
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> _______________________________________________
> Bacula-devel mailing list
> Bacula-devel@...
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>
--
Please consider the environment before printing this email

Oh, I must have missed the part about 7 million files. In that case,
you will need a well tuned DB, and personally, I would use Postgres.
5 hours seems to me much too long for the insert. I am sure that
DB tuning will make a big difference -- maybe as much as a factor
of 10.
Kern
On 08/16/2013 03:01 PM, azurIt wrote:
> Hi,
>
> i'm not using unmodified MySQL configuration. My Bacula database has about 21 GB and i'm mainly having problems with inserting into File table after the virtual full backup of our e-mail server. Job has more then 7 000 000 of files and insert takes about 5 hours. Just to explain why i'm searching how to speed up things without HW upgrades.
>
> azur
>
>
>
>
> ______________________________________________________________
>> Hello,
>>
>> Thanks for your question. I have asked my database expert who
>> says the same as I do but more in detail. I will include his response
>> below.
>>
>> Bottom line: we have spent a long time determining the best indexes
>> for Bacula, which are the ones we release in the code, so we do not
>> recommend making any changes. That said, we have not tested removing
>> the "extra" index for several years and it may be that database engines
>> have evolved. The problem with changing the Bacula default index setup
>> is that it may perform well in the beginning, but seemingly simple
>> changes can have big surprises in little used queries that are
>> seldom used, but by removing or changing
>> an index you can make a difference of a factor of 1000, which means that
>> an unusual query that runs rather quickly can become a real bottleneck.
>>
>> So change indexes at your own risk.
>>
>> Probably you are using an untunned MySQL with the default conf (my.cnf)
>> file rather than one of the examples for larger databases.
>> By proper tunning you can significantly improve the performance.
>> If you have a big database say bigger
>> than 1 or 2 GB, our experience is that you will get *much* better
>> performance
>> with PostgreSQL, providing you tune the conf parameters correctly (out
>> of the
>> box, the Postgres conf is a real bummer).
>>
>> Best regards,
>> Kern
>>
>> === email from someone who knows SQL better than I do ====
>>
>> The single index on JobId is compact, the other is very large, I guess the
>> scanning time is not exactly the same (i.e. the single index on JobId can
>> be much faster)
>>
>> Yes, Postgresql can use composed index and may not require it, but for
>> MySQL, I have some doubt if it can be used all the time (maybe recent
>> versions). It is hard to read their execution plan to be sure how
>> they use it.
>>
>> This user could do some tests, it's rather simple to drop
>> the index and run a large restore on a big database (probably not
>> interesting if the File table contains less than 300,000,000 records).
>>
>> If we have a bit of time one day, it might be good to test this
>> modification on large and "recent" databases.
>>
>> Anyway, if this user is using MySQL AND has performance problems
>> during insert, he probably didn't modify the default my.cnf.
>>
>> ===================== end included email ==============================
>>
>>
>>
>> On 08/16/2013 01:30 PM, azurIt wrote:
>>> Hi,
>>>
>>> i'm having some MySQL performance difficulties so i started to search what can i do better. My table 'File' had these indexes created:
>>> CREATE INDEX file_jobid_idx on File (JobId);
>>> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId);
>>>
>>> Which looks correct according to documentation:
>>> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html
>>>
>>> BUT! The first index apperas to be unneeded as it's part of the second index. According to MySQL documentation, 'any leftmost prefix of the index can be used by the optimizer to find row':
>>> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
>>>
>>> I suggest to remove it.
>>>
>>> The same applies also for PostgreSQL:
>>> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html
>>>
>>> azur
>>>
>>> ------------------------------------------------------------------------------
>>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
>>> It's a free troubleshooting tool designed for production.
>>> Get down to code-level detail for bottlenecks, with <2% overhead.
>>> Download for free and get started troubleshooting in minutes.
>>> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Bacula-devel mailing list
>>> Bacula-devel@...
>>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>>>
>>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> _______________________________________________
> Bacula-devel mailing list
> Bacula-devel@...
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>

Hi,
i'm not using unmodified MySQL configuration. My Bacula database has about 21 GB and i'm mainly having problems with inserting into File table after the virtual full backup of our e-mail server. Job has more then 7 000 000 of files and insert takes about 5 hours. Just to explain why i'm searching how to speed up things without HW upgrades.
azur
______________________________________________________________
>Hello,
>
>Thanks for your question. I have asked my database expert who
>says the same as I do but more in detail. I will include his response
>below.
>
>Bottom line: we have spent a long time determining the best indexes
>for Bacula, which are the ones we release in the code, so we do not
>recommend making any changes. That said, we have not tested removing
>the "extra" index for several years and it may be that database engines
>have evolved. The problem with changing the Bacula default index setup
>is that it may perform well in the beginning, but seemingly simple
>changes can have big surprises in little used queries that are
>seldom used, but by removing or changing
>an index you can make a difference of a factor of 1000, which means that
>an unusual query that runs rather quickly can become a real bottleneck.
>
>So change indexes at your own risk.
>
>Probably you are using an untunned MySQL with the default conf (my.cnf)
>file rather than one of the examples for larger databases.
>By proper tunning you can significantly improve the performance.
>If you have a big database say bigger
>than 1 or 2 GB, our experience is that you will get *much* better
>performance
>with PostgreSQL, providing you tune the conf parameters correctly (out
>of the
>box, the Postgres conf is a real bummer).
>
>Best regards,
>Kern
>
>=== email from someone who knows SQL better than I do ====
>
>The single index on JobId is compact, the other is very large, I guess the
>scanning time is not exactly the same (i.e. the single index on JobId can
>be much faster)
>
>Yes, Postgresql can use composed index and may not require it, but for
>MySQL, I have some doubt if it can be used all the time (maybe recent
>versions). It is hard to read their execution plan to be sure how
>they use it.
>
>This user could do some tests, it's rather simple to drop
>the index and run a large restore on a big database (probably not
>interesting if the File table contains less than 300,000,000 records).
>
>If we have a bit of time one day, it might be good to test this
>modification on large and "recent" databases.
>
>Anyway, if this user is using MySQL AND has performance problems
>during insert, he probably didn't modify the default my.cnf.
>
>===================== end included email ==============================
>
>
>
>On 08/16/2013 01:30 PM, azurIt wrote:
>> Hi,
>>
>> i'm having some MySQL performance difficulties so i started to search what can i do better. My table 'File' had these indexes created:
>> CREATE INDEX file_jobid_idx on File (JobId);
>> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId);
>>
>> Which looks correct according to documentation:
>> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html
>>
>> BUT! The first index apperas to be unneeded as it's part of the second index. According to MySQL documentation, 'any leftmost prefix of the index can be used by the optimizer to find row':
>> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
>>
>> I suggest to remove it.
>>
>> The same applies also for PostgreSQL:
>> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html
>>
>> azur
>>
>> ------------------------------------------------------------------------------
>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
>> It's a free troubleshooting tool designed for production.
>> Get down to code-level detail for bottlenecks, with <2% overhead.
>> Download for free and get started troubleshooting in minutes.
>> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Bacula-devel mailing list
>> Bacula-devel@...
>> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>>
>
>

Hello,
Thanks for your question. I have asked my database expert who
says the same as I do but more in detail. I will include his response
below.
Bottom line: we have spent a long time determining the best indexes
for Bacula, which are the ones we release in the code, so we do not
recommend making any changes. That said, we have not tested removing
the "extra" index for several years and it may be that database engines
have evolved. The problem with changing the Bacula default index setup
is that it may perform well in the beginning, but seemingly simple
changes can have big surprises in little used queries that are
seldom used, but by removing or changing
an index you can make a difference of a factor of 1000, which means that
an unusual query that runs rather quickly can become a real bottleneck.
So change indexes at your own risk.
Probably you are using an untunned MySQL with the default conf (my.cnf)
file rather than one of the examples for larger databases.
By proper tunning you can significantly improve the performance.
If you have a big database say bigger
than 1 or 2 GB, our experience is that you will get *much* better
performance
with PostgreSQL, providing you tune the conf parameters correctly (out
of the
box, the Postgres conf is a real bummer).
Best regards,
Kern
=== email from someone who knows SQL better than I do ====
The single index on JobId is compact, the other is very large, I guess the
scanning time is not exactly the same (i.e. the single index on JobId can
be much faster)
Yes, Postgresql can use composed index and may not require it, but for
MySQL, I have some doubt if it can be used all the time (maybe recent
versions). It is hard to read their execution plan to be sure how
they use it.
This user could do some tests, it's rather simple to drop
the index and run a large restore on a big database (probably not
interesting if the File table contains less than 300,000,000 records).
If we have a bit of time one day, it might be good to test this
modification on large and "recent" databases.
Anyway, if this user is using MySQL AND has performance problems
during insert, he probably didn't modify the default my.cnf.
===================== end included email ==============================
On 08/16/2013 01:30 PM, azurIt wrote:
> Hi,
>
> i'm having some MySQL performance difficulties so i started to search what can i do better. My table 'File' had these indexes created:
> CREATE INDEX file_jobid_idx on File (JobId);
> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId);
>
> Which looks correct according to documentation:
> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html
>
> BUT! The first index apperas to be unneeded as it's part of the second index. According to MySQL documentation, 'any leftmost prefix of the index can be used by the optimizer to find row':
> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
>
> I suggest to remove it.
>
> The same applies also for PostgreSQL:
> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html
>
> azur
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> _______________________________________________
> Bacula-devel mailing list
> Bacula-devel@...
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>

Hello Arkadiusz,
These are nice ideas, and I will certainly accept your patch, but there are
a few items to work out:
1. Please go to http://www.bacula.org and click on the FLA License in the left
menu, print it, fill it out, sign it, and send it to me. This
permits Bacula
and myself to use the code you submit.
2. Please read our Developer's guide that gives you an idea how we
indent and
use braces {} in expressions. This is not a critical item, but we
are very
determined to keep the code in a uniform style, so it helps if you
submit
with the correct style.
3. For backward compatibility, we will need to make sure the default
behavior
of the FD is the same as before rather than the new behavior that
you have
defined. This is important.
4. I would probably call your new directive: Allow RunScripts, and it
would by
default be turned on. As with item 2, this is a minor point that
is easy for
us to correct in integrating it, but can help you for this and
future contributions.
I am waiting to receive the FLA,
Best regards,
Kern
On 08/12/2013 09:38 AM, Arkadiusz Bubała wrote:
> Hello,
> we've prepared patch to add option to disable RunScript functionality
> in Bacula File Daemon. The goal was that the user can't execute script
> on the client side for the security reasons. New configuration option
> has been called "AllowScripts" and if it is set to "yes" Bacula File
> Daemon is able to execute scripts. By default this option is set to
> "no" so when the user tries to execute script he'll get an error:
> "2999 Bad Executing scripts is not allowed".
>
> We have also other idea of giving configurable restrictions in Bacula
> client for backup and restore. We would like to give opportunity of
> configure what directories will be accessible by server for making
> backup from. This can be achieved by adding option in Bacula client
> configuration file that will store regular expression describing what
> paths can be backed up. We would like to add this configuration option
> also for restore. To let restore backups only in certain paths
> described by regular expressions. Those changes are very useful in our
> environment where we would like to integrate Bacula client. We won't
> let backup internal system files, only user data can be backed up.
> Also restore should be possible only in space separated for user. We
> also think that this changes are useful for any Bacula user because it
> is possible to restrict access to certain files on client machine and
> don't allow restore to places where it shouldn't be done. For example
> user can restrict restore only to specified path and make sure that
> some system files won't be changed during restore.
>
>
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>
>
> _______________________________________________
> Bacula-devel mailing list
> Bacula-devel@...
> https://lists.sourceforge.net/lists/listinfo/bacula-devel

Hello,
we've prepared patch to add option to disable RunScript functionality in
Bacula File Daemon. The goal was that the user can't execute script on
the client side for the security reasons. New configuration option has
been called "AllowScripts" and if it is set to "yes" Bacula File Daemon
is able to execute scripts. By default this option is set to "no" so
when the user tries to execute script he'll get an error: "2999 Bad
Executing scripts is not allowed".
We have also other idea of giving configurable restrictions in Bacula
client for backup and restore. We would like to give opportunity of
configure what directories will be accessible by server for making
backup from. This can be achieved by adding option in Bacula client
configuration file that will store regular expression describing what
paths can be backed up. We would like to add this configuration option
also for restore. To let restore backups only in certain paths described
by regular expressions. Those changes are very useful in our environment
where we would like to integrate Bacula client. We won't let backup
internal system files, only user data can be backed up. Also restore
should be possible only in space separated for user. We also think that
this changes are useful for any Bacula user because it is possible to
restrict access to certain files on client machine and don't allow
restore to places where it shouldn't be done. For example user can
restrict restore only to specified path and make sure that some system
files won't be changed during restore.
--
Best regards
Arkadiusz Bubała
Open-E Poland Sp. z o.o.
http://www.open-e.com

Hello,
Some of you may remember that a number of years ago, we held a
Bacula Conference in Yverdon-les-Bains, Switzerland. I really enjoyed
this conference and found it very useful. Since then, we have been
unable to hold anotherone due to lack of resources. I am very pleased
to announce that we are planning a bigger and better conference to be
held in Berlin, Germany on the 21st of March 2014.
This is a conference for all Bacula users as well as Bacula Partners. It
is being sponsored by Bacula Systems, but is oriented specifically towards
the community.
Your presence will be important as it will provide you with insights
into future
plans for the community version; show the best ways to take advantage
- as a community user - of Bacula Enterprise Edition features, outline
the
new Bacula Training program, develop specific technical show cases, provide
you with an update on Open Source directly from theFree Software
Foundation Europe and talk about specific case studies.
I see at least 5 major reasons to attend:
1. To get an update on the community version product strategy, planned
features and input your ideas and needs
2. To hear - from the source - what is going on in Open Source,
with Karsten Gerloff, President of FSFE
3. To meet with me, share experiences with Bacula users,
Bacula Systems customers and Partners
4.To meet with the developers from the community as well as
from Bacula Systems and see how they contribute to the
evolution of Bacula
5. To meet, connect and share while enjoying the dinner offered
by Bacula Systems the evening before the Conference day
Since this is a worldwide event, allconference presentations will be
given in English.
A dinner, sponsored by Bacula Systems, will be offered on the 20th of March
commencing at 8pm. Please, be sure to arrive on time.
Please mark the date of 21 March 2014 (and the evening before for our
dinner)
in your calendars. Shortly, I will send you moreinformation about the
exact conference location, reduced hotels rates, presentations and the
registration process.
I am hoping to meet as many of you as possibleduring this
Bacula Users & Partners Conference,
Meanwhile, I wish you a great summer break!
Kern

Hello,
(this mail goes both to Bacula developers and bacula-server/client
FreeBSD ports maintainer; not sure who can help)
We are successfully using Bacula in our FreeBSD servers for many years
and we are extremely happy with it. FreeBSD ports system has an NLS
switch that can be used to enable/disable NLS support during ports
building and installation (e.g. OPTIONS_UNSET+=NLS in /etc/make.conf
would globally disable NLS support in any port built).
If NLS option is set, then sysutils/bacula-(server|client) ports would
be built with "CONFIGURE_ARGS+=--enable-nls" and a dependency to GNU
gettext. If NLS option is not set, then the ports would be built with
"CONFIGURE_ARGS+=--disable-nls" and *no* gettext dependency.
Running Bacula configure script with "--disable-nls" on a system that
has GNU gettext already installed (that is /usr/local/lib/libintl.so
available in a FreeBSD system with devel/gettext port installed) would
result to "Libraries: -lpthread -lintl" and thus a gettext "stealth"
dependency.
Is this desirable? I thought that "--disable-nls" would result to *no*
gettext dependency regardless gettext availability in the system. We
have bacula-client installed in dozens of our servers using centrally
created packages and skipping a gettext dependency is a must (not to
mention a "stealth" unrecorded dependency).
Regards,
Panagiotis
--
Panagiotis J. Christias Network Management Center
p.christias@... National Technical Univ. of Athens, GREECE