Help Needed to Improve RT Performance

1

Hi,

I’m experiencing some performance issues with my Request Tracker (RT) setup and would appreciate any advice on how to optimize it.

Recently, I encountered the following error message:

Your query exceeded the maximum run time and was stopped. Try modifying your query to improve the performance or contact your RT admin.

Current Configuration:

  • RT Version: 5.0.6
  • Operating System: Red Hat Enterprise Linux 8.9 (Ootpa)
  • Database: MySQL (MariaDB 10.3.39) for Linux (x86_64), using readline 5.1
  • Hardware:
    • 2 vCPUs
    • 8 GB RAM
    • 4 GB Swap

Configuration Adjustments:

In an attempt to resolve the issue, I modified the fcgid.conf file by increasing the FcgidConnectTimeout value from 20 to 60 seconds. This stopped the warning from appearing. Here is the relevant configuration:

<IfModule mod_fcgid.c>
  FcgidConnectTimeout 60
  FcgidMaxRequestLen 1073741824

  <IfModule mod_mime.c>
    AddHandler fcgid-script .fcgi
  </IfModule>
</IfModule>

Database Information:

The database size is approximately 5.2 GB, with the following table sizes:

Table Size (MB)
Attachments 4793.20
sessions 334.58
Transactions 23.03
ObjectCustomFieldValues 13.98
CachedGroupMembers 12.67
Groups 7.55
Tickets 3.41
GroupMembers 2.72

Questions:

  • Would it be beneficial to move the Attachments table to an external drive, given its size?
  • Are there other optimizations you would recommend to improve the overall performance?

Thanks in advance for your help!

2

Hi,
I’ve not seen such problems with our system that has 19GB of Attachments.
However, your sessions table seems very large. Are you running rt-clean-sessions regularly?
https://docs.bestpractical.com/rt/5.0.7/rt-clean-sessions.html
Simon.

1 Like
4

Hey,

A few things to try:

Increase the number of vCPUs assigned to the server, and/or move the database to a separate server. More RAM may help as well, check the memory usage.

You may want to try moving Attachments to external storage. Check out RT::ExternalStorage - RT 5.0.7 Documentation - Best Practical .

Check the MariaDB logs for slow queries, might give some hints if there are some indices missing.

Cheers,
Andrew

1 Like
5

When externalising attachments, you need to choose the ExternalStorageCutoffSize carefully. The default is 10MB which may not be enough to make a sizeable change in your database. In our postgres database, I used the following query to analyse the Attachments table:

select 
	pg_size_pretty(sum(length(content))) as TotalSize, 
	length(cast(length(content) as text)) as OrderOfMag 
	from attachments 
	where contentencoding != 'external' 
	group by OrderOfMag 
	order by OrderOfMag desc
;

This gives a result similar to this:

 totalsize | orderofmag
-----------+------------
 293 MB    |          7
 6451 MB   |          6
 5183 MB   |          5
 2211 MB   |          4
 301 MB    |          3
 4116 kB   |          2
 65 kB     |          1
(7 rows)

OrderOfMag is the order of magnitude of the content field, eg 1234 bytes would be 4, 7865431 would be 7. With the default setting, only records of magnitude 8 will be externalised.

I found that I needed to set ExternalStorageCutoffSize to 10000 to make any significant reduction in the table size.

Note that the rt-externalize-attachments script maintains a “high-water mark” record so that it can speed up subsequent runs. If you modify ExternalStorageCutoffSize, you will need to cancel the high water mark to force it to re-run on previous records:

delete from Attributes where Attributes.Name='ExternalStorage';

Best wishes,

Simon.

1 Like
6

Hi,
Thank you for your help @SimonW and @Andrew_Ruthven.

SimonW, I’ll just run rt-clean-sessions. If you write that your database is 19 GB and works fine, I won’t mess around and move it to an external drive.

vCPU and RAM are not loaded. RT works fine.

7

Hi,

You might try turning on the slow query log in MySQL to help identify what’s causing the issue.

https://dev.mysql.com/doc/refman/9.0/en/slow-query-log.html

Simon.