On 12/21/2014 11:17 AM, D S wrote:
Re: [Bacula-users] Bacula MySQL Agressive Tuning
Hello,
just to add one more related question/problem to this thread:
- using MySQL/MyISAM
- about 15M files from several clients
- 2.5-3M files per client
- the problem: the Dir inserting Attributes action takes hours (5-6) -
even more than the VirtualFull takes to complete
- the query (currently) is: INSERT INTO Filename (Name) SELECT a.Name
FROM (SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS (SELECT
Name FROM Filename AS f WHERE f.Name = a.Name)
- the temp table takes 550-600MB in /tmp
On the very first Full backup, "Dir inserting Attributes" has been
completed in just minutes. Increasing this by a factor of 100 for the
next Full means there is an unsuitable configuration in MySQL.
MyISAM "can" be quick at inserting records at the end of a table, but
has both table locking and a single key buffer lock causing contention,
so is much, much slower at inserts that are not at the end of a table.
MyISAM also does not have a change buffer. In short, MyISAM is a poor
choice for an INSERT heavy application like Bacula. Use should use
InnoDB instead. Set innodb_buffer_pool_size to as much memory as you
dare allocate to mysql without affecting other services. Since backups
are INSERT heavy, set innodb_change_buffer_max_size to 50% (the default
is 25%), allowing up to half of the buffer_pool memory to be used for
the change buffer.
I've seen other reports for such behavior but couldn't find any
definitive suggestions on which parameters are important to tweak in
this case and some relations between number of files in DB and
expected MySQL's parameter values.
Thank you
Regards.
Sunday, December 21, 2014, 5:24:00 PM:
Mr. Bacula Users,
What do you think of those MySQL tuning hints for Bacula, specifically
about this innodb_flush_log_at_trx_commit option?
=> http://www.innovation-brigade.com/index.php?module=Content&type=user&func=display&tid=1&pid=2
it seems to improve MySQL attributes despooling time by a 1000 factor,
from hundreds transactions per second to millions.
Regards,
==============================================================================
Heitor Medrado de Faria - LPIC-III | ITIL-F
12 a 23 de janeiro de 2015 - Treinamento Telepresencial Bacula:
http://www.bacula.com.br/?p=2174
61 2021-8260 <tel:%2B55%2061%202021-8260> | 8268-4220
<tel:%2B55%2061%208268-4220>
Site: www.bacula.com.br <http://www.bacula.com.br/> | Facebook:
heitor.faria <http://www.facebook.com/heitor.faria> | Gtalk:
heitorfa...@gmail.com <mailto:heitorfa...@gmail.com>
===============================================================================
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
