Very slow query in RT 3.4.1

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.

smime.p7s (2.06 KB)

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 …

-anyaFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of
rt-users-request@lists.bestpractical.com
Sent: Monday, April 04, 2005 11:26 AM
To: rt-users@lists.bestpractical.com
Subject: RT-Users Digest, Vol 13, Issue 12

Send RT-Users mailing list submissions to
rt-users@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@lists.bestpractical.com

You can reach the person managing the list at
rt-users-owner@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@solarmetric.com
Subject: [rt-users] Very slow query in RT 3.4.1
To: rt-users@lists.bestpractical.com
Message-ID: 8662dd419cfce927a22f3b711160d0c9@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@Niku.com
Subject: RE: [rt-users] RT vs …
To: “Atom Powers” APowers@PyramidBrew.com, "Christopher Welsh"
cpwe@deakin.edu.au, "rt-users"
rt-users@lists.bestpractical.com
Message-ID:
57D9A9C0457AEF4CB1125E91E652DF9C020DA9B9@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)

From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@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

From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@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® Track-It!® 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® Track-It!® Alert Provide technicians with
notification and escalation capabilities using pager and email.
Intuit®
Track-It!® Audit Collect detailed hardware and software information
from
Windows® 9x, NT, 2000 and XP systems. Intuit® Track-It!® Audit
Plus
Manage and initiate auditing from a central console for added
flexibility Intuit® Track-It!® Deploy Install and update software on
every local, mobile and remote PC, laptop or server across your entire
organization - without leaving your PC. Intuit® Track-It!®
KnowledgeBase
Boost first-level help desk technician productivity by giving them
instant access to thousands of problems/resolutions to leading
applications including Microsoft®, Netscape®, Novell®, Lotus®,
Adobe®,
Symantec® and more. Intuit® Track-It!® Mac Audit Collect detailed
hardware and software information from Apple® Macintosh® 8.x and OS
X to
10.x computers on your network. Intuit® Track-It!® 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® Track-It!® Remote Provide technicians with the ability to
remotely control PCs from within Intuit Track-It!, including
bi-directional file transfers and chat sessions. Intuit® Track-It!®
Sync
Enable technicians to create, edit and close work orders remotely with
handheld computers that support synchronization with Microsoft®
Outlook®
2000 and 2002. Intuit® Track-It!® 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®
Track-It!® Self Service Plus Add your own problem/solution entries to
the database and allow end-users to search for solutions in freeform
style. Intuit® Track-It!® 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@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@baylink.com
Subject: Re: [rt-users] create tickets with multiline text field using
the cli!
To: "rt-users@lists.fsck.com" rt-users@lists.fsck.com
Message-ID: 20050404141950.A3877@cgi.jachomes.com
Content-Type: text/plain; charset=us-ascii

but I answered the questions in the first mail :wink: 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

(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@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@transcore.com
Subject: [rt-users] RT 3.2.3 with Oracle
To: rt-users@lists.bestpractical.com
Message-ID: 9D47B29B41BE6F4EA256BE6BBC8D3315E5CE98@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@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

End of RT-Users Digest, Vol 13, Issue 12

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

Has anyone had any luck with making any indexes that would help with
that query, or is it just doomed to always be slow for MySQL? Sadly,
MySQL’s query cache doesn’t help with it either, since the query
contains a ticket-specific key in the WHERE clause.

We’re desperate to solve this. We’ll need to downgrade to 3.0.0 if we
can’t fix the slow page loading time.On Apr 5, 2005, at 10:16 AM, Anya Figlin wrote:

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

On Apr 4, 2005, at 10:57 AM, Marc Prud’hommeaux wrote:

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,PrincipalI
d,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.

Marc Prud’hommeaux
SolarMetric Inc.

smime.p7s (2.06 KB)

any reason you don’t want to upgrade to mysql 4.1. I’ve been running 4.1
for about 6 months now and have been very happy with the results.

Seems to me that if you’re upgrading RT, upgrading mysql at the same time
seems logical (how often to you get to do an upgrade anyways?).

Ramon Kagan
York University, Computing and Network Services
Information Security - Senior Information Security Analyst
(416)736-2100 #20263
rkagan@yorku.ca


I have not failed. I have just I don’t know the secret to success,
found 10,000 ways that don’t work. but the secret to failure is
trying to please everybody.
- Thomas Edison - Bill Cosby
----------------------------------- ------------------------------------On Tue, 5 Apr 2005, Marc Prud’hommeaux wrote:

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

Has anyone had any luck with making any indexes that would help with
that query, or is it just doomed to always be slow for MySQL? Sadly,
MySQL’s query cache doesn’t help with it either, since the query
contains a ticket-specific key in the WHERE clause.

We’re desperate to solve this. We’ll need to downgrade to 3.0.0 if we
can’t fix the slow page loading time.

On Apr 5, 2005, at 10:16 AM, Anya Figlin wrote:

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

On Apr 4, 2005, at 10:57 AM, Marc Prud’hommeaux wrote:

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,PrincipalI
d,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.


Marc Prud’hommeaux
SolarMetric Inc.

Ramon & all-

Is there reason to believe that MySQL 4.1 would do any better than 4.0
with the query? Have other people solved this problem by upgrading? I’m
a bit frightened by the menacing 4.1 release notes that discuss how
difficult it is to roll back to 4.0, so I’d rather not attempt it
unless people are confident that 4.1 will solve this problem.On Apr 5, 2005, at 12:11 PM, Ramon Kagan wrote:

any reason you don’t want to upgrade to mysql 4.1. I’ve been running
4.1
for about 6 months now and have been very happy with the results.

Seems to me that if you’re upgrading RT, upgrading mysql at the same
time
seems logical (how often to you get to do an upgrade anyways?).

Ramon Kagan
York University, Computing and Network Services
Information Security - Senior Information Security Analyst
(416)736-2100 #20263
rkagan@yorku.ca



I have not failed. I have just I don’t know the secret to
success,
found 10,000 ways that don’t work. but the secret to failure is
trying to please everybody.

  • Thomas Edison - Bill Cosby


On Tue, 5 Apr 2005, Marc Prud’hommeaux wrote:

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

Has anyone had any luck with making any indexes that would help with
that query, or is it just doomed to always be slow for MySQL? Sadly,
MySQL’s query cache doesn’t help with it either, since the query
contains a ticket-specific key in the WHERE clause.

We’re desperate to solve this. We’ll need to downgrade to 3.0.0 if we
can’t fix the slow page loading time.

On Apr 5, 2005, at 10:16 AM, Anya Figlin wrote:

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

On Apr 4, 2005, at 10:57 AM, Marc Prud’hommeaux wrote:

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

  1. ) 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,Principa
lI
d,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.


Marc Prud’hommeaux
SolarMetric Inc.

Marc Prud’hommeaux
SolarMetric Inc.

smime.p7s (2.06 KB)

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

With all due respect, please allow me to suggest that your best
investment of time and effort may be to go hard-core on despamming
those mailboxes…?

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

  If you can read this... thank a system administrator.  Or two.  --me

You all might want to check out this before you do any upgrading or
downgrading…speaks of memory and swap management…could be what’s
ailing you.

http://www.ale.org/archive/ale/ale-2001-09/msg00165.htmlOn Tue, 2005-04-05 at 12:24 -0700, Marc Prud’hommeaux wrote:

Ramon & all-

Is there reason to believe that MySQL 4.1 would do any better than 4.0
with the query? Have other people solved this problem by upgrading? I’m
a bit frightened by the menacing 4.1 release notes that discuss how
difficult it is to roll back to 4.0, so I’d rather not attempt it
unless people are confident that 4.1 will solve this problem.

On Apr 5, 2005, at 12:11 PM, Ramon Kagan wrote:

any reason you don’t want to upgrade to mysql 4.1. I’ve been running
4.1
for about 6 months now and have been very happy with the results.

Seems to me that if you’re upgrading RT, upgrading mysql at the same
time
seems logical (how often to you get to do an upgrade anyways?).

Ramon Kagan
York University, Computing and Network Services
Information Security - Senior Information Security Analyst
(416)736-2100 #20263
rkagan@yorku.ca



I have not failed. I have just I don’t know the secret to
success,
found 10,000 ways that don’t work. but the secret to failure is
trying to please everybody.
- Thomas Edison - Bill Cosby


On Tue, 5 Apr 2005, Marc Prud’hommeaux wrote:

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

Has anyone had any luck with making any indexes that would help with
that query, or is it just doomed to always be slow for MySQL? Sadly,
MySQL’s query cache doesn’t help with it either, since the query
contains a ticket-specific key in the WHERE clause.

We’re desperate to solve this. We’ll need to downgrade to 3.0.0 if we
can’t fix the slow page loading time.

On Apr 5, 2005, at 10:16 AM, Anya Figlin wrote:

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

On Apr 4, 2005, at 10:57 AM, Marc Prud’hommeaux wrote:

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

  1. ) 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,Principa
lI
d,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.


Marc Prud’hommeaux
SolarMetric Inc.


Marc Prud’hommeaux
SolarMetric Inc.


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@bestpractical.com for details.

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

Well, even if I did reduce the amount of spam, that wouldn’t help the
problem at hand, since the 200K+ spam users would still remain in the
Users, Groups, and Principles tables in the database. To my knowledge,
it is still impermissible to delete users and groups, correct?On Apr 5, 2005, at 1:17 PM, rt-users-request@lists.bestpractical.com wrote:

Date: Tue, 5 Apr 2005 15:50:55 -0400
From: “Jay R. Ashworth” jra@baylink.com
Subject: Re: [rt-users] Very slow query in RT 3.4.1
To: rt-users@lists.bestpractical.com
Message-ID: 20050405155055.B15135@cgi.jachomes.com
Content-Type: text/plain; charset=us-ascii

On Tue, Apr 05, 2005 at 12:07:26PM -0700, Marc Prud’hommeaux wrote:

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

With all due respect, please allow me to suggest that your best
investment of time and effort may be to go hard-core on despamming
those mailboxes…?

Cheers,
– jra

Marc Prud’hommeaux
SolarMetric Inc.

smime.p7s (2.06 KB)

Well, even if I did reduce the amount of spam, that wouldn’t help the
problem at hand, since the 200K+ spam users would still remain in the
Users, Groups, and Principles tables in the database. To my knowledge,
it is still impermissible to delete users and groups, correct?

I should think that DELETE CASCADE would be acceptable for users
created from spam. Jesse? Russ? Steve?

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

  If you can read this... thank a system administrator.  Or two.  --me

At Tuesday 4/5/2005 04:39 PM, Jay R. Ashworth wrote:>On Tue, Apr 05, 2005 at 01:34:21PM -0700, Marc Prud’hommeaux wrote:

Well, even if I did reduce the amount of spam, that wouldn’t help the
problem at hand, since the 200K+ spam users would still remain in the
Users, Groups, and Principles tables in the database. To my knowledge,
it is still impermissible to delete users and groups, correct?

I should think that DELETE CASCADE would be acceptable for users
created from spam. Jesse? Russ? Steve?

I don’t know if you mean this Steve, but anyway, I’d be very nervous
about deleting anything using SQL statements.

Steve

At Tuesday 4/5/2005 04:39 PM, Jay R. Ashworth wrote:

Well, even if I did reduce the amount of spam, that wouldn’t help the
problem at hand, since the 200K+ spam users would still remain in the
Users, Groups, and Principles tables in the database. To my knowledge,
it is still impermissible to delete users and groups, correct?

I should think that DELETE CASCADE would be acceptable for users
created from spam. Jesse? Russ? Steve?

I don’t know if you mean this Steve, but anyway, I’d be very nervous
about deleting anything using SQL statements.

I did, in fact, mean you (though I wasn’t sure if I’d remembered your
name correctly).

Hmmm… there has to be some reasonable way to clean up the results
of that particular problem…

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

  If you can read this... thank a system administrator.  Or two.  --me

I should think that DELETE CASCADE would be acceptable for users
created from spam. Jesse? Russ? Steve?

Please search the list archives before giving empty answers,
RTx::Shredder can delete tickets and associated data from the db

-- Niels.

That’s what I was afraid of. Short up switching from MySQL to
PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
all that might help with the search? Would trying to trim the size of
the Principals or Groups tables help (they have 200,000+ rows in each,
due primarily to the large amount of spam we receive via support)?

With all due respect, please allow me to suggest that your best
investment of time and effort may be to go hard-core on despamming
those mailboxes…?

It’s a generic problem for anyone supporting a public site where even
if you can filter the spam you end up with many thousands of users
created from email addresses who typically only ask one question.

Les Mikesell
les@futuresource.com

I should think that DELETE CASCADE would be acceptable for users
created from spam. Jesse? Russ? Steve?

Please search the list archives before giving empty answers,

Well, thank you, Neils.

I enjoy being spanked in public.

RTx::Shredder can delete tickets and associated data from the db

At least you provided some useful data with your self-importance.

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

  If you can read this... thank a system administrator.  Or two.  --me