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 <k...@sibbald.com <mailto:k...@sibbald.com>>
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@lists.sourceforge.net
<mailto:Bacula-devel@lists.sourceforge.net>
>>> 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@lists.sourceforge.net
<mailto:Bacula-devel@lists.sourceforge.net>
> 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@lists.sourceforge.net
<mailto:Bacula-devel@lists.sourceforge.net>
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@lists.sourceforge.net
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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
