On Tuesday 14 February 2006 21:06, Magnus Hagander wrote: > > > When I show the processlist several times in a row it is nearly > > > always SELECTing some filename in the Filename table. > > > > That's expected - it selects to find out if the filename has > > to be inserted. By the way - is there a portable way of > > having unique file names, and, in case of inserting a > > duplicate one, failing in a way that allows Bacula to process > > the FilenameId? This might save some time. > > Unfortunately, I suspect stored procedures won't work because > > of major differences between SQLite, PostgreSQL and MySQL. > > If there was a way to do it in a stored proc, that would probably be > fine even if the syntax of the proc differed - because the proc would be > part of the schema, just like table and index definitions. As long as > the call syntax would be the same. > > However, I don't beleive sqlite does stored procs, and I doubt it's in > the plans - doesn't really make sense for an embedded db. > > I think the standard way of doing it is using a SAVEPOINT. This is > supported by postgresql and mysql 4 (with innodb, of course). Sqlite > doesn't, it seems. > > > If you're thinking of doing the INSERT->SELECT (try insert, do select if > it fails) for filenames, I think you're on a bad track though. That's > only good if you will in most cases do INSERT and just in some cases to > SELECT. Looking at my own database, it certainly looks different - once > a full backup is through, most files never change their names so they > wouldn't need INSERTs. > > The best scheme would probably be SELECT->INSERT->SELECT (second select > if the insert failed). That you can do with SAVEPOINTs (around the > INSERT). > > Another thought - does the filename *HAVE* to be unique? You can gain a > lot of concurrency if you say "I want to use the existing one in 99.99% > of the cases, but I don't care about reaching 100%". Then you can do > SELECT->INSERT and the INSERT will *always* succeed. In case a > concurrent proces sinserted the same name, you get two ids for the same > name. You could either live with that, or clean it up at the end of the > job, perhaps? > (or is this perhaps already done today? Since there is no unique > constraint on it?)
Yes, this is how it works today. It is UNIQUE because Bacula uses a mutex in the driver code to lock the database around the SELECT->INSERT. However, as you indicated, most of the time after a full save the only thing that happens is a SELECT to get the FilenameId and the PathId. > > (Sorry if I'm ranting about things that are completely out of the scope > you were talking about ;-)) > > //Magnus > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://sel.as-us.falkag.net/sel?cmd________________________________________ >_______ Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
