On 5.10.2015 20.32, Stephen Thompson wrote:
>
> mysql> show indexes 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
> | 4494348205 | NULL | NULL | | BTREE | |
> | File | 1 | JobId | 1 | JobId | A
> | 19 | NULL | NULL | | BTREE | |
> | File | 1 | JobId | 2 | PathId | A
> | 408577109 | NULL | NULL | | BTREE | |
> | File | 1 | JobId | 3 | FilenameId | A
> | 4494348205 | NULL | NULL | | BTREE | |
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
>
I seem to have an extra index from that..
show indexes from File;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| File | 0 | PRIMARY | 1 | FileId | A
| 5,095,248 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId | 1 | JobId | A
| 2,736 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId_2 | 1 | JobId | A
| 44 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId_2 | 2 | PathId | A
| 1,019,049 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId_2 | 3 | FilenameId | A
| 5,095,248 | NULL | NULL | | BTREE | |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
>
>
> On 10/05/2015 10:30 AM, Stephen Thompson wrote:
>>
>>
>> Phil,
>>
>> Good question. I vaguely recollect doing that a few years back, but I
>> don't immediately see any additional indexing. Where can I reference
>> what the default indexes are supposed to be?
>>
>> thanks,
>> Stephen
>>
>>
>>
>> On 10/05/2015 10:28 AM, Phil Stracchino wrote:
>>> On 10/05/15 13:17, Stephen Thompson wrote:
>>>> At any rate, the only thing that's changed was the upgrade from 7.0.5 to
>>>> 7.2.0. The table involved is definitely the File table. We do have
>>>> jobs with 20-30 million records, so those jobs can be slow when it comes
>>>> time for attribute insertion into the database (or to read out a file
>>>> list for Accurate backups). This why we've historically had innodb lock
>>>> timeout of 3600. However, it's only last week after the upgrade that
>>>> we've ever had queries extend beyond that hour mark.
>>>
>>> Stephen,
>>> Just as a thought, there have been a number of threads on this mailing
>>> list recommending additional or modified indexes on the File table.
>>> Have you added the suggested additional indexes?
>>>
>>>
>>
>
--
jarif.bit
------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|