Re: [Bacula-users] Fatal error sql

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 AT lists.sourceforge DOT net/msg61883.html

The user was having a similar issue, please find here the complete thread: https://www.mail-archive.com/bacula-users AT lists.sourceforge DOT net/msg61787.html

Hope this helps.

Best regards,

Ana

On Thu, Apr 13, 2017 at 10:45 PM, Ana Emília M. Arruda <emiliaarruda AT gmail DOT 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.kozic AT mint DOT 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 (emiliaarruda AT gmail DOT 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" configuration.

Best regards,

Ana

On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <petar.kozic AT mint DOT 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 (emiliaarruda AT gmail DOT 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.kozic AT mint DOT 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+news AT linuxpages DOT 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 = ""> > 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
[email protected].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
[email protected].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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Re: [Bacula-users] Fatal error sql_create.c