[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] small mysql patch

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 
  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
     "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
Bacula-devel mailing list

This mailing list archive is a service of Copilotco.