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>
> 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
> >>> 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
> >
>
>
>
> ------------------------------------------------------------------------------
> 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
>
--
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
