View Issue Details

IDProjectCategoryView StatusLast Update
0000848bareos-core[All Projects] directorpublic2018-05-04 17:58
ReporterhostedpowerAssigned Tostephand 
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionno change required 
PlatformLinuxOSDebianOS Version8
Product Version16.2.6 
Fixed in Version 
Summary0000848: Can't restore anything, blank page in webui
DescriptionHi,


This only started since a while, probably one of the latest versions.

If we go to restore in the UI we can restore the full jobs, but none of the incremental jobs (always incremental scheme).

It seems to have issues with composing the list in case of incremental jobs.

We didn't even run trough a consolidate yet and we see these issues.

MySQL = 5.7, but we have this for ages, with the first 16.x version we were certainly able to restore.


Regards
Jo
Steps To ReproduceAlways incremental jobs, try to restore incremental job.
UI stays blank, + icon doesn't show folder tree
Additional Informationbobmats

[Mon Aug 28 15:44:33.891903 2017] [:error] [pid 8935] [client 178.117.190.152:60955] PHP Warning: unpack(): Type N: not enough input, need 4, have 0 in /usr/share/bareos-webui/vendor/Bareos/library/Bareos/BSock/BareosBSock.php on line 170, referer: https://backups.xxxx.com/restore/?type=client&jobid=&client=web.hosted-power.com&restoreclient=&restorejob=&where=&fileset=&mergefilesets=0&mergejobs=0
[Mon Aug 28 15:45:21.896016 2017] [:error] [pid 8935] [client 178.117.190.152:60955] PHP Notice: Undefined index: jobids in /usr/share/bareos-webui/module/Restore/src/Restore/Model/RestoreModel.php on line 107, referer: https://backups.xxxx.com/restore/?type=client&jobid=&client=web.hosted-power.com&restoreclient=&restorejob=&where=&fileset=&mergefilesets=0&mergejobs=0
[Mon Aug 28 15:45:21.896074 2017] [:error] [pid 8935] [client 178.117.190.152:60955] PHP Notice: Undefined index: jobids in /usr/share/bareos-webui/module/Restore/src/Restore/Model/RestoreModel.php on line 108, referer: https://backups.xxxx.com/restore/?type=client&jobid=&client=web.hosted-power.com&restoreclient=&restorejob=&where=&fileset=&mergefilesets=0&mergejobs=0
[Mon Aug 28 15:45:21.896086 2017] [:error] [pid 8935] [client 178.117.190.152:60955] PHP Warning: Invalid argument supplied for foreach() in /usr/share/bareos-webui/module/Restore/src/Restore/Model/RestoreModel.php on line 108, referer: https://backups.xxxx.com/restore/?type=client&jobid=&client=web.hosted-power.com&restoreclient=&restorejob=&where=&fileset=&mergefilesets=0&mergejobs=0


First error most often seen while trying to get the folder tree.
TagsNo tags attached.
bareos-master: impact
bareos-master: action
bareos-19.2: impact
bareos-19.2: action
bareos-18.2: impact
bareos-18.2: action
bareos-17.2: impact
bareos-17.2: action
bareos-16.2: impact
bareos-16.2: action
bareos-15.2: impact
bareos-15.2: action
bareos-14.2: impact
bareos-14.2: action
bareos-13.2: impact
bareos-13.2: action
bareos-12.4: impact
bareos-12.4: action

Relationships

related to 0000837 closedstephand performance issues with .bvfs_lsdirs while having a large number of directories 

Activities

hostedpower

hostedpower

2017-08-29 11:40

reporter   ~0002712

Hi,

It's mainly with a lot of incremental jobs and when filesets have to be merged.

We see this kind of query on our SQL server:

                                                                 |
| 13097 | bareos | localhost | bareos | Query | 6523 | Sending data | SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE FilenameId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0 |
| 15168 | bareos | localhost | bareos | Query | 9 | Sending data | SELECT 'D', tmp.PathId, 0, tmp.Path, JobId, LStat, FileId FROM (SELECT PPathId AS PathId, '..' AS Path FROM PathHierarchy WHERE PathId = 1203029 UNION SELECT 1203029 AS PathId, '.' AS Path) AS tmp LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (tmp.PathId = listfile1.PathId) ORDER BY tmp.Path, JobId DESC |
| 15171 | root | localhost | NULL | Query | 0 | starting | show full processlist


It's like hanging or running forever, maybe a deadlock or a query that never will succeed
hostedpower

hostedpower

2017-08-29 11:43

reporter   ~0002713

Is it possible it does the same query over and over ?

                                                                                                                                                                                                                                                                                                                                  |
| 13097 | bareos | localhost | bareos | Query | 6649 | Sending data | SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE FilenameId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0 |
| 15171 | root | localhost | NULL | Query | 0 | starting | show full processlist |
| 15178 | bareos | localhost | bareos | Query | 101 | Sending data | SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE FilenameId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0 |
+-------+--------+-----------+--------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------

We see it now twice because we tried a second time, but the query seems to be exactly the same and being run over and over...

If we run it manually it seems to deadlock/hang :/
hostedpower

hostedpower

2017-08-29 12:39

reporter   ~0002714

I restarted bareos and mysql so it was comletely relieved from any tasks.

Then I executed this query manually on the MySQL server and it's busy for more than 1 hour:

mysql> SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE FilenameId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0;

I can't believe this MySQL is so slow, there must be something wrong with the query somehow it seems.
hostedpower

hostedpower

2017-08-29 19:33

reporter   ~0002715

ok waited almost 6 hours and I have a result now, but this not normal for a db of only about 5 GB. I think at least indexes are missing and who knows what else is wrong.


mysql> SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE FilenameId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0;
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
| D | PathId | 0 | Path | JobId | LStat | FileId |
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
| D | 106558 | 0 | /etc/ | 34 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZgvW5 BZgvW5 A A d | 8863607 |
| D | 106558 | 0 | /etc/ | 193 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZjv7q BZjv7q A A d | 13615730 |
| D | 106558 | 0 | /etc/ | 309 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZkyDf BZkyDf A A d | 15375568 |
| D | 106558 | 0 | /etc/ | 387 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZlaSJ BZlaSJ A A d | 16053424 |
| D | 106558 | 0 | /etc/ | 426 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZlsRv BZlsRv A A d | 16291924 |
| D | 106558 | 0 | /etc/ | 465 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZl+Ne BZl+Ne A A d | 17192481 |
| D | 106558 | 0 | /etc/ | 581 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZnJMw BZnJMw A A d | 17884971 |
| D | 106558 | 0 | /etc/ | 698 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZn6n5 BZn6n5 A A d | 18751727 |
| D | 106558 | 0 | /etc/ | 775 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZos6X BZos6X A A d | 18948720 |
| D | 106815 | 0 | /lib/ | 34 | MoB GAC EHt Q A A A BAA BAA I BZVqNU BZZTcA BZZTcA A A d | 8872390 |
| D | 108168 | 0 | /dev/ | 34 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 8861202 |
| D | 108168 | 0 | /dev/ | 73 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 9852775 |
| D | 108168 | 0 | /dev/ | 115 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13024579 |
| D | 108168 | 0 | /dev/ | 154 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13475461 |
| D | 108168 | 0 | /dev/ | 193 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13615644 |
| D | 108168 | 0 | /dev/ | 231 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13724854 |
| D | 108168 | 0 | /dev/ | 270 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13928512 |
| D | 108168 | 0 | /dev/ | 309 | G D EHt P A A A sY BAA A BZkzNc BZZTvo BZZTvo A A d | 15375567 |
| D | 108168 | 0 | /dev/ | 348 | G D EHt P A A A sY BAA A BZkzNc BZZTvo BZZTvo A A d | 15744392 |
| D | 108168 | 0 | /dev/ | 387 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 16053254 |
| D | 108168 | 0 | /dev/ | 426 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 16291923 |
| D | 108168 | 0 | /dev/ | 465 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17192469 |
| D | 108168 | 0 | /dev/ | 503 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17521033 |
| D | 108168 | 0 | /dev/ | 542 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17733800 |
| D | 108168 | 0 | /dev/ | 581 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17884969 |
| D | 108168 | 0 | /dev/ | 620 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18109142 |
| D | 108168 | 0 | /dev/ | 659 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18311144 |
| D | 108168 | 0 | /dev/ | 698 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18751725 |
| D | 108168 | 0 | /dev/ | 737 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18865804 |
| D | 108168 | 0 | /dev/ | 775 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18948609 |
| D | 108168 | 0 | /dev/ | 814 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 19186808 |
| D | 108170 | 0 | /sys/ | 34 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 8863608 |
| D | 108170 | 0 | /sys/ | 73 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 9852776 |
| D | 108170 | 0 | /sys/ | 115 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13024580 |
| D | 108170 | 0 | /sys/ | 154 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13475462 |
| D | 108170 | 0 | /sys/ | 193 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13615731 |
| D | 108170 | 0 | /sys/ | 231 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13724855 |
| D | 108170 | 0 | /sys/ | 270 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13928590 |
| D | 108170 | 0 | /sys/ | 309 | P B EFt N A A A A BAA A BZkzYl BZZTvk BZZTvk A A d | 15375569 |
| D | 108170 | 0 | /sys/ | 348 | P B EFt N A A A A BAA A BZkzYl BZZTvk BZZTvk A A d | 15744393 |
| D | 108170 | 0 | /sys/ | 387 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 16053425 |
| D | 108170 | 0 | /sys/ | 426 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 16291925 |
| D | 108170 | 0 | /sys/ | 465 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17192482 |
| D | 108170 | 0 | /sys/ | 503 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17521036 |
| D | 108170 | 0 | /sys/ | 542 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17733803 |
| D | 108170 | 0 | /sys/ | 581 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17884972 |
| D | 108170 | 0 | /sys/ | 620 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18109143 |
| D | 108170 | 0 | /sys/ | 659 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18311145 |
| D | 108170 | 0 | /sys/ | 698 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18751728 |
| D | 108170 | 0 | /sys/ | 737 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18865805 |
| D | 108170 | 0 | /sys/ | 775 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18948721 |
| D | 108170 | 0 | /sys/ | 814 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 19186809 |
| D | 108251 | 0 | /var/ | 34 | MoB UAB EHt N A A A BAA BAA I BXBQ/G BZZUZK BZZUZK A A d | 8549998 |
| D | 108390 | 0 | /mnt/ | 34 | MoB KAB EHt D A A A BAA BAA I BWxdD9 BZiCJf BZiCJf A A d | 8863611 |
| D | 108390 | 0 | /mnt/ | 309 | MoB KAB EHt D A A A BAA BAA I BWxdD9 BZkyEi BZkyEi A A d | 15375571 |
| D | 108392 | 0 | /srv/ | 34 | MoB QAB EHt C A A A BAA BAA I BWxdD9 BVP3g+ BVP4Pu A A d | 8550001 |
| D | 108393 | 0 | /opt/ | 34 | MoB KAC EHt C A A A BAA BAA I BWxdD9 BVP3g+ BVP4Pu A A d | 8863609 |
| D | 108396 | 0 | /home/ | 34 | MoB GAB EHt C A A A BAA BAA I BWxdD9 BZW0XH BZW0XH A A d | 8550103 |
| D | 108397 | 0 | /lib64/ | 34 | MoB IAB EHt C A A A BAA BAA I BZVqL1 BZVqL2 BZVqL2 A A d | 8550003 |
| D | 111598 | 0 | /usr/ | 34 | MoB SAC EHt K A A A BAA BAA I BZVqMX BVP3g+ BVP4Px A A d | 8938780 |
| D | 111599 | 0 | /run/ | 34 | R CQD EHt V A A A OE BAA A BZZTvk BZijRl BZijRl A A d | 8872391 |
| D | 111599 | 0 | /run/ | 73 | R CQD EHt V A A A OE BAA A BZZTvk BZi4Xi BZi4Xi A A d | 9852778 |
| D | 111599 | 0 | /run/ | 115 | R CQD EHt V A A A OE BAA A BZZTvk BZjNdi BZjNdi A A d | 13024582 |
| D | 111599 | 0 | /run/ | 154 | R CQD EHt V A A A OE BAA A BZZTvk BZjiji BZjiji A A d | 13475464 |
| D | 111599 | 0 | /run/ | 193 | R CQD EHt V A A A OE BAA A BZZTvk BZj66w BZj66w A A d | 13615733 |
| D | 111599 | 0 | /run/ | 231 | R CQD EHt V A A A OE BAA A BZZTvk BZkMvj BZkMvj A A d | 13724857 |
| D | 111599 | 0 | /run/ | 270 | R CQD EHt V A A A OE BAA A BZZTvk BZklGq BZklGq A A d | 13928592 |
| D | 111599 | 0 | /run/ | 309 | R CQD EHt V A A A OE BAA A BZkzYl BZk27j BZk27j A A d | 15375572 |
| D | 111599 | 0 | /run/ | 348 | R CQD EHt V A A A OE BAA A BZkzYl BZlMBj BZlMBj A A d | 15744395 |
| D | 111599 | 0 | /run/ | 387 | R Rv EHt W A A A Nc BAA A BZlbWN BZlhND BZlhND A A d | 16053430 |
| D | 111599 | 0 | /run/ | 426 | R Rv EHt W A A A Nc BAA A BZlbWN BZl2Nj BZl2Nj A A d | 16291927 |
| D | 111599 | 0 | /run/ | 465 | R Rv EHt W A A A Nc BAA A BZlbWN BZmLp4 BZmLp4 A A d | 17192484 |
| D | 111599 | 0 | /run/ | 503 | R Rv EHt W A A A Nc BAA A BZlbWN BZmglg BZmglg A A d | 17521038 |
| D | 111599 | 0 | /run/ | 542 | R Rv EHt W A A A Nc BAA A BZlbWN BZm1fj BZm1fj A A d | 17733805 |
| D | 111599 | 0 | /run/ | 581 | R Rv EHt W A A A Nc BAA A BZlbWN BZnKli BZnKli A A d | 17884974 |
| D | 111599 | 0 | /run/ | 620 | R Rv EHt W A A A Nc BAA A BZlbWN BZnfrj BZnfrj A A d | 18109145 |
| D | 111599 | 0 | /run/ | 659 | R Rv EHt W A A A Nc BAA A BZlbWN BZn0xj BZn0xj A A d | 18311147 |
| D | 111599 | 0 | /run/ | 698 | R Rv EHt W A A A OE BAA A BZlbWN BZoJ3j BZoJ3j A A d | 18751730 |
| D | 111599 | 0 | /run/ | 737 | R Rv EHt W A A A OE BAA A BZlbWN BZoe9j BZoe9j A A d | 18865807 |
| D | 111599 | 0 | /run/ | 775 | R Rv EHt X A A A OY BAA A BZlbWN BZo0Di BZo0Di A A d | 18948725 |
| D | 111599 | 0 | /run/ | 814 | R Rv EHt X A A A OY BAA A BZlbWN BZpJJi BZpJJi A A d | 19186811 |
| D | 111961 | 0 | /root/ | 34 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZaTFR BZaTFR A A d | 8550102 |
| D | 111961 | 0 | /root/ | 309 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZkxO/ BZkxO/ A A d | 15083686 |
| D | 111961 | 0 | /root/ | 348 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZlKha BZlKha A A d | 15744389 |
| D | 111961 | 0 | /root/ | 387 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZlj3j BZlj3j A A d | 16053241 |
| D | 111961 | 0 | /root/ | 426 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZlt+X BZlt+X A A d | 16291353 |
| D | 111961 | 0 | /root/ | 465 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZl/rt BZl/rt A A d | 17086639 |
| D | 111961 | 0 | /root/ | 503 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZmfxd BZmfxd A A d | 17520973 |
| D | 111961 | 0 | /root/ | 581 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZnHoR BZnHoR A A d | 17883614 |
| D | 111961 | 0 | /root/ | 775 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZor46 BZor46 A A d | 18948536 |
| D | 111965 | 0 | /media/ | 34 | MoB IAC EHt D A A A BAA BAA I BWxdD9 BVP3g4 BVP4Px A A d | 8861201 |
| D | 111966 | 0 | /sbin/ | 34 | MoB OAC EHt C A A A BAA BAA I BZVqNg BZgvWW BZgvWW A A d | 8872681 |
| D | 111966 | 0 | /sbin/ | 387 | MoB OAC EHt C A A A BAA BAA I BZVqNg BZlaSJ BZlaSJ A A d | 16053433 |
| D | 111967 | 0 | /lost+found/ | 34 | MoB L EHA C A A A EAA BAA g BWxdD9 BVP3g3 BVP4Pu A A d | 8872392 |
| D | 111968 | 0 | /bin/ | 34 | MoB CAB EH9 C A A A BAA BAA I BZVqNg BZgvWW BZgvWW A A d | 8872534 |
| D | 111971 | 0 | /boot/ | 34 | MoB CAC EHt D A A A BAA BAA I BZVqNw BZiUTS BZiUTS A A d | 8861524 |
| D | 529927 | 0 | /gluster/ | 34 | MoB PAAB EHt E A A A BAA BAA I BZWQk4 BZZT7I BZZT7I A A d | 8861198 |
| D | 529927 | 0 | /gluster/ | 465 | MoB PAAB EHt D A A A BAA BAA I BZWQk4 BZmLoZ BZmLoZ A A d | 17192468 |
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
98 rows in set (5 hours 40 min 9.62 sec)

mysql>
hostedpower

hostedpower

2017-09-01 14:35

reporter   ~0002719

hmm any update on this? I could provide db backup or something.

This is really major issue since we have no way to restore properly atm :(

Disk has 50000 iops, there are 8 cpu cores and 24 GB of RAM in this machine, it should be plenty ...
frank

frank

2017-09-01 15:05

manager   ~0002720

Did you follow the suggestions made in our 16.2.6 release notes?

- MySQL/MariaDB

When using MySQL or MariaDB, creating the following index improves the performance:

CREATE INDEX PathId_JobId_FileIndex_FileNameId ON File(PathId,_JobId, _FileIndex,_FilenameId);

Run following command to create the index:

echo "CREATE INDEX PathId_JobId_FileIndex_FileNameId ON File(PathId,_JobId, _FileIndex,_FilenameId);" | mysql -u root bareos

However, with larger amounts of directories and/or involved jobs, even with this index the performance of .bvfs_lsdirs may still be insuļ¬ƒcient.

We are working on optimizing the SQL query for MySQL/MariaDB to solve this problem.
hostedpower

hostedpower

2017-09-01 15:52

reporter   ~0002721

Thx a lot for feedback.

I didn't do such command, I recreated mysql after installing 16.2.6, I would assume it did automatically?

I tried now:

mysql> CREATE INDEX PathId_JobId_FileIndex_FileNameId ON File(PathId,_JobId, _FileIndex,_FilenameId);
ERROR 1072 (42000): Key column '_JobId' doesn't exist in table
hostedpower

hostedpower

2017-09-01 15:57

reporter   ~0002722

Ok I did this:

CREATE INDEX PathId_JobId_FileIndex_FileNameId ON File(PathId,JobId,FileIndex,FilenameId);
Query OK, 0 rows affected (31.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

I retry the query now, but it doesn't look good :(

If the change was made in latest version only, I think it should be undone asap :(
frank

frank

2017-09-01 16:09

manager   ~0002723

Sorry, such underscores were a copy and paste error. But you already figured out, good.

As stated above, we are working on optimizing the Query for MySQL/MariaDB to solve this problem.
frank

frank

2017-09-01 16:10

manager   ~0002724

Going to change the report category from webui to director because it is mainly related to the DIRD and Catalog DB.
hostedpower

hostedpower

2017-09-01 16:31

reporter   ~0002725

Yes it takes hours again it seems :(

Is there a way to get working behaviour again?
stephand

stephand

2017-09-01 20:06

developer   ~0002726

I'll try to fix it asap.

Also do
ANALYZE TABLE File;
in mysql after creating the index, looks like mysql won't use the index otherwise.
hostedpower

hostedpower

2017-09-02 10:05

reporter   ~0002728

Ok did all that, it took 6 hours this time.

Almost can't believe it's so heavy while it was fine before.

mysql> SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( S ELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHier archy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.Pa thId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426 ,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE Filenam eId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34, 73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS J obId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.Pa thId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0;
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
| D | PathId | 0 | Path | JobId | LStat | FileId |
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
| D | 106558 | 0 | /etc/ | 34 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZgvW5 BZgvW5 A A d | 8863607 |
| D | 106558 | 0 | /etc/ | 193 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZjv7q BZjv7q A A d | 13615730 |
| D | 106558 | 0 | /etc/ | 309 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZkyDf BZkyDf A A d | 15375568 |
| D | 106558 | 0 | /etc/ | 387 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZlaSJ BZlaSJ A A d | 16053424 |
| D | 106558 | 0 | /etc/ | 426 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZlsRv BZlsRv A A d | 16291924 |
| D | 106558 | 0 | /etc/ | 465 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZl+Ne BZl+Ne A A d | 17192481 |
| D | 106558 | 0 | /etc/ | 581 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZnJMw BZnJMw A A d | 17884971 |
| D | 106558 | 0 | /etc/ | 698 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZn6n5 BZn6n5 A A d | 18751727 |
| D | 106558 | 0 | /etc/ | 775 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZos6X BZos6X A A d | 18948720 |
| D | 106815 | 0 | /lib/ | 34 | MoB GAC EHt Q A A A BAA BAA I BZVqNU BZZTcA BZZTcA A A d | 8872390 |
| D | 108168 | 0 | /dev/ | 34 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 8861202 |
| D | 108168 | 0 | /dev/ | 193 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13615644 |
| D | 108168 | 0 | /dev/ | 231 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13724854 |
| D | 108168 | 0 | /dev/ | 270 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 13928512 |
| D | 108168 | 0 | /dev/ | 309 | G D EHt P A A A sY BAA A BZkzNc BZZTvo BZZTvo A A d | 15375567 |
| D | 108168 | 0 | /dev/ | 348 | G D EHt P A A A sY BAA A BZkzNc BZZTvo BZZTvo A A d | 15744392 |
| D | 108168 | 0 | /dev/ | 387 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 16053254 |
| D | 108168 | 0 | /dev/ | 426 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 16291923 |
| D | 108168 | 0 | /dev/ | 465 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17192469 |
| D | 108168 | 0 | /dev/ | 503 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17521033 |
| D | 108168 | 0 | /dev/ | 542 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17733800 |
| D | 108168 | 0 | /dev/ | 581 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 17884969 |
| D | 108168 | 0 | /dev/ | 620 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18109142 |
| D | 108168 | 0 | /dev/ | 659 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18311144 |
| D | 108168 | 0 | /dev/ | 698 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18751725 |
| D | 108168 | 0 | /dev/ | 737 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18865804 |
| D | 108168 | 0 | /dev/ | 775 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18948609 |
| D | 108168 | 0 | /dev/ | 814 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 19186808 |
| D | 108170 | 0 | /sys/ | 34 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 8863608 |
| D | 108170 | 0 | /sys/ | 193 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13615731 |
| D | 108170 | 0 | /sys/ | 231 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13724855 |
| D | 108170 | 0 | /sys/ | 270 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 13928590 |
| D | 108170 | 0 | /sys/ | 309 | P B EFt N A A A A BAA A BZkzYl BZZTvk BZZTvk A A d | 15375569 |
| D | 108170 | 0 | /sys/ | 348 | P B EFt N A A A A BAA A BZkzYl BZZTvk BZZTvk A A d | 15744393 |
| D | 108170 | 0 | /sys/ | 387 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 16053425 |
| D | 108170 | 0 | /sys/ | 426 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 16291925 |
| D | 108170 | 0 | /sys/ | 465 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17192482 |
| D | 108170 | 0 | /sys/ | 503 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17521036 |
| D | 108170 | 0 | /sys/ | 542 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17733803 |
| D | 108170 | 0 | /sys/ | 581 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 17884972 |
| D | 108170 | 0 | /sys/ | 620 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18109143 |
| D | 108170 | 0 | /sys/ | 659 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18311145 |
| D | 108170 | 0 | /sys/ | 698 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18751728 |
| D | 108170 | 0 | /sys/ | 737 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18865805 |
| D | 108170 | 0 | /sys/ | 775 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18948721 |
| D | 108170 | 0 | /sys/ | 814 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 19186809 |
| D | 108251 | 0 | /var/ | 34 | MoB UAB EHt N A A A BAA BAA I BXBQ/G BZZUZK BZZUZK A A d | 8549998 |
| D | 108390 | 0 | /mnt/ | 34 | MoB KAB EHt D A A A BAA BAA I BWxdD9 BZiCJf BZiCJf A A d | 8863611 |
| D | 108390 | 0 | /mnt/ | 309 | MoB KAB EHt D A A A BAA BAA I BWxdD9 BZkyEi BZkyEi A A d | 15375571 |
| D | 108392 | 0 | /srv/ | 34 | MoB QAB EHt C A A A BAA BAA I BWxdD9 BVP3g+ BVP4Pu A A d | 8550001 |
| D | 108393 | 0 | /opt/ | 34 | MoB KAC EHt C A A A BAA BAA I BWxdD9 BVP3g+ BVP4Pu A A d | 8863609 |
| D | 108396 | 0 | /home/ | 34 | MoB GAB EHt C A A A BAA BAA I BWxdD9 BZW0XH BZW0XH A A d | 8550103 |
| D | 108397 | 0 | /lib64/ | 34 | MoB IAB EHt C A A A BAA BAA I BZVqL1 BZVqL2 BZVqL2 A A d | 8550003 |
| D | 111598 | 0 | /usr/ | 34 | MoB SAC EHt K A A A BAA BAA I BZVqMX BVP3g+ BVP4Px A A d | 8938780 |
| D | 111599 | 0 | /run/ | 34 | R CQD EHt V A A A OE BAA A BZZTvk BZijRl BZijRl A A d | 8872391 |
| D | 111599 | 0 | /run/ | 193 | R CQD EHt V A A A OE BAA A BZZTvk BZj66w BZj66w A A d | 13615733 |
| D | 111599 | 0 | /run/ | 231 | R CQD EHt V A A A OE BAA A BZZTvk BZkMvj BZkMvj A A d | 13724857 |
| D | 111599 | 0 | /run/ | 270 | R CQD EHt V A A A OE BAA A BZZTvk BZklGq BZklGq A A d | 13928592 |
| D | 111599 | 0 | /run/ | 309 | R CQD EHt V A A A OE BAA A BZkzYl BZk27j BZk27j A A d | 15375572 |
| D | 111599 | 0 | /run/ | 348 | R CQD EHt V A A A OE BAA A BZkzYl BZlMBj BZlMBj A A d | 15744395 |
| D | 111599 | 0 | /run/ | 387 | R Rv EHt W A A A Nc BAA A BZlbWN BZlhND BZlhND A A d | 16053430 |
| D | 111599 | 0 | /run/ | 426 | R Rv EHt W A A A Nc BAA A BZlbWN BZl2Nj BZl2Nj A A d | 16291927 |
| D | 111599 | 0 | /run/ | 465 | R Rv EHt W A A A Nc BAA A BZlbWN BZmLp4 BZmLp4 A A d | 17192484 |
| D | 111599 | 0 | /run/ | 503 | R Rv EHt W A A A Nc BAA A BZlbWN BZmglg BZmglg A A d | 17521038 |
| D | 111599 | 0 | /run/ | 542 | R Rv EHt W A A A Nc BAA A BZlbWN BZm1fj BZm1fj A A d | 17733805 |
| D | 111599 | 0 | /run/ | 581 | R Rv EHt W A A A Nc BAA A BZlbWN BZnKli BZnKli A A d | 17884974 |
| D | 111599 | 0 | /run/ | 620 | R Rv EHt W A A A Nc BAA A BZlbWN BZnfrj BZnfrj A A d | 18109145 |
| D | 111599 | 0 | /run/ | 659 | R Rv EHt W A A A Nc BAA A BZlbWN BZn0xj BZn0xj A A d | 18311147 |
| D | 111599 | 0 | /run/ | 698 | R Rv EHt W A A A OE BAA A BZlbWN BZoJ3j BZoJ3j A A d | 18751730 |
| D | 111599 | 0 | /run/ | 737 | R Rv EHt W A A A OE BAA A BZlbWN BZoe9j BZoe9j A A d | 18865807 |
| D | 111599 | 0 | /run/ | 775 | R Rv EHt X A A A OY BAA A BZlbWN BZo0Di BZo0Di A A d | 18948725 |
| D | 111599 | 0 | /run/ | 814 | R Rv EHt X A A A OY BAA A BZlbWN BZpJJi BZpJJi A A d | 19186811 |
| D | 111961 | 0 | /root/ | 34 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZaTFR BZaTFR A A d | 8550102 |
| D | 111961 | 0 | /root/ | 309 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZkxO/ BZkxO/ A A d | 15083686 |
| D | 111961 | 0 | /root/ | 348 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZlKha BZlKha A A d | 15744389 |
| D | 111961 | 0 | /root/ | 387 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZlj3j BZlj3j A A d | 16053241 |
| D | 111961 | 0 | /root/ | 426 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZlt+X BZlt+X A A d | 16291353 |
| D | 111961 | 0 | /root/ | 465 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZl/rt BZl/rt A A d | 17086639 |
| D | 111961 | 0 | /root/ | 503 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZmfxd BZmfxd A A d | 17520973 |
| D | 111961 | 0 | /root/ | 581 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZnHoR BZnHoR A A d | 17883614 |
| D | 111961 | 0 | /root/ | 775 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZor46 BZor46 A A d | 18948536 |
| D | 111965 | 0 | /media/ | 34 | MoB IAC EHt D A A A BAA BAA I BWxdD9 BVP3g4 BVP4Px A A d | 8861201 |
| D | 111966 | 0 | /sbin/ | 34 | MoB OAC EHt C A A A BAA BAA I BZVqNg BZgvWW BZgvWW A A d | 8872681 |
| D | 111966 | 0 | /sbin/ | 387 | MoB OAC EHt C A A A BAA BAA I BZVqNg BZlaSJ BZlaSJ A A d | 16053433 |
| D | 111967 | 0 | /lost+found/ | 34 | MoB L EHA C A A A EAA BAA g BWxdD9 BVP3g3 BVP4Pu A A d | 8872392 |
| D | 111968 | 0 | /bin/ | 34 | MoB CAB EH9 C A A A BAA BAA I BZVqNg BZgvWW BZgvWW A A d | 8872534 |
| D | 111971 | 0 | /boot/ | 34 | MoB CAC EHt D A A A BAA BAA I BZVqNw BZiUTS BZiUTS A A d | 8861524 |
| D | 529927 | 0 | /gluster/ | 34 | MoB PAAB EHt E A A A BAA BAA I BZWQk4 BZZT7I BZZT7I A A d | 8861198 |
| D | 529927 | 0 | /gluster/ | 465 | MoB PAAB EHt D A A A BAA BAA I BZWQk4 BZmLoZ BZmLoZ A A d | 17192468 |
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
89 rows in set (6 hours 36 min 47.29 sec)
stephand

stephand

2017-09-04 20:30

developer   ~0002731

Sorry, I wasn't able to find a proper fix for this yet. I can't understand why MySQL is so incredibly slow here even with an additional index, while it was easy to get this performing well in PostgreSQL.

The problematic part in MySQL is this:
... AND PathVisibility1.PathId NOT IN ( SELECT PathId FROM File WHERE Filenam eId = 5 AND JobId = ( SELECT MAX(JobId) FROM PathVisibility WHERE PathId = PathVisibility1.PathId AND JobId IN (34, 73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AND FileIndex = 0) ...

The purpose is not to show empty dirs which have been deleted in accurate jobs.

Right now, unfortunatelty the only thing I can recommend in this case is to go back to 16.2.5
hostedpower

hostedpower

2017-09-04 20:42

reporter   ~0002732

Hi Stephan,

How can I go back easily? The packages have version 16.2.6 in the repo.

Are the 16.2.5 Debian packages available to download somwewhere? Can I go back to 16.2.5 without having database issue or something ? :)

PS: I tried this query and it took 0.3 seconds instead of over 6 hours!!!

 SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) AND PathVisibility1.PathId ) AS listpath1 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0;

Couldn't you split it into 2 queries to filter out the paths afterwards or something like that?
hostedpower

hostedpower

2017-09-04 21:01

reporter   ~0002733

Just talked with MySQSL db expert: he says to simply run 2 separate queries , put results in hashtable and filter out in program code. It will be lightning fast he says ...
stephand

stephand

2017-09-04 22:43

developer   ~0002734

Thanks for that hint, but the bvfs code is structured to handle single query results. We are currently investigating if adding a column in the PathVisibility table that indicates deleted directories can be helpful to construct a the query that performs better in MySQL.
stephand

stephand

2017-09-15 15:46

developer   ~0002736

Could you please try how the following SQL Query performs on your system:

SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listpath1 JOIN ( SELECT PVD1.PathId AS PathId FROM ( SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId FROM PathVisibility AS PV1 WHERE JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) GROUP BY PathId) AS PVD1 INNER JOIN PathVisibility AS PV2 ON (PV2.PathId = PVD1.PathId AND PV2.JobId = PVD1.MaxJobId) INNER JOIN File AS F2 ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FilenameId = 5) WHERE F2.FileIndex != 0 UNION SELECT PathId FROM Path WHERE PATH='' UNION SELECT PathId FROM Path WHERE PATH='/') AS listpath2 ON (listpath1.PathId = listpath2.PathId) JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0;
stephand

stephand

2017-09-15 15:53

developer   ~0002737

You mentioned in
https://groups.google.com/forum/#!searchin/bareos-users/mysql$205.7|sort:relevance/bareos-users/hvKLbWcHaG4/IWLEwuFjBwAJ
that you changed the create tables script to get Bareos work with MySQL 5.7, it would be nice if you could share what exactly you changed. Was it only the Quota table or also the Media and Jobs tables?
hostedpower

hostedpower

2017-09-15 17:15

reporter   ~0002738

Hi Stephan,


SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listpath1 JOIN ( SELECT PVD1.PathId AS PathId FROM ( SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId FROM PathVisibility AS PV1 WHERE JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) GROUP BY PathId) AS PVD1 INNER JOIN PathVisibility AS PV2 ON (PV2.PathId = PVD1.PathId AND PV2.JobId = PVD1.MaxJobId) INNER JOIN File AS F2 ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FilenameId = 5) WHERE F2.FileIndex != 0 UNION SELECT PathId FROM Path WHERE PATH='' UNION SELECT PathId FROM Path WHERE PATH='/') AS listpath2 ON (listpath1.PathId = listpath2.PathId) JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.FilenameId = 5 AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)) AS listfile1 ON (listpath1.PathId = listfile1.PathId) ) AS A ORDER BY 2,3 DESC LIMIT 2000 OFFSET 0;
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
| D | PathId | 0 | Path | JobId | LStat | FileId |
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
| D | 106558 | 0 | /etc/ | 34 | MoB EAC EHt Bp A A A BAA BAA I BZVqOB BZgvW5 BZgvW5 A A d | 8863607 |
| D | 106558 | 0 | /etc/ | 775 | MoB EAC EHt Bw A A A BAA BAA I BZVqOB BZos6X BZos6X A A d | 18948720 |
| D | 108168 | 0 | /dev/ | 34 | G D EHt P A A A sY BAA A BZiUTN BZZTvo BZZTvo A A d | 8861202 |
| D | 108168 | 0 | /dev/ | 737 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18865804 |
| D | 108168 | 0 | /dev/ | 775 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 18948609 |
| D | 108168 | 0 | /dev/ | 814 | G QB EHt P A A A sY BAA A BZlbWR BZlbWR BZlbWR A A d | 19186808 |
| D | 108170 | 0 | /sys/ | 34 | P B EFt N A A A A BAA A BZZTvk BZZTvk BZZTvk A A d | 8863608 |
| D | 108170 | 0 | /sys/ | 737 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18865805 |
| D | 108170 | 0 | /sys/ | 775 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 18948721 |
| D | 108170 | 0 | /sys/ | 814 | P B EFt N A A A A BAA A BZlbWN BZlbWN BZlbWN A A d | 19186809 |
| D | 108392 | 0 | /srv/ | 34 | MoB QAB EHt C A A A BAA BAA I BWxdD9 BVP3g+ BVP4Pu A A d | 8550001 |
| D | 108393 | 0 | /opt/ | 34 | MoB KAC EHt C A A A BAA BAA I BWxdD9 BVP3g+ BVP4Pu A A d | 8863609 |
| D | 108396 | 0 | /home/ | 34 | MoB GAB EHt C A A A BAA BAA I BWxdD9 BZW0XH BZW0XH A A d | 8550103 |
| D | 108397 | 0 | /lib64/ | 34 | MoB IAB EHt C A A A BAA BAA I BZVqL1 BZVqL2 BZVqL2 A A d | 8550003 |
| D | 111599 | 0 | /run/ | 34 | R CQD EHt V A A A OE BAA A BZZTvk BZijRl BZijRl A A d | 8872391 |
| D | 111599 | 0 | /run/ | 737 | R Rv EHt W A A A OE BAA A BZlbWN BZoe9j BZoe9j A A d | 18865807 |
| D | 111599 | 0 | /run/ | 775 | R Rv EHt X A A A OY BAA A BZlbWN BZo0Di BZo0Di A A d | 18948725 |
| D | 111599 | 0 | /run/ | 814 | R Rv EHt X A A A OY BAA A BZlbWN BZpJJi BZpJJi A A d | 19186811 |
| D | 111961 | 0 | /root/ | 34 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZaTFR BZaTFR A A d | 8550102 |
| D | 111961 | 0 | /root/ | 775 | MoB MAC EHA K A A A BAA BAA I BZVqGW BZor46 BZor46 A A d | 18948536 |
| D | 111965 | 0 | /media/ | 34 | MoB IAC EHt D A A A BAA BAA I BWxdD9 BVP3g4 BVP4Px A A d | 8861201 |
| D | 111966 | 0 | /sbin/ | 34 | MoB OAC EHt C A A A BAA BAA I BZVqNg BZgvWW BZgvWW A A d | 8872681 |
| D | 111967 | 0 | /lost+found/ | 34 | MoB L EHA C A A A EAA BAA g BWxdD9 BVP3g3 BVP4Pu A A d | 8872392 |
| D | 111968 | 0 | /bin/ | 34 | MoB CAB EH9 C A A A BAA BAA I BZVqNg BZgvWW BZgvWW A A d | 8872534 |
| D | 111971 | 0 | /boot/ | 34 | MoB CAC EHt D A A A BAA BAA I BZVqNw BZiUTS BZiUTS A A d | 8861524 |
+---+--------+---+--------------+-------+-----------------------------------------------------------+----------+
25 rows in set (2.40 sec)

Looks good, it's slower than 16.2.5, but acceptable (the query in 16.2.5 is only 0.3 seconds or something)

The index suggested earlier is a good idea to have by default probably!!


About that post: that is very old and is already resolved as far as I know.

Please look at this, that is a new issue with latest 5.7 versions: https://bugs.bareos.org/view.php?id=842
stephand

stephand

2017-09-15 18:55

developer   ~0002739

Thanks a lot for your feedback.

I'm seeing this query twice as fast, when I repeat it, probably due to cache.
You'd need to drop the cache, to see the effect, for example:

[root@vgr-c7bpdevsrv bin]# systemctl stop mariadb; free && sync && echo 3 > /proc/sys/vm/drop_caches && free ; systemctl start mariadb
              total used free shared buff/cache available
Mem: 3882296 153704 2735948 8848 992644 3515756
Swap: 1572860 0 1572860
              total used free shared buff/cache available
Mem: 3882296 152540 3648016 8848 81740 3586784
Swap: 1572860 0 1572860
[root@vgr-c7bpdevsrv bin]# ./test_bvfs_lsdirs_query.sh
Job: Data1, Last JobId: 50, PATHID: 626092
7013 rows in set (1.61 sec)
Job: Data2, Last JobId: 47, PATHID: 757162
7010 rows in set (1.55 sec)
Job: Data3, Last JobId: 48, PATHID: 888232
7010 rows in set (1.56 sec)
Job: Data4, Last JobId: 49, PATHID: 1019302
7010 rows in set (1.73 sec)
[root@vgr-c7bpdevsrv bin]#
[root@vgr-c7bpdevsrv bin]# ./test_bvfs_lsdirs_query.sh
Job: Data1, Last JobId: 50, PATHID: 626092
7013 rows in set (0.00 sec)
Job: Data2, Last JobId: 47, PATHID: 757162
7010 rows in set (0.00 sec)
Job: Data3, Last JobId: 48, PATHID: 888232
7010 rows in set (0.00 sec)
Job: Data4, Last JobId: 49, PATHID: 1019302
7010 rows in set (0.00 sec)
[root@vgr-c7bpdevsrv bin]# free
              total used free shared buff/cache available
Mem: 3882296 716148 2168308 8848 997840 2953396
Swap: 1572860 0 1572860

I'll also need to check if the index is still optimal for the changed query.
However, the result already looks good, and we don't even need to change the
PathVisibility table.

Regarding MySQL 5.7:
It's not officially supported, see
http://doc.bareos.org/master/html/bareos-manual-main-reference.html#MySQL/MariaDBSupport

We also have
https://bugs.bareos.org/view.php?id=705
It's planned to fix this for Bareos 17.x

Regards

Stephan
hostedpower

hostedpower

2017-09-15 21:10

reporter   ~0002740

It's 1 second now indeed, maybe data was not very hot on first try.
stephand

stephand

2017-09-22 18:47

developer   ~0002758

I think I found a query that should be a bit faster, could you try it please:

SELECT 'D', PathId, 0, Path, JobId, LStat, FileId
FROM (
    SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId
    FROM (
        SELECT listpath1.PathId AS PathId
        FROM (
            SELECT DISTINCT PathHierarchy1.PathId AS PathId
            FROM PathHierarchy AS PathHierarchy1
            INNER JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId)
            INNER JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId)
            WHERE PathHierarchy1.PPathId = 108169
            AND PathVisibility1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)
            ) AS listpath1
        LEFT JOIN
          (
          SELECT PVD1.PathId AS PathId
          FROM (
             SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId FROM PathVisibility AS PV1 WHERE JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814) GROUP BY PathId
                ) AS PVD1
                INNER JOIN File AS F2 ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.FilenameId = 5)
          ) AS listpath2 ON (listpath1.PathId = listpath2.PathId)
        WHERE listpath2.PathId IS NULL
    ) AS listpath3
    INNER JOIN Path AS Path1 ON (listpath3.PathId = Path1.PathId)
    LEFT JOIN (
        SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId
        FROM File AS File1
        WHERE File1.FilenameId = 5
        AND File1.JobId IN (34,73,115,154,193,231,270,309,348,387,426,465,503,542,581,620,659,698,737,775,814)
    ) AS listfile1 ON (listpath3.PathId = listfile1.PathId)
) AS A
ORDER BY 2, 3 DESC LIMIT 2000 OFFSET 0;

Regards,

Stephan
hostedpower

hostedpower

2017-09-25 11:47

reporter   ~0002762

It returns an empty set now, but seems very fast, but probably it's because it's empty :)
stephand

stephand

2017-10-13 11:42

developer   ~0002790

Hello,

Bareos 16.2.7 was released, which contains an updated underlying SQL query for .bvfs_lsdirs that fixes the performance problem on MySQL/MariaDB.

The best index for the updated SQL query now is:

CREATE INDEX PathId_JobId_FileNameId_FileIndex ON File(PathId,JobId,FilenameId,FileIndex);

The old index should also work fine, but the new index will probably perform a bit better. So in your case, if you have enough disk space, I'd recommend to create the above new index first, then drop the old index:

DROP INDEX PathId_JobId_FileIndex_FileNameId ON File;

And it's important to run analyze on the File table after this:

ANALYZE TABLE File;

Your feedback if this fix works for you is appreciated.

Regards,

Stephan
hostedpower

hostedpower

2017-10-17 11:39

reporter   ~0002795

Hi,


It's not working very well as far as I can see. I tried one host and that worked within a few seconds (db was cold as well).

Tried restore of another and I see it's making a lot of queries



mysql> show full processlist;
+------+--------+-----------+--------+---------+------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+-----------+--------+---------+------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1474 | bareos | localhost | bareos | Sleep | 290 | | NULL |
| 1579 | bareos | localhost | bareos | Query | 0 | end | INSERT INTO PathHierarchy (PathId,PPathId) SELECT ph.PathId,ph.PPathId FROM (SELECT 378514 AS PathId, 378515 AS PPathId) ph WHERE NOT EXISTS (SELECT 1 FROM PathHierarchy phi WHERE phi.PathId = ph.PathId) |
| 1595 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+------+--------+-----------+--------+---------+------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show full processlist;
+------+--------+-----------+--------+---------+------+-----------+---------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+-----------+--------+---------+------+-----------+---------------------------------------------------------+
| 1474 | bareos | localhost | bareos | Sleep | 297 | | NULL |
| 1579 | bareos | localhost | bareos | Query | 0 | executing | SELECT PPathId FROM PathHierarchy WHERE PathId = 341789 |
| 1595 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+------+--------+-----------+--------+---------+------+-----------+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show full processlist;
+------+--------+-----------+--------+---------+------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+-----------+--------+---------+------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1474 | bareos | localhost | bareos | Sleep | 300 | | NULL |
| 1579 | bareos | localhost | bareos | Query | 0 | executing | INSERT INTO PathHierarchy (PathId,PPathId) SELECT ph.PathId,ph.PPathId FROM (SELECT 332259 AS PathId, 343641 AS PPathId) ph WHERE NOT EXISTS (SELECT 1 FROM PathHierarchy phi WHERE phi.PathId = ph.PathId) |
| 1595 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+------+--------+-----------+--------+---------+------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show full processlist;
+------+--------+-----------+--------+---------+------+------------+---------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+-----------+--------+---------+------+------------+---------------------------------------------------------+
| 1474 | bareos | localhost | bareos | Sleep | 308 | | NULL |
| 1579 | bareos | localhost | bareos | Query | 0 | statistics | SELECT PPathId FROM PathHierarchy WHERE PathId = 455494 |
| 1595 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+------+--------+-----------+--------+---------+------+------------+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Any idea what it's doing? This is taking a long time for sure and the GUI timed out again.

Regards
Jo
hostedpower

hostedpower

2017-10-17 11:40

reporter   ~0002796

PS: just did some more tests, second time it's pretty fast it seems!!
stephand

stephand

2017-10-18 08:24

developer   ~0002797

The queries you mentioned result from .bvfs_update, the Bareos WebUI runs it automatically. If it has not been run for the given JobIDs, it takes some time.

You could consider adding the following to your jobdef to run .bvfs_update right after jobs finished:

  Run Script {
    RunsWhen = After
    RunsOnFailure = No
    FailJobOnError = No
    RunsOnClient = No
    console = ".bvfs_update jobid=%i"
  }

Like this, you won't see the delay when opening the restore tree in Bareos WebUI for the first time.
hostedpower

hostedpower

2017-10-19 09:45

reporter   ~0002803

While this seems resolved, our other issue seems to be back again full force since the upgrade: https://bugs.bareos.org/view.php?id=854
hostedpower

hostedpower

2018-03-27 13:01

reporter   ~0002951

Hi,

Is this a regression in 17.2.x ?

Just upgraded and wow it's slow again now for the moment :(
hostedpower

hostedpower

2018-03-27 13:04

reporter   ~0002952

PS: Here is an example query taking ages:

                                                                                                                                                                                                                                                                                                                                                                                |
| 592 | bareos | localhost | bareos | Query | 391 | Sending data | SELECT 'D', SpecialDir.PathId, SpecialDir.Path, JobId, LStat, FileId FROM ( SELECT 108169 AS PathId, '.' AS Path UNION SELECT PPathId AS PathId, '..' AS Path FROM PathHierarchy WHERE PathId = 108169 ) AS SpecialDir LEFT JOIN ( SELECT PathId, JobId, LStat, FileId FROM File WHERE File.Name = '' AND File.JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) ) AS DirAttribute ON (SpecialDir.PathId = DirAttribute.PathId) UNION SELECT 'D', PathId, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT listpath1.PathId AS PathId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 INNER JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) INNER JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) ) AS listpath1 LEFT JOIN ( SELECT PVD1.PathId AS PathId FROM ( SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId FROM PathVisibility AS PV1 WHERE JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) GROUP BY PathId ) AS PVD1 INNER JOIN File AS F2 ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.Name = '') ) AS listpath2 ON (listpath1.PathId = listpath2.PathId) WHERE listpath2.PathId IS NULL ) AS listpath3 INNER JOIN Path AS Path1 ON (listpath3.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.Name = '' AND File1.JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) ) AS listfile1 ON (listpath3.PathId = listfile1.PathId) ) AS A ORDER BY Path ASC,JobId DESC LIMIT 1000 OFFSET 0 |

| 660 | bareos | localhost | bareos | Query | 180 | Sending data | SELECT 'D', SpecialDir.PathId, SpecialDir.Path, JobId, LStat, FileId FROM ( SELECT 108169 AS PathId, '.' AS Path UNION SELECT PPathId AS PathId, '..' AS Path FROM PathHierarchy WHERE PathId = 108169 ) AS SpecialDir LEFT JOIN ( SELECT PathId, JobId, LStat, FileId FROM File WHERE File.Name = '' AND File.JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) ) AS DirAttribute ON (SpecialDir.PathId = DirAttribute.PathId) UNION SELECT 'D', PathId, Path, JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat AS LStat, listfile1.FileId AS FileId FROM ( SELECT listpath1.PathId AS PathId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM PathHierarchy AS PathHierarchy1 INNER JOIN Path AS Path2 ON (PathHierarchy1.PathId = Path2.PathId) INNER JOIN PathVisibility AS PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE PathHierarchy1.PPathId = 108169 AND PathVisibility1.JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) ) AS listpath1 LEFT JOIN ( SELECT PVD1.PathId AS PathId FROM ( SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId FROM PathVisibility AS PV1 WHERE JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) GROUP BY PathId ) AS PVD1 INNER JOIN File AS F2 ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.Name = '') ) AS listpath2 ON (listpath1.PathId = listpath2.PathId) WHERE listpath2.PathId IS NULL ) AS listpath3 INNER JOIN Path AS Path1 ON (listpath3.PathId = Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 WHERE File1.Name = '' AND File1.JobId IN (15484,16377,14802,14886,14969,15052,15135,15221,15297,15382,15455,15529,15603,15682,15763,15846,15930,16020,16104,16171,16254,16344) ) AS listfile1 ON (listpath3.PathId = listfile1.PathId) ) AS A ORDER BY Path ASC,JobId DESC LIMIT 1000 OFFSET 0 |
hostedpower

hostedpower

2018-03-27 13:10

reporter   ~0002954

Hmm very strange after server reboot it's working again.
stephand

stephand

2018-05-04 10:53

developer   ~0002990

Does this problem still exist?

After upgrade to 17.2 the indexes on the File table should look like this:

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 | 35023296 | NULL | NULL | | BTREE | | |
| File | 1 | JobId_PathId_Name | 1 | JobId | A | 33456 | NULL | NULL | | BTREE | | |
| File | 1 | JobId_PathId_Name | 2 | PathId | A | 6122214 | NULL | NULL | | BTREE | | |
| File | 1 | JobId_PathId_Name | 3 | Name | A | 35023296 | 255 | NULL | | BTREE | | |
| File | 1 | PathId_JobId_FileIndex | 1 | PathId | A | 4654833 | NULL | NULL | | BTREE | | |
| File | 1 | PathId_JobId_FileIndex | 2 | JobId | A | 9403753 | NULL | NULL | | BTREE | | |
| File | 1 | PathId_JobId_FileIndex | 3 | FileIndex | A | 31459738 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

Note: https://bugs.bareos.org/view.php?id=854 is a different problem, don't see any relation to this one.
hostedpower

hostedpower

2018-05-04 11:18

reporter   ~0002993

Can't reproduce, after server reboot all kept working well for the moment regarding this.

However some machines we cannot restore (no files listed at all, whatever we do). Another issue is open here somewhere for this :)
stephand

stephand

2018-05-04 17:58

developer   ~0002995

Not reproducible.

Issue History

Date Modified Username Field Change
2017-08-28 15:57 hostedpower New Issue
2017-08-29 10:19 frank Assigned To => frank
2017-08-29 10:19 frank Status new => assigned
2017-08-29 11:40 hostedpower Note Added: 0002712
2017-08-29 11:43 hostedpower Note Added: 0002713
2017-08-29 12:39 hostedpower Note Added: 0002714
2017-08-29 19:33 hostedpower Note Added: 0002715
2017-09-01 14:35 hostedpower Note Added: 0002719
2017-09-01 15:05 frank Note Added: 0002720
2017-09-01 15:52 hostedpower Note Added: 0002721
2017-09-01 15:57 hostedpower Note Added: 0002722
2017-09-01 16:09 frank Note Added: 0002723
2017-09-01 16:10 frank Note Added: 0002724
2017-09-01 16:10 frank Category webui => director
2017-09-01 16:10 frank Assigned To frank =>
2017-09-01 16:12 frank Status assigned => new
2017-09-01 16:31 hostedpower Note Added: 0002725
2017-09-01 20:03 stephand Assigned To => stephand
2017-09-01 20:03 stephand Status new => assigned
2017-09-01 20:06 stephand Note Added: 0002726
2017-09-02 10:05 hostedpower Note Added: 0002728
2017-09-04 20:30 stephand Note Added: 0002731
2017-09-04 20:42 hostedpower Note Added: 0002732
2017-09-04 21:01 hostedpower Note Added: 0002733
2017-09-04 22:43 stephand Note Added: 0002734
2017-09-15 15:46 stephand Note Added: 0002736
2017-09-15 15:53 stephand Note Added: 0002737
2017-09-15 15:55 stephand Relationship added related to 0000837
2017-09-15 17:15 hostedpower Note Added: 0002738
2017-09-15 18:55 stephand Note Added: 0002739
2017-09-15 21:10 hostedpower Note Added: 0002740
2017-09-22 18:47 stephand Note Added: 0002758
2017-09-25 11:47 hostedpower Note Added: 0002762
2017-10-13 11:42 stephand Note Added: 0002790
2017-10-13 11:42 stephand Status assigned => feedback
2017-10-17 11:39 hostedpower Note Added: 0002795
2017-10-17 11:39 hostedpower Status feedback => assigned
2017-10-17 11:40 hostedpower Note Added: 0002796
2017-10-18 08:24 stephand Note Added: 0002797
2017-10-19 09:45 hostedpower Note Added: 0002803
2018-03-27 13:01 hostedpower Note Added: 0002951
2018-03-27 13:04 hostedpower Note Added: 0002952
2018-03-27 13:10 hostedpower Note Added: 0002954
2018-05-04 10:53 stephand Note Added: 0002990
2018-05-04 10:53 stephand Status assigned => feedback
2018-05-04 11:18 hostedpower Note Added: 0002993
2018-05-04 11:18 hostedpower Status feedback => assigned
2018-05-04 17:58 stephand Note Added: 0002995
2018-05-04 17:58 stephand Status assigned => closed
2018-05-04 17:58 stephand Resolution open => no change required