Re: [Bacula-devel] small mysql patch

[Dan Langille <dan@xxxxxxxxxxxx>]

Eric Bollengier wrote:
> Hello Yuri,
> 
> Adding indexes will probably speed up dbcheck, but it will slow down the 
> attribute insertion process and grow up the database.

Do we have statistics on the performance slowdown?

Seriously, not adding Foreign Keys or indexes because they slow things 
down is not a good decision.  Databases deal with indexes all the time.

Adding the indexes would also reduce the support required for dbcheck.

In my ideal world, dbcheck is not required.

> FYI, postgresql can use composite indexes, so it will require less indexes 
> than mysql.

If the index problem is just MySQL related, perhaps we should be 
including these indexes with PostgreSQL.  FWIW, I seem to have these 
indexes already:

bacula=# \d file
                              Table "public.file"
    Column   |  Type   |                       Modifiers
------------+---------+-------------------------------------------------------
  fileid     | integer | not null default 
nextval('file_fileid_seq'::regclass)
  fileindex  | integer | not null default 0
  jobid      | integer | not null
  pathid     | integer | not null
  markid     | integer | not null default 0
  lstat      | text    | not null
  md5        | text    | not null
  filenameid | integer | not null
Indexes:
     "file_pkey" PRIMARY KEY, btree (fileid)
     "file_filenameid_idx" btree (filenameid)
     "file_jobid_idx" btree (jobid)
     "file_jpfid_idx" btree (jobid, pathid, filenameid)
     "file_pathid" btree (pathid)
     "file_pathid_idx" btree (pathid)
     "testing" btree ((fileid::bigint))


 From Yuri:

> +#  CREATE INDEX idxP   ON File (PathId);
> +#  CREATE INDEX idxFI  ON File (FilenameId);
> +#  CREATE INDEX idxJPF ON File (JobId, PathId, FilenameId);
> +#  CREATE INDEX idxFJ  ON File (FileId, JobId);
> +#  CREATE INDEX idxPJ  ON File (PathId, JobId);

I suggest that perhaps not all these indexes are required to achieve 
decent performance.

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel