RT 4.4.2 - Articles - search from comment/reply resulting in weird timeouts

I’m having an odd issue since we upgraded to RT 4.4.2 (which we only recently discovered).

We tend to use the Articles feature in 2 ways:

  1. For the drop down list with default e-mails.
  2. As a knowledge base with simple q&a.

When I go into Articles everything works as expected. I can see all topics, can browse all articles and searching works as expected. No delays, no weird behavior.

The issues we have however are coming from the comment/reply feature for Articles, where you can search for an Article when working on a comment/reply. The Articles feature allows you to do 3 things:

  1. Search for Articles matching the search criteria.
  2. Enter the Article ID.
  3. Select an Article from the drop down list

When using options 2 and 3 (enter ID or select from list) everything works. So far so good.

However, searching for an article results in extremely slow database queries which in turn result in timeout issues with mod_fcgid (default 40 seconds) and status 500 errors for that users current session. The odd thing is, the queries eventually give good results (it just takes extremely long).

This is an example of a query that will eventually time-out:

# Thread_id: 10  Schema: rt  QC_hit: No
# Query_time: 110.475090  Lock_time: 0.000137  Rows_sent: 1  Rows_examined: 43
use rt;
SET timestamp=1513329402;
SELECT DISTINCT main.* FROM Articles main JOIN ObjectClasses ObjectClasses_2  ON ( ObjectClasses_2.Class = main.Class ) JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON ( ObjectCustomFieldValues_1.Disabled = '0' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id )  WHERE (ObjectCustomFieldValues_1.LargeContent LIKE '%agenda%' OR ObjectCustomFieldValues_1.Content LIKE '%agenda%') AND (main.Disabled = '0') AND ( ( ObjectClasses_2.ObjectId = '3' AND ObjectClasses_2.ObjectType = 'RT::Queue' )  OR  ( ObjectClasses_2.ObjectId = '0' AND ObjectClasses_2.ObjectType = 'RT::System' ) )  ORDER BY main.SortOrder ASC, main.Name ASC;

The queries eventuelly give good results, but to receive those on the user end I need to increase the time-out of mod_fcgid. The fcgid timeout results in an error on the apache end of the spectrum, resulting in a status 500 error for the user.

However, waiting 120 seconds (or even longer sometimes) is unacceptable. Especially since the same query for a word / sentence from the Articles menu is nearly instant.
So either the query is wrong, or the feature in comment/reply is broken, or my database does something weird, or my settings are wrong, or ???

I’m at a loss to where to look for a solution for this issue. I’ve ran the rt-validator, it finds a bunch of references to articles that don’t exist anymore. I’m trying to resolve these atm, though I doubt that’s the issue (these non-existing articles are just old / deleted articles).

Has anyone seen this issue on their own installation? (might be a bug?) If not, I’m all open for ideas on how to troubleshoot this issue.

Thanks in advance!

Does anyone have an insight on this issue I’m having?

I’ve finished the rt-validator command and this didn’t resolve the issue.

These are the Article related settings that I have:

Set($ArticleOnTicketCreate, 1);
Set($HideArticleSearchOnReplyCreate, 0);
Set($LinkArticlesOnInclude, 1);

Also, this might be relevant, I’ve enabled full text search upon upgrading to RT 4.4.2 from our 4.2 installation. These are my full text settings:

Set(%FullTextSearch,
Enable => 1,
Indexed => 1,
Table => ‘AttachmentsIndex’,
);

As such, I’ve ran tests in my testing environment without full text indexing enabled. Even then the issues remain, so my guess would be that full text search isn’t involved in the article search.

I’ve tried to find the code that does the actual searching, but I’m not getting further then this file:

/opt/rt4/share/html/Articles/Elements/BeforeMessageBox

Though this doesn’t seem to lead me to the actual database query used.

What I’m trying to figure out is if the queries used in this feature (on correspond/create/new tickets search for articles) are different from the queries used in the articles view.

P.s. I’m wondering if this issue should be under Users or Developers. Maybe I should be moving this to the Developers corner?

What are the indexes for table ObjectCustomFieldValues fields (LargeContent, Content)? These are the key fields used by search 1. If they are not set up for content matching searches, this would be a source of delay.

/jeff

This is the current index on that database:

MariaDB [rt]> show index from ObjectCustomFieldValues from rt;
+-------------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                   | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ObjectCustomFieldValues |          0 | PRIMARY                  |            1 | id          | A         |    74010384 |     NULL | NULL   |      | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues1 |            1 | Content     | A         |      831577 |     NULL | NULL   | YES  | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues2 |            1 | CustomField | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues2 |            2 | ObjectType  | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues2 |            3 | ObjectId    | A         |    12335064 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.74 sec)

It seems that I’m missing LargeContent? Whats the correct way to create this index? I’ve ran the scripts for indexing RT ( /opt/rt4/sbin/rt-setup-fulltext-index ), but will running that again fix this issue or will I have to manually fix this?

It looks like you need to add an index [ObjectCustomFieldValues3 = LargeContent, for content].
I don’t know the system well enough to tell you how to make a word indexed field. You will need to ask others.

/jeff

Hi,

I thought that I had already replied to this thread. It looks like the Fulltext in the current form in RT does not handle the LargeContent field like the original Oracle/PostgreSQL setup from the wiki. You will need to either duplicate the setup for the Attachments fulltext indexing or use some other index that will allow you to index LIKE expressions. I know that PostgreSQL has pg_trgm that can do that. MySQL does have n-gram indexing but it does not look like it can use it to assist with LIKE queries. Good luck.

Regards,
Ken

Thanks for the replies!

I haven’t had enough time to look into creating the correct indices. But when reading these replies I understand that this isn’t something that I can easily fix, so I’ll take my time in resolving this one.

Any insight is welcome though.