View Issue Details

IDProjectCategoryView StatusLast Update
0000904bareos-coredirectorpublic2018-05-04 10:37
Reporteraderumier Assigned To 
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionunable to reproduce 
Product Version17.2.4 
Summary0000904: restore incremental, take hours to build filelist (missing index on File table / mysql 5.5)
DescriptionHi,
since the new database format on 17.2,
when I try to restore an incremental backup, it's take hours to build the filelist. (mysql process 100% cpu).

A simple

"create index FileId ON File(FileId);"

fix the problem (2min vs 3hours)

I have 512198948 entries in File table.
TagsNo tags attached.

Activities

stephand

stephand

2018-02-08 18:40

developer   ~0002901

There should be a primary key index on File(FileId) already before and also after upgrading to Bareos 17.2

Before upgrade, it should look like this:

mysql> describe File;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| FileId | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| FileIndex | int(10) unsigned | YES | | 0 | |
| JobId | int(10) unsigned | NO | MUL | NULL | |
| PathId | int(10) unsigned | NO | MUL | NULL | |
| FilenameId | int(10) unsigned | NO | | NULL | |
| DeltaSeq | smallint(5) unsigned | YES | | 0 | |
| MarkId | int(10) unsigned | YES | | 0 | |
| LStat | tinyblob | NO | | NULL | |
| MD5 | tinyblob | YES | | NULL | |
+------------+----------------------+------+-----+---------+----------------+
9 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 | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| File | 0 | PRIMARY | 1 | FileId | A | 11 | NULL | NULL | | BTREE | | |
| File | 1 | JobId | 1 | JobId | A | 1 | NULL | NULL | | BTREE | | |
| File | 1 | JobId | 2 | PathId | A | 6 | NULL | NULL | | BTREE | | |
| File | 1 | JobId | 3 | FilenameId | A | 11 | NULL | NULL | | BTREE | | |
| File | 1 | PathId | 1 | PathId | A | 6 | NULL | NULL | | BTREE | | |
| File | 1 | PathId | 2 | JobId | A | 6 | NULL | NULL | | BTREE | | |
| File | 1 | PathId | 3 | FilenameId | A | 11 | NULL | NULL | | BTREE | | |
| File | 1 | PathId | 4 | FileIndex | A | 11 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

After upgrade to Bareos 17.2 it should look like this:

mysql> describe File;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| FileId | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| FileIndex | int(10) unsigned | YES | | 0 | |
| JobId | int(10) unsigned | NO | MUL | NULL | |
| PathId | int(10) unsigned | NO | MUL | NULL | |
| DeltaSeq | smallint(5) unsigned | YES | | 0 | |
| MarkId | int(10) unsigned | YES | | 0 | |
| Fhinfo | decimal(20,0) | YES | | 0 | |
| Fhnode | decimal(20,0) | YES | | 0 | |
| LStat | tinyblob | NO | | NULL | |
| MD5 | tinyblob | NO | | NULL | |
| Name | blob | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
11 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 | Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| File | 0 | PRIMARY | 1 | FileId | A | 581 | NULL | NULL | | BTREE | | |
| File | 1 | JobId_PathId_Name | 1 | JobId | A | 7 | NULL | NULL | | BTREE | | |
| File | 1 | JobId_PathId_Name | 2 | PathId | A | 44 | NULL | NULL | | BTREE | | |
| File | 1 | JobId_PathId_Name | 3 | Name | A | 581 | 255 | NULL | | BTREE | | |
| File | 1 | PathId_JobId_FileIndex | 1 | PathId | A | 41 | NULL | NULL | | BTREE | | |
| File | 1 | PathId_JobId_FileIndex | 2 | JobId | A | 44 | NULL | NULL | | BTREE | | |
| File | 1 | PathId_JobId_FileIndex | 3 | FileIndex | A | 581 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

So an index on the FileId column of the File table should always exist.
stephand

stephand

2018-02-08 18:46

developer   ~0002902

May be in your case the index already did not exist before the upgrade to Bareos 17.2, or something went wrong when running the DB upgrade, did you see any errors when running the update_bareos_tables Script?
How do your indexes on the File table look now (SHOW INDEX FROM File;)?
stephand

stephand

2018-05-04 10:37

developer   ~0002989

We didn't get any further feedback here.
Issue is not reproducible.

Issue History

Date Modified Username Field Change
2018-02-07 16:55 aderumier New Issue
2018-02-07 18:33 joergs Assigned To => stephand
2018-02-07 18:33 joergs Status new => assigned
2018-02-08 18:40 stephand Note Added: 0002901
2018-02-08 18:46 stephand Note Added: 0002902
2018-02-08 18:46 stephand Status assigned => feedback
2018-05-04 10:37 stephand Note Added: 0002989
2018-05-04 10:37 stephand Status feedback => closed
2018-05-04 10:37 stephand Assigned To stephand =>
2018-05-04 10:37 stephand Resolution open => unable to reproduce