Bug 833474 - system.config.listFiles() is taking too long to return data
system.config.listFiles() is taking too long to return data
Status: CLOSED CURRENTRELEASE
Product: Spacewalk
Classification: Community
Component: API (Show other bugs)
1.8
All All
medium Severity high
: ---
: ---
Assigned To: Stephen Herr
Red Hat Satellite QA List
:
Depends On:
Blocks: space18
  Show dependency treegraph
 
Reported: 2012-06-19 10:31 EDT by Stephen Herr
Modified: 2012-11-01 12:19 EDT (History)
3 users (show)

See Also:
Fixed In Version: spacewalk-java-1.8.109-1
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: 831320
Environment:
Last Closed: 2012-11-01 12:19:25 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Additional Patch ORA-00911 (999 bytes, patch)
2012-06-19 19:02 EDT, Marcelo Moreira de Mello
no flags Details | Diff

  None (edit)
Description Stephen Herr 2012-06-19 10:31:47 EDT
+++ This bug was initially created as a clone of Bug #831320 +++

Created attachment 591255 [details]
Python Reproducer Script

Description of problem:

The main problem is that a when calling the method
system.config.listFiles() it is taking too long to finish. During our
troubleshooting, we had to increase the ProxyTimeout on mod_ajp to 1800
seconds. Afterwards we didn't hit the error 503 and the configuration
files were displayed (after +- 8 minutes).

We imported the customer database internally and were able to
reproduce the same issue shown at the customer environment. Even
updating the database schema and the indexes the behavior were kept the
same.
   
    We wrote a Python script in order to reproducer the issue (see
list.py attached on this mail). Below we can see query spent 7min and 54
seconds to render. 
   
   
$ ./list.py
<snip>
Systems Found: 297
   
Listing Config Files: abe (1000010239)
------------------------------------------------------------
    Time Elapsed on system.config.listFiles(): 0:07:54.090957
    Total Files: (373)

        path: /auto     channel: Cindi Common CFMU
        path: /auto/data     channel: Cindi Common CFMU
        path: /auto/home     channel: Cindi Common CFMU
        path: /boot/grub/device.map     channel: Cindi Server RHEL5
        path: /boot/grub/grub.conf     channel: Cindi Common RHEL5


Version-Release number of selected component (if applicable):
spacewalk-java-1.2.39-120.el5sat
spacewalk-schema-1.2.21-16.el5sat
satellite-schema-5.4.0.19-1.el5sat


How reproducible:
100%

Steps to Reproduce:
1. connect on the reproducer <snip>
2. execute the query manually or execute the list.py reproducer script

  
Actual results:

 SQL query is taking too long


Expected results:

 SQL query runs within a reasonable time 

Additional info:
Going further, we were able to reproduce the same behavior running the
SQL manually directly on SQLPLUS (ModeFactory.getMode("config_queries",
"file_names_for_system"));

  The results were displayed after 8 minutes and 16 seconds (after
updated the DB indexes using db-control utility).
 
SQL> set timing on;
SQL> SELECT  CFN.id, CR.id AS config_revision_id, CR.revision AS
config_revision,
        CF.id AS config_file_id, CC.id AS config_channel_id,
        CC.name AS config_channel_name, CCT.label AS config_channel_type,
        CFT.label AS config_file_type, cc.label as config_channel_label,
cr.modified as last_modified_date
    FROM  rhnConfigRevision CR, rhnConfigFile CF,
          rhnConfigChannel CC, rhnConfigFileName CFN,
          rhnConfigChannelType CCT, rhnConfigFileType CFT
    WHERE  CFN.id IN (SELECT  DISTINCT CFN.id
                          FROM  rhnConfigFileName CFN,
rhnServerConfigChannel SCC,
                                rhnConfigFile CF, rhnUserServerPerms USP,
                                rhnConfigChannel CC,
rhnConfigChannelType CCT
                          WHERE  SCC.server_id = 1000010239
                            AND  USP.server_id = SCC.server_id
                            AND  USP.user_id = 1
                            AND  SCC.config_channel_id =
CF.config_channel_id
                            AND  SCC.config_channel_id = CC.id
                            AND  CC.confchan_type_id = CCT.id
                            AND  CCT.label != 'server_import'
                            AND  CF.config_file_name_id = CFN.id
                         )
     AND  CR.id = ( SELECT  NVL( ( SELECT  CR.id
                                      FROM  rhnConfigChannel CC,
rhnConfigChannelType CCT,
                                            rhnConfigRevision CR,
rhnServerConfigChannel SCC, rhnConfigFile CF
                                       WHERE  SCC.server_id = 1000010239
                                         AND  CF.config_channel_id =
SCC.config_channel_id
                                         AND  CF.config_file_name_id =
CFNX.id
                                         AND  SCC.config_channel_id = CC.id
                                         AND  CC.confchan_type_id = CCT.id
                                         AND  CCT.label = 'local_override'
                                         AND 
CF.latest_config_revision_id = CR.id) ,
                                  ( SELECT  CR.id
                                       FROM  rhnConfigRevision CR,
rhnServerConfigChannel SCC, rhnConfigFile CF
                                        WHERE 
CF.latest_config_revision_id = CR.id
                                         AND  CF.config_file_name_id =
CFNX.id
                                         AND  CF.config_channel_id =
SCC.config_channel_id
                                         AND  SCC.server_id = 1000010239
                                         AND  SCC.position = ( SELECT 
min(SCC.position)
                                                                FROM 
rhnServerConfigChannel SCC, rhnConfigFile CF
                                                                WHERE 
SCC.server_id = 1000010239
                                                                  AND 
CF.config_channel_id = SCC.config_channel_id
                                                                  AND 
CF.config_file_name_id = CFNX.id ) ) ) AS config_revision_id
                        FROM  rhnConfigFileName CFNX
                        WHERE  CFNX.id = CFN.id )
        AND  CR.id = CF.latest_config_revision_id
        AND  CR.config_file_type_id = CFT.id
        AND  CF.config_channel_id = CC.id
        AND  CC.confchan_type_id = CCT.id;

{....SNIP....}
 
373 rows selected.

Elapsed: 00:08:16.04


--- Additional comment from sherr@redhat.com on 2012-06-19 10:20:45 EDT ---

The problem was that the sub queries in the elaborator end up running many, many times. I have a simplified query that will return the information we want while only doing the necessary joins twice. The sub-query is necessary because if a file is in more than one config channel we are subscribed to we want to only list the one in the highest priority config channel. I will switch the api over to using this query.

select cc.label as config_channel_label,
       cft.label AS config_file_type,
       cr.modified as last_modified_date,
       cc.name AS config_channel_name,
       cct.label AS config_channel_type,
       cfn.path
  from rhnConfigChannel cc,
       rhnConfigChannelType cct,
       rhnConfigFileType cft,
       rhnConfigRevision cr,
       rhnConfigFile cf,
       rhnConfigFileName cfn,
       rhnServerConfigChannel scc,
       (select  cf.config_file_name_id as id, min(scc.position) as position
          FROM  rhnServerConfigChannel SCC,
                rhnConfigFile CF, rhnUserServerPerms USP,
                rhnConfigChannel CC, rhnConfigChannelType CCT
         WHERE  SCC.server_id = 1000010239
           AND  USP.server_id = SCC.server_id
           AND  USP.user_id = 1
           AND  SCC.config_channel_id = CF.config_channel_id
           AND  SCC.config_channel_id = CC.id
           AND  CC.confchan_type_id = CCT.id
           AND  CCT.label != 'server_import'
           group by cf.config_file_name_id) tmp
 where tmp.id = cfn.id
   and scc.position = tmp.position
   and scc.config_channel_id = cc.id
   and scc.server_id = 1000010239
   and cc.confchan_type_id = cct.id
   and cf.latest_config_revision_id = cr.id
   and cf.config_channel_id = cc.id
   and cf.config_file_name_id = cfn.id
   and cr.config_file_type_id = cft.id
order by cfn.path;


373 rows selected.
Elapsed: 00:00:00.29
Comment 1 Stephen Herr 2012-06-19 10:49:52 EDT
Pushed to spacewalk master: 6bba7b2a49e5aea7eab21533de5bb88cd41857ad
Comment 2 Marcelo Moreira de Mello 2012-06-19 19:02:33 EDT
Created attachment 593097 [details]
Additional Patch ORA-00911


  Hello, 

    Per notes on https://bugzilla.redhat.com/show_bug.cgi?id=831320#c11, this patch attached is also needed. 

2012-06-19 18:30:19,107 [TP-Processor3] ERROR com.redhat.rhn.frontend.xmlrpc.BaseHandler - Error calling method: 
java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
	
{ .... SNIP ... }

	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:775)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:704)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:897)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
	at java.lang.Thread.run(Thread.java:736)
Caused by: 
com.redhat.rhn.common.db.WrappedSQLException: ORA-00911: invalid character


   Best Regards, 
mmello
Comment 3 Stephen Herr 2012-06-20 08:27:27 EDT
Ah, right you are mmello. Oops. I don't know how that snuck in there.

Committed to spacewalk master: 67f8efb912dd4bfe7468e1c029addb98352aa2db
Comment 4 Stephen Herr 2012-07-13 11:23:44 EDT
Also we need to take into account the possibility that the config channels priority is null, which it is for a server's individual config channel:
869eb498001de015249d0395529a172728d33954
Comment 5 Jan Pazdziora 2012-10-30 15:24:08 EDT
Moving ON_QA. Packages that address this bugzilla should now be available in yum repos at http://yum.spacewalkproject.org/nightly/
Comment 6 Jan Pazdziora 2012-11-01 12:19:25 EDT
Spacewalk 1.8 has been released: https://fedorahosted.org/spacewalk/wiki/ReleaseNotes18

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