[rt-users] RE: Very slow query in RT 3.4.1
Anya Figlin
afiglin at pictage.com
Tue Apr 5 10:16:34 EDT 2005
we've experienced a similar problem when running large queries on
postgresql 7.3. Once we upgraded postgres version to 7.4, the time to run
the same large query went down from 50 seconds to 5 seconds!
As I was looking through RT-Users digest a couple of days ago, I noticed
that one of the developers replied to a slow query question by saying that
there was no optimizer in mysql (as far as I recall). If that'd be the
case, queries like this one would take forever ...
-anya
-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of
rt-users-request at lists.bestpractical.com
Sent: Monday, April 04, 2005 11:26 AM
To: rt-users at lists.bestpractical.com
Subject: RT-Users Digest, Vol 13, Issue 12
Send RT-Users mailing list submissions to
rt-users at lists.bestpractical.com
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
or, via email, send a message with subject or body 'help' to
rt-users-request at lists.bestpractical.com
You can reach the person managing the list at
rt-users-owner at lists.bestpractical.com
When replying, please edit your Subject line so it is more specific than
"Re: Contents of RT-Users digest..."
Today's Topics:
1. Very slow query in RT 3.4.1 (Marc Prud'hommeaux)
2. RE: RT vs ........ (Mai Le)
3. Re: create tickets with multiline text field using the cli!
(Jay R. Ashworth)
4. RT 3.2.3 with Oracle (Labonte, Phil)
----------------------------------------------------------------------
Message: 1
Date: Mon, 4 Apr 2005 10:57:43 -0700
From: Marc Prud'hommeaux <marc at solarmetric.com>
Subject: [rt-users] Very slow query in RT 3.4.1
To: rt-users at lists.bestpractical.com
Message-ID: <8662dd419cfce927a22f3b711160d0c9 at solarmetric.com>
Content-Type: text/plain; charset="us-ascii"
RT Users-
We're using RT 3.4.1 with MySQL 4.0.21 with perl 5.8.6 on Linux (kernel
2.2.20).
We recently upgraded from RT 3.0.0 to 3.4.1 in the hopes that it would speed
up our ever-slowing-down system. A number aspects of the system did indeed
seem to speed up, but replying to a ticket (hitting the "Reply" link from
Display.html) has become unbearably slow. Looking in my "mysql-slow.log"
log, I see that the main offending query is as
follows:
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL ACL_2,
Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND
((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND
( Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =
'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( (
(Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 5) OR (
Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 43004) )
AND Groups_3.Type = ACL_2.PrincipalType) ) AND (ACL_2.ObjectType =
'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId =
5) ) ORDER BY main.Name ASC;
If I manually run the query, it takes around 50 seconds to complete. If I
run an EXPLAIN on it, I see:
+----------------------+--------
+-----------------------------------------------------------------------
------------------------------+----------+---------
+------------------------------+-------
+-----------------------------------------------------------+
| table | type | possible_keys
| key
| key_len | ref | rows | Extra
|
+----------------------+--------
+-----------------------------------------------------------------------
------------------------------+----------+---------
+------------------------------+-------
+-----------------------------------------------------------+
| ACL_2 | range |
ACL1,RightName,PrincipalType,ObjectId,ObjectId_2,ObjectType,PrincipalId,
PrincipalType_2,RightName_2 | ACL1 | 54 | NULL
| 3 | Using where; Using index; Using temporary; Using
filesort |
| main | ALL | PRIMARY,Users3,Users_id_idx_mwp
| NULL
| NULL | NULL | 35305 |
|
| Principals_1 | eq_ref | PRIMARY,Disabled,id
| PRIMARY
| 4 | main.id | 1 | Using where;
Distinct |
| CachedGroupMembers_4 | ref | DisGrouMem,GrouMem,MemberId
|
MemberId | 5 | Principals_1.id | 1 | Using
where; Distinct |
| Groups_3 | eq_ref |
PRIMARY,Groups1,Groups2,Type,Domain,id,Instance,Type_2
| PRIMARY | 4 |
CachedGroupMembers_4.GroupId | 1 | Using where; Distinct
|
+----------------------+--------
+-----------------------------------------------------------------------
------------------------------+----------+---------
+------------------------------+-------
+-----------------------------------------------------------+
Does anyone know of any quick fix for this? Creating a new index or
something? I've seen a number of people report the problem on the list
in the past, but I couldn't find any satisfactory solution.
A second question: is the file rt-3.4.1/etc/constraints.mysql meant to
be used? It looks like it might contain some useful foreign key and
index definitions, but it never appears to be automatically called, and
I can't find any reference to the file in any documentation anywhere.
Is it experimental, or can it be applied to an existing RT schema?
--
Marc Prud'hommeaux
SolarMetric Inc.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2108 bytes
Desc: not available
Url :
http://lists.bestpractical.com/pipermail/rt-users/attachments/20050404/1a79b
9de/smime-0001.bin
------------------------------
Message: 2
Date: Mon, 4 Apr 2005 11:01:40 -0700
From: "Mai Le" <mle at Niku.com>
Subject: RE: [rt-users] RT vs ........
To: "Atom Powers" <APowers at PyramidBrew.com>, "Christopher Welsh"
<cpwe at deakin.edu.au>, "rt-users"
<rt-users at lists.bestpractical.com>
Message-ID:
<57D9A9C0457AEF4CB1125E91E652DF9C020DA9B9 at RWC-EXCHANGE-VS.Niku.com>
Content-Type: text/plain; charset="us-ascii"
Our organization also just threw out Track-It for the reasons Atom
listed below. We did not use the hardware/software audit feature. SMS
does the job for us. Track-It also does not an easy way to time-stamp
updates to the ticket. When the staff work on a ticket, email is sent
manually. All updates have to be cut-and-paste in which is a cumbersome
task so many of them skipped this step which brought up problems during
SOX auditing.
The best feature we got from RT was email notification and the ability
to work on the ticket via email. It's much simpler for the users and
the staff. We use auto-assigning owner based on queue script to
simulate that feature in Track-It.
Here is an additional list of feature comparision:
Crap about Track-It:
- Does not time-stamp changes to the ticket. You can't tell when was
the last time the end user update the ticket or the staff update the
ticket. Dates can be changed easily by the staff. (ie, closed today
but specify that it was closed 2weeks ago)
- It's IE-centric !!!
- It runs on Windows so it's another MS box to worry about for security
Plus for RT:
- Multiple users can be part of the ticket (cc or requestors). This is
really useful for us especially for approvals. Users make request for
some additional access, cc: their manager. The manager then can reply
with the approval. In Track-It, the staff would have to manually send
out the notification on manager, update that they did send it out. Once
the manager approved, the staff would need to copy and paste the reply
into the Track-It (SOX requirements).
- It integrates well with our Active Directory (we pull name, phone
number, office, and department from AD). 1 password for both system
(huge plus). People can no longer give excuses that they can't put in a
ticket because they forgot their Track-It password. The Staff no longer
needs to create the account for helpdesk. They just simply log in the
first time to create the account.
Account information gets updated automatically whenever it changes under
AD. (We have a weekly script that sync the database)
-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Atom
Powers
Sent: Monday, April 04, 2005 9:25 AM
To: Christopher Welsh; rt-users
Subject: RE: [rt-users] RT vs ........
I have recently moved our HelpDesk away from Track-It into RT. Track-It
is first and foremost a computer tracking and auditing system. The
HelpDesk facilities are half-baked, at best; we used Track-It for three
years, and it never got any better. I hated Track-It.
- It does not integrate well into email.
- Tickets have to be requested and updated from the Track-It interface.
- The Track-It client is slow.
- Track-It has virtually no ticket-management/escalation ability.
- *Nobody* liked to use Track-It, none of our users entered tickets in
it and none of the HelpDesk used Track-It to manage tickets.
RT has been a huge success and people are actually using it.
- It is much easier to enter tickets into RT.
- RT can send out notifications via email.
- The RT inteface is much easier customize and use.
- Ticket tracking, ownership, and escalation are very easy to use.
But Track-It has some features that you may find useful.
- It has a client that can audit computer hardware and software.
- It can associate a ticket with a specific user/system.
- It can manage purchases and assign the equipment to a system.
We still have Track-It around to audit computers, but as soon as I can
replace that capability it's going out the window. I don't know what
kind of features you want from Track-It, but I would take a hard look at
other options before purchasing it.
----
Perfection is just a word I use occasionally with mustard.
Atom Powers
Systems Administrator
Pyramid Breweries Inc.
206.682.8322 x251
-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of
Christopher Welsh
Sent: Monday, April 04, 2005 4:49 AM
To: rt-users
Subject: [rt-users] RT vs ........
Hi,
Let me first say I am being sincere here with my questions. I simply
wish to find reasons to keep RT over Track-IT. It's solid and does a
good job.
Need your thoughts. I wish to keep RT, unfortunately it seems it's not a
case of apple for apples, and my workmate puts together some good points
and Track-IT seems feature rich. Mind you that unlike what he says about
us putting RT in to get us started was crap. No one in their right mind
installs RT on Mandrake linux as a short term stop gap.
The way he puts things makes track-it definitely is much more feature
rich.
From what he says below, Track-it seems to be much more than just a
helpdesk.
Any points in favour of RT?
Anyone usedTrack-IT or know about it?
Anything better than these two products?
Anyone contract offer setup support for RT? As the prices my workmate
found on the Bestpracticle site are beyond our scope
Your thoughts? Please note that when I drafted my email to him, I did
not know what exactly the product was he was looking at.
Begin my original message
-------------------------
Over the weekend I enabled the following in RT:
1. Job - Escalation
o Jobs are escalated every 24 hours. They are set to highest
priority after 4 days (I est 4 days to be when we have
resolved most jobs by.)
2. End user - Self service web interface - users can check / add
jobs to the queue - requested by Pauld
3. Reminded - via email - sends a list of open/new jobs with
priorities to the help desk operator
o You are reminded via email about the following:
o New jobs from two days of creation
o Jobs still open every 7 days
Other requests Todo.
o Knowledge base - is installed not yet configured.
o Add sender's name to his/her job in the "new" jobs list in
the queue
o Enable Active directory authentication for Self Service web
interface.
Customisation
The system supports overlays, so one can customize any aspect of
the helpdesk with little interruption to future upgrades. Nice
feature. ie. Someone on the list is working on a wizard interface
(rather than
standard web interface) for users. Users can use a wizard to guide
them though the steps of adding jobs to the helpdesk.
Clarification on Friday's meeting
In relation to changing helpdesk systems, I'm not sure I explained
my view clearly in the meeting, as the thought of replacing the
helpdesk came as a surprise, especially after the pressure
involved in getting it up and running earlier this year. I'd love
to see what this other help desk systems offers, however at this
stage, I'm not convinced investing $5,000.00+ for a new helpdesk
will offer any significantly better benefits other than some basic
features (remote desktop, etc) already offered by Microsoft and
others for free.
One question raised in the meeting was that of having time to
enable helpdesk features in a timely manner. In addition to us
looking at this other helpdesk system, I'd like to get costs for
commercial support for the existing RT helpdesk system? As the
company that offers this product away also offers commercial
support. I think there are others too. I'd believe this could be
cost effective way of getting all the features we need.
Andrew,
Can you email me web site of the new helpdesk mentioned in the
meeting for assessment. TA
Workmates reply to my original email -------------------------------
I do not think that the RT Helpdesk has the ability to cover all aspects
of what we require. I think it is a great Free Package. But being free
does not mean that it is really free. The reason RT was deployed was
because we needed something to start tracking the work load and it has
done that job to an extent. It has also enabled us to develop a better
understanding of how helpdesk software works, and what we require as an
application with time for configuration and maintenance. Here is the
cost for installation support from the RT Helpdesk website_
http://www.bestpractical.com_ .
As the creator of RT and its related products, we are the most
knowledgeable company around when it comes to installing RT and RT-based
technologies.
o $2000 for up to six hours of installation and
installation-related troubleshooting.
o Installation support does not include configuration over and
above the defaults recommended by Best Practical.
o If we believe installation will take more than six hours, we
will attempt to notify you immediately so you can decide
whether we should continue for an additional charge.
We also offer customized support packages designed to meet specific
customers' needs.
Academic discounts are available.
And here is the Application support (I am not sure what currency that
this is in I would guess it is in US dollars)
*Bronze Level Support ($1,500 per quarter, $6,000 per year)*
For companies processing less than 1,000 tickets per quarter in one
ticket database.
o Best Practical will respond to four separate incident
support requests per quarter.
o Best Practical will use reasonable efforts to respond to
incident support requests within one business day of receipt
of request.
o Support requests come via e-mail, sent by your company's
designated customer representative.
o _Installation support <installation.html>_ is not included.
o Additional support (e.g. for additional ticket databases)
available at $200 per hour for a minimum of four hours.
I suppose that you could look at it like the difference between Cisco
and Alloy networking products, RT Helpdesk is like an Alloy switch that
will do the job but not have all the bells and whistles of the Cisco
Helpdesk like the Pacen Track-It helpdesk system.
The commercial products offer significantly greater amount of benefits
not only remote desktop support as can be seen by there brief
descriptions below. From reading the full feature list of RT it does not
come close to being able to compete with either of these helpdesk
management products.
I received an email from ManageEngine_
http://manageengine.adventnet.com/products/service-desk/helpdesk-screens
.html
_
Some of the features of this HelpDesk are: Self-Service Portal,
Knowledge Base,
Service Level Agreement to keep track of warranty Ends,
HelpDesk Reports,
Integrated NMS (Auto ticket generation on failure of network devices or
applications monitored by OpManager. Centralized tracking of all network
failure related tickets which is very helpful during auditing. Timely
notice ability and rectification of network failures which results in
maximum uptime of all your network devices and applications. )
Hardware and Software Inventory,
Software Licence Tracking,
Purchase Order Tracking,
Contract Management,
Inventory Reports,.
Another one probably the best I have come across is this one from
Intuit_ http://www.pacen.com.au/trackit/trackit.htm_
Some of the Features of this one
Intuit(r) Track-It!(r) 6.5 Enterprise Edition A perfect solution for IT
departments with three or more technicians who manage and support
thousands or more assets and large numbers of end users at multiple
locations. Edition, Enterprise Edition adds advanced help desk and asset
management capabilities. Advanced asset management features allow
customers to leverage their existing IT infrastructure - including
databases, servers and Microsoft Active Directory.
The Add-On Modules Intuit(r) Track-It!(r) Alert Provide technicians with
notification and escalation capabilities using pager and email.
Intuit(r)
Track-It!(r) Audit Collect detailed hardware and software information
from
Windows(r) 9x, NT, 2000 and XP systems. Intuit(r) Track-It!(r) Audit
Plus
Manage and initiate auditing from a central console for added
flexibility Intuit(r) Track-It!(r) Deploy Install and update software on
every local, mobile and remote PC, laptop or server across your entire
organization - without leaving your PC. Intuit(r) Track-It!(r)
KnowledgeBase
Boost first-level help desk technician productivity by giving them
instant access to thousands of problems/resolutions to leading
applications including Microsoft(r), Netscape(r), Novell(r), Lotus(r),
Adobe(r),
Symantec(r) and more. Intuit(r) Track-It!(r) Mac Audit Collect detailed
hardware and software information from Apple(r) Macintosh(r) 8.x and OS
X to
10.x computers on your network. Intuit(r) Track-It!(r) Receive Captures
emails from applications, network management tools and end users, and
automatically converts them to work orders. Additionally, users can
check the status of their work orders and add additional information.
Intuit(r) Track-It!(r) Remote Provide technicians with the ability to
remotely control PCs from within Intuit Track-It!, including
bi-directional file transfers and chat sessions. Intuit(r) Track-It!(r)
Sync
Enable technicians to create, edit and close work orders remotely with
handheld computers that support synchronization with Microsoft(r)
Outlook(r)
2000 and 2002. Intuit(r) Track-It!(r) Self Service Give users the
ability to
submit work orders including attachments , view status of their work
orders and messages from help desk staff, search Intuit Track-It!
Solutions for self help, and perform an audit on their PC. Intuit(r)
Track-It!(r) Self Service Plus Add your own problem/solution entries to
the database and allow end-users to search for solutions in freeform
style. Intuit(r) Track-It!(r) Technician Web Give technicians seamless
access to Intuit Track-It! from a browser. Intuit Track-It! Remote users
can take remote control of PCs using a browser.
I have emailed the local suppliers to get back to me with a quote and
also requested a 3 Month Trial Key for the software.
So as we can test the software.
End Workmates comments -------------------------------
Sincerely,
Chris W
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
RT Administrator and Developer training is coming to your town soon!
(Boston, San Francisco, Austin, Sydney) Contact
training at bestpractical.com for details.
Be sure to check out the RT Wiki at http://wiki.bestpractical.com
CONFIDENTIALITY NOTICE: The information contained in this message and
or attachments is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination, copying, or other use of this
information by persons or entities other than the intended recipient is
prohibited. If you received this e-mail or its attachments in error,
please contact the sender and delete the material from any system and
destroy any copies.
------------------------------
Message: 3
Date: Mon, 4 Apr 2005 14:19:50 -0400
From: "Jay R. Ashworth" <jra at baylink.com>
Subject: Re: [rt-users] create tickets with multiline text field using
the cli!
To: "rt-users at lists.fsck.com" <rt-users at lists.fsck.com>
Message-ID: <20050404141950.A3877 at cgi.jachomes.com>
Content-Type: text/plain; charset=us-ascii
On Mon, Apr 04, 2005 at 06:26:28PM +0200, Sven Sternberger wrote:
> but I answered the questions in the first mail ;-) In my my original
> posting I wrote:
> >>I think I tried everything (quoting,htmltags,singlequote)
>
> so when I write quote I mean all quoting include \
> and \\ and '' and "" and stuff like or just <br>
> (I think I wrote this in the second mail, too)
And yet, what you did not answer was...
> >>Why don't you try actually answering said questions, and we'll see if
> >>we can help you.
> ah yes, STDIN, that don't work in the CLI there are fields where
> you have to enter values like >> TEXT="huhu" <<, and there is no "-"
> mechanism.
*Is* there no "-" mechanism?
[ checks online help ]
I think you might want to look at the -i option, before giving up.
Cheers,
-- jra
--
Jay R. Ashworth
jra at baylink.com
Designer Baylink RFC
2100
Ashworth & Associates The Things I Think '87
e24
St Petersburg FL USA http://baylink.pitas.com +1 727 647
1274
If you can read this... thank a system administrator. Or two. --me
------------------------------
Message: 4
Date: Mon, 4 Apr 2005 14:26:01 -0400
From: "Labonte, Phil" <phil.labonte at transcore.com>
Subject: [rt-users] RT 3.2.3 with Oracle
To: <rt-users at lists.bestpractical.com>
Message-ID: <9D47B29B41BE6F4EA256BE6BBC8D3315E5CE98 at torex1.tcore.com>
Content-Type: text/plain; charset="us-ascii"
Is there a how to, or step by step to get RT 3.2.3 to work with Oracle?
Phil
------------------------------
_______________________________________________
RT-Users mailing list
RT-Users at lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
End of RT-Users Digest, Vol 13, Issue 12
****************************************
More information about the rt-users
mailing list