Bug 1418034 - Orphaned configuration still left in database
Summary: Orphaned configuration still left in database
Status: CLOSED ERRATA
Alias: None
Product: JBoss Operations Network
Classification: JBoss
Component: Database
Version: JON 3.3.8
Hardware: Unspecified
OS: Unspecified
low
medium
Target Milestone: ER01
: JON 3.3.11
Assignee: Ruben Vargas Palma
QA Contact: Filip Brychta
URL:
Whiteboard:
Keywords: Triaged
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2017-01-31 16:34 UTC by Filip Brychta
Modified: 2018-10-16 17:07 UTC (History)
6 users (show)

(edit)
Clone Of:
(edit)
Last Closed: 2018-10-16 17:06:49 UTC


Attachments (Terms of Use)
complete server log (2.68 MB, text/plain)
2018-02-02 15:38 UTC, Filip Brychta
no flags Details
oracle trace logs (3.98 MB, application/x-gzip)
2018-02-02 15:40 UTC, Filip Brychta
no flags Details


External Trackers
Tracker ID Priority Status Summary Last Updated
Red Hat Product Errata RHSA-2018:2930 None None None 2018-10-16 17:07 UTC
Red Hat Bugzilla 1323332 None CLOSED Orphaned configuration left in database 2019-06-17 21:15 UTC

Internal Trackers: 1323332

Description Filip Brychta 2017-01-31 16:34:05 UTC
Description of problem:
Original issue was partly resolved in bz1323332 but there are still some orphaned configurations.

There are still some orphaned configuration and the number is growing when importing new resources or creating new drift definitions.

Clean installation:
orphaned configurations: 499 out of which 488 were empty configurations (not being referenced from RHQ_CONFIG_PROPERTY)

When EAP6 was imported:
orphaned configurations: 896 out of which 885 were empty configurations (not being referenced from RHQ_CONFIG_PROPERTY)

When some drift definitions were created:
orphaned configurations: 1832 out of which 1821 were empty configurations (not being referenced from RHQ_CONFIG_PROPERTY)


After that it was stable, no matter how many changes (snapshots) there were.
I also tried to remove drifts and add them again and the number was stable.

So it seems the number of orphaned empty configurations is related to number of resource types of inventoried resources and number of different drift definition types.


Version-Release number of selected component (if applicable):
JON 3.3.8

How reproducible:
Always

Steps to Reproduce:
1. install JON 3.3.8
2. count number of orphaned configuration using this query:
SELECT COUNT (DISTINCT RHQ_CONFIG.ID) FROM RHQ_CONFIG
    LEFT JOIN RHQ_CONFIG_PROPERTY ON RHQ_CONFIG.ID = RHQ_CONFIG_PROPERTY.CONFIGURATION_ID
    WHERE NOT EXISTS (
        SELECT RHQ_ALERT_NOTIFICATION.SENDER_CONFIG_ID FROM RHQ_ALERT_NOTIFICATION
        WHERE RHQ_ALERT_NOTIFICATION.SENDER_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_ALERT_NOTIFICATION.EXTRA_CONFIG_ID FROM RHQ_ALERT_NOTIFICATION
        WHERE RHQ_ALERT_NOTIFICATION.EXTRA_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_BUNDLE_DEPLOYMENT.CONFIG_ID FROM RHQ_BUNDLE_DEPLOYMENT
        WHERE RHQ_BUNDLE_DEPLOYMENT.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONFIG_GROUP_UPDATE.CONFIGURATION_ID FROM RHQ_CONFIG_GROUP_UPDATE
        WHERE RHQ_CONFIG_GROUP_UPDATE.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONFIG_TEMPLATE.CONFIG_ID FROM RHQ_CONFIG_TEMPLATE
        WHERE RHQ_CONFIG_TEMPLATE.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONFIG_UPDATE.CONFIGURATION_ID FROM RHQ_CONFIG_UPDATE
        WHERE RHQ_CONFIG_UPDATE.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONTENT_SOURCE.CONFIGURATION_ID FROM RHQ_CONTENT_SOURCE
        WHERE RHQ_CONTENT_SOURCE.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CREATE_RES_HIST.CONFIGURATION_ID FROM RHQ_CREATE_RES_HIST
        WHERE RHQ_CREATE_RES_HIST.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DASHBOARD.CONFIGURATION_ID FROM RHQ_DASHBOARD
        WHERE RHQ_DASHBOARD.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DASHBOARD_PORTLET.CONFIGURATION_ID FROM RHQ_DASHBOARD_PORTLET
        WHERE RHQ_DASHBOARD_PORTLET.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DRIFT_DEFINITION.CONFIG_ID FROM RHQ_DRIFT_DEFINITION
        WHERE RHQ_DRIFT_DEFINITION.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DRIFT_DEF_TEMPLATE.CONFIG_ID FROM RHQ_DRIFT_DEF_TEMPLATE
        WHERE RHQ_DRIFT_DEF_TEMPLATE.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_INSTALLED_PKG_HIST.DEPLOYMENT_CONFIG_ID FROM RHQ_INSTALLED_PKG_HIST
        WHERE RHQ_INSTALLED_PKG_HIST.DEPLOYMENT_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_OPERATION_HISTORY.PARAMETERS_CONFIG_ID FROM RHQ_OPERATION_HISTORY
        WHERE RHQ_OPERATION_HISTORY.PARAMETERS_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_OPERATION_HISTORY.RESULTS_CONFIG_ID FROM RHQ_OPERATION_HISTORY
        WHERE RHQ_OPERATION_HISTORY.RESULTS_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_PACKAGE_VERSION.CONFIG_ID FROM RHQ_PACKAGE_VERSION
        WHERE RHQ_PACKAGE_VERSION.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_PLUGIN.PLUGIN_CONFIG_ID FROM RHQ_PLUGIN
        WHERE RHQ_PLUGIN.PLUGIN_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_PLUGIN.JOBS_CONFIG_ID FROM RHQ_PLUGIN
        WHERE RHQ_PLUGIN.JOBS_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RAW_CONFIG.CONFIG_ID FROM RHQ_RAW_CONFIG
        WHERE RHQ_RAW_CONFIG.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RESOURCE_TYPE.BUNDLE_CONFIG_ID FROM RHQ_RESOURCE_TYPE
        WHERE RHQ_RESOURCE_TYPE.BUNDLE_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RESOURCE.RES_CONFIGURATION_ID FROM RHQ_RESOURCE
        WHERE RHQ_RESOURCE.RES_CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RESOURCE.PLUGIN_CONFIGURATION_ID FROM RHQ_RESOURCE
        WHERE RHQ_RESOURCE.PLUGIN_CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_SUBJECT.CONFIGURATION_ID FROM RHQ_SUBJECT
        WHERE RHQ_SUBJECT.CONFIGURATION_ID = RHQ_CONFIG.ID
    );
3. import EAP 6
4. run the query again
5. create some drifts
6. run the query again

Actual results:
The number of orphaned configurations is growing

Expected results:
There should be no orphaned configurations

Additional info:
This bz is to finish work done by bz1323332 which added some improvements but did not resolve the issue completely.

Comment 4 Jay Shaughnessy 2018-01-11 18:19:32 UTC
One thing to note given the description:

Clean installation: orphans 499, 488 empty (non-empty 11)
EAP6 was imported : orphans 896, 885 empty (non-empty 11)
drift definitions : orphans 1832, 1821 empty (non-empty 11)

so the non-empty confis is constant after install and 11 doesn't seem like a big deal.  The complaint is for empty configs, which are a lot lighter than populated configs, but maybe still a problem.  Either way, it's a clue, the issue(s) seems to be around cleaning up empty config.

Comment 5 Larry O'Leary 2018-01-11 19:29:44 UTC
Right. 

Even though those configs are empty and do not consume a significant amount of space, they are still indexed and result in the database table containing more rows then it should. Although the number of rows per table is unlimited for Oracle and PostgreSQL, the way this data is stored in blocks can result in fragmentation that will add unneeded overhead. I have seen Oracle Database query execution times more then triple when the number of rows in a single table passes 1,000,000. The end result is severe system degradation. 

In most cases, adding additional resources provides relief and purging unneeded data helps. But if the configuration is empty it adds unnecessary load and if its orphaned, it can't be purged.

Comment 7 Simeon Pinder 2018-01-30 15:37:17 UTC
Moving to ON_QA.

JON 3.3.10 CR01 artifacts are available for test from here:
http://download.eng.bos.redhat.com/brewroot/packages/org.jboss.on-jboss-on-parent/3.3.0.GA/166/maven/org/jboss/on/jon-server-patch/3.3.0.GA/jon-server-patch-3.3.0.GA.zip
 *Note: jon-server-patch-3.3.0.GA.zip maps to CR01 build of
 jon-server-3.3.0.GA-update-10.zip.

Comment 8 Filip Brychta 2018-02-02 15:38:04 UTC
Number of orphaned configurations is not growing when following repro steps but there are 2 issues.
1) lots of 'ORA-00060: deadlock detected while waiting for resource' on oracle:
Oracle version: oracle-xe-11.2.0-1.0.x86_64

Repro steps:
a) install JON 3.3.10.CR01 on oracle db

It's not visible on JON 3.3.9. Attaching full server.log and trace logs from oracle.

2) Number of orphaned configurations is still growing when changing configuration of some resource for first time. Second update of configuration for the same resource does not increase number of orphaned configs. This issue might be tracked in new bz since the repro steps are different. This issues is visible on 3.3.9 too.

Comment 9 Filip Brychta 2018-02-02 15:38 UTC
Created attachment 1390214 [details]
complete server log

Comment 10 Filip Brychta 2018-02-02 15:40 UTC
Created attachment 1390215 [details]
oracle trace logs

Comment 15 Josejulio Martínez 2018-09-06 17:13:08 UTC
commit 7d7005ecdcec4715396637247642140cc4a3300f
Author: ruben.vargas <ruben.vp8510@gmail.com>
Date:   Mon Sep 3 16:51:59 2018 -0500

    Try to update configurations instead of replace/delete it

Comment 21 errata-xmlrpc 2018-10-16 17:06:49 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHSA-2018:2930


Note You need to log in before you can comment on or make changes to this bug.