RE: [Bacula-users] SELECTs during backup using lots of CPU

[Magnus H. <mh...@so...>]

> > When I show the processlist several times in a row it is nearly =20
> > always SELECTing some filename in the Filename table.
>=20
> That's expected - it selects to find out if the filename has=20
> to be inserted. By the way - is there a portable way of=20
> having unique file names, and, in case of inserting a=20
> duplicate one, failing in a way that allows Bacula to process=20
> the FilenameId? This might save some time.=20
> Unfortunately, I suspect stored procedures won't work because=20
> 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?)

(Sorry if I'm ranting about things that are completely out of the scope
you were talking about ;-))

//Magnus