Huge Attachments table in 3.4.4

RT Users
1

Hi.

I have upgraded RT from 3.0.11 to 3.4.4. Mysql 4.0.18, Apache 1.3.27
(with static modperl module).
The database we are using is quite large.
The main problem we are having is when a user needs to comment on a
ticket, the loading of a ticket can take quite a long time.
This is due to mysql churning through data.
I’d like to find out how to speed up this process.

How do I find out what tables mysql is checking through when RT is
loading the Update.html page?

I’m pretty sure that the lag is caused by an oversized attachments table.
Are there any suggestions for additional indexes on the attachments
table that could speed things up?

Thanks.

Kind regards.

Luke

2

I’m pretty sure that the lag is caused by an oversized attachments table.
Are there any suggestions for additional indexes on the attachments
table that could speed things up?

For what it’s worth, the easiest and most cost-effective solution for
speed problems on DBMS-backed apps is always “get more RAM”.

By “More RAM”, I mean that (given today’s prices, and assuming it’s a
production system supporting real people spending real money) 2GB isn’t
remotely unreasonable. Step 2 is “split the code from the DBMS”; that
one may be pricey enough to require more analysis.

It’s almost always a first step cheaper than lots of diagnosis time,
unless your time is free. (Really free, not just no-marginal-cost
free).

Cheers,
– jra
Jay R. Ashworth jra@baylink.com
Designer Baylink RFC 2100
Ashworth & Associates The Things I Think '87 e24
St Petersburg FL USA http://baylink.pitas.com +1 727 647 1274

"NPR has a lot in common with Nascar... we both turn to the left."
	- Peter Sagal, on Wait Wait, Don't Tell Me!
3

Hi.

I have upgraded RT from 3.0.11 to 3.4.4. Mysql 4.0.18, Apache 1.3.27
(with static modperl module).
The database we are using is quite large.
The main problem we are having is when a user needs to comment on a
ticket, the loading of a ticket can take quite a long time.
This is due to mysql churning through data.
I’d like to find out how to speed up this process.

How do I find out what tables mysql is checking through when RT is
loading the Update.html page?
http://wiki.bestpractical.com/?Debug

I’m pretty sure that the lag is caused by an oversized attachments table.
Are there any suggestions for additional indexes on the attachments
table that could speed things up?

Thanks.

Kind regards.

–
Luke

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com

Best regards, Ruslan.

4

Hi.

I have upgraded RT from 3.0.11 to 3.4.4. Mysql 4.0.18, Apache 1.3.27
(with static modperl module).
The database we are using is quite large.
The main problem we are having is when a user needs to comment on a
ticket, the loading of a ticket can take quite a long time.
This is due to mysql churning through data.
I’d like to find out how to speed up this process.

How do I find out what tables mysql is checking through when RT is
loading the Update.html page?

You might want to start with Jeremy Zawodney’s ‘mytop’ tool.

Jesse

5

Hi.

Jesse Vincent wrote:

Hi.

I have upgraded RT from 3.0.11 to 3.4.4. Mysql 4.0.18, Apache 1.3.27
(with static modperl module).
The database we are using is quite large.
The main problem we are having is when a user needs to comment on a
ticket, the loading of a ticket can take quite a long time.
This is due to mysql churning through data.
I’d like to find out how to speed up this process.

How do I find out what tables mysql is checking through when RT is
loading the Update.html page?

You might want to start with Jeremy Zawodney’s ‘mytop’ tool.

Thanks. I have installed this and it’s great :-).

I have found by using mysqladmin ‘processlist’ and by logging
slowqueries that the query responsible for the slowness is this:
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND
( Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain =
‘UserDefined’ OR Groups_3.Domain = ‘ACLEquivalence’)) OR ( (
(Groups_3.Domain = ‘RT::Queue-Role’ AND Groups_3.Instance = 23) OR (
Groups_3.Domain = ‘RT::Ticket-Role’ AND Groups_3.Instance = 238803) )
AND Groups_3.Type = ACL_2.PrincipalType) ) AND (ACL_2.ObjectType =
‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’ AND ACL_2.ObjectId =
23) ) ORDER BY main.Name ASC;
________________________________

Any ideas on how to increase the speed of this query?
I’m currently running RT 3.4.4 upgraded from 3.0.11 on a relatively slow
box.
If I run the query after a fresh restart of mysql, it takes 150 seconds
(yes it’s a large database :slight_smile:
If I then run the following command
‘optimize table Users, Principals, ACL, Groups, CachedGroupMembers;’
the query takes roughly 90 seconds to complete.
This is an improvement but hasn’t addressed the root cause of why the
query takes so long.

There may be an index that I can add to the tables to improve
performance to normal speed which should be around 3-5 seconds on this box.

Here are the existing indexes on the tables involved in this query:
/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> show index from Users;
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| Users | 0 | PRIMARY | 1 | id |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 0 | Users1 | 1 | Name |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 1 | Users2 | 1 | Name |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 1 | Users3 | 1 | id |
A | 99402 | NULL | NULL | | BTREE | |
| Users | 1 | Users3 | 2 | EmailAddress |
A | 99402 | NULL | NULL | YES | BTREE | |
| Users | 1 | Users4 | 1 | EmailAddress |
A | 99402 | NULL | NULL | YES | BTREE | |
mysql> show index from Principals;
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| Principals | 0 | PRIMARY | 1 | id |
A | 1212037 | NULL | NULL | | BTREE | |
| Principals | 1 | Principals2 | 1 | ObjectId |
A | 1212037 | NULL | NULL | YES | BTREE | |
mysql> show index from ACL;
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| ACL | 0 | PRIMARY | 1 | id |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 1 | RightName |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 2 | ObjectType |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 3 | ObjectId |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 4 | PrincipalType |
A | 77 | NULL | NULL | | BTREE | |
| ACL | 1 | ACL1 | 5 | PrincipalId |
A | 77 | NULL | NULL | | BTREE | |
mysql> show index from Groups;
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| Groups | 0 | PRIMARY | 1 | id |
A | 1094066 | NULL | NULL | | BTREE | |
| Groups | 1 | Groups2 | 1 | Type |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups2 | 2 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups2 | 3 | Domain |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Instance | 1 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 2 | Instance |
A | 547033 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 3 | Type |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Domain_3 | 4 | id |
A | 1094066 | NULL | NULL | | BTREE | |
| Groups | 1 | Groups1 | 1 | Domain |
A | 17 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups1 | 2 | Instance |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups1 | 3 | Type |
A | 1094066 | NULL | NULL | YES | BTREE | |
| Groups | 1 | Groups1 | 4 | id |
A | 1094066 | NULL | NULL | | BTREE | |
mysql> show index from CachedGroupMembers;
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
| CachedGroupMembers | 0 | PRIMARY | 1 |
id | A | 2491767 | NULL | NULL | |
BTREE | |
| CachedGroupMembers | 1 | DisGrouMem | 1 |
GroupId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | DisGrouMem | 2 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | DisGrouMem | 3 |
Disabled | A | 2491767 | NULL | NULL | |
BTREE | |
| CachedGroupMembers | 1 | GrouMem | 1 |
GroupId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | GrouMem | 2 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | MemberId | 1 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | ImmediateParentId | 1 |
ImmediateParentId | A | 2491767 | NULL | NULL | YES |
BTREE | |
| CachedGroupMembers | 1 | ImmediateParentId | 2 |
MemberId | A | 2491767 | NULL | NULL | YES |
BTREE | |
________________________________________________

Any tips or suggestions on how to improve performance of this
application would be highly appreciated :slight_smile:
Kind regards.
Luke.

Luke

6

[ lots of info cut for brevity except the problem query ]

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND
( Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain =
‘UserDefined’ OR Groups_3.Domain = ‘ACLEquivalence’)) OR ( (
(Groups_3.Domain = ‘RT::Queue-Role’ AND Groups_3.Instance = 23) OR (
Groups_3.Domain = ‘RT::Ticket-Role’ AND Groups_3.Instance = 238803) )
AND Groups_3.Type = ACL_2.PrincipalType) ) AND (ACL_2.ObjectType =
‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’ AND ACL_2.ObjectId =
23) ) ORDER BY main.Name ASC;

Maybe this is the answer:

http://rt3.fsck.com/Ticket/Display.html?id=6624

Note: The above link used to work, now it asks
me for a login. We add these indicies to
speed up the ACL processing stuff.

Jesse, isn’t the RT bug database open for public review?

Here are the postgresql index creation instructions.

create index groups3 on groups(instance);
create index groups4 on groups(lower(type));
create index groups5 on groups(lower(domain));

You may also want to add more RAM to your server. We also found that
when the sessions table gets large that some degredation tends to occur.
Here is the postgresql statement that we use to clean out older sessions.

DELETE FROM sessions WHERE lastupdated < (NOW() - INTERVAL ‘3 DAY’)

Let us all know your results. Thanks.

dano

7

Hi Dan.
Thanks.

I’ll try out the options you specified for indexing and keep the list
informed.

Dan O’Neill wrote:

Oh, and you may want to optimize your tables on a daily basis with
something along the lines of

myisamchk --quick --check-only-changed --sort-index --analyze --optimize

I may not have all the options correct as I’ve migrated away from
mysql to postgresql for all my database needs. Regardless, we do
garbage collection/optimization on our RT database using the
postgresql vacuumdb command on a daily basis - it helps.

I’m using innodb tables. :slight_smile:

Luke

8

Maybe this is the answer:

http://rt3.fsck.com/Ticket/Display.html?id=6624

Note: The above link used to work, now it asks
me for a login. We add these indicies to
speed up the ACL processing stuff.

Jesse, isn’t the RT bug database open for public review?

guest/guest

9

Hi.

Ok. I’m now at the point where:
-(I’m about to fetch the axe from the shed :slight_smile:
-I’ve removed all indexes from the Groups table
-created the following indexes:

create unique index groups_key on Groups(id);
create index Groups1 ON Groups (Domain, Instance, Type, id);
create index Groups2 ON Groups (Type, Instance, Domain);

Then I did a trial run: 138 seconds to complete the query

I created 2 more indexes

create index Groups3 on Groups(Instance);
create index Groups4 on Groups(Type);

Another trial run: 137 seconds.

I created the following index:
create index Groups5 on Groups(Domain);

144 seconds.

The query in question is the same as on the following site and the
indexes are taken from that site.
http://rt3.fsck.com/Ticket/Display.html?id=6624

after dropping the last index I added (it seemed to only worsen the pain) I
stopped and started both mysql and apache
then got 150 seconds.
hmmm…

So these particular indexes didn’t help.

I’ve used the expain select syntax to show what keys are being used in
the query, it show that I have no keys being used on the Users table.
I’ve tried several keys on the Users table but with no success.
I’ve documented everything so can supply you with the indexes if interested.

I’m sure there is an index out there that does what I want,
Any suggestions/help appreciated.

Thanks.
Kind regards.
Luke.

Luke

10

Luke,

Are you up for trying the patch we’ve been testing for this? If so,
please see attachedOn Fri, Oct 28, 2005 at 04:41:05PM +0930, Luke Vanderfluit wrote:

Hi.

Ok. I’m now at the point where:
-(I’m about to fetch the axe from the shed :slight_smile:
-I’ve removed all indexes from the Groups table
-created the following indexes:

create unique index groups_key on Groups(id);
create index Groups1 ON Groups (Domain, Instance, Type, id);
create index Groups2 ON Groups (Type, Instance, Domain);

Then I did a trial run: 138 seconds to complete the query

I created 2 more indexes

create index Groups3 on Groups(Instance);
create index Groups4 on Groups(Type);

Another trial run: 137 seconds.

I created the following index:
create index Groups5 on Groups(Domain);

144 seconds.

The query in question is the same as on the following site and the
indexes are taken from that site.
http://rt3.fsck.com/Ticket/Display.html?id=6624

after dropping the last index I added (it seemed to only worsen the pain) I
stopped and started both mysql and apache
then got 150 seconds.
hmmm…

So these particular indexes didn’t help.

I’ve used the expain select syntax to show what keys are being used in
the query, it show that I have no keys being used on the Users table.
I’ve tried several keys on the Users table but with no success.
I’ve documented everything so can supply you with the indexes if interested.

I’m sure there is an index out there that does what I want,
Any suggestions/help appreciated.

Thanks.
Kind regards.
Luke.

–
Luke

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com

rights.patch (29.7 KB)

clone.patch (781 Bytes)

11

Hi.

Jesse Vincent wrote:

in dbix::searchbuilder

Thanks,
I’ll let you know if it helped :slight_smile:

Kind regards.
Luke.

-----Original Message-----
From: Luke Vanderfluit lvanderf@internode.com.au
Date: Sunday, Oct 30, 2005 9:02 pm
Subject: Re: [rt-users] Huge Attachments table in 3.4.4

Hi.

Jesse Vincent wrote:
Luke,

Are you up for trying the patch we’ve been testing for this? If so, please see attached

I’ve added the patches from the rights.patch file to the appropriate files. That’s fine.

However I can’t find the right location in the SearchBuilder.pm file to insert the code from the clone.patch file.

I’m using 3.4.4, where do I insert the code from the clone.patch file?

Thanks.

Kind regards.

Luke.

     Hi.

Ok. I’m now at the point where:
-(I’m about to fetch the axe from the shed :slight_smile:
-I’ve removed all indexes from the Groups table
-created the following indexes:

create unique index groups_key on Groups(id);
create index Groups1 ON Groups (Domain, Instance, Type, id);
create index Groups2 ON Groups (Type, Instance, Domain);

Then I did a trial run: 138 seconds to complete the query

I created 2 more indexes

create index Groups3 on Groups(Instance);
create index Groups4 on Groups(Type);

Another trial run: 137 seconds.

I created the following index:
create index Groups5 on Groups(Domain);

144 seconds.

The query in question is the same as on the following site and the
indexes are taken from that site.
http://rt3.fsck.com/Ticket/Display.html?id=6624 after dropping the last index I added (it seemed to only worsen the pain) I stopped and started both mysql and apache
then got 150 seconds.
hmmm…

So these particular indexes didn’t help.

I’ve used the expain select syntax to show what keys are being used in
the query, it show that I have no keys being used on the Users table.
I’ve tried several keys on the Users table but with no success.
I’ve documented everything so can supply you with the indexes if interested.

I’m sure there is an index out there that does what I want,
Any suggestions/help appreciated.

Thanks.
Kind regards.
Luke.

Luke

12

Hi Jesse.

I have entered the modification into the SearchBuilder.pm file
Then recompile the DBIx::SearchBuilder package.

Now I get a message that Clone.pm can’t be located in the @INC

And the install of DBIx::SearchBuilder fails.

If I install that module without the modification, then try to reload
RT, it complains as well :frowning:

I think the first way is the way but how do I fix the problem that it
can’t find Clone.pm.

Thanks.
Kind regards.
Luke.

Jesse Vincent wrote:

in dbix::searchbuilder
Best,
Jesse

-----Original Message-----
From: Luke Vanderfluit lvanderf@internode.com.au
Date: Sunday, Oct 30, 2005 9:02 pm
Subject: Re: [rt-users] Huge Attachments table in 3.4.4

Hi.

Jesse Vincent wrote:
Luke,

Are you up for trying the patch we’ve been testing for this? If so, please see attached

I’ve added the patches from the rights.patch file to the appropriate files. That’s fine.

However I can’t find the right location in the SearchBuilder.pm file to insert the code from the clone.patch file.

I’m using 3.4.4, where do I insert the code from the clone.patch file?

Thanks.

Kind regards.

Luke.

     Hi.

Ok. I’m now at the point where:
-(I’m about to fetch the axe from the shed :slight_smile:
-I’ve removed all indexes from the Groups table
-created the following indexes:

create unique index groups_key on Groups(id);
create index Groups1 ON Groups (Domain, Instance, Type, id);
create index Groups2 ON Groups (Type, Instance, Domain);

Then I did a trial run: 138 seconds to complete the query

I created 2 more indexes

create index Groups3 on Groups(Instance);
create index Groups4 on Groups(Type);

Another trial run: 137 seconds.

I created the following index:
create index Groups5 on Groups(Domain);

144 seconds.

The query in question is the same as on the following site and the
indexes are taken from that site.
http://rt3.fsck.com/Ticket/Display.html?id=6624 after dropping the last index I added (it seemed to only worsen the pain) I stopped and started both mysql and apache
then got 150 seconds.
hmmm…

So these particular indexes didn’t help.

I’ve used the expain select syntax to show what keys are being used in
the query, it show that I have no keys being used on the Users table.
I’ve tried several keys on the Users table but with no success.
I’ve documented everything so can supply you with the indexes if interested.

I’m sure there is an index out there that does what I want,
Any suggestions/help appreciated.

Thanks.
Kind regards.
Luke.

Luke

13

Try installing Clone from CPAN?On Mon, Oct 31, 2005 at 02:29:12PM +1030, Luke Vanderfluit wrote:

Hi Jesse.

I have entered the modification into the SearchBuilder.pm file
Then recompile the DBIx::SearchBuilder package.

Now I get a message that Clone.pm can’t be located in the @INC

And the install of DBIx::SearchBuilder fails.

If I install that module without the modification, then try to reload
RT, it complains as well :frowning:

I think the first way is the way but how do I fix the problem that it
can’t find Clone.pm.

Thanks.
Kind regards.
Luke.

Jesse Vincent wrote:

in dbix::searchbuilder
Best,
Jesse

-----Original Message-----
From: Luke Vanderfluit lvanderf@internode.com.au
Date: Sunday, Oct 30, 2005 9:02 pm
Subject: Re: [rt-users] Huge Attachments table in 3.4.4

Hi.

Jesse Vincent wrote:
Luke,

Are you up for trying the patch we’ve been testing for this? If so, please
see attached

I’ve added the patches from the rights.patch file to the appropriate
files. That’s fine.

However I can’t find the right location in the SearchBuilder.pm file to
insert the code from the clone.patch file.

I’m using 3.4.4, where do I insert the code from the clone.patch file?

Thanks.

Kind regards.

Luke.

On Fri, Oct 28, 2005 at 04:41:05PM +0930, Luke Vanderfluit wrote:
Hi.

Ok. I’m now at the point where:
-(I’m about to fetch the axe from the shed :slight_smile:
-I’ve removed all indexes from the Groups table
-created the following indexes:

create unique index groups_key on Groups(id);
create index Groups1 ON Groups (Domain, Instance, Type, id);
create index Groups2 ON Groups (Type, Instance, Domain);

Then I did a trial run: 138 seconds to complete the query

I created 2 more indexes

create index Groups3 on Groups(Instance);
create index Groups4 on Groups(Type);

Another trial run: 137 seconds.

I created the following index:
create index Groups5 on Groups(Domain);

144 seconds.

The query in question is the same as on the following site and the
indexes are taken from that site.
http://rt3.fsck.com/Ticket/Display.html?id=6624 after dropping the last
index I added (it seemed to only worsen the pain) I stopped and started
both mysql and apache
then got 150 seconds.
hmmm…

So these particular indexes didn’t help.

I’ve used the expain select syntax to show what keys are being used in
the query, it show that I have no keys being used on the Users table.
I’ve tried several keys on the Users table but with no success.
I’ve documented everything so can supply you with the indexes if
interested.

I’m sure there is an index out there that does what I want,
Any suggestions/help appreciated.

Thanks.
Kind regards.
Luke.

–
Luke

14

Hi Jesse.

Jesse Vincent wrote:

Try installing Clone from CPAN?

Duh, What’s with my brain today?

Guess what.
An absolute improvement :slight_smile:
The query is now loading in normal time, yiha!

Thanks for your help,
Kind regards.
Luke.>On Mon, Oct 31, 2005 at 02:29:12PM +1030, Luke Vanderfluit wrote:

Hi Jesse.

I have entered the modification into the SearchBuilder.pm file
Then recompile the DBIx::SearchBuilder package.

Now I get a message that Clone.pm can’t be located in the @INC

And the install of DBIx::SearchBuilder fails.

If I install that module without the modification, then try to reload
RT, it complains as well :frowning:

I think the first way is the way but how do I fix the problem that it
can’t find Clone.pm.

Thanks.
Kind regards.
Luke.

Jesse Vincent wrote:

in dbix::searchbuilder
Best,
Jesse

-----Original Message-----
From: Luke Vanderfluit lvanderf@internode.com.au
Date: Sunday, Oct 30, 2005 9:02 pm
Subject: Re: [rt-users] Huge Attachments table in 3.4.4

Hi.

Jesse Vincent wrote:
Luke,

Are you up for trying the patch we’ve been testing for this? If so, please
see attached

I’ve added the patches from the rights.patch file to the appropriate
files. That’s fine.

However I can’t find the right location in the SearchBuilder.pm file to
insert the code from the clone.patch file.

I’m using 3.4.4, where do I insert the code from the clone.patch file?

Thanks.

Kind regards.

Luke.

On Fri, Oct 28, 2005 at 04:41:05PM +0930, Luke Vanderfluit wrote:
Hi.

Ok. I’m now at the point where:
-(I’m about to fetch the axe from the shed :slight_smile:
-I’ve removed all indexes from the Groups table
-created the following indexes:

create unique index groups_key on Groups(id);
create index Groups1 ON Groups (Domain, Instance, Type, id);
create index Groups2 ON Groups (Type, Instance, Domain);

Then I did a trial run: 138 seconds to complete the query

I created 2 more indexes

create index Groups3 on Groups(Instance);
create index Groups4 on Groups(Type);

Another trial run: 137 seconds.

I created the following index:
create index Groups5 on Groups(Domain);

144 seconds.

The query in question is the same as on the following site and the
indexes are taken from that site.
http://rt3.fsck.com/Ticket/Display.html?id=6624 after dropping the last
index I added (it seemed to only worsen the pain) I stopped and started
both mysql and apache
then got 150 seconds.
hmmm…

So these particular indexes didn’t help.

I’ve used the expain select syntax to show what keys are being used in
the query, it show that I have no keys being used on the Users table.
I’ve tried several keys on the Users table but with no success.
I’ve documented everything so can supply you with the indexes if
interested.

I’m sure there is an index out there that does what I want,
Any suggestions/help appreciated.

Thanks.
Kind regards.
Luke.

–
Luke

Luke

15

Hi.

The patches that you sent and I have applied to the installation of
rt3.4.4 are working well for the query we were having trouble with.
However now I’m having trouble with another query.
This query:

SELECT DISTINCT main.* FROM Groups main , Principals Principals_1 WHERE
((Principals_1.Disabled = ‘0’)) AND ((main.Domain = ‘UserDefined’)) AND
((main.id = Principals_1.id)or(main.id != ‘2926’)) ORDER BY main.Name ASC

is generated when I want to view the ‘member’ of a group

It seems that the connection to the server is lost.
If I try a simple query in the mysql client after I have executed the
above query (requested the Members.html page) from a browser, the
connection to the server is lost.

I hope this isn’t too hard to fix. It seems to be a consequence of
applying the patches.

When I killed the mysql process, I got the following error messages:

error: DBD::mysql::st execute failed: Lost connection to MySQL
server during query at
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm
line 67.
context:
…
63:
64: $self->{update_sth}->bind_param(1, $session->{serialized});
65: $self->{update_sth}->bind_param(2,
$session->{data}->{_session_id});
66:
67: $self->{update_sth}->execute;
68:
69: $self->{update_sth}->finish;
70: }
71:
…

code stack:
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm:67
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm:523
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm:477
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm:1068

raw error
http://bld-sparc.internode.on.net/Admin/Groups/Members.html?id=2926#raw

DBD::mysql::st execute failed: Lost connection to MySQL server during query at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm line 67.

Trace begun at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Exceptions.pm line 131
HTML::Mason::Exceptions::rethrow_exception(‘DBD::mysql::st execute failed: Lost connection to MySQL server during query at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm line 67.^J’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm line 67
Apache::Session::Store::DBI::update(‘Apache::Session::Store::MySQL=HASH(0x1ed5cd4)’, ‘Apache::Session::MySQL=HASH(0x1ed5c80)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm line 523
Apache::Session::save(‘Apache::Session::MySQL=HASH(0x1ed5c80)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm line 477
Apache::Session::DESTROY(‘Apache::Session::MySQL=HASH(0x1ed5c80)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 1068
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 1068
HTML::Mason::Request::comp(undef, undef, undef, ‘id’, 2926) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 338
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 338
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 297
HTML::Mason::Request::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0x1b28a94)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm line 134
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm line 134
HTML::Mason::Request::ApacheHandler::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0x1b28a94)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm line 827
HTML::Mason::ApacheHandler::handle_request(‘HTML::Mason::ApacheHandler=HASH(0x18e2554)’, ‘Apache=SCALAR(0x1a1cfdc)’) called at /usr/local/rt3/bin/webmux.pl line 123
eval {…} at /usr/local/rt3/bin/webmux.pl line 123
RT::Mason::handler(‘Apache=SCALAR(0x1a1cfdc)’) called at /dev/null line 0
eval {…} at /dev/null line 0

Kind regards.
Luke.

Luke

16

Hi.

I have added 2 indexes to the database.
That seems to have solved the problem.
I added:

create index Principals4 on Principals(Disabled);
create index Groups5 on Groups(Domain);

After that, all was fine :slight_smile:

Kind regards.
Luke.

Luke Vanderfluit wrote:

Hi.

The patches that you sent and I have applied to the installation of
rt3.4.4 are working well for the query we were having trouble with.
However now I’m having trouble with another query.
This query:

SELECT DISTINCT main.* FROM Groups main , Principals Principals_1
WHERE ((Principals_1.Disabled = ‘0’)) AND ((main.Domain =
‘UserDefined’)) AND ((main.id = Principals_1.id)or(main.id !=
‘2926’)) ORDER BY main.Name ASC

is generated when I want to view the ‘member’ of a group

It seems that the connection to the server is lost.
If I try a simple query in the mysql client after I have executed the
above query (requested the Members.html page) from a browser, the
connection to the server is lost.

I hope this isn’t too hard to fix. It seems to be a consequence of
applying the patches.

When I killed the mysql process, I got the following error messages:

error: DBD::mysql::st execute failed: Lost connection to MySQL
server during query at
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm
line 67.
context:
…
63:
64: $self->{update_sth}->bind_param(1, $session->{serialized});
65: $self->{update_sth}->bind_param(2,
$session->{data}->{_session_id});
66:
67: $self->{update_sth}->execute;
68:
69: $self->{update_sth}->finish;
70: }
71:
…

code stack:
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm:67
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm:523
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm:477
/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm:1068

raw error
http://bld-sparc.internode.on.net/Admin/Groups/Members.html?id=2926#raw

DBD::mysql::st execute failed: Lost connection to MySQL server during query at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm line 67.

Trace begun at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Exceptions.pm line 131
HTML::Mason::Exceptions::rethrow_exception(‘DBD::mysql::st execute failed: Lost connection to MySQL server during query at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm line 67.^J’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session/Store/DBI.pm line 67
Apache::Session::Store::DBI::update(‘Apache::Session::Store::MySQL=HASH(0x1ed5cd4)’, ‘Apache::Session::MySQL=HASH(0x1ed5c80)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm line 523
Apache::Session::save(‘Apache::Session::MySQL=HASH(0x1ed5c80)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Apache/Session.pm line 477
Apache::Session::DESTROY(‘Apache::Session::MySQL=HASH(0x1ed5c80)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 1068
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 1068
HTML::Mason::Request::comp(undef, undef, undef, ‘id’, 2926) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 338
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 338
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/Request.pm line 297
HTML::Mason::Request::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0x1b28a94)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm line 134
eval {…} at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm line 134
HTML::Mason::Request::ApacheHandler::exec(‘HTML::Mason::Request::ApacheHandler=HASH(0x1b28a94)’) called at /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm line 827
HTML::Mason::ApacheHandler::handle_request(‘HTML::Mason::ApacheHandler=HASH(0x18e2554)’, ‘Apache=SCALAR(0x1a1cfdc)’) called at /usr/local/rt3/bin/webmux.pl line 123
eval {…} at /usr/local/rt3/bin/webmux.pl line 123
RT::Mason::handler(‘Apache=SCALAR(0x1a1cfdc)’) called at /dev/null line 0
eval {…} at /dev/null line 0

Kind regards.
Luke.

–
Luke

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com

Luke