>>>>> 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 _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
