Hello Petar,
I'm sorry I didn't give you details about the best value for '
innodb_autoinc_lock_mode'
to use with bacula catalog.
Having "innodb_autoinc_lock_mode=2" is the best option for performance of bulk
INSERTs (you can see some INSERT...SELECT statements in the logs you sent
here).
I am not a DBA, but I would recommend you to have this value set to 2 for
better performance and avoid the locks/timeouts. Please be careful with
this value if you use replication (quite sure you don't need/use this for
bacula catalog).
The issue you are having is related to attribute insertion in catalog after
job runs. Since you are reporting this when dealing with some heavy
(maybe?) simultaneous full backup jobs, you will need to have your MariaDB
well tuned to get this working.
I would also recommend you to have your files pruned regularly from
catalog, so this file table doesn't grow too much in the case you deal with
millions of files.
You told in one of your emails: "I have enabled batch insert". It is
strongly recommended to build bacula with batch insert enabled and this is
the default in newer versions.
Phil Stracchino gave very good recommendations here:
https://www.mail-archive.com/bacula-users@lists.sourceforge.net/msg61883.html
The user was having a similar issue, please find here the complete thread:
https://www.mail-archive.com/bacula-users@lists.sourceforge.net/msg61787.html
Hope this helps.
Best regards,
Ana
On Thu, Apr 13, 2017 at 10:45 PM, Ana Emília M. Arruda <
emiliaarr...@gmail.com> wrote:
> Hello Petar,
>
> Please have a look here: http://bacula.10910.n7.nabble.com/innodb-autoinc-
> lock-mode-td82493.html
>
> Maybe the script for MySQL tunning can help with MariaDB too:
> https://github.com/major/MySQLTuner-perl
>
> Best regards,
>
> Ana
>
> On Thu, Apr 13, 2017 at 10:09 PM, Petar Kozić <petar.ko...@mint.rs> wrote:
>
>> I using MariaDB 10.1.
>> Which value of innodb_autoinc_lock_mode is best for Bacula, do you know ?
>>
>>
>>
>> *—*
>>
>> *Petar Kozić*
>> System Administrator
>>
>>
>>
>> On April 13, 2017 at 10:04:23 PM, Ana Emília M. Arruda (
>> emiliaarr...@gmail.com) wrote:
>>
>> Hello Petar,
>>
>> Could you please let us know which Bacula/MySQL Database version are you
>> using?
>>
>> Maybe it would be a good idea to have a look at "innodb_autoinc_lock_mode
>> <https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode>
>> " configuration.
>>
>> Best regards,
>>
>> Ana
>>
>>
>>
>> On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <petar.ko...@mint.rs> wrote:
>>
>>> Thank you on your answer. Yes I have about 80GB free in drive where is
>>> SD working directory.
>>>
>>>
>>> *—*
>>>
>>> *Petar Kozić*
>>> System Administrator
>>>
>>>
>>>
>>> On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda (
>>> emiliaarr...@gmail.com) wrote:
>>>
>>> Hi Petar,
>>>
>>> If you are using spool attributes, you need enough space in the SD
>>> working directory to store them before SD send attributes to Director.
>>>
>>> Could you please check if you have enough space in the SD working
>>> directory?
>>>
>>> Best regards,
>>> Ana
>>>
>>> On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <petar.ko...@mint.rs>
>>> wrote:
>>>
>>>> Yes, I have.
>>>>
>>>> I was upload on pasterbin:
>>>> http://pastebin.ca/3794414
>>>>
>>>> Log is contionous with same error.
>>>>
>>>>
>>>> *—*
>>>>
>>>> *Petar Kozić*
>>>> System Administrator
>>>>
>>>>
>>>> On April 12, 2017 at 11:03:51 AM, Josip Deanovic (
>>>> djosip+n...@linuxpages.net) wrote:
>>>>
>>>> On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
>>>> > @Josip Deanovic
>>>> > Are you using spooling?
>>>> > If yes, is your spool directory on the same file system as your
>>>> database
>>>> > data?
>>>> >
>>>> > No, I don’t spooling Data, only Attributes
>>>> >
>>>> > My config:
>>>> > Spool Data = no
>>>> > Spool Attributes = yes
>>>> >
>>>> >
>>>> > I was increase innodb_timewait_lockout = 150 but error appears again.
>>>> >
>>>> > Some ideas ?
>>>>
>>>> Do you have anything useful in your mysql logs?
>>>>
>>>> --
>>>> Josip Deanovic
>>>>
>>>> ------------------------------------------------------------
>>>> ------------------
>>>> Check out the vibrant tech community on one of the world's most
>>>> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>>>> _______________________________________________
>>>> Bacula-users mailing list
>>>> Bacula-users@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/bacula-users
>>>>
>>>>
>>>> ------------------------------------------------------------
>>>> ------------------
>>>> Check out the vibrant tech community on one of the world's most
>>>> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>>>> _______________________________________________
>>>> Bacula-users mailing list
>>>> Bacula-users@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/bacula-users
>>>>
>>>>
>>>
>>
>
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
