Problem (?) with DBIx::Searchbuilder

Ulrich_Kiermayr · April 26, 2003, 11:15am

Hello,

Since an Upgrade I have often the folloing message im my logs:
RT: Database handle destroyed without explicit disconnect at
/usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line
269. (/opt/rt3/lib/RT.pm:222)

I have not (yet) spotted any serious consequences, but it does not feel
right:

Setup:
perl 5.8.0
Apache/1.3.27 (Unix) mod_ssl/2.8.12 OpenSSL/0.9.6g mod_perl/1.27
configured – resuming normal operations

Modules are all new form CPAN
DBIx::SearchBuilder is 0.81_4
All of the above compiled from sources, the rest is RedHat 7.3

RT is 3.0.2pre3 and the Database is Postgres.

any ideas what might cause this?
lg + thx uk
Ulrich Kiermayr Zentraler Informatikdienst der Universitaet Wien
Network Security Universitaetsstrasse 7, 1010 Wien, Austria
eMail: ulrich.kiermayr@univie.ac.at Tel: (+43 1) 4277 / 14104
Hotline: security.zid@univie.ac.at Fax: (+43 1) 4277 / 9140
GPG Key fingerprint = BF0D 5749 4DC1 ED74 AB67 7180 105F 491D A8D7 64D8

Vajko_Peter · April 26, 2003, 1:32pm

RT 3.0.2pre3 with postgresql 7.3 is extremely slow at these actions. It
starts a select like this:

SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups
Groups_2, Principals Pr
incipals_3, Principals Principals_4, ACL ACL_5, CachedGroupMembers
CachedGroupMembers_6, CachedGroupMember
s CachedGroupMembers_7 WHERE ((ACL_5.RightName = 'Su

and then postgres just runs, runs and runs (CPU 100%). About 3-4 minutes
later I’ve got the result. All this with a database with 10 users and
about 150 tickets. I suspect the WhoHaveRigths sub in Users_Overlay.pm.

Any ideas why this is happening?

Jamie_Wilkinson · April 28, 2003, 5:29am

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@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@spacepants.org http://spacepants.org/jaq.gpg

Vajko_Peter · April 29, 2003, 6:59am

Jamie Wilkinson 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:

It did not help very much. What really helped was the upgrade to the
latest SearchBuilder (0.81_04). 0.80 which is required by RT is not able
to cope with Postgres.

Jamie_Wilkinson · May 2, 2003, 12:15am

This one time, at band camp, Vajkó Péter wrote:

Jamie Wilkinson 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:

It did not help very much. What really helped was the upgrade to the
latest SearchBuilder (0.81_04). 0.80 which is required by RT is not able
to cope with Postgres.

Yeah, you shuold have tried that in the first place. The
integer-instead-of-varchar patch helps speed up a different query after the
SearchBuilder upgrade.

jaq@spacepants.org http://spacepants.org/jaq.gpg