Strangely, our RT server has suddenly started being really slow loading
up the search page and running searches - the same query is logged in
the slow-log every time - any ideas:
Time: 070405 16:01:56
User@Host: rt_user[rt_user] @ localhost []
Query_time: 38 Lock_time: 0 Rows_sent: 288 Rows_examined: 54167891
SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
CachedGroupMem
bers CachedGroupMembers_2, Groups Groups_3, ACL ACL_4 WHERE
((ACL_4.PrincipalTy
pe = Groups_3.Type)) AND ((ACL_4.RightName = ‘OwnTicket’)) AND
((CachedGroupMemb
ers_2.MemberId = Principals_1.id)) AND ((Groups_3.id =
CachedGroupMembers_2.Grou
pId)) AND ((Principals_1.Disabled = ‘0’)) AND
((Principals_1.PrincipalType = ‘Us
er’)) AND ((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id))
AND ((ACL
4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType = ‘RT::System’)) AND
((Groups
3.Domain = ‘RT::Queue-Role’) OR (Groups_3.Domain = ‘RT::System-Role’))
ORDER BY
main.RealName ASC;
Philip Kime
NOPS Systems Architect
310 401 0407
Strangely, our RT server has suddenly started being really slow
loading up the search page and running searches - the same query is
logged in the slow-log every time - any ideas:
Likely you granted some large group of users the right to own tickets.
PGP.sig (186 Bytes)
Hmm - not that I know of - we don’t have that many users, even if they
could all own tickets, it would be about 1300 users. Is that large
enough to be a problem? Normally the Owner list is less than 100 users
so I could check if someone did something. It seems back to normal now
though …
I noticed that the DB query was in the state “Copying to tmp table” for
most of the time the query took.
PK
Hmm - not that I know of - we don’t have that many users, even if
they could all own tickets, it would be about 1300 users. Is that
large enough to be a problem? Normally the Owner list is less than
100 users so I could check if someone did something. It seems back
to normal now though …
That was 288 users for the list, so maybe.
I noticed that the DB query was in the state “Copying to tmp table”
for most of the time the query took.
That might indicate that your db wants a bit of tuning.
-j
PGP.sig (186 Bytes)
At Thursday 4/5/2007 09:14 PM, Philip Kime wrote:
Hmm - not that I know of - we don’t have that many users, even if
they could all own tickets, it would be about 1300 users. Is that
large enough to be a problem?
Philip,
A quick check would be to remove the owner drop-down and see if
things speed up. If you’re interested, we replaced the drop-down list
with a simple input field for performance reasons, and I could dig
out our change - let me know.
Steve
"Rows_sent: 288 ", So it’s not that large group, but “Rows_examined:
54167891” wwhat is really wierd.
Yeah. MySQL 5?
5.0.27. It is intermittent - first time I’ve seen it was yesterday.
Still using SearchBuilder 1.43 on that box.
PK
"Rows_sent: 288 ", So it’s not that large group, but “Rows_examined:
54167891” wwhat is really wierd.
Yeah. MySQL 5?
5.0.27. It is intermittent - first time I’ve seen it was yesterday.
Still using SearchBuilder 1.43 on that box.
Hey Todd, you were looking for an RT query that’s pessimal on 5.0?
PGP.sig (186 Bytes)
Hi
Our approval process that we need to put in place doesn’t work all that
well with the approval process as documented with templates. Our
process requires me to copy all the data from the originating ticket
then have the user populate one additional field in the approval ticket.
I have been able to create the ticket, copy the data, update the
dependencies for the ticket and display the ticket in the edit form.
However, even if I assign $ARGS{‘Type’} or $ARGS{‘type’} to be
"approval" it does not create the approval…rather that is, the ticket
shows up in the “Change Management” Queue, but does not appear in the
Approvals page as is the case when using the templates.
Can someone please point me in the direction of how I would manually
indicate that a ticket is to be an approval ticket?
Thanks,
Jeff Stark
It’s happening all the time now for some reason. This is really odd - it
just seems to have started - here’s a typical example - state is
“Copying to tmp table” during the execution:
Time: 070406 17:25:05
User@Host: rt_user[rt_user] @ localhost []
Query_time: 38 Lock_time: 0 Rows_sent: 288 Rows_examined: 54320878
mysql> explain SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_4 JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
CachedGroupMember
s CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId =
Principals_1.id ) J
OIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId )
WHERE (P
rincipals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType = Groups_3.Type)
AND (Princ
ipals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’) AND
(ACL_4.RightNam
e = ‘OwnTicket’) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR
(ACL_4.ObjectType = ‘R
T::System’)) AND ((Groups_3.Domain = ‘RT::Queue-Role’) OR
(Groups_3.Domain = ‘RT
::System-Role’)) ORDER BY main.RealName ASC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 696
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: main
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 696
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Principals_1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rt3.main.id
rows: 1
Extra: Using where; Distinct
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: ref
possible_keys: DisGrouMem,SHRD_CGM1
key: SHRD_CGM1
key_len: 5
ref: rt3.main.id
rows: 1
Extra: Using where; Using index; Distinct
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ACL_4
type: range
possible_keys: ACL1
key: ACL1
key_len: 54
ref: NULL
rows: 77
Extra: Using where; Using index; Distinct
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: Groups_3
type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
key: PRIMARY
key_len: 4
ref: rt3.CachedGroupMembers_2.GroupId
rows: 1
Extra: Using where; Distinct
5 rows in set (0.01 sec)
Thanks Torsten,
Not sure what happened, but suddenly it began working…I must have had
a Typo somewhere.
Anyhow, now its working great.
Thanks,
Jeff StarkFrom: Ham MI-ID, Torsten Brumm [mailto:torsten.brumm@Kuehne-Nagel.com]
Sent: Friday, April 06, 2007 8:14 PM
To: Jeff Stark; rt-users@lists.bestpractical.com
Subject: AW: [rt-users] Manually Creating Approvals
Hji jeff,
Have you checked inside the db what type the newly created ticket is? It
must be of type ‘approval’
Then check the rights to your approval queue, has the guy who should
approve enought rights there?
Torsten
Most probably, 4.1 gens good plan for this.
It just occurred to me that this seems to have started since I added the
extra indexes for the Shredder - is that possible? It is actually
possible to have “conflicting” indexes? Here are the indexes I added:
CREATE INDEX SHRD_CGM1 ON CachedGroupMembers(MemberId, GroupId,
Disabled);
CREATE INDEX SHRD_CGM2 ON CachedGroupMembers(ImmediateParentId,
MemberId);
CREATE UNIQUE INDEX SHRD_GM1 ON GroupMembers(MemberId, GroupId);
CREATE INDEX SHRD_TXN1 ON Transactions(ReferenceType, OldReference);
CREATE INDEX SHRD_TXN2 ON Transactions(ReferenceType, NewReference);
CREATE INDEX SHRD_TXN3 ON Transactions(Type, OldValue);
CREATE INDEX SHRD_TXN4 ON Transactions(Type, NewValue);
Best regards, Ruslan.