[jira] [Created] (TRAFODION-1187) LP Bug: 1445583 - showstats command performance slow with security enabled

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-1187:
-------------------------------------

             Summary: LP Bug: 1445583 - showstats command performance slow with security enabled
                 Key: TRAFODION-1187
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1187
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-security
            Reporter: Paul Low
            Assignee: Roberta Marton
            Priority: Critical
             Fix For: 2.0-incubating


Showstats is taking 5 times longer to execute when security is enabled.

Original issue reported by Gary:

In the overnight run testing baseline git150410 (before turning on security), the group of 17 showstats commands required 5 minutes 44 seconds to complete.  After turning on security, the same group of showstats commands now take 29 minutes 18 seconds to complete…an increase of over 5-fold.

Reply from Roberta:

Basically, when showstat starts, we verify that the user has privs.  If so, then no additional privilege checks are performed. 
When we verify privileges, we first verify that the user has the MANAGE_STATISTICS component privilege.  This call reads two tables, one to get the component privilege information and one to get the list of roles the user belongs to.  For showstats, we also check for SHOW component privilege, this adds two more I/Os.  We then check for SELECT privilege.  Select privilege information is cached, component privilege information is not.
I assume these extra I/O’s could be causing performance issues.

I could reorder the checks to first verify that the user has SELECT priv and then component privs. I should be doing this anyway.
I could also add a CQD to not check for component privilege to see if this is the cause.  Let me play a bit with the code and see if it makes any difference.

Update:

I have run some tests. It takes about 1 second to perform showstats as DB__ROOT.
It takes about 5 seconds to perform showstats as any other user.
This matches what Gary is seeing.

I removed the code to check for authorized user both in show statistics code and in the mainline code.  
However, I am still seeing the longer times by non DB__ROOT users.  This makes no sense since all authorization checks have been removed.

Found the issue: 

The degradation is occurring while we are gathering privileges for the histogram tables.

Histogram tables are considered system metadata and their definitions are hardcoded.  However, there are several attributes of these tables that cannot be hard coded such as object owner, object UID, and privilege information.
Today there is a special path to retrieve schema/object owner and object UID, and cache it.  However privilege information is not considered.  Therefore, I/Os are performed at compilation time for metadata objects instead of using cached information. 
The fix is to add privilege information to this special path.  Therefore, privileges will only be gathered once and used multiple times for metadata objects just like user objects.
This change should improve accesses to all metadata tables  including repository tables when security is enabled.
As part of this fix, I will also hardcode definitions of privilege manager metadata which will avoid I/O when accessing these tables.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)