Bug 833474

+++ 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 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