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.
>
> 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 ...
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=lnk&kid3432&bid#0486&dat1642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
