|
Re: [Bacula-users] Bacula 2.2.8, dbcheck never completes
2008-08-19 09:55:38
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'm glad I opened the right can of worms! The documentation was never
that clear on this stuff (which I know because I read everything I could
get my hands on about Bacula and indexes). :)
Yuri Timofeev wrote:
> Ok, now dbcheck working very fast.
>
> Summary.
>
> CREATE INDEX idxPathId ON File (PathId);
> CREATE INDEX idxFilenameId 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);
>
>
> --- 8) Check for orphaned File records
>
> EXPLAIN SELECT File.FileId, Job.JobId FROM File LEFT OUTER JOIN Job ON
> (File.JobId = Job.JobId) WHERE Job.JobId IS NULL;
> +----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+--------------------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra
> |
> +----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+--------------------------------------+
> | 1 | SIMPLE | File | index | NULL | idxFJ | 8
> | NULL | 8251910 | Using index
> |
> | 1 | SIMPLE | Job | eq_ref | PRIMARY | PRIMARY | 4
> | bacula.File.JobId | 1 | Using where; Using index; Not exists
> |
> +----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+--------------------------------------+
>
>
> SHOW INDEX FROM File;
> +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
> Comment |
> +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | File | 0 | PRIMARY | 1 | FileId | A
> | 8251910 | NULL | NULL | | BTREE |
> |
> | File | 1 | JobId | 1 | JobId | A
> | 3638 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxPathId | 1 | PathId | A
> | 24270 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxFilenameId | 1 | FilenameId | A
> | 187543 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxJPF | 1 | JobId | A
> | 3638 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxJPF | 2 | PathId | A
> | 434311 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxJPF | 3 | FilenameId | A
> | 8251910 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxFJ | 1 | FileId | A
> | 8251910 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxFJ | 2 | JobId | A
> | 8251910 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxPJ | 1 | PathId | A
> | 24270 | NULL | NULL | | BTREE |
> |
> | File | 1 | idxPJ | 2 | JobId | A
> | 434311 | NULL | NULL | | BTREE |
> |
> +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
>
> SHOW INDEX FROM Job;
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
> Comment |
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Job | 0 | PRIMARY | 1 | JobId | A
> | 4590 | NULL | NULL | | BTREE | |
> | Job | 1 | Name | 1 | Name | A
> | 62 | 128 | NULL | | BTREE | |
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
>
> --- 9) Check for orphaned Path records
>
> EXPLAIN SELECT DISTINCT Path.PathId, File.PathId FROM Path LEFT OUTER
> JOIN File ON (Path.PathId = File.PathId) WHERE File.PathId IS NULL;
> +----+-------------+-------+-------+-----------------+-----------+---------+--------------------+-------+--------------------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra
> |
> +----+-------------+-------+-------+-----------------+-----------+---------+--------------------+-------+--------------------------------------+
> | 1 | SIMPLE | Path | index | NULL | PRIMARY | 4
> | NULL | 35107 | Using index; Using temporary
> |
> | 1 | SIMPLE | File | ref | idxPathId,idxPJ | idxPathId | 4
> | bacula.Path.PathId | 340 | Using where; Using index; Not
> exists |
> +----+-------------+-------+-------+-----------------+-----------+---------+--------------------+-------+--------------------------------------+
>
> -------------------------------------------------------------------------
> 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-users mailing list
> Bacula-users AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
- --
---- _ _ _ _ ___ _ _ _
|Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Systems Programmer II
|$&| |__| | | |__/ | \| _| |novosirj AT umdnj DOT edu - 973/972.0922 (2-0922)
\__/ Univ. of Med. and Dent.|IST/AST - NJMS Medical Science Bldg - C630
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFIqtDFmb+gadEcsb4RAp/AAJ0XfY+MH3Okm2JYahznbwc69ej4SQCdF2Ar
zXhYvs4fTdV7F81r5oALN9U=
=cEYj
-----END PGP SIGNATURE-----
novosirj.vcf
Description: Vcard
-------------------------------------------------------------------------
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-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
|
|
