Project

General

Profile

Actions

Bug #8057

closed

Queries on expected reports are really slow with more than 2000 nodes

Added by Nicolas CHARLES over 8 years ago. Updated over 8 years ago.

Status:
Released
Priority:
N/A
Category:
Performance and scalability
Target version:
Severity:
UX impact:
User visibility:
Effort required:
Priority:
Name check:
Fix check:
Regression:

Description

When we have severall thousands nodes, the queries to get expected reports in 3.0+ is too slow

There are two parts to improve, the query itself and the index


Subtasks 2 (0 open2 closed)

Bug #8058: Add indexes on expected reports tableReleasedNicolas CHARLES2016-03-10Actions
Bug #8060: Add migration scripts for indexes creationReleasedNicolas CHARLES2016-03-10Actions
Actions #1

Updated by Nicolas CHARLES over 8 years ago

We need to change

 SELECT E.pkid, NNN.nodeid, NNN.nodeconfigids FROM expectedreports E INNER JOIN ( SELECT NN.nodejoinkey, NN.nodeid, NN.nodeconfigids FROM ( SELECT N.nodejoinkey, N.nodeid, N.nodeconfigids, generate_subscripts ( N.nodeconfigids, 1 ) AS v FROM expectedreportsnodes N ) AS NN WHERE NN.nodeconfigids [ v ] IN ( '-729463641', '-1360796549', '-1647020772', '-1069407658', '-169478327', '414076556', '-1160380758', '587719827', '2003792667', '-1527719623', '-2002282454', '-1391152750', '1775299637', '-327440080', '1438665908', '-1264048383', '902539539', '-1998333504', '548284150', '-1187765222', '538198140', '-1934245639', '963348761', '2141341279' ) ) AS NNN ON E.nodejoinkey = NNN.nodejoinkey;

to

EXPLAIN ANALYSE select
   E.pkid, NNN.nodeid, NNN.nodeconfigids
  from expectedreports E
   inner join (
                    select N.nodejoinkey, N.nodeid, N.nodeconfigids
                    from expectedreportsnodes N
                  where (nodeconfigids && '{"-729463641", "-1360796549", "-1647020772", "-1069407658", "-169478327", "414076556", "-1160380758", "587719827", "2003792667", 
                    "-1527719623", "-2002282454", "-1391152750", "1775299637", "-327440080", "1438665908", "-1264048383", "902539539", "-1998333504", "548284150", "-1187765222", "538198140", 
                    "-1934245639", "963348761", "2141341279" }')
                ) as NNN
     on E.nodejoinkey = NNN.nodejoinkey;

(replace the generate_subscript to the &&)

Actions #2

Updated by Vincent MEMBRÉ over 8 years ago

  • Target version changed from 3.1.9 to 3.0.14
Actions #3

Updated by François ARMAND over 8 years ago

  • Status changed from New to In progress
  • Assignee set to François ARMAND
Actions #4

Updated by François ARMAND over 8 years ago

  • Status changed from In progress to Pending technical review
  • Assignee changed from François ARMAND to Nicolas CHARLES
  • Pull Request set to https://github.com/Normation/rudder/pull/1060
Actions #5

Updated by François ARMAND over 8 years ago

  • Status changed from Pending technical review to Pending release
  • % Done changed from 0 to 100
Actions #6

Updated by Vincent MEMBRÉ over 8 years ago

  • Status changed from Pending release to Released

This bug has been fixed in Rudder 2.11.19, 3.0.14, 3.1.8 and 3.2.1 which were released today.

Actions

Also available in: Atom PDF