View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0000904 | bareos-core | director | public | 2018-02-07 16:55 | 2018-05-04 10:37 |
Reporter | aderumier | Assigned To | |||
Priority | normal | Severity | minor | Reproducibility | have not tried |
Status | closed | Resolution | unable to reproduce | ||
Product Version | 17.2.4 | ||||
Summary | 0000904: restore incremental, take hours to build filelist (missing index on File table / mysql 5.5) | ||||
Description | Hi, 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. | ||||
Tags | No tags attached. | ||||
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. |
|
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;)? |
|
We didn't get any further feedback here. Issue is not reproducible. |
|
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 |