Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi Ruslan,

I have changed those indexes this morning and will monitor the results 
today. I created the second new index as CGM2 to prevent the system 
complaining about duplicate keys.


Thanks

Richard

Ruslan Zakirov wrote:

4 seconds is still slow, but better than 100-400.

About your indexes. You can and I really suggest to delete the
following indexes on CGM table:
* DROP INDEX GrouMem ON CachedGroupMembers;
* DROP INDEX group1 ON CachedGroupMembers;
* DROP INDEX member1 ON CachedGroupMembers;

And instead create indexes:
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, ImmediateParentId);

Both will be part of 3.8's schema update.

On Wed, Mar 19, 2008 at 8:35 PM, Richard Ellis <[EMAIL PROTECTED]> wrote:
  

 Hi Ruslan,

 You are a genius. Response time for the Query Builder is now back to 4
seconds which is good enough for me :0.

 Thanks to all your team for all the efforts to work out what was wrong.


 Thanks

 Richard


 Ruslan Zakirov wrote:

 Hey, Rechard, the latest results suggest me that we've saddled this
beast :) at least that what explain says and I hope it's correct.

You can check that query again and it should be fast. Wanna try?

You can use SELECT SQL_NO_CACHE ... to make sure it's reproducible and
is not cache hit.



On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis <[EMAIL PROTECTED]>
wrote:


 Hi Ruslan,

 here's the two sets of results.


 Thanks

 Richard









  


--
Sun.com   * Richard Ellis *
Technical Developer, .Sun eBusiness

*Sun Microsystems, Inc.*
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email [EMAIL PROTECTED]
sun.com

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Thomas Hecker]

Hi Everybody,

well fist of all - thanks for this topic. You all made my day, because for
the moment my performance problem seems to be fixed. I had the same problem
with the owner-dropdown getting bigger and bogger ower time.

In my case the problem was the inside the main queue we use for first level
support the unprivileged user group had the "own ticket" permisson right
(don't ask me why). So my mistake was, that when i was looking for wrong
userpermissions i only looked at the users and the groups, not the queues.

So mayby some of you won't need to do some quick hackings, simply check the
permission of your queues.

Greetings
Thomas



Am 17.03.2008 12:19 Uhr schrieb "Mathew" unter <[EMAIL PROTECTED]>:

> You shouldn't have had to write a patch to fix the immense user drop
> down.  I've created queues with matching groups and assigned the own
> ticket right to only the groups that correspond to each queue.
> 
> The Everyone group only has CreateTicket on two public facing queues
> (all others are available for correspondence but not ticket creation),
> Priveleged Users has all the major rights which all users require across
> all queues and Unprivileged Users has only rights which customers would
> need to interact with a ticket.
> 
> This has provide more than enough lock down to keep users created by
> spam out of our drop down.
> 
> Mathew
> 
> Ham MI-ID, Torsten Brumm wrote:
>> Hi Mathew, Richard,
>> I tried also this weekend to upgrade to 3.6.6 and gave it up yesterday
>> evening (rolled back to 3.6.5).
>> 
>> To your Problem:
>> 
>> If you open the Search Builder menu, it takes a long time to build the
>> page.?!? Have a loko into the owner dropdown menu. Did you find there more
>> people as expacted? In my case i find a lot of people there, more than have
>> the rights to own tickets in the queues.
>> 
>> I have NOT SET the OwnTickets right globally  And now it will be very
>> strange at my Live systems (and test box too).
>> 
>> Inside the owner dropdown, i find also NOT PRIVILEGDED USERS!!!
>> 
>> OK, what i have tested:
>> 
>> Logged in as normal user with rights to 3 Queues.
>> 
>> This queues have per queue 5 people with the right to own a ticket here. (so
>> i looked for 15 people inside the owner dropdown) but i got a list of round
>> about several thousands!!!
>> 
>> OK to fix it fast:
>> 
>> Here is my diff to the /share/html/Search/Elements/PickBasics
>> 
>> [EMAIL PROTECTED]:/opt/rt3/local/html/Search/Elements# diff
>> /opt/rt3/local/html/Search/Elements/PickBasics
>> /opt/rt3/share/html/Search/Elements/PickBasics
>> 111,112c113
>> < 
>> < %#<& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute =>
>> 'Name' &>
>> ---
>>> <& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute => 'Name'
>>> &>
>> 
>> OK, it's replacing the SelectOwner Dropdown, which is not working well here
>> with a noremal input box.
>> 
>> This speeds up the Searchbuilder a lot!
>> 
>> Btw. This "Problem" with the Owner Dropdown inside the searchbuilder we have
>> since RT 3.4.x and it is not working well since this time.
>> 
>> Hope this helps.
>> Torsten 
>> 
>> 
>> 
>> Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.),
>> Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark
>> Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen,
>> Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich
>> haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg
>> Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne
>> 
>> 
>> -Ursprüngliche Nachricht-
>> Von: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] Im Auftrag von Mathew
>> Gesendet: Montag, 17. März 2008 11:06
>> An: Richard Ellis
>> Cc: rt-users@lists.bestpractical.com
>> Betreff: Re: [rt-users] RT 3.6.4 poor query performance
>> 
>> Well, in that case, I recommend witchcraft. ;)
>> 
>> Mathew
>> 
>> Richard Ellis wrote:
>>> Hi All,
>>> 
>>> We have upgraded to 3.6.6 over the weekend and also run some
>>> optimisation against the database but performance is still very poor.
>>> 
>>> I have looked at RTx::RightMatrix and Everyone definately does not
>>> have OwnTickets rights unless that is lying to me, which I doubt.
>>> I've used the tuni

Re: [rt-users] RT 3.6.4 poor query performance

[Ruslan Zakirov]

4 seconds is still slow, but better than 100-400.

About your indexes. You can and I really suggest to delete the
following indexes on CGM table:
* DROP INDEX GrouMem ON CachedGroupMembers;
* DROP INDEX group1 ON CachedGroupMembers;
* DROP INDEX member1 ON CachedGroupMembers;

And instead create indexes:
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, ImmediateParentId);

Both will be part of 3.8's schema update.

On Wed, Mar 19, 2008 at 8:35 PM, Richard Ellis <[EMAIL PROTECTED]> wrote:
>
>  Hi Ruslan,
>
>  You are a genius. Response time for the Query Builder is now back to 4
> seconds which is good enough for me :0.
>
>  Thanks to all your team for all the efforts to work out what was wrong.
>
>
>  Thanks
>
>  Richard
>
>
>  Ruslan Zakirov wrote:
>
>  Hey, Rechard, the latest results suggest me that we've saddled this
> beast :) at least that what explain says and I hope it's correct.
>
> You can check that query again and it should be fast. Wanna try?
>
> You can use SELECT SQL_NO_CACHE ... to make sure it's reproducible and
> is not cache hit.
>
>
>
> On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis <[EMAIL PROTECTED]>
> wrote:
>
>
>  Hi Ruslan,
>
>  here's the two sets of results.
>
>
>  Thanks
>
>  Richard
>
>
>
>



-- 
Best regards, Ruslan.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jeff Voskamp]

Jesse Vincent wrote:
>
> On Wed, Mar 19, 2008 at 07:42:30PM +0300, Ruslan Zakirov wrote:
>   
>> Jesse, I know that they both have index on CachedGroupMembers table
>> that starts from 'MemberId' column. And it does mess up optimizer and
>> doesn't matter if it's one column or multiple like in (MemberId,
>> GroupId, Disabled) index (Jeff created such thing). We really need
>> such index in the core on CGM table, otherwise people have problems
>> with searches by watchers (like in "Requestor is XXX" search or "More
>> about XXX" box). It's very sad that mysql can not deal with that. Fix
>> I've implemented in 3.6.6 helps people on setups with few ACL records
>> and few queues, but not in these two cases.
>> 
>
> Got it
It's a rename of one of the suggested indices in
http://search.cpan.org/~ruz/RTx-Shredder-0.07/lib/RTx/Shredder.pm
(see the Notes section).

CREATE INDEX SHREDDER_CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);



Jeff
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jeff Voskamp]

Ruslan Zakirov wrote:
> Jeff, always Cc the list.
>
> Version of your mysql server?
>
> As far as I can see you suffer from mysql bug, output from your server
> is equal in both cases what is really wrong and mysql must use new
> index in those test queries I sent to the list.
>
> There are several options:
> 1) Delete any indexes on CachedGroupMembers table which starts from
> MemberId column, but that will slowdown other queries and may be
> terribly, depends on proprotions of your DB.
> 2) Upgrade to mysql 5.0.45 or greater and create index I suggested in
> this thread earlier.
> 3) I have another idea how we can improve that in the code, but that
> needs more investigation with a lot of users' feedback and a lot of
> mine and users' time.
>
> As long as MySQL 4.x has ended its life time and 5.0.x is stable
> version then I think it's fair enough to recommend recent versions
> instead of continuose refactoring of the code to make all those broken
> mysqls happy.
>   
I'll try to remember to "reply all" from here on in.

We're on Mysql-5.0.22 as packaged by RedHat for Enterprise Linux 5.1.

Dropping indexes for now.  Can re-instate later. Then I can also drop my 
coding hacks.

Will look into getting a shiny new MySQL.

jeff Voskamp
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi Ruslan,

You are a genius. Response time for the Query Builder is now back to 4 
seconds which is good enough for me :0.


Thanks to all your team for all the efforts to work out what was wrong.

Thanks

Richard


Ruslan Zakirov wrote:

Hey, Rechard, the latest results suggest me that we've saddled this
beast :) at least that what explain says and I hope it's correct.

You can check that query again and it should be fast. Wanna try?

You can use SELECT SQL_NO_CACHE ... to make sure it's reproducible and
is not cache hit.



On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis <[EMAIL PROTECTED]> wrote:
  

 Hi Ruslan,

 here's the two sets of results.


 Thanks

 Richard




  
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Ruslan Zakirov]

Jeff, always Cc the list.

Version of your mysql server?

As far as I can see you suffer from mysql bug, output from your server
is equal in both cases what is really wrong and mysql must use new
index in those test queries I sent to the list.

There are several options:
1) Delete any indexes on CachedGroupMembers table which starts from
MemberId column, but that will slowdown other queries and may be
terribly, depends on proprotions of your DB.
2) Upgrade to mysql 5.0.45 or greater and create index I suggested in
this thread earlier.
3) I have another idea how we can improve that in the code, but that
needs more investigation with a lot of users' feedback and a lot of
mine and users' time.

As long as MySQL 4.x has ended its life time and 5.0.x is stable
version then I think it's fair enough to recommend recent versions
instead of continuose refactoring of the code to make all those broken
mysqls happy.

On Wed, Mar 19, 2008 at 6:22 PM, Jeff Voskamp <[EMAIL PROTECTED]> wrote:
>
> Ruslan Zakirov wrote:
>  > Ok, I have an idea how to fix that problem
>  >
>  > Here is new file for testing that will give me more info to find the
>  > best way to fixing this. We're really close.
>  >
>  > You can run it using:
>  > mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql 
> >test.res
>  >
>  > As a first step to fix it you can create the following index on Groups 
> table:
>  > CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);
>  >
>  > Please, run commands from the attachment twice before indexing and after.
>  >
>  > Thank you for the feedback.
>  >
>  > On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> wrote:
>  >
>  >>  Hi Ruslan,
>  >>
>  >>  Really appreciate the help on this. I'd love to find out why we are 
> seeing
>  >> such odd results:
>  >>
>  >>  298 ticket owners when their are only 88 active users
>  >>  1.5 million rows of data when we only have 9983 ticks as of this morning.
>  >>
>  >>  Really odd
>  >>
>  >>  Thanks
>  >>
>  >>  Richard
>  >>
>  Since we were also having problems here's our output.
>  spw.out is before. spw.out2 is after.
>
>  Jeff Voskamp
>  University of Waterloo
>
> ++-+--++--+--+-+---+--+--+
>  | id | select_type | table| type   | possible_keys   
>  | key  | key_len | ref   
> | rows | Extra|
>  
> ++-+--++--+--+-+---+--+--+
>  |  1 | SIMPLE  | main | range  | PRIMARY 
>  | PRIMARY  | 4   | NULL  
> | 4138 | Using where; Using temporary; Using filesort |
>  |  1 | SIMPLE  | Groups_3 | ref| 
> PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1a | 67  
> | const |  630 | Using where; Using 
> index; Distinct   |
>  |  1 | SIMPLE  | Principals_1 | eq_ref | PRIMARY,Principals4 
>  | PRIMARY  | 4   | rt3_inst.main.id  
> |1 | Using where; Distinct|
>  |  1 | SIMPLE  | CachedGroupMembers_2 | ref| DisGrouMem,MyCGM1   
>  | MyCGM1   | 10  | 
> rt3_inst.main.id,rt3_inst.Groups_3.id |1 | Using where; Using index; 
> Distinct   |
>  |  1 | SIMPLE  | ACL_4| range  | ACL1
>  | ACL1 | 54  | NULL  
> |  371 | Using where; Using index; Distinct   |
>  
> ++-+--++--+--+-+---+--+--+
>  +---+---+
>  | PrincipalType | COUNT(id) |
>  +---+---+
>  | Cc| 1 |
>  | Group |   372 |
>  +---+---+
>  
> ++-+---+---+---+--+-+--+--+--+
>  | id | select_type | table | type  | possible_keys | key  | key_len | ref  | 
> rows | Extra|
>  
> ++-+---+---+---+--+-+--+--+--+
>  |  1 | SIMPLE  | ACL_4 | range | ACL1  | ACL1 | 54  | NULL | 
>  371 | Using where; Using index |
>  
> +-

Re: [rt-users] RT 3.6.4 poor query performance

[Ruslan Zakirov]

Hey, Rechard, the latest results suggest me that we've saddled this
beast :) at least that what explain says and I hope it's correct.

You can check that query again and it should be fast. Wanna try?

You can use SELECT SQL_NO_CACHE ... to make sure it's reproducible and
is not cache hit.



On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis <[EMAIL PROTECTED]> wrote:
>
>  Hi Ruslan,
>
>  here's the two sets of results.
>
>
>  Thanks
>
>  Richard
>

-- 
Best regards, Ruslan.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Wed, Mar 19, 2008 at 07:42:30PM +0300, Ruslan Zakirov wrote:
> Jesse, I know that they both have index on CachedGroupMembers table
> that starts from 'MemberId' column. And it does mess up optimizer and
> doesn't matter if it's one column or multiple like in (MemberId,
> GroupId, Disabled) index (Jeff created such thing). We really need
> such index in the core on CGM table, otherwise people have problems
> with searches by watchers (like in "Requestor is XXX" search or "More
> about XXX" box). It's very sad that mysql can not deal with that. Fix
> I've implemented in 3.6.6 helps people on setups with few ACL records
> and few queues, but not in these two cases.

Got it.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Ruslan Zakirov]

Jesse, I know that they both have index on CachedGroupMembers table
that starts from 'MemberId' column. And it does mess up optimizer and
doesn't matter if it's one column or multiple like in (MemberId,
GroupId, Disabled) index (Jeff created such thing). We really need
such index in the core on CGM table, otherwise people have problems
with searches by watchers (like in "Requestor is XXX" search or "More
about XXX" box). It's very sad that mysql can not deal with that. Fix
I've implemented in 3.6.6 helps people on setups with few ACL records
and few queues, but not in these two cases.

On Wed, Mar 19, 2008 at 7:28 PM, Jesse Vincent <[EMAIL PROTECTED]> wrote:
>
>
>
>  On Wed, Mar 19, 2008 at 04:22:46PM +, Richard Ellis wrote:
>  > Hi Ruslan,
>  >
>  > here's the two sets of results.
>
>  FWIW, from your response to ruslan, it _does_ look like your hand-added
>  "group1" index was messing up the query planner. It's on GroupId, while
>  we already had an index on GroupId, MemberId.
>


-- 
Best regards, Ruslan.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Wed, Mar 19, 2008 at 04:38:12PM +, Richard Ellis wrote:
> Hi Jesse,
> 
> Thanks. To the best of my knowledge nobody has added any indexes to the 
> database on anything except what RT patches apply on each upgrade. This 
> DB was originally 3.0 and has been upgraded more times than I want to 
> think about over the years to 3.6.6 now :)

That index doesn't follow RT's standard index naming/capitalization scheme. 
Someone may have gone behind your back ;)

> 
> Richard
> 
> 
> Jesse Vincent wrote:
> >
> >On Wed, Mar 19, 2008 at 04:22:46PM +, Richard Ellis wrote:
> >  
> >>Hi Ruslan,
> >>
> >>here's the two sets of results.
> >>
> >
> >FWIW, from your response to ruslan, it _does_ look like your hand-added
> >"group1" index was messing up the query planner. It's on GroupId, while
> >we already had an index on GroupId, MemberId.
> >
> >
> >
> >
> >
> >  
> >>Thanks
> >>
> >>Richard
> >>
> >>
> >>Ruslan Zakirov wrote:
> >>
> >>>Ok, I have an idea how to fix that problem
> >>>
> >>>Here is new file for testing that will give me more info to find the
> >>>best way to fixing this. We're really close.
> >>>
> >>>You can run it using:
> >>>mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql 
> >>>  
> test.res
> 
> >>>As a first step to fix it you can create the following index on Groups 
> >>>table:
> >>>CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);
> >>>
> >>>Please, run commands from the attachment twice before indexing and after.
> >>>
> >>>Thank you for the feedback.
> >>>
> >>>On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> 
> >>>wrote:
> >>> 
> >>>  
> Hi Ruslan,
> 
> Really appreciate the help on this. I'd love to find out why we are 
> seeing
> such odd results:
> 
> 298 ticket owners when their are only 88 active users
> 1.5 million rows of data when we only have 9983 ticks as of this 
> morning.
> 
> Really odd
> 
> Thanks
> 
> Richard
> 
> 
>    
> 
> >>>[snip]
> >>>
> >>>
> >>> 
> >>>  
> >
> >  
> >>++-+--++---+-+-+--+--+--+
> >>| id | select_type | table| type   | possible_keys
> >>| key | key_len | ref  | rows | Extra 
> >>|
> >>++-+--++---+-+-+--+--+--+
> >>|  1 | SIMPLE  | main | range  | PRIMARY,Users3   
> >>| PRIMARY | 4   | NULL | 1317 | Using 
> >>where; Using temporary; Using filesort | |  1 | SIMPLE  | 
> >>Principals_1 | eq_ref | PRIMARY   | 
> >>PRIMARY | 4   | rt3.main.id  |1 | Using 
> >>where; Distinct| |  1 | SIMPLE  | 
> >>CachedGroupMembers_2 | ref| DisGrouMem,GrouMem,group1,member1 | 
> >>member1 | 5   | rt3.Principals_1.id  |1 | Using 
> >>where; Distinct| |  1 | SIMPLE  | ACL_4   
> >>| range  | ACL1  | ACL1| 54  | NULL   
> >>|  296 | Using where; Using index; Distinct   | |  1 | SIMPLE 
> >>| Groups_3 | eq_ref | PRIMARY,Groups1,Groups2   | 
> >>PRIMARY | 4   | rt3.CachedGroupMembers_2.GroupId |1 | Using 
> >>where; Distinct| 
> >>++-+--++---+-+-+--+--+--+
> >>+---+---+
> >>| PrincipalType | COUNT(id) |
> >>+---+---+
> >>| Group |   298 | 
> >>+---+---+
> >>++-+---+---+---+--+-+--+--+--+
> >>| id | select_type | table | type  | possible_keys | key  | key_len | ref 
> >>| rows | Extra|
> >>++-+---+---+---+--+-+--+--+--+
> >>|  1 | SIMPLE  | ACL_4 | range | ACL1  | ACL1 | 54  | 
> >>NULL |  296 | Using where; Using index | 
> >>++-+---+---+---+--+-+--+--+--+
> >>++
> >>| COUNT(Groups_3.id) |
> >>++
> >>|  0 | 
> >>++
> >>++-+--+---+-+-+-+-+--+--+
> >>| id | select_type | table| type  | possible_keys   | key |

Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi Jesse,

Thanks. To the best of my knowledge nobody has added any indexes to the 
database on anything except what RT patches apply on each upgrade. This 
DB was originally 3.0 and has been upgraded more times than I want to 
think about over the years to 3.6.6 now :)

Richard


Jesse Vincent wrote:
>
> On Wed, Mar 19, 2008 at 04:22:46PM +, Richard Ellis wrote:
>   
>> Hi Ruslan,
>>
>> here's the two sets of results.
>> 
>
> FWIW, from your response to ruslan, it _does_ look like your hand-added
> "group1" index was messing up the query planner. It's on GroupId, while
> we already had an index on GroupId, MemberId.
>
>
>
>
>
>   
>> Thanks
>>
>> Richard
>>
>>
>> Ruslan Zakirov wrote:
>> 
>>> Ok, I have an idea how to fix that problem
>>>
>>> Here is new file for testing that will give me more info to find the
>>> best way to fixing this. We're really close.
>>>
>>> You can run it using:
>>> mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql 
>>>   
 test.res
 
>>> As a first step to fix it you can create the following index on Groups 
>>> table:
>>> CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);
>>>
>>> Please, run commands from the attachment twice before indexing and after.
>>>
>>> Thank you for the feedback.
>>>
>>> On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> 
>>> wrote:
>>>  
>>>   
 Hi Ruslan,

 Really appreciate the help on this. I'd love to find out why we are 
 seeing
 such odd results:

 298 ticket owners when their are only 88 active users
 1.5 million rows of data when we only have 9983 ticks as of this morning.

 Really odd

 Thanks

 Richard



 
>>> [snip]
>>>
>>>
>>>  
>>>   
>
>   
>> ++-+--++---+-+-+--+--+--+
>> | id | select_type | table| type   | possible_keys   
>>   | key | key_len | ref  | rows | 
>> Extra|
>> ++-+--++---+-+-+--+--+--+
>> |  1 | SIMPLE  | main | range  | PRIMARY,Users3  
>>   | PRIMARY | 4   | NULL | 1317 | 
>> Using where; Using temporary; Using filesort | 
>> |  1 | SIMPLE  | Principals_1 | eq_ref | PRIMARY 
>>   | PRIMARY | 4   | rt3.main.id  |1 | 
>> Using where; Distinct| 
>> |  1 | SIMPLE  | CachedGroupMembers_2 | ref| 
>> DisGrouMem,GrouMem,group1,member1 | member1 | 5   | rt3.Principals_1.id  
>> |1 | Using where; Distinct| 
>> |  1 | SIMPLE  | ACL_4| range  | ACL1
>>   | ACL1| 54  | NULL |  296 | 
>> Using where; Using index; Distinct   | 
>> |  1 | SIMPLE  | Groups_3 | eq_ref | PRIMARY,Groups1,Groups2 
>>   | PRIMARY | 4   | rt3.CachedGroupMembers_2.GroupId |1 | 
>> Using where; Distinct| 
>> ++-+--++---+-+-+--+--+--+
>> +---+---+
>> | PrincipalType | COUNT(id) |
>> +---+---+
>> | Group |   298 | 
>> +---+---+
>> ++-+---+---+---+--+-+--+--+--+
>> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | 
>> rows | Extra|
>> ++-+---+---+---+--+-+--+--+--+
>> |  1 | SIMPLE  | ACL_4 | range | ACL1  | ACL1 | 54  | NULL | 
>>  296 | Using where; Using index | 
>> ++-+---+---+---+--+-+--+--+--+
>> ++
>> | COUNT(Groups_3.id) |
>> ++
>> |  0 | 
>> ++
>> ++-+--+---+-+-+-+-+--+--+
>> | id | select_type | table| type  | possible_keys   | key | key_len 
>> | ref | rows | Extra|
>> ++-+--+---+-+-+-+-+--+--+
>> |  1 | SIMPLE  | ACL_4|

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Wed, Mar 19, 2008 at 04:22:46PM +, Richard Ellis wrote:
> Hi Ruslan,
> 
> here's the two sets of results.

FWIW, from your response to ruslan, it _does_ look like your hand-added
"group1" index was messing up the query planner. It's on GroupId, while
we already had an index on GroupId, MemberId.





> 
> Thanks
> 
> Richard
> 
> 
> Ruslan Zakirov wrote:
> >Ok, I have an idea how to fix that problem
> >
> >Here is new file for testing that will give me more info to find the
> >best way to fixing this. We're really close.
> >
> >You can run it using:
> >mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql 
> >>test.res
> >
> >As a first step to fix it you can create the following index on Groups 
> >table:
> >CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);
> >
> >Please, run commands from the attachment twice before indexing and after.
> >
> >Thank you for the feedback.
> >
> >On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> 
> >wrote:
> >  
> >> Hi Ruslan,
> >>
> >> Really appreciate the help on this. I'd love to find out why we are 
> >> seeing
> >>such odd results:
> >>
> >> 298 ticket owners when their are only 88 active users
> >> 1.5 million rows of data when we only have 9983 ticks as of this morning.
> >>
> >> Really odd
> >>
> >> Thanks
> >>
> >> Richard
> >>
> >>
> >>
> >
> >[snip]
> >
> >
> >  

> ++-+--++---+-+-+--+--+--+
> | id | select_type | table| type   | possible_keys
>  | key | key_len | ref  | rows | 
> Extra|
> ++-+--++---+-+-+--+--+--+
> |  1 | SIMPLE  | main | range  | PRIMARY,Users3   
>  | PRIMARY | 4   | NULL | 1317 | 
> Using where; Using temporary; Using filesort | 
> |  1 | SIMPLE  | Principals_1 | eq_ref | PRIMARY  
>  | PRIMARY | 4   | rt3.main.id  |1 | 
> Using where; Distinct| 
> |  1 | SIMPLE  | CachedGroupMembers_2 | ref| 
> DisGrouMem,GrouMem,group1,member1 | member1 | 5   | rt3.Principals_1.id   
>|1 | Using where; Distinct| 
> |  1 | SIMPLE  | ACL_4| range  | ACL1 
>  | ACL1| 54  | NULL |  296 | 
> Using where; Using index; Distinct   | 
> |  1 | SIMPLE  | Groups_3 | eq_ref | PRIMARY,Groups1,Groups2  
>  | PRIMARY | 4   | rt3.CachedGroupMembers_2.GroupId |1 | 
> Using where; Distinct| 
> ++-+--++---+-+-+--+--+--+
> +---+---+
> | PrincipalType | COUNT(id) |
> +---+---+
> | Group |   298 | 
> +---+---+
> ++-+---+---+---+--+-+--+--+--+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | 
> rows | Extra|
> ++-+---+---+---+--+-+--+--+--+
> |  1 | SIMPLE  | ACL_4 | range | ACL1  | ACL1 | 54  | NULL |  
> 296 | Using where; Using index | 
> ++-+---+---+---+--+-+--+--+--+
> ++
> | COUNT(Groups_3.id) |
> ++
> |  0 | 
> ++
> ++-+--+---+-+-+-+-+--+--+
> | id | select_type | table| type  | possible_keys   | key | key_len | 
> ref | rows | Extra|
> ++-+--+---+-+-+-+-+--+--+
> |  1 | SIMPLE  | ACL_4| range | ACL1| ACL1| 54  | 
> NULL|  296 | Using where; Using index | 
> |  1 | SIMPLE  | Groups_3 | ref   | Groups1,Groups2 | Groups2 | 67  | 
> rt3.ACL_4.PrincipalType | 1345 | Using where; Using index | 
> ++-+--+---+-+-+-+-+--+--+
> ++-+---+---+---+--+-

Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi Ruslan,

here's the two sets of results.

Thanks

Richard


Ruslan Zakirov wrote:

Ok, I have an idea how to fix that problem

Here is new file for testing that will give me more info to find the
best way to fixing this. We're really close.

You can run it using:
mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql >test.res

As a first step to fix it you can create the following index on Groups table:
CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);

Please, run commands from the attachment twice before indexing and after.

Thank you for the feedback.

On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> wrote:
  

 Hi Ruslan,

 Really appreciate the help on this. I'd love to find out why we are seeing
such odd results:

 298 ticket owners when their are only 88 active users
 1.5 million rows of data when we only have 9983 ticks as of this morning.

 Really odd

 Thanks

 Richard





[snip]


  
++-+--++---+-+-+--+--+--+
| id | select_type | table| type   | possible_keys  
   | key | key_len | ref  | rows | Extra
|
++-+--++---+-+-+--+--+--+
|  1 | SIMPLE  | main | range  | PRIMARY,Users3 
   | PRIMARY | 4   | NULL | 1317 | Using 
where; Using temporary; Using filesort | 
|  1 | SIMPLE  | Principals_1 | eq_ref | PRIMARY
   | PRIMARY | 4   | rt3.main.id  |1 | Using 
where; Distinct| 
|  1 | SIMPLE  | CachedGroupMembers_2 | ref| 
DisGrouMem,GrouMem,group1,member1 | member1 | 5   | rt3.Principals_1.id 
 |1 | Using where; Distinct| 
|  1 | SIMPLE  | ACL_4| range  | ACL1   
   | ACL1| 54  | NULL |  296 | Using 
where; Using index; Distinct   | 
|  1 | SIMPLE  | Groups_3 | eq_ref | PRIMARY,Groups1,Groups2
   | PRIMARY | 4   | rt3.CachedGroupMembers_2.GroupId |1 | Using 
where; Distinct| 
++-+--++---+-+-+--+--+--+
+---+---+
| PrincipalType | COUNT(id) |
+---+---+
| Group |   298 | 
+---+---+
++-+---+---+---+--+-+--+--+--+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | 
rows | Extra|
++-+---+---+---+--+-+--+--+--+
|  1 | SIMPLE  | ACL_4 | range | ACL1  | ACL1 | 54  | NULL |  
296 | Using where; Using index | 
++-+---+---+---+--+-+--+--+--+
++
| COUNT(Groups_3.id) |
++
|  0 | 
++
++-+--+---+-+-+-+-+--+--+
| id | select_type | table| type  | possible_keys   | key | key_len | 
ref | rows | Extra|
++-+--+---+-+-+-+-+--+--+
|  1 | SIMPLE  | ACL_4| range | ACL1| ACL1| 54  | 
NULL|  296 | Using where; Using index | 
|  1 | SIMPLE  | Groups_3 | ref   | Groups1,Groups2 | Groups2 | 67  | 
rt3.ACL_4.PrincipalType | 1345 | Using where; Using index | 
++-+--+---+-+-+-+-+--+--+
++-+---+---+---+--+-+--+--+--+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | 
rows | Extra|
++-+---+---+---+--+-+--+--+--+
|  1 | SIMPLE  | ACL_4 | range | ACL1  | ACL1 | 54  | NULL |  
296 | Using where; Using index | 
++-+---+---+---+--+-+--+--+--+
++-+-

Re: [rt-users] RT 3.6.4 poor query performance

[Ruslan Zakirov]

Ok, I have an idea how to fix that problem

Here is new file for testing that will give me more info to find the
best way to fixing this. We're really close.

You can run it using:
mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql >test.res

As a first step to fix it you can create the following index on Groups table:
CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);

Please, run commands from the attachment twice before indexing and after.

Thank you for the feedback.

On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <[EMAIL PROTECTED]> wrote:
>
>  Hi Ruslan,
>
>  Really appreciate the help on this. I'd love to find out why we are seeing
> such odd results:
>
>  298 ticket owners when their are only 88 active users
>  1.5 million rows of data when we only have 9983 ticks as of this morning.
>
>  Really odd
>
>  Thanks
>
>  Richard
>
>

[snip]


-- 
Best regards, Ruslan.


search_possible_owners.mysql.sql
Description: Binary data
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Mathew]

The vast amount of data could be due to spam.  I set up a script which 
regularly (daily) searches for spam and the users automatically created 
by it.  It then runs Shredder on the list I built.  This is made easier 
by creating a spam only queue into which all incoming spam is placed.

Additionally, we've instituted a method of preventing non-authorized 
users from creating tickets.  We have a script which pulls the email 
addresses of our customers employees from our customer database.  It 
then builds a procmail script.

If the email address is listed in the procmail script the incoming email 
is passed to rtx-mailgate.  If the email address doesn't exist in the 
list they get a bounce back.  This eliminated about 75% of our spam problem.

The other 25% was from our other public facing queue to which security 
and abuse issues are reported.  These emails are placed in our _SPAM 
queue on which the above clean-up script runs.  It doesn't always get 
everything because it isn't configured to handle Bcc and Cc addresses 
but the requestor address is always groomed.  The emails in that queue 
are marked as deleted and Shredder then grooms out all deleted emails.

On another note, we just installed a Barracuda system which has been a 
blessing.  The clean-up script went from a daily scrubbing of between 
150-250 emails and users to between 0 and 20.

Mathew

Richard Ellis wrote:
> Hi Ruslan,
> 
> Really appreciate the help on this. I'd love to find out why we are 
> seeing such odd results:
> 
> 298 ticket owners when their are only 88 active users
> 1.5 million rows of data when we only have 9983 ticks as of this morning.
> 
> Really odd
> 
> Thanks
> 
> Richard
> 
> 

-- 
Keep up with me and what I'm up to: http://theillien.blogspot.com
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Ruslan Zakirov]

Hello, Richard.

Jesse asked me to look into this problem. Could you please run SQL
commands from the attachment. It's a list of COUNT and EXPLAIN
queries. I hope that will help us identify problems.

On Tue, Mar 18, 2008 at 7:31 PM, Richard Ellis <[EMAIL PROTECTED]> wrote:
>
>  Hi Jesse,
>
>  We are using 5.0.51a at the moment, because 5.0.34 was reported as having
> issues in several posts on the forum and an upgrade recommended.
>
>  Richard
>
>
>
>
>  Jesse Vincent wrote:
>
>
>  What version of mysql is this again?
>
>  My explain output looks like this:
>
>
> +--++-+-+-+---+--+---+
>  | table| type   | possible_keys   | key |
> key_len | ref   | rows | Extra
> |
>
> +--++-+-+-+---+--+---+
>  | Groups_3 | ref| PRIMARY,Groups1,Groups2 | Groups1 |
> 65 | const |  108 | Using where; Using index; Using
> temporary; Using filesort |
>  | CachedGroupMembers_2 | ref| DisGrouMem,GrouMem,MemberId | GrouMem |
> 5 | Groups_3.id   |1 | Using where; Using index
> |
>  | Principals_1 | eq_ref | PRIMARY | PRIMARY |
> 4 | CachedGroupMembers_2.MemberId |1 | Using where
> |
>  | ACL_4| range  | ACL1| ACL1|
> 50 | NULL  |   36 | Using where; Using index
> |
>  | main | eq_ref | PRIMARY,Users3  | PRIMARY |
> 4 | Principals_1.id   |1 |
> |
>
> +--++-+-+-+---+--+---+
>
>  You'll note that it's starting on the Groups table rather than the Users
> table, which I suspect is a lot less expensive (and yes, we have a smaller
> RT database than you)
>
>  I sort of wonder whether that "member1" key is causing your RT to make a
> bad call about join ordering.
>
>
>
>
>  On Mar 18, 2008, at 12:21 PM, Richard Ellis wrote:
>
> Database changed
>  mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4
> JOIN
>-> Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN
>-> CachedGroupMembers CachedGroupMembers_2  ON (
>-> CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3
>-> ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE
>-> (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType =
> Groups_3.Type)
>-> AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User')
>-> AND (ACL_4.RightName = 'OwnTicket') AND (Groups_3.Domain =
>-> 'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR
>-> (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC;
>
> ++-+--++---+-+-+--+--+--+
>  | id | select_type | table| type   | possible_keys|
> key | key_len | ref  | rows | Extra
> |
>
> ++-+--++---+-+-+--+--+--+
>  |  1 | SIMPLE  | main | range  | PRIMARY,Users3
> | PRIMARY | 4   | NULL | 1378 | Using where;
> Using temporary; Using filesort |
>  |  1 | SIMPLE  | Principals_1 | eq_ref | PRIMARY|
> PRIMARY | 4   | rt3.main.id  |1 | Using where;
> Distinct|
>  |  1 | SIMPLE  | CachedGroupMembers_2 | ref|
> DisGrouMem,GrouMem,group1,member1 | member1 | 5   | rt3.Principals_1.id
> |1 | Using where; Distinct|
>  |  1 | SIMPLE  | ACL_4| range  | ACL1| ACL1
> | 54  | NULL |  296 | Using where; Using
> index; Distinct   |
>  |  1 | SIMPLE  | Groups_3 | eq_ref |
> PRIMARY,Groups1,Groups2| PRIMARY | 4   |
> rt3.CachedGroupMembers_2.GroupId |1 | Using where; Distinct
> |
>
> ++-+--++---+-+-+--+--+--+
>  5 rows in set (0.01 sec)
>
>  mysql>
>
>
>  Is this giving you a clue where the problem is? Didn't think their were 15
> million rows of data, their are only 10,000 tickets in total.
>
>  Jesse Vincent wro

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

[Please keep ccing the list. Free support is a lot more valuable when  
the community's involved]


What version of mysql is this again?

My explain output looks like this:

+--++- 
+-+-+---+-- 
+---+
| table| type   | possible_keys   |  
key | key_len | ref   | rows |  
Extra |
+--++- 
+-+-+---+-- 
+---+
| Groups_3 | ref| PRIMARY,Groups1,Groups2 |  
Groups1 |  65 | const |  108 | Using  
where; Using index; Using temporary; Using filesort |
| CachedGroupMembers_2 | ref| DisGrouMem,GrouMem,MemberId |  
GrouMem |   5 | Groups_3.id   |1 | Using  
where; Using index  |
| Principals_1 | eq_ref | PRIMARY |  
PRIMARY |   4 | CachedGroupMembers_2.MemberId |1 | Using  
where   |
| ACL_4| range  | ACL1|  
ACL1|  50 | NULL  |   36 | Using  
where; Using index  |
| main | eq_ref | PRIMARY,Users3  |  
PRIMARY |   4 | Principals_1.id   |1  
|   |
+--++- 
+-+-+---+-- 
+---+


You'll note that it's starting on the Groups table rather than the  
Users table, which I suspect is a lot less expensive (and yes, we have  
a smaller RT database than you)


I sort of wonder whether that "member1" key is causing your RT to make  
a bad call about join ordering.





On Mar 18, 2008, at 12:21 PM, Richard Ellis wrote:

Database changed
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL  
ACL_4 JOIN

  -> Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN
  -> CachedGroupMembers CachedGroupMembers_2  ON (
  -> CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups  
Groups_3

  -> ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE
  -> (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType =  
Groups_3.Type)
  -> AND (Principals_1.id != '1') AND (Principals_1.PrincipalType =  
'User')

  -> AND (ACL_4.RightName = 'OwnTicket') AND (Groups_3.Domain =
  -> 'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR
  -> (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC;
++-+--+ 
+---+-+- 
+--+-- 
+--+
| id | select_type | table| type   |  
possible_keys| key | key_len |  
ref  | rows |  
Extra |
++-+--+ 
+---+-+- 
+--+-- 
+--+
|  1 | SIMPLE  | main | range  |  
PRIMARY,Users3| PRIMARY | 4   |  
NULL | 1378 | Using where; Using  
temporary; Using filesort |
|  1 | SIMPLE  | Principals_1 | eq_ref | PRIMARY 
| PRIMARY | 4   | rt3.main.id  |1 |  
Using where; Distinct|
|  1 | SIMPLE  | CachedGroupMembers_2 | ref|  
DisGrouMem,GrouMem,group1,member1 | member1 | 5   |  
rt3.Principals_1.id  |1 | Using where;  
Distinct|
|  1 | SIMPLE  | ACL_4| range  | ACL1|  
ACL1| 54  | NULL |  296 | Using  
where; Using index; Distinct   |
|  1 | SIMPLE  | Groups_3 | eq_ref |  
PRIMARY,Groups1,Groups2| PRIMARY | 4   |  
rt3.CachedGroupMembers_2.GroupId |1 | Using where;  
Distinct|
++-+--+ 
+---+-+- 
+--+-- 
+--+

5 rows in set (0.01 sec)

mysql>


Is this giving you a clue where the problem is? Didn't think their  
were 15 million rows of data, their are only 10,000 tickets in total.


Jesse Vincent wrote:

So, you have a query that ran for 400+ seconds an examined fifteen
million rows. That seems..wrong. What does this say?

EXPLAIN SEL

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

So, you have a query that ran for 400+ seconds an examined fifteen
million rows. That seems..wrong. What does this say?

EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN
Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN 
CachedGroupMembers CachedGroupMembers_2  ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  
ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE 
(Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) 
AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User')  
AND (ACL_4.RightName = 'OwnTicket') AND (Groups_3.Domain =  
'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR  
(ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC; 



On Tue, Mar 18, 2008 at 04:10:19PM +, Richard Ellis wrote:
> Hi Jesse,
> 
> The output of the slow query log for the last 23 hours:
> 
> # [EMAIL PROTECTED]: rt_user[rt_user] @ localhost []
> # Query_time: 6  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> use rt3;
> SELECT GET_LOCK('Apache-Session-ce4e206474839cb7dd09a5216f86ce9e', 3600);
> # Time: 080317 12:17:03
> # [EMAIL PROTECTED]: rt_user[rt_user] @ localhost []
> # Query_time: 6  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-ce4e206474839cb7dd09a5216f86ce9e', 3600);
> # Time: 080317 12:17:47
> # [EMAIL PROTECTED]: rt_user[rt_user] @ localhost []
> # Query_time: 8  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-ce4e206474839cb7dd09a5216f86ce9e', 3600);
> # Time: 080317 13:07:11
> # [EMAIL PROTECTED]: rt_user[rt_user] @ localhost []
> # Query_time: 411  Lock_time: 0  Rows_sent: 0  Rows_examined: 15418603
> SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN 
> Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN 
> CachedGroupMembers CachedGroupMembers_2  ON ( 
> CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  
> ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE 
> (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) 
> AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') 
> AND (ACL_4.RightName = 'OwnTicket') AND (Groups_3.Domain = 
> 'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR 
> (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC;
> # Time: 080317 13:07:13
> # [EMAIL PROTECTED]: rt_user[rt_user] @ localhost []
> # Query_time: 46  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551', 3600);
> # [EMAIL PROTECTED]: rt_user[rt_user] @ localhost []
> # Query_time: 346  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551', 3600);
> 
> Thanks
> 
> Richard
> 
> Jesse Vincent wrote:
> >Sounds like someone previously changed the config file without
> >restarting.
> >
> >
> >On Mon, Mar 17, 2008 at 05:22:52PM +, Richard Ellis wrote:
> >  
> >>Looks like theres a problem with the logfile
> >>
> >>080317 10:04:58  mysqld started
> >>InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of 
> >>different size 0 5242880 bytes
> >>InnoDB: than specified in the .cnf file 0 16777216 bytes!
> >>080317 10:04:59 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
> >>Version: '5.0.51a-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL 
> >>Community Server (GPL)
> >>080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect 
> >>information in file: './rt3/Users.frm'
> >>080317 10:05

Re: [rt-users] RT 3.6.4 poor query performance

[Kenneth Crocker]

Mathew,


You mentioned needing "SeeQueue" for "Unprivileged" users and you 
already have it for "Privileged" users. Why not grant "CreateTicket", 
"SeeQueue" Globally for "Everyone" (saves redundant granting) and 
"ShowTicket" Globally for "Requestors"? That way any requestor can 
always "See" their ticket and everyone can "Create" one in the Queue of 
their choice. Then Grant "ReplyToTicket" on a Queue level to the groups 
that support each particular Queue.
I'm of the opinion that there is so much redundant "Granting" of 
privileges out there without realizing that it makes it difficult to 
know why queries are taking so long. Yet, these redundant privileges are 
slowing down the system cause so many users have rights they weren't 
meant to have. Just a thought.

Kenn
LBNL

On 3/17/2008 4:37 AM, Mathew wrote:
> We have
> 
> Everyone: CreateTicket on two public facing queues
> 
> Privileged: CommentOnTicket, CreateTicket, SeeQueue, ShowOutgoingEmail, 
> ShowTicket, ShowTicketComments, Watch on all queues
> 
> Unprivileged: CreateTicket, SeeQueue on one public facing queue (now 
> that I think about it CreateTicket here seems redundant); ReplyToTicket, 
> Watch on all queues.
> 
> Individual groups: AssignCustomFields, ModifyTicket, OwnTicket, 
> ReplyToTicket, StealTicket, TakeTicket on only their corresponding queues.
> 
> Our unprivileged users need SeeQueue because we have a customer portal 
> which requires it (we don't use the self-service interface).
> 
> Mathew
> 
> Ham MI-ID, Torsten Brumm wrote:
>> Hi Matthew,
>>
>> We have:
>>
>> Everyone: CreateTicket
>> Privileged: CreateSavedSearch, EditSavedSearches, LoadSavedSearch, 
>> ShowSavedSearches
>>
>> Roles:
>>
>> Requestor: ReplyToTicket, ShowTicket
>>
>> The rest is done on a queue base.
>>
>> OK, if i have now 1 Queue and grant to this queue 1 Group the right to 
>> OwnTicket, then the dropdown must have the member of this group (from my 
>> logical understanding) and definitve NO UNPRIVILEGED USERS i think!
>>
>> Btw. We rolled back...
>>
>> Torsten
>>
>>
>> Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), 
>> Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark 
>> Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, 
>> Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich 
>> haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg 
>> Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne
>>
>>
> 

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi Jesse,

That's worrying because according to the master list of users I have, 
their should only be 88 users who have permissions to own tickets. Thats 
probably not the cause of the performance issue though, so I'll shelve 
that for now to look at later.


I'll turn slow queries on now and send results tomorrow.

Rik


Jesse Vincent wrote:

On Mar 17, 2008, at 12:26 PM, Richard Ellis wrote:

mysql> SELECT * from ACL, CachedGroupMembers, Groups where 
ACL.RightName = 'OwnTicket' and ACL.PrincipalId = Groups.id and 
Groups.id = CachedGroupMembers.GroupId;


Ok. Those results tell me there should be 290 names in your 
"SelectOwner" drop down. (That's a lot, but not enough that you should 
see 400s perf ;)


Do you have mysql logging slow queries? If not, can you?




--
Sun.com   * Richard Ellis *
Technical Developer, .Sun eBusiness

*Sun Microsystems, Inc.*
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email [EMAIL PROTECTED]
sun.com

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Mar 17, 2008, at 12:26 PM, Richard Ellis wrote:

mysql> SELECT * from ACL, CachedGroupMembers, Groups where  
ACL.RightName = 'OwnTicket' and ACL.PrincipalId = Groups.id and  
Groups.id = CachedGroupMembers.GroupId;


Ok. Those results tell me there should be 290 names in your  
"SelectOwner" drop down. (That's a lot, but not enough that you should  
see 400s perf ;)


Do you have mysql logging slow queries? If not, can you?




PGP.sig
Description: This is a digitally signed message part
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Mon, Mar 17, 2008 at 04:08:52PM +, Richard Ellis wrote:
> ok, that doesn't look good
> 
> 
> mysql> select * from ACL, CachedGroupMembers, Groups where ACL.RightName 
> = 'OwnTicket' and ACL.PrincipalId = Groups.id and Groups.id = 
> CachedGroupMembers.GroupId and (CachedGroupMembers.MemberId = '3' or 
> CachedGroupMembers.MemberId = '5'); Empty set (0.04 sec)
>

Looks good for you, not for me ;)  That means that my trivial fix isn't
going to catch it.

Next:

SELECT * from ACL, CachedGroupMembers, Groups where ACL.RightName = 'OwnTicket' 
and ACL.PrincipalId = Groups.id and Groups.id = CachedGroupMembers.GroupId;

This will generate a _lot_ of data. Probably best not to CC the list on
the full response.




___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Mar 17, 2008, at 11:48 AM, Richard Ellis wrote:


Hi Jesse,



Ok. Next up:

select * from ACL, CachedGroupMembers, Groups where ACL.RightName =  
'OwnTicket' and ACL.PrincipalId = Groups.id and Groups.id =  
CachedGroupMembers.GroupId and (CachedGroupMembers.MemberId = '3' or  
CachedGroupMembers.MemberId = '5');



(The 3 and 5 there should be reasonably stable across RT instances.  
They should correspond to these rows from Groups


|  3 | | Pseudogroup for internal use | SystemInternal  |  
Everyone |0 |
|  5 | | Pseudogroup for internal use | SystemInternal  |  
Unprivileged |0 |





PGP.sig
Description: This is a digitally signed message part
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Mar 17, 2008, at 8:29 AM, Richard Ellis wrote:


Hi Guys,

I'm seeing the same thing. A lot more users in that dropdown than  
should be there.




Richard,

I'd like to help get to the bottom of this.  Can you send the output of:

SELECT * from ACL where RightName = 'OwnTicket'





PGP.sig
Description: This is a digitally signed message part
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi Guys,

I'm seeing the same thing. A lot more users in that dropdown than should 
be there.


I'll do the quick and dirty hack for now to get this working again.

Thanks

Richard


Mathew wrote:

We have

Everyone: CreateTicket on two public facing queues

Privileged: CommentOnTicket, CreateTicket, SeeQueue, 
ShowOutgoingEmail, ShowTicket, ShowTicketComments, Watch on all queues


Unprivileged: CreateTicket, SeeQueue on one public facing queue (now 
that I think about it CreateTicket here seems redundant); 
ReplyToTicket, Watch on all queues.


Individual groups: AssignCustomFields, ModifyTicket, OwnTicket, 
ReplyToTicket, StealTicket, TakeTicket on only their corresponding 
queues.


Our unprivileged users need SeeQueue because we have a customer portal 
which requires it (we don't use the self-service interface).


Mathew

Ham MI-ID, Torsten Brumm wrote:

Hi Matthew,

We have:

Everyone: CreateTicket
Privileged: CreateSavedSearch, EditSavedSearches, LoadSavedSearch, 
ShowSavedSearches


Roles:

Requestor: ReplyToTicket, ShowTicket

The rest is done on a queue base.

OK, if i have now 1 Queue and grant to this queue 1 Group the right 
to OwnTicket, then the dropdown must have the member of this group 
(from my logical understanding) and definitve NO UNPRIVILEGED USERS i 
think!


Btw. We rolled back...

Torsten


Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann 
(Vors.), Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten 
Meincke, Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: 
Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, 
Persönlich haftende Gesellschaft: Kühne & Nagel A.G., Sitz: 
Contern/Luxemburg Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne







--
Sun.com   * Richard Ellis *
Technical Developer, .Sun eBusiness

*Sun Microsystems, Inc.*
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email [EMAIL PROTECTED]
sun.com

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Mathew]

We have

Everyone: CreateTicket on two public facing queues

Privileged: CommentOnTicket, CreateTicket, SeeQueue, ShowOutgoingEmail, 
ShowTicket, ShowTicketComments, Watch on all queues

Unprivileged: CreateTicket, SeeQueue on one public facing queue (now 
that I think about it CreateTicket here seems redundant); ReplyToTicket, 
Watch on all queues.

Individual groups: AssignCustomFields, ModifyTicket, OwnTicket, 
ReplyToTicket, StealTicket, TakeTicket on only their corresponding queues.

Our unprivileged users need SeeQueue because we have a customer portal 
which requires it (we don't use the self-service interface).

Mathew

Ham MI-ID, Torsten Brumm wrote:
> Hi Matthew,
> 
> We have:
> 
> Everyone: CreateTicket
> Privileged: CreateSavedSearch, EditSavedSearches, LoadSavedSearch, 
> ShowSavedSearches
> 
> Roles:
> 
> Requestor: ReplyToTicket, ShowTicket
> 
> The rest is done on a queue base.
> 
> OK, if i have now 1 Queue and grant to this queue 1 Group the right to 
> OwnTicket, then the dropdown must have the member of this group (from my 
> logical understanding) and definitve NO UNPRIVILEGED USERS i think!
> 
> Btw. We rolled back...
> 
> Torsten
> 
> 
> Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), 
> Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark 
> Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, 
> Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich 
> haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg 
> Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne
> 
> 

-- 
Keep up with me and what I'm up to: http://theillien.blogspot.com
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Ham MI-ID, Torsten Brumm]

Hi Matthew,

We have:

Everyone: CreateTicket
Privileged: CreateSavedSearch, EditSavedSearches, LoadSavedSearch, 
ShowSavedSearches

Roles:

Requestor: ReplyToTicket, ShowTicket

The rest is done on a queue base.

OK, if i have now 1 Queue and grant to this queue 1 Group the right to 
OwnTicket, then the dropdown must have the member of this group (from my 
logical understanding) and definitve NO UNPRIVILEGED USERS i think!

Btw. We rolled back...

Torsten


Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), 
Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark 
Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: 
Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: 
Kühne & Nagel A.G., Sitz: Contern/Luxemburg Geschäftsführender Verwaltungsrat: 
Klaus-Michael Kühne


-Ursprüngliche Nachricht-
Von: Mathew [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 17. März 2008 12:20
An: Ham MI-ID, Torsten Brumm
Cc: Richard Ellis; rt-users@lists.bestpractical.com
Betreff: Re: AW: [rt-users] RT 3.6.4 poor query performance

You shouldn't have had to write a patch to fix the immense user drop down.  
I've created queues with matching groups and assigned the own ticket right to 
only the groups that correspond to each queue.

The Everyone group only has CreateTicket on two public facing queues (all 
others are available for correspondence but not ticket creation), Priveleged 
Users has all the major rights which all users require across all queues and 
Unprivileged Users has only rights which customers would need to interact with 
a ticket.

This has provide more than enough lock down to keep users created by spam out 
of our drop down.

Mathew

Ham MI-ID, Torsten Brumm wrote:
> Hi Mathew, Richard,
> I tried also this weekend to upgrade to 3.6.6 and gave it up yesterday 
> evening (rolled back to 3.6.5).
> 
> To your Problem:
> 
> If you open the Search Builder menu, it takes a long time to build the 
> page.?!? Have a loko into the owner dropdown menu. Did you find there more 
> people as expacted? In my case i find a lot of people there, more than have 
> the rights to own tickets in the queues.
> 
> I have NOT SET the OwnTickets right globally  And now it will be very 
> strange at my Live systems (and test box too).
> 
> Inside the owner dropdown, i find also NOT PRIVILEGDED USERS!!!
> 
> OK, what i have tested:
> 
> Logged in as normal user with rights to 3 Queues.
> 
> This queues have per queue 5 people with the right to own a ticket here. (so 
> i looked for 15 people inside the owner dropdown) but i got a list of round 
> about several thousands!!!
> 
> OK to fix it fast:
> 
> Here is my diff to the /share/html/Search/Elements/PickBasics
> 
> [EMAIL PROTECTED]:/opt/rt3/local/html/Search/Elements# diff 
> /opt/rt3/local/html/Search/Elements/PickBasics 
> /opt/rt3/share/html/Search/Elements/PickBasics
> 111,112c113
> < 
> < %#<& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute 
> => 'Name' &>
> ---
>> <& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute => 
>> 'Name' &>
> 
> OK, it's replacing the SelectOwner Dropdown, which is not working well here 
> with a noremal input box.
> 
> This speeds up the Searchbuilder a lot!
> 
> Btw. This "Problem" with the Owner Dropdown inside the searchbuilder we have 
> since RT 3.4.x and it is not working well since this time.
> 
> Hope this helps.
> Torsten
> 
> 
> 
> Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann 
> (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten 
> Meincke, Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: 
> Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, 
> Persönlich haftende Gesellschaft: Kühne & Nagel A.G., Sitz: 
> Contern/Luxemburg Geschäftsführender Verwaltungsrat: Klaus-Michael 
> Kühne
> 
> 
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von 
> Mathew
> Gesendet: Montag, 17. März 2008 11:06
> An: Richard Ellis
> Cc: rt-users@lists.bestpractical.com
> Betreff: Re: [rt-users] RT 3.6.4 poor query performance
> 
> Well, in that case, I recommend witchcraft. ;)
> 
> Mathew
> 
> Richard Ellis wrote:
>> Hi All,
>>
>> We have upgraded to 3.6.6 over the weekend and also run some 
>> optimisation against the database but performance is still very poor.
>>
>> I have looked at RTx::RightMatrix and Everyone definately does not 
>> have OwnTickets rights unless that is lying to me, which I doubt.
>> I'v

Re: [rt-users] RT 3.6.4 poor query performance

[Mathew]

You shouldn't have had to write a patch to fix the immense user drop 
down.  I've created queues with matching groups and assigned the own 
ticket right to only the groups that correspond to each queue.

The Everyone group only has CreateTicket on two public facing queues 
(all others are available for correspondence but not ticket creation), 
Priveleged Users has all the major rights which all users require across 
all queues and Unprivileged Users has only rights which customers would 
need to interact with a ticket.

This has provide more than enough lock down to keep users created by 
spam out of our drop down.

Mathew

Ham MI-ID, Torsten Brumm wrote:
> Hi Mathew, Richard,
> I tried also this weekend to upgrade to 3.6.6 and gave it up yesterday 
> evening (rolled back to 3.6.5).
> 
> To your Problem:
> 
> If you open the Search Builder menu, it takes a long time to build the 
> page.?!? Have a loko into the owner dropdown menu. Did you find there more 
> people as expacted? In my case i find a lot of people there, more than have 
> the rights to own tickets in the queues.
> 
> I have NOT SET the OwnTickets right globally  And now it will be very 
> strange at my Live systems (and test box too).
> 
> Inside the owner dropdown, i find also NOT PRIVILEGDED USERS!!!
> 
> OK, what i have tested:
> 
> Logged in as normal user with rights to 3 Queues.
> 
> This queues have per queue 5 people with the right to own a ticket here. (so 
> i looked for 15 people inside the owner dropdown) but i got a list of round 
> about several thousands!!!
> 
> OK to fix it fast:
> 
> Here is my diff to the /share/html/Search/Elements/PickBasics
> 
> [EMAIL PROTECTED]:/opt/rt3/local/html/Search/Elements# diff 
> /opt/rt3/local/html/Search/Elements/PickBasics 
> /opt/rt3/share/html/Search/Elements/PickBasics
> 111,112c113
> < 
> < %#<& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute => 
> 'Name' &>
> ---
>> <& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute => 'Name' &>
> 
> OK, it's replacing the SelectOwner Dropdown, which is not working well here 
> with a noremal input box.
> 
> This speeds up the Searchbuilder a lot!
> 
> Btw. This "Problem" with the Owner Dropdown inside the searchbuilder we have 
> since RT 3.4.x and it is not working well since this time.
> 
> Hope this helps.
> Torsten 
> 
> 
> 
> Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), 
> Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark 
> Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, 
> Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich 
> haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg 
> Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne
> 
> 
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Mathew
> Gesendet: Montag, 17. März 2008 11:06
> An: Richard Ellis
> Cc: rt-users@lists.bestpractical.com
> Betreff: Re: [rt-users] RT 3.6.4 poor query performance
> 
> Well, in that case, I recommend witchcraft. ;)
> 
> Mathew
> 
> Richard Ellis wrote:
>> Hi All,
>>
>> We have upgraded to 3.6.6 over the weekend and also run some 
>> optimisation against the database but performance is still very poor.
>>
>> I have looked at RTx::RightMatrix and Everyone definately does not 
>> have OwnTickets rights unless that is lying to me, which I doubt.
>> I've used the tuning-primer.sh script to do some tuning and 
>> performance has improved somewhat, as query builder now only take 300 
>> seconds average to load instead of 400, but it is still unusable which 
>> is frustrating the users. It's going to take another 36 hours before I 
>> can check how the optimisation is going.
>>
>> I couldn't get the MySQLTuner to run, but I'll take a look at the perl 
>> this week if I get a chance.
>>
>> If anyone else has any ideas at all, I'm open to suggestions, 
>> including witchcraft :)
>>
>> Richard
>>
> 
> --
> Keep up with me and what I'm up to: http://theillien.blogspot.com 
> ___
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com Commercial support: [EMAIL 
> PROTECTED]
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
> 

-- 
Keep up with me and what I'm up to: http://theillien.blogspot.com
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Ham MI-ID, Torsten Brumm]

Hi Mathew, Richard,
I tried also this weekend to upgrade to 3.6.6 and gave it up yesterday evening 
(rolled back to 3.6.5).

To your Problem:

If you open the Search Builder menu, it takes a long time to build the page.?!? 
Have a loko into the owner dropdown menu. Did you find there more people as 
expacted? In my case i find a lot of people there, more than have the rights to 
own tickets in the queues.

I have NOT SET the OwnTickets right globally  And now it will be very 
strange at my Live systems (and test box too).

Inside the owner dropdown, i find also NOT PRIVILEGDED USERS!!!

OK, what i have tested:

Logged in as normal user with rights to 3 Queues.

This queues have per queue 5 people with the right to own a ticket here. (so i 
looked for 15 people inside the owner dropdown) but i got a list of round about 
several thousands!!!

OK to fix it fast:

Here is my diff to the /share/html/Search/Elements/PickBasics

[EMAIL PROTECTED]:/opt/rt3/local/html/Search/Elements# diff 
/opt/rt3/local/html/Search/Elements/PickBasics 
/opt/rt3/share/html/Search/Elements/PickBasics
111,112c113
< 
< %#<& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute => 'Name' 
&>
---
> <& /Elements/SelectOwner, Name => "ValueOfActor", ValueAttribute => 'Name' &>

OK, it's replacing the SelectOwner Dropdown, which is not working well here 
with a noremal input box.

This speeds up the Searchbuilder a lot!

Btw. This "Problem" with the Owner Dropdown inside the searchbuilder we have 
since RT 3.4.x and it is not working well since this time.

Hope this helps.
Torsten 



Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), 
Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark 
Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: 
Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: 
Kühne & Nagel A.G., Sitz: Contern/Luxemburg Geschäftsführender Verwaltungsrat: 
Klaus-Michael Kühne


-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Mathew
Gesendet: Montag, 17. März 2008 11:06
An: Richard Ellis
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] RT 3.6.4 poor query performance

Well, in that case, I recommend witchcraft. ;)

Mathew

Richard Ellis wrote:
> Hi All,
> 
> We have upgraded to 3.6.6 over the weekend and also run some 
> optimisation against the database but performance is still very poor.
> 
> I have looked at RTx::RightMatrix and Everyone definately does not 
> have OwnTickets rights unless that is lying to me, which I doubt.
> I've used the tuning-primer.sh script to do some tuning and 
> performance has improved somewhat, as query builder now only take 300 
> seconds average to load instead of 400, but it is still unusable which 
> is frustrating the users. It's going to take another 36 hours before I 
> can check how the optimisation is going.
> 
> I couldn't get the MySQLTuner to run, but I'll take a look at the perl 
> this week if I get a chance.
> 
> If anyone else has any ideas at all, I'm open to suggestions, 
> including witchcraft :)
> 
> Richard
> 

--
Keep up with me and what I'm up to: http://theillien.blogspot.com 
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com Commercial support: [EMAIL 
PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Mathew]

Well, in that case, I recommend witchcraft. ;)

Mathew

Richard Ellis wrote:
> Hi All,
> 
> We have upgraded to 3.6.6 over the weekend and also run some 
> optimisation against the database but performance is still very poor.
> 
> I have looked at RTx::RightMatrix and Everyone definately does not have 
> OwnTickets rights unless that is lying to me, which I doubt.
> I've used the tuning-primer.sh script to do some tuning and performance 
> has improved somewhat, as query builder now only take 300 seconds 
> average to load instead of 400, but it is still unusable which is 
> frustrating the users. It's going to take another 36 hours before I can 
> check how the optimisation is going.
> 
> I couldn't get the MySQLTuner to run, but I'll take a look at the perl 
> this week if I get a chance.
> 
> If anyone else has any ideas at all, I'm open to suggestions, including 
> witchcraft :)
> 
> Richard
> 

-- 
Keep up with me and what I'm up to: http://theillien.blogspot.com
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi All,

We have upgraded to 3.6.6 over the weekend and also run some 
optimisation against the database but performance is still very poor.


I have looked at RTx::RightMatrix and Everyone definately does not have 
OwnTickets rights unless that is lying to me, which I doubt.
I've used the tuning-primer.sh script to do some tuning and performance 
has improved somewhat, as query builder now only take 300 seconds 
average to load instead of 400, but it is still unusable which is 
frustrating the users. It's going to take another 36 hours before I can 
check how the optimisation is going.


I couldn't get the MySQLTuner to run, but I'll take a look at the perl 
this week if I get a chance.


If anyone else has any ideas at all, I'm open to suggestions, including 
witchcraft :)


Richard

[EMAIL PROTECTED] wrote:

Today's Topics:

   1. Re: RT 3.6.4 poor query performance (Jesse Vincent)
   2. Re: RT 3.6.4 poor query performance (Toby Darling)


--

Message: 1
Date: Thu, 13 Mar 2008 11:45:53 -0400
From: Jesse Vincent <[EMAIL PROTECTED]>
Subject: Re: [rt-users] RT 3.6.4 poor query performance
To: Richard Ellis <[EMAIL PROTECTED]>
Cc: rt-users@lists.bestpractical.com
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="us-ascii"


On Mar 13, 2008, at 8:46 AM, Richard Ellis wrote:

  

Hi All,

We have recently updated our RT instance from 3.4.6 running on  
Solaris 9

to a new server running Solaris 10 and 3.6.4.




You probably want RT 3.6.6 if you're on MySQL. Ruz did some serious  
query optimization.  But my first guess is that you've granted  
"Everybody" the right to OwnTickets somewhere.




  
Everything works perfectly and we have removed all of the  
customisations
that we used to use for a virtually vanilla 3.6.4 install. However,  
when

opening the Query Builder page, performance slows to a massive extent.
Average page load is under 1 second, but performing any action in  
Query

Builder averages 400 seconds.

I thought it was the database so upgraded mysql from 5.0.34 to  
5.0.51a,
but that made no difference. I then upgraded DBIx::SearchBuilder to  
the

latest version and DBD::mysql but it is still as bad. Even upgraded
every single perl module to latest and restarted everything, but still
as bad.

mysqlcheck rt3 says everything is ok.

There are only 10,000 tickets so it shouldn't be a volume problem.

Running on perl 5.8.8.

Anyone any ideas?

Richard

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com




-- next part --
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
Url : http://lists.bestpractical.com/pipermail/rt-users/attachments/20080313/85609c29/attachment-0001.pgp 


--

Message: 2
Date: Thu, 13 Mar 2008 15:53:24 +
From: Toby Darling <[EMAIL PROTECTED]>
Subject: Re: [rt-users] RT 3.6.4 poor query performance
To: Richard Ellis <[EMAIL PROTECTED]>,rt-users

Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Hi Richard

  
That crashes out with a divide by zero error in the scripting, but it 
looks really cool.


[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2M (Tables: 39)
[--] Data in InnoDB tables: 1015M (Tables: 20)
Use of uninitialized value in division (/) at ./mysqltuner.pl line 362 (#1)

Illegal division by zero at ./mysqltuner.pl line 362 (#2)
   (F) You tried to divide a number by 0.  Either something was wrong in
   your logic, or you need to put a conditional in to guard against
   meaningless input.



That indicates something went wrong earlier in getting $physical_memory 
(assuming you're using v0.8.6). Any error messages or warnings at the 
start?. Try chucking some print statements in the os_setup function to 
see where it's going wrong.


I should point out that I've nothing to do with the MySQLTuner project, 
I've not even actually run it myself, just looked at the output with the 
guys that look after the mysql/database.


Cheers
Toby
  


--
Sun.com <http://www.sun.com>  * Richard Ellis *
Technical Developer, .Sun eBusiness

*Sun Microsystems, Inc.*
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email [EMAIL PROTECTED]
sun.com

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Graham Dunn]

Toby Darling wrote:
> Hi Richard
> 
>> That crashes out with a divide by zero error in the scripting, but it 
>> looks really cool.
>>
>> [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
>> [--] Data in MyISAM tables: 2M (Tables: 39)
>> [--] Data in InnoDB tables: 1015M (Tables: 20)
>> Use of uninitialized value in division (/) at ./mysqltuner.pl line 362 (#1)
>>
>> Illegal division by zero at ./mysqltuner.pl line 362 (#2)
>>(F) You tried to divide a number by 0.  Either something was wrong in
>>your logic, or you need to put a conditional in to guard against
>>meaningless input.
> 
> That indicates something went wrong earlier in getting $physical_memory 
> (assuming you're using v0.8.6). Any error messages or warnings at the 
> start?. Try chucking some print statements in the os_setup function to 
> see where it's going wrong.

If you're running under FreeBSD < 7 (I think), change:

 } elsif ($os =~ /BSD/) {^M
 $physical_memory = `sysctl -n hw.realmem`;^M
 $swap_memory = `swapinfo | grep '^/' | awk '{ s+= \$2 } 
END { print s }'`;^M

to

 } elsif ($os =~ /BSD/) {^M
 $physical_memory = `sysctl -n hw.physmem`;^M
 $swap_memory = `swapinfo | grep '^/' | awk '{ s+= \$2 } 
END { print s }'`;^M



___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Toby Darling]

Hi Richard

> That crashes out with a divide by zero error in the scripting, but it 
> looks really cool.
> 
> [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> [--] Data in MyISAM tables: 2M (Tables: 39)
> [--] Data in InnoDB tables: 1015M (Tables: 20)
> Use of uninitialized value in division (/) at ./mysqltuner.pl line 362 (#1)
> 
> Illegal division by zero at ./mysqltuner.pl line 362 (#2)
>(F) You tried to divide a number by 0.  Either something was wrong in
>your logic, or you need to put a conditional in to guard against
>meaningless input.

That indicates something went wrong earlier in getting $physical_memory 
(assuming you're using v0.8.6). Any error messages or warnings at the 
start?. Try chucking some print statements in the os_setup function to 
see where it's going wrong.

I should point out that I've nothing to do with the MySQLTuner project, 
I've not even actually run it myself, just looked at the output with the 
guys that look after the mysql/database.

Cheers
Toby

LEGAL NOTICE
Unless expressly stated otherwise, information contained in this
message is confidential. If this message is not intended for you,
please inform [EMAIL PROTECTED] and delete the message.
The Cambridge Crystallographic Data Centre is a company Limited
by Guarantee and a Registered Charity.
Registered in England No. 2155347 Registered Charity No. 800579
Registered office 12 Union Road, Cambridge CB2 1EZ.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Jesse Vincent]

On Mar 13, 2008, at 8:46 AM, Richard Ellis wrote:


Hi All,

We have recently updated our RT instance from 3.4.6 running on  
Solaris 9

to a new server running Solaris 10 and 3.6.4.



You probably want RT 3.6.6 if you're on MySQL. Ruz did some serious  
query optimization.  But my first guess is that you've granted  
"Everybody" the right to OwnTickets somewhere.




Everything works perfectly and we have removed all of the  
customisations
that we used to use for a virtually vanilla 3.6.4 install. However,  
when

opening the Query Builder page, performance slows to a massive extent.
Average page load is under 1 second, but performing any action in  
Query

Builder averages 400 seconds.

I thought it was the database so upgraded mysql from 5.0.34 to  
5.0.51a,
but that made no difference. I then upgraded DBIx::SearchBuilder to  
the

latest version and DBD::mysql but it is still as bad. Even upgraded
every single perl module to latest and restarted everything, but still
as bad.

mysqlcheck rt3 says everything is ok.

There are only 10,000 tickets so it shouldn't be a volume problem.

Running on perl 5.8.8.

Anyone any ideas?

Richard

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com





PGP.sig
Description: This is a digitally signed message part
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT 3.6.4 poor query performance

[Toby Darling]

Let mysqltuner (http://rackerhacker.com/mysqltuner/) have a look at your 
database. Lots of useful stuff. We went from 180 second full text 
queries, to 7 seconds, just be tweaking innodb_buffer_pool_size.

Cheers
Toby

Richard Ellis wrote:
> Hi All,
> 
> We have recently updated our RT instance from 3.4.6 running on Solaris 9 
> to a new server running Solaris 10 and 3.6.4.
> 
> Everything works perfectly and we have removed all of the customisations 
> that we used to use for a virtually vanilla 3.6.4 install. However, when 
> opening the Query Builder page, performance slows to a massive extent. 
> Average page load is under 1 second, but performing any action in Query 
> Builder averages 400 seconds.
> 
> I thought it was the database so upgraded mysql from 5.0.34 to 5.0.51a, 
> but that made no difference. I then upgraded DBIx::SearchBuilder to the 
> latest version and DBD::mysql but it is still as bad. Even upgraded 
> every single perl module to latest and restarted everything, but still 
> as bad.
> 
> mysqlcheck rt3 says everything is ok.
> 
> There are only 10,000 tickets so it shouldn't be a volume problem.
> 
> Running on perl 5.8.8.
> 
> Anyone any ideas?
> 
> Richard
> 
> ___
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: [EMAIL PROTECTED]
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com

LEGAL NOTICE
Unless expressly stated otherwise, information contained in this
message is confidential. If this message is not intended for you,
please inform [EMAIL PROTECTED] and delete the message.
The Cambridge Crystallographic Data Centre is a company Limited
by Guarantee and a Registered Charity.
Registered in England No. 2155347 Registered Charity No. 800579
Registered office 12 Union Road, Cambridge CB2 1EZ.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

[rt-users] RT 3.6.4 poor query performance

[Richard Ellis]

Hi All,

We have recently updated our RT instance from 3.4.6 running on Solaris 9 
to a new server running Solaris 10 and 3.6.4.

Everything works perfectly and we have removed all of the customisations 
that we used to use for a virtually vanilla 3.6.4 install. However, when 
opening the Query Builder page, performance slows to a massive extent. 
Average page load is under 1 second, but performing any action in Query 
Builder averages 400 seconds.

I thought it was the database so upgraded mysql from 5.0.34 to 5.0.51a, 
but that made no difference. I then upgraded DBIx::SearchBuilder to the 
latest version and DBD::mysql but it is still as bad. Even upgraded 
every single perl module to latest and restarted everything, but still 
as bad.

mysqlcheck rt3 says everything is ok.

There are only 10,000 tickets so it shouldn't be a volume problem.

Running on perl 5.8.8.

Anyone any ideas?

Richard

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com