 |
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 |
|
|
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 :/ |
|
|
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. |
|
|
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> |
|
|
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 ... |
|
|
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. |
|
|
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 |
|
|
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 :( |
|
|
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. |
|
|
Going to change the report category from webui to director because it is mainly related to the DIRD and Catalog DB. |
|
|
Yes it takes hours again it seems :( Is there a way to get working behaviour again? |
|
|
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. |
|
|
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) |
|
|
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 |
|
|
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? |
|
|
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 ... |
|
|
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. |
|
|
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; |
|
|
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? |
|
|
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 |
|
|
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 |
|
|
It's 1 second now indeed, maybe data was not very hot on first try. |
|
|
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 |
|
|
It returns an empty set now, but seems very fast, but probably it's because it's empty :) |
|
|
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 |
|
|
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 |
|
|
PS: just did some more tests, second time it's pretty fast it seems!! |
|
|
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. |
|
|
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 |
|
|
Hi, Is this a regression in 17.2.x ? Just upgraded and wow it's slow again now for the moment :( |
|
|
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 | |
|
|
Hmm very strange after server reboot it's working again. |
|
|
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. |
|
|
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 :) |
|
|
Not reproducible. |
- Anonymous
