|
Re: [Bacula-users] Bacula MySQL Agressive Tuning
2014-12-22 11:06:59
On 12/21/2014 11:17 AM, D S wrote:
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:
------------------------------------------------------------------------------
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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
|
|
