Extremely slow rt response, query hanging

Hello,
One of our users constructed a search on our RT 3.2.2 instance which
is slowing the system down quite a lot:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ((main.Subject LIKE ‘%Performance%’)OR
( (Attachments_2.Content LIKE ‘%Performance%’)AND
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket) ) )

I have the feeling that ‘something’ in there is less than ideal, and
would like to get some input by you folks as to what can be done to
speed that up (I found two queries like this hogging the cpu for 200
hours.

The database is MySQL 4.0.24. I updated DBIx::Searchbuilder to
version 1.33 from 1.27, with no difference in results. The database
contains roughly 2400 tickets.

Any help is appreciated.

Regards,
Harald

Harald Wagener
Technischer Leiter

Foote Cone & Belding
FCB Wilkens
An der Alster 42
20099 Hamburg
Germany

T: +49 (0)40 2881 1252
F: +49 (0)40 2881 1217
hwagener@hamburg.fcb.com
http://www.footeconebelding.de

The query is problem for ages :frowning:
There is several solutions:

  1. turn off search by attachment content;
  2. enable this condition only when applied conditions limit result set
    to the sane number of tickets(for example 1000);
  3. add additional conditions automaticaly. it was suggested that when
    people search in attachments content they always want search in the
    text of the message (not in a files), so we could add additinal
    conditions “Filename IS NULL AND ContentType = ‘text/plain’” to this
    condition. This is not final solution, but could be part of;
  4. rework RT DB and split text attachments (really attachments which
    RT parses and converts to UTF-8) from all attachments, this would
    allow us to use FULLTEXT indexes, but it’s task for future major
    versions.On 12/7/05, Harald Wagener hwagener@hamburg.fcb.com wrote:

Hello,
One of our users constructed a search on our RT 3.2.2 instance which
is slowing the system down quite a lot:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ((main.Subject LIKE ‘%Performance%’)OR
( (Attachments_2.Content LIKE ‘%Performance%’)AND
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket) ) )

I have the feeling that ‘something’ in there is less than ideal, and
would like to get some input by you folks as to what can be done to
speed that up (I found two queries like this hogging the cpu for 200
hours.

The database is MySQL 4.0.24. I updated DBIx::Searchbuilder to
version 1.33 from 1.27, with no difference in results. The database
contains roughly 2400 tickets.

Any help is appreciated.

Regards,
Harald


Harald Wagener
Technischer Leiter

Foote Cone & Belding
FCB Wilkens
An der Alster 42
20099 Hamburg
Germany

T: +49 (0)40 2881 1252
F: +49 (0)40 2881 1217
hwagener@hamburg.fcb.com
http://www.footeconebelding.de


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

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

Download a free sample chapter of RT Essentials from O’Reilly Media at http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at http://bestpractical.com/services/training.html

Best regards, Ruslan.

At Wednesday 12/7/2005 03:54 AM, Harald Wagener wrote:

Hello,
One of our users constructed a search on our RT 3.2.2 instance which
is slowing the system down quite a lot:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ((main.Subject LIKE ‘%Performance%’)OR
( (Attachments_2.Content LIKE ‘%Performance%’)AND
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket) ) )

For what it’s worth, we’ve experienced this when searching on Subject &
Content together (RT 3.4.2, Oracle 9). Either a subject search or a content
search is OK, but not both together. I haven’t had time to profile the
query, so I can’t shed any light on a solution.

Steve

At Wednesday 12/7/2005 03:54 AM, Harald Wagener wrote:

Hello,
One of our users constructed a search on our RT 3.2.2 instance which
is slowing the system down quite a lot:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ((main.Subject LIKE ‘%Performance%’)OR
( (Attachments_2.Content LIKE ‘%Performance%’)AND
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket) ) )

For what it’s worth, we’ve experienced this when searching on Subject &
Content together (RT 3.4.2, Oracle 9). Either a subject search or a content
search is OK, but not both together. I haven’t had time to profile the
query, so I can’t shed any light on a solution.
Random guess is that when you add two conditions then DB have to use
temporary table.

Steve


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

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

Download a free sample chapter of RT Essentials from O’Reilly Media at http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at http://bestpractical.com/services/training.html

Best regards, Ruslan.

At Wednesday 12/7/2005 03:54 AM, Harald Wagener wrote:

Hello,
One of our users constructed a search on our RT 3.2.2 instance which
is slowing the system down quite a lot:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ((main.Subject LIKE ‘%Performance%’)OR
( (Attachments_2.Content LIKE ‘%Performance%’)AND
(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.Ticket) ) )

For what it’s worth, we’ve experienced this when searching on Subject &
Content together (RT 3.4.2, Oracle 9). Either a subject search or a content
search is OK, but not both together. I haven’t had time to profile the
query, so I can’t shed any light on a solution.

I just had a look at our Oracle9 instance with our production RT on it
and tried to run that query but it contains atleast one mistake. Could
be the fact that we’re running RT-3.4.3 and not 3.2.2
So this is the query that is run on our instance when asking for subject
like ‘structuur’ and content matching ‘betrekking’.

SELECT COUNT (DISTINCT main.ID)
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((transactions_1.objecttype = ‘RT::Ticket’))
AND ((main.effectiveid = main.ID))
AND ((main.status != ‘deleted’))
AND ((main.TYPE = ‘ticket’))
AND ( (LOWER (main.subject) LIKE ‘%structuur%’)
AND ( (contains (attachments_2.content, ‘betrekking’) > 0)
AND (attachments_2.transactionid = transactions_1.ID)
AND (main.ID = transactions_1.objectid)
)
)

There are a couple of things to be noted:

  • we modify the query if it searches for content (contains clause)
  • there is an appropriate index for this.
  • we must explicitly use wildcards if we want to use them (SB does the
    reverse)

Execution time of this query is 350ms :wink:
What would kill its performance is using wildcards at the front of the
search string (’%betrekking%’), killing its index. This will be slow for
any database relying on indices.
See the following example from our dbiprof output:
#####[ 1 ]###########################################################
Count : 15
Total Time : 991.093903 seconds
Longest Time : 859.530297 seconds
Shortest Time : 0.000017 seconds
Average Time : 66.072927 seconds
Key 1 :

/* Formatted on 2005/12/09 10:57 (Formatter Plus v4.8.7) */
SELECT COUNT (DISTINCT main.ID)
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((transactions_1.objecttype = ‘RT::Ticket’))
AND ((main.effectiveid = main.ID))
AND ((main.status != ‘deleted’))
AND ((main.TYPE = ‘ticket’))
AND ( (main.queue = ‘27’)
AND ( (contains (attachments_2.content, ‘%fixed%’) > 0)
AND (attachments_2.transactionid = transactions_1.ID)
AND (main.ID = transactions_1.objectid)
)
)

And the corresponding entry from the first query:
#####[ 37 ]###########################################################
Count : 15
Total Time : 2.809959 seconds
Longest Time : 2.155377 seconds
Shortest Time : 0.000011 seconds
Average Time : 0.187331 seconds
Key 1 :

Hope this will help you Steve and maybe other users using Oracle with
problems searching on Content.

Joop

Joop van de Wege JoopvandeWege@mococo.nl