Hello,
On 1/17/2006 11:39 PM, Frank Altpeter wrote:
On 1/17/06, Frank Altpeter <[EMAIL PROTECTED]> wrote:
Since some days i try to clean up my database, but it seems to be
overloaded with obsolete data...
When i dbcheck the database with position 9 (Check for orphaned Path
records), it takes days and i have no other choice than aborting because i
cannot keep the backup server down that long.
The function 9 executes a "SELECT DISTINCT Path.PathId,File.PathId FROM
Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId" which seems to be
too big and mysql processlist tells me that it will copy it to a tmp
table. The last time i tried this one was running for 4 days without an
end.
Well, the bacula db is currently about 3.2 GB in size, where the File
table has 2.1 GB size and about 900 MB indices.
Interesting. My bacula catalog is not that big, but it's in a database
on a rather small server. The last time I did a complete dbcheck it took
, as far as i recall, less than three hours.
Any other idea how this could be fixed?
Well, after learning a bit more about mysql and the functions in there
i came to the conclusion, that there is no way out than setting up a
clean and new system.
The above query from dbcheck cannot come to an end, when one looks at this:
mysql> explain SELECT DISTINCT Path.PathId,File.PathId FROM Path LEFT
OUTER JOIN File ON (Path.PathId=File.PathId);
+-------+-------+---------------+------------+---------+------+----------+------------------------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------+-------+---------------+------------+---------+------+----------+------------------------------+
| Path | index | NULL | PRIMARY | 4 | NULL |
337827 | Using index; Using temporary |
| File | index | NULL | FilenameId | 8 | NULL |
21911576 | Using index |
+-------+-------+---------------+------------+---------+------+----------+------------------------------+
2 rows in set (0.01 sec)
If i understand that correctly (shame on me but i'm no database guru),
this means that mysql has to do a total of 7402321985352 distinctions
to solve. This means that the above query runs about 8576 days, given
a server that is able to do 10.000 distinctions per second.
So, there seems no way in cleaning up my tables and thus i think i
start over ... and use postgresql next time ...
I'm not sure if PostgreSQL will not suffer from that problem... I guess
the problem must be somewhere else. See my mysql aoutput:
mysql> explain SELECT DISTINCT Path.PathId,File.PathId FROM Path LEFT
-> OUTER JOIN File ON (Path.PathId=File.PathId);
+-------+-------+---------------+---------+---------+-------------+--------+------------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+---------+---------+-------------+--------+------------------------------+
| Path | index | NULL | PRIMARY | 4 | NULL | 123494 |
Using index; Using temporary |
| File | ref | PathId | PathId | 4 | Path.PathId | 108 |
Using index |
+-------+-------+---------------+---------+---------+-------------+--------+------------------------------+
2 rows in set (0.00 sec)
Here, mysql would use different indices.
I suppose you're missing some important index. See below for what I have:
mysql> show index from Path;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Path | 0 | PRIMARY | 1 | PathId | A |
123494 | NULL | NULL | | BTREE | |
| Path | 1 | Path | 1 | Path | A |
NULL | 50 | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> 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 |
12210429 | NULL | NULL | | BTREE | |
| File | 1 | JobId | 1 | JobId | A |
388 | NULL | NULL | | BTREE | |
| File | 1 | PathId | 1 | PathId | A |
113059 | NULL | NULL | | BTREE | |
| File | 1 | FilenameId | 1 | FilenameId | A |
678357 | NULL | NULL | | BTREE | |
| File | 1 | JobId_2 | 1 | JobId | A |
388 | NULL | NULL | | BTREE | |
| File | 1 | JobId_2 | 2 | PathId | A |
642654 | NULL | NULL | | BTREE | |
| File | 1 | JobId_2 | 3 | FilenameId | A |
12210429 | NULL | NULL | | BTREE | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
PathId in Table File should be the one you need.
Arno
Thanks anyway for any suggestions.
--
Le deagh dhùraghd,
Frank Altpeter
Two of the most famous products of Berkeley are LSD and Unix.
I don't think that this is a coincidence.
-- Anonymous
-------------------------------------------------------
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=k&kid3432&bid#0486&dat1642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
--
IT-Service Lehmann [EMAIL PROTECTED]
Arno Lehmann http://www.its-lehmann.de
-------------------------------------------------------
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&kid3432&bid#0486&dat1642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
