Re: Something amiss in bacula-9.6.7: Possible race condition

>>>>> On Fri, 15 Jan 2021 12:19:29 -0500, Phil Stracchino said:
> 
> This both filters already-existing Paths out of the batch set much more
> efficiently, AND does not fail if another job inserts those same Paths
> first.  If you compare the execution plans for the two queries you will
> see that the second performs ENORMOUSLY better.

Batch mode locks the tables, so it is impossible for another job to insert at
the same time.


> I'm frankly rather skeptical of the benefits of batch mode on MySQL
> anyway, since as far as I can see its principal impact is that every
> piece of data has to be written into the database TWICE.  (Once into the
> batch file, then a second time to copy from the batch file to the main
> tables.)  I would think that a much better way to do this would be to
> cache, say, 1000 records at a time in memory, then flush that memory
> cache into the database.  You can't reduce database traffic by caching
> the database traffic in the database.  That only INCREASES database traffic.

I think the purpose of batch mode is to reduce the number of queries per file.

In non-batch mode, it does 2 single-row selects and up to 3 single-row inserts
per file.

In batch mode, it does 1 single-row insert per file and then 3 complicated
queries to move that information into the main tables.

Each query is a round-trip between the director and the db, so batch mode will
greatly reduce that overhead.  Also, in PostgreSQL, the insert into the batch
table is done using a low level copy operation rather than an SQL query.

The tradeoff might change in Bacula 11 though, because it no longer has the
filename table so non-batch mode only does 1 single-row row select and up to 2
single-row inserts per file.

__Martin