Project

General

Profile

Actions

Bug #7987

closed

rudder-upgrade takes too long

Added by Janos Mattyasovszky about 5 years ago. Updated about 5 years ago.

Status:
Released
Priority:
N/A
Category:
Packaging
Target version:
Severity:
User visibility:
Effort required:
Priority:

Description

The pgsql query

select count(*) from (select xpath('/entry/*[@fileFormat=<?>]',data) AS x from eventlog) as Y where array_upper(x, 1) > 0
takes too long if you have 300k+ entries.

Server: SLES11 SP3 // postgresql91-server-9.1.18-0.3.1

This is also heavily impacted by the fact, that the script /opt/rudder/bin/rudder-upgrade, that contains this statement, is run twice on a single-server installation:

A 3.0.11->3.0.13 update took:

  rpm rudder-inventory-ldap: 61 minutes
  rpm rudder-webapp:         61 minutes

This could been solved if you would:
  • Save each type of update that is not required, because it has been checked during any previous update.
  • Execute this kind of maintenance by the jetty in an async job, not during the RPM installation.

Subtasks 2 (0 open2 closed)

Bug #8001: create an index for detection of eventlog versionReleasedFrançois ARMAND2016-02-29Actions
Bug #8002: Merge error in branch 3.1 for fileFormat detectionReleasedFrançois ARMAND2016-02-29Actions
Actions #1

Updated by Janos Mattyasovszky about 5 years ago

  • Description updated (diff)
Actions #3

Updated by Janos Mattyasovszky about 5 years ago

I did duplicate the data "eventlog" (27G Table when dumped, 1.7G compressed) and did some tests on it.

Query without the index:

# time psql -d rudder -c "select count(*) from (select xpath('/entry/*[@fileFormat=4]',data) AS x from eventlog) as Y where array_upper(x, 1) > 0;" 
 count
-------
     0
(1 row)

real    5m1.342s
user    0m0.000s
sys     0m0.000s

Added an Index like this:

# time psql -d rudder -c "CREATE INDEX eventlog_fileFormat ON eventlog (((((xpath('/entry/*/@fileFormat',data))[1])::text)::integer))" 
CREATE INDEX

real    5m1.029s
user    0m0.000s
sys     0m0.000s

Re-Run the same query:

# time psql -d rudder -c "select count(*) from (select xpath('/entry/*[@fileFormat=4]',data) AS x from eventlog) as Y where array_upper(x, 1) > 0;" 
 count
-------
     0
(1 row)

real    5m1.093s
user    0m0.000s
sys     0m0.000s

Basically no change, you'd have to change the original query, since it queries the whole same dataset for each fileFormat version twice, it would be a lot better to query all distinct values found as fileFormats, and do decisions according to which versions have been found...

Actions #4

Updated by Nicolas CHARLES about 5 years ago

Hum, if there's no change in the query time, then the index is not used.
Index looks correct, so maybe the query isn't

Actions #5

Updated by Janos Mattyasovszky about 5 years ago

What actually would help (without actually improving the original query's speed) is to do something like this:

# time psql -d rudder -c "select distinct Format from (select ((xpath('/entry//@fileFormat',data))[1]::text) as Format from eventlog) AS foo;" 
 format
--------

 5
 6
(3 rows)

real    5m13.226s
user    0m0.000s
sys     0m0.000s

This would only parse all the table content once, then you could make decisions based on what results came back.

Actions #6

Updated by Janos Mattyasovszky about 5 years ago

As discussed on IRC:

New query without an Index in place (still long):

# time psql -d rudder -c "select count(*) from (select ((xpath('/entry//@fileFormat',data))[1]::text) as id from eventlog) as T where id='5'" 
 count
--------
 138272
(1 row)

real    5m7.942s
user    0m0.000s
sys     0m0.000s

Putting the index in place:

# time psql -d rudder -c "CREATE INDEX eventlog_fileFormat ON eventlog (((((xpath('/entry//@fileFormat',data))[1])::text)));" 
CREATE INDEX

real    5m9.031s
user    0m0.000s
sys     0m0.000s

Now the query runs like a charm in basically not time:

# time psql -d rudder -c "select count(*) from (select ((xpath('/entry//@fileFormat',data))[1]::text) as id from eventlog) as T where id='5'" 
 count
--------
 138272
(1 row)

real    0m0.093s
user    0m0.000s
sys     0m0.000s

Actions #7

Updated by Nicolas CHARLES about 5 years ago

  • Category changed from Server components to Packaging
  • Assignee set to Nicolas CHARLES
  • Target version set to 2.11.19
Actions #8

Updated by Nicolas CHARLES about 5 years ago

  • Status changed from New to In progress
Actions #9

Updated by Nicolas CHARLES about 5 years ago

  • Status changed from In progress to Pending technical review
  • Assignee changed from Nicolas CHARLES to Benoît PECCATTE
  • Pull Request set to https://github.com/Normation/rudder-packages/pull/893
Actions #10

Updated by Nicolas CHARLES about 5 years ago

  • Status changed from Pending technical review to Pending release
  • % Done changed from 50 to 100
Actions #11

Updated by Vincent MEMBRÉ about 5 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