[rt-users] Extremely slow response when adding comment or change people

Jamie Wilkinson jaq at spacepants.org
Mon Apr 28 01:29:08 EDT 2003


This one time, at band camp, Vajkó Péter wrote:
>RT 3.0.2pre3 with postgresql 7.3 is extremely slow at these actions. It 
>starts a select like this:

>about 150 tickets. I suspect the WhoHaveRigths sub in Users_Overlay.pm.
>
>Any ideas why this is happening?

Not sure, but I found a speed problem with the Instance field of the Group
table, it was a varchar(64) but it was storing integers (or null entries)
only.

I've modified the table, converting instance to integer like so:

( i forget the actual sql i used, but hopefully you get the idea)
alter table groups add column tmp (integer);
update tmp from groups set tmp = int4(instance) where instance is not null;
update tmp from groups set tmp = 0 where instance is null;
select tmp, instance from groups; (do a visual compare here to check all's
        well)
alter table groups drop column instance;
alter table groups add column instance (integer);
update instance from groups set instance = tmp;
alter table groups drop column tmp;

then recreate the indexes on the table (best results by doing \d groups
before modifying the table and recreating them all afterwards)

and once that's done, apply this patch that updates the code to use integers
instead of strings for the field.

I couldn't see why the field was a varchar in the first place, as the field
seems to hold either a string of a ticket number or an empty string -- which
can easily be replaced by an integer containing a ticket number or zero, or
as zero seems to be a special case, it could be just NULL which is a valid
value in this case.  An instance of 0 is held for internal groups and user
created groups, whereas the non-zero instances refer to each of the watcher
groups for each ticket.  This means that Groups grows linearly about 5x or
so that of the Tickets table -- with 350 odd tickets we had about 1600
groups defined.

This sped up the computation about 75% on our tests, as postgres was no
longer doing a string comparison 1500 or so times

diff -ruN install/opt/rt3/lib/RT/Group.pm ../rt-3.0.1-anchor/lib/RT/Group.pm
--- install/opt/rt3/lib/RT/Group.pm	2003-04-16 01:54:15.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Group.pm	2003-04-22 18:16:09.000000000 +1000
@@ -69,7 +69,7 @@
   varchar(255) 'Description'.
   varchar(64) 'Domain'.
   varchar(64) 'Type'.
-  varchar(64) 'Instance'.
+  integer 'Instance'.
 
 =cut
 
@@ -83,7 +83,7 @@
                 Description => '',
                 Domain => '',
                 Type => '',
-                Instance => '',
+                Instance => 0,
 
 		  @_);
     $self->SUPER::Create(
@@ -91,7 +91,7 @@
                          Description => $args{'Description'},
                          Domain => $args{'Domain'},
                          Type => $args{'Type'},
-                         Instance => $args{'Instance'},
+                         Instance => int($args{'Instance'}),
 );
 
 }
@@ -182,7 +182,7 @@
 =item Instance
 
 Returns the current value of Instance. 
-(In the database, Instance is stored as varchar(64).)
+(In the database, Instance is stored as integer.)
 
 
 
@@ -191,7 +191,7 @@
 
 Set Instance to VALUE. 
 Returns (1, 'Status message') on success and (0, 'Error Message') on failure.
-(In the database, Instance will be stored as a varchar(64).)
+(In the database, Instance will be stored as a integer.)
 
 
 =cut
@@ -212,7 +212,7 @@
         Type => 
 		{read => 1, write => 1, type => 'varchar(64)', default => ''},
         Instance => 
-		{read => 1, write => 1, type => 'varchar(64)', default => ''},
+		{read => 1, write => 1, type => 'integer', default => 0},
 
  }
 };
diff -ruN install/opt/rt3/lib/RT/Group_Overlay.pm ../rt-3.0.1-anchor/lib/RT/Group_Overlay.pm
--- install/opt/rt3/lib/RT/Group_Overlay.pm	2003-04-16 01:54:15.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Group_Overlay.pm	2003-04-22 18:22:38.000000000 +1000
@@ -285,7 +285,7 @@
 
         $self->LoadByCols( "Domain" => 'ACLEquivalence',
                             "Type" => 'UserEquiv',
-                           "Instance" => $princ->Id);
+                           "Instance" => int($princ->Id));
 }
 
 # }}}
@@ -305,7 +305,7 @@
                     @_);
 
         $self->LoadByCols( "Domain" => 'Personal',
-                           "Instance" => $args{'User'},
+                           "Instance" => int($args{'User'}),
                            "Type" => '',
                            "Name" => $args{'Name'} );
 }
@@ -327,7 +327,7 @@
     my $identifier = shift;
 
         $self->LoadByCols( "Domain" => 'SystemInternal',
-                           "Instance" => '',
+                           "Instance" => 0,
                            "Name" => '',
                            "Type" => $identifier );
 }
@@ -354,7 +354,7 @@
                 Type => undef,
                 @_);
         $self->LoadByCols( Domain => 'RT::Ticket-Role',
-                           Instance =>$args{'Ticket'}, 
+                           Instance => int($args{'Ticket'}), 
                            Type => $args{'Type'}
                            );
 }
@@ -381,7 +381,7 @@
                 Type => undef,
                 @_);
         $self->LoadByCols( Domain => 'RT::Queue-Role',
-                           Instance =>$args{'Queue'}, 
+                           Instance => int($args{'Queue'}), 
                            Type => $args{'Type'}
                            );
 }
@@ -444,7 +444,7 @@
         Description => undef,
         Domain      => undef,
         Type        => undef,
-        Instance    => undef,
+        Instance    => 0,
         InsideTransaction => undef,
         @_
     );
@@ -466,7 +466,7 @@
         Description => $args{'Description'},
         Type        => $args{'Type'},
         Domain      => $args{'Domain'},
-        Instance    => $args{'Instance'}
+        Instance    => int($args{'Instance'})
     );
     my $id = $self->Id;
     unless ($id) {
@@ -517,7 +517,7 @@
         return ( 0, $self->loc('Permission Denied') );
     }
 
-    return($self->_Create( Domain => 'UserDefined', Type => '', Instance => '', @_));
+    return($self->_Create( Domain => 'UserDefined', Type => '', Instance => 0, @_));
 }
 
 # }}}
@@ -542,7 +542,7 @@
                            Type => 'UserEquiv',
                            Name => 'User '. $princ->Object->Id,
                            Description => 'ACL equiv. for user '.$princ->Object->Id,
-                           Instance => $princ->Id,
+                           Instance => int($princ->Id),
                            InsideTransaction => 1);
       unless ($id) {
         $RT::Logger->crit("Couldn't create ACL equivalence group");
@@ -609,7 +609,7 @@
         $self->_Create(
             Domain      => 'Personal',
             Type        => '',
-            Instance    => $args{'PrincipalId'},
+            Instance    => int($args{'PrincipalId'}),
             Name        => $args{'Name'},
             Description => $args{'Description'}
         )
@@ -635,7 +635,7 @@
 
 sub CreateRoleGroup {
     my $self = shift;
-    my %args = ( Instance => undef,
+    my %args = ( Instance => 0,
                  Type     => undef,
                  Domain   => undef,
                  @_ );
@@ -645,7 +645,7 @@
 
 
     return ( $self->_Create( Domain            => $args{'Domain'},
-                             Instance          => $args{'Instance'},
+                             Instance          => int($args{'Instance'}),
                              Type              => $args{'Type'},
                              InsideTransaction => 1 ) );
 }
diff -ruN install/opt/rt3/lib/RT/Groups_Overlay.pm ../rt-3.0.1-anchor/lib/RT/Groups_Overlay.pm
--- install/opt/rt3/lib/RT/Groups_Overlay.pm	2003-04-16 01:54:15.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Groups_Overlay.pm	2003-04-22 18:22:31.000000000 +1000
@@ -80,7 +80,7 @@
 sub LimitToSystemInternalGroups {
     my $self = shift;
     $self->Limit(FIELD => 'Domain', OPERATOR => '=', VALUE => 'SystemInternal');
-    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => '');
+    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => 0);
 }
 
 
@@ -98,7 +98,7 @@
 sub LimitToUserDefinedGroups {
     my $self = shift;
     $self->Limit(FIELD => 'Domain', OPERATOR => '=', VALUE => 'UserDefined');
-    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => '');
+    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => 0);
 }
 
 
@@ -140,7 +140,7 @@
     my $self = shift;
     my $queue = shift;
     $self->Limit(FIELD => 'Domain', OPERATOR => '=', VALUE => 'RT::Queue-Role');
-    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => $queue);
+    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => int($queue));
 }
 
 # }}}
@@ -157,7 +157,7 @@
     my $self = shift;
     my $Ticket = shift;
     $self->Limit(FIELD => 'Domain', OPERATOR => '=', VALUE => 'RT::Ticket-Role');
-    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => '$Ticket');
+    $self->Limit(FIELD => 'Instance', OPERATOR => '=', VALUE => int($Ticket));
 }
 
 # }}}
diff -ruN install/opt/rt3/lib/RT/Principal_Overlay.pm ../rt-3.0.1-anchor/lib/RT/Principal_Overlay.pm
--- install/opt/rt3/lib/RT/Principal_Overlay.pm	2003-04-16 01:54:15.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Principal_Overlay.pm	2003-04-22 18:19:07.000000000 +1000
@@ -442,7 +442,7 @@
     my $self = shift;
     my $type = shift;
     my $id = shift;
-    my $clause = "(Groups.Domain = '".$type."-Role' AND Groups.Instance = '" . $id. "') ";
+    my $clause = "(Groups.Domain = '".$type."-Role' AND Groups.Instance = " . $id. ") ";
 
     return($clause);
 }
diff -ruN install/opt/rt3/lib/RT/Queue_Overlay.pm ../rt-3.0.1-anchor/lib/RT/Queue_Overlay.pm
--- install/opt/rt3/lib/RT/Queue_Overlay.pm	2003-04-16 01:54:15.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Queue_Overlay.pm	2003-04-22 18:19:24.000000000 +1000
@@ -487,7 +487,7 @@
 
     foreach my $type (@types) {
         my $type_obj = RT::Group->new($self->CurrentUser);
-        my ($id, $msg) = $type_obj->CreateRoleGroup(Instance => $self->Id, 
+        my ($id, $msg) = $type_obj->CreateRoleGroup(Instance => int($self->Id), 
                                                      Type => $type,
                                                      Domain => 'RT::Queue-Role');
         unless ($id) {
diff -ruN install/opt/rt3/lib/RT/Ticket_Overlay.pm ../rt-3.0.1-anchor/lib/RT/Ticket_Overlay.pm
--- install/opt/rt3/lib/RT/Ticket_Overlay.pm	2003-04-16 01:54:16.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Ticket_Overlay.pm	2003-04-22 18:19:48.000000000 +1000
@@ -1279,7 +1279,7 @@
     foreach my $type (@types) {
         my $type_obj = RT::Group->new($self->CurrentUser);
         my ($id, $msg) = $type_obj->CreateRoleGroup(Domain => 'RT::Ticket-Role',
-                                                       Instance => $self->Id, 
+                                                       Instance => int($self->Id), 
                                                        Type => $type);
         unless ($id) {
             $RT::Logger->error("Couldn't create a ticket group of type '$type' for ticket ".
diff -ruN install/opt/rt3/lib/RT/Users_Overlay.pm ../rt-3.0.1-anchor/lib/RT/Users_Overlay.pm
--- install/opt/rt3/lib/RT/Users_Overlay.pm	2003-04-16 01:54:16.000000000 +1000
+++ ../rt-3.0.1-anchor/lib/RT/Users_Overlay.pm	2003-04-22 18:20:58.000000000 +1000
@@ -251,8 +251,8 @@
     if ( defined $args{'Object'} ) {
     if ( ref($args{'Object'}) eq 'RT::Ticket' ) {
         $or_check_ticket_roles =
-          " OR ( $groups.Domain = 'RT::Ticket-Role' AND $groups.Instance = '"
-          . $args{'Object'}->Id . "') ";
+          " OR ( $groups.Domain = 'RT::Ticket-Role' AND $groups.Instance = "
+          . $args{'Object'}->Id . ") ";
 
         # If we're looking at ticket rights, we also want to look at the associated queue rights.
         # this is a little bit hacky, but basically, now that we've done the ticket roles magic, we load the queue object
@@ -263,9 +263,9 @@
     # TODO XXX This really wants some refactoring
     if ( ref($args{'Object'}) eq 'RT::Queue' ) {
         $or_check_roles =
-          " OR ( ( ($groups.Domain = 'RT::Queue-Role' AND $groups.Instance = '"
+          " OR ( ( ($groups.Domain = 'RT::Queue-Role' AND $groups.Instance = "
           . $args{'Object'}->Id
-          . "') $or_check_ticket_roles ) "
+          . ") $or_check_ticket_roles ) "
           . " AND $groups.Type = $acl.PrincipalType AND $groups.Id = $groupprinc.id AND $groupprinc.PrincipalType = 'Group') ";
     }
 
>
>
>_______________________________________________
>rt-users mailing list
>rt-users at lists.fsck.com
>http://lists.fsck.com/mailman/listinfo/rt-users
>
>Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

-- 
jaq at spacepants.org                           http://spacepants.org/jaq.gpg



More information about the rt-users mailing list