On Tuesday 25 October 2005 14:02, Volker Sauer wrote:
> On Di, 25 Okt 2005, Arno Lehmann <al@...> wrote:
> > Hmm. You didn't tell us what server you have behind your cataog
> > database, and if that machine is usually loaded or not.
>
> Sorry, I was a little in a hurry when I wrote the mail!
> It's a dual 1.3GHz Pentium 3 with 1.5G of RAM and 2x120GB Disks. It
> run's some other little things, too. The load during the day is about 1,
> in the night it's less. I don't think that harware is the problem,
> because one week ago, bacula used to run on a 2x2GHz Athlon with 2GB RAM
> (dedicated to bacula) and the problem was the same.
>
> > Anyway, for reference: I've got an Athlon 500 MHz with 512 MB RAM
> > working as my all-purpose server. That machines load is usually less
> > than one, this weeks average was 0.6, for example.
> > The Bacula catalog File table is a little smaller than yours: 1.3 GB
> > data, >13500000 rows, and about 800 MB indexes.
> > Pruning / recycling is also automatic here, but usually takes less than
> > 15 minutes. I haven't any reports with pruning in them to give more
> > exact times at the moment. Anyway, the DIR never was busier longer than
> > I expected.
>
> That's great. That's what I expect!
>
> [.....]
>
> > Well, a "show index from File" gives me 7 result rows, representing 5
> > indexes.
>
> Mine show this:
>
> mysql> show index from File;
>
> | File | 0 | PRIMARY | 1 | FileId | A
> | 22336637 | NULL | NULL | | BTREE | |
> | File | 1 | FilenameId | 1 | FilenameId | A
> | 7445545 | NULL | NULL | | BTREE | |
> | File | 1 | FilenameId | 2 | PathId | A
> | 22336637 | NULL | NULL | | BTREE | |
>
> It's only three indexes!! Probably that's the reason! There's indexes
> missing! I'll look in the bacula source an create all the missing
> indexes! I expect this to fix the problem!
Well, something is wrong here, you should have an index on JobId as follows:
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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 | 1309549 | NULL | NULL | | BTREE | |
| File | 1 | FilenameId | 1 | FilenameId | A | 218258 | NULL | NULL | | BTREE | |
| File | 1 | FilenameId | 2 | PathId | A | 654774 | NULL | NULL | | BTREE | |
| File | 1 | JobId | 1 | JobId | A | 119 | NULL | NULL | | BTREE | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)
>
> Thanks for your help!
> Regards
> Volker
--
Best regards,
Kern
(">
/\
V_V
|