PerformanceTuning

From Request Tracker Wiki
Jump to navigation Jump to search

Properly configured, RT can run quickly on minimal hardware. This section of the wiki is an attempt to guide you through small changes in system, database and RT installation that can make a big impact on performance.

Version Support

Comments provided here are for RT versions 3.0.10 or better.

Overview

The major components of any RT system are:

  • Hardware
  • Operating system
  • A properly installed database application
  • The RT package, release 3.0.10 or better
  • All the perl modules required by that release of RT
  • A properly installed web server
  • Practical localized considerations for your business

Improper installation of any of the above items will lead to performance issues. However, continued focus on any one of the above items will not always lead to improved performance. It's a combination of changes across the board that will help you get a speedy RT system up and running.

How fast should you expect RT to run? Looking up a ticket should take less than a second regardless of the number of tickets in the database and assuming a reasonable length of comments and attachments. The limitation should be the speed at which your computer and browser can render the HTML in the page.

Myths

FastCGI vs. mod_perl

FastCGI is better than mod_perl -or- mod_perl is better than FastCGI - choose your poison, either method of executing RT can result in good response time. The traffic in the mailing lists has not been definitive on this point. If you have RT running, even if its slow, under either FastCGI or mod_perl, just leave it alone. Spending time in this area will probably not result in tremendous improvements.

In theory mod_perl can better share memory between processes, but it's not easy to prove in practice. However you can have fewer FastCGI processes, each can serve several apache children.

MySQL vs. Postgres

MySQL is better than Postgres -or- Postgres is better than MySQL. - Improperly configured, both databases will perform poorly. Properly configured, both will run very quickly.

Hardware

You'll need a fairly good sized computer system to run RT in a reasonable fashion. Consider that you are installing a Database backed customer and problem management system which you're going to use to help run your business. That said, as of September 2004, $1,000 spent on a 1U server with a 2Ghz single Pentium CPU with 1GB RAM and a 7200 RPM IDE disk drive is a good platform on which to run RT. SCSI interfaces will give you even better performance than IDE or SATA as they can handle parallel drive accesses better, but, it may not be worth the extra dollars. If you're going to spend extra dollars in a particular area of your server, spend those dollars on RAM. Then think about faster CPU. Then think about faster disk.

Disk I/O

The major bottleneck in any platform you choose will be disk I/O. (I don't believe this is true for most RT installations -- JesseVincent) - (I had major I/O issues that were alleviated when we moved from one disk to 2 disks running RAID 1 with the speed recommended below. Version 3.4.5 -- RT User) (RAID 1--mirroring--will NOT alleviate disk bottlenecks. In fact, it tends to make them worse. What you mean is RAID 1+0, aka RAID10. -- RJohnson) (RAID1 will improve disk reads; you're reading from two disks instead of one and can split the I/O between them. Best case, writes will be the same as with a single disk; most likely case, 5-10% degradation (number off the top of my head) worst case (unlikely) they will go half as fast. How much this matters for RT performance, I dunno. -- danpritts) (RAID1 on GNU/Linux does not read from both disks at once, danpritts is correct in theory but not in practice. Double checked January 2012. -- ward).

So let's test that first.

On a linux system you can determine the disk I/O performance by using the /hdparm/, here is an example from a modest machine (400MHz cpu) with an IBM 10,000 RPM scsi drive.

hdparm -t /dev/sdc1

/dev/sdc1:

Timing buffered disk reads:  64 MB in  1.92 seconds = 33.33 MB/sec

If you're using any kind of modern hardware you should get at least this level of performance. If you're not, consider getting a better hard drive. As of September 2004 a Western Digital Caviar SE, 7200rpm, EIDE, 80GB with an 8mb cache can be purchased for around $70 US. There is no reason to skimp on disk performance.

Memory (RAM)

You're running a database server. Databases perform best when their indexes and frequently used data structures are memory resident. If you've installed a stock operating system and stock database servers, regardless of whether it's MySQL or Postgres, you must make some adjustments in order to get any kind of reasonable performance.

Default Unix/Linux installations are not "database server" installations. One more time, you're running a database server. Fortunately the changes to be made are simple.

The basic idea, regardless of OS, is that you want to allow the databases to use as much RAM as they are comfortable using. They won't use everything, but give them some breathing room and you'll be very pleased with the results.

Here are instructions for a RedHat Linux Fedora Core 1 installation for adjusting shared memory:

1. Edit /etc/sysctl.conf and add these lines

kernel.shmmax=536870912
kernel.shmmni=4096
kernel.shmall=2097152

NOTE: "mni" is not a typo.

2. Execute the command "/sbin/sysctl -p" to make these changes take effect

For postgresql, do this:

1. Edit /var/lib/pgsql/data/postgresql.conf and change these lines

shared_buffers = 6004
sort_mem = 8096

2. Stop and start postgresql

For MySQL, look at the /my-large.cnf/ configuration file example that is provided with the installation. It's usually in /usr/share/doc/mysql-server* directory. The key settings are:

NOTE: You really must use INNODB tables as it's required after the RT 3.2.x release.

set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = sort_buffer=2M

Stop and restart mysql when you're done. (You'll need to delete /var/lib/mysql/ib_logfileX, if you change the log file size)

Memory, like disk, is also quite inexpensive. As of September 2004, 1GB of PC2700 DDR is $89 US

Note that raising the buffer_pool_size to a value like 256MB will speed the thing up even more. For a RT setup i'd suggest using a buffer pool at about 50% of your RAM size. Increasing the logfile size will speed up writing to the database, so is increasing the log buffer.

Query Optimization

For RT 3.6.0 and older, there is an database index missing that will help performance for installs with larget numbers of tickets using custom fields.

CREATE INDEX ObjectCustomFieldValues3 ON ObjectCustomFieldValues (ObjectId,ObjectType);

As you RT installation grows, ticket display will slow down without this index. If you are using Asset Tracker the problem is more pronounced because of extensive use of custom fields in AT installs.

I (Todd) found the long running queries by using the excellent tool mytop (http://jeremy.zawodny.com/mysql/mytop/). Hopefully this index will be added in 3.6.1+

The DB Schema for SQLite has fewer indexes compared to MySQL and PG. SQLiteIndexes explains how to add them to get much better performance.

Postgresql Indices

My organization just switched to RT 4.0.x using a Postgresql 9.2 database with full text indexing & searches. We have over 16,000 total users and ~600,000 tickets in our database. RT was barely usable, but after logging slow queries one of our developers created the following indices:

create index concurrently idx_groups_instance_domain_ticketrole_type_requestor on Groups(Instance) where LOWER(Domain) = LOWER('RT::Ticket-Role') AND LOWER(Type) = LOWER('Requestor');

create index concurrently idx_groups_instance_domain_queuerole_type_admincc on Groups(Instance) LOWER(Domain) = LOWER('RT::Queue-Role') AND LOWER(Type) = LOWER('AdminCc');

create index concurrently idx_groups_instance_domain_queuerole_type_admincc on Groups(Instance) where LOWER(Domain) = LOWER('RT::Queue-Role') AND LOWER(Type) = LOWER('AdminCc');

create index concurrently idx_groups_instance_domain_ticketrole_type_admincc on Groups(Instance) where LOWER(Domain) = LOWER('RT::Ticket-Role') AND LOWER(Type) = LOWER('AdminCc');

create index concurrently idx_groups_instance_domain_queuerole_type_cc on Groups(Instance) where LOWER(Domain) = LOWER('RT::Queue-Role') AND LOWER(Type) = LOWER('Cc');

create index concurrently idx_groups_instance_domain_ticketrole_type_cc on Groups(Instance) where LOWER(Domain) = LOWER('RT::Ticket-Role') AND LOWER(Type) = LOWER('Cc');

create index concurrently idx_groups_instance_domain_ticketrole_type_owner on Groups(Instance) where LOWER(Domain) = LOWER('RT::Ticket-Role') AND LOWER(Type) = LOWER('Owner');

Most pages have gone from a load time of several seconds to being too quick to bother with measuring.

Those indexes are overly specific, see this bug instead. Tsibley (talk) 01:41, May 25, 2013 (UTC)

Slow merged ticket performance

in RT 3.8.1 merged tickets may often exhibit slow response times or their access may result in outright timeouts in apache. this is caused by RT trying to write hundreds of messages to syslog, and can be remedied by adding this setting to RT_SiteConfig.pm

Set($LogToSyslog , 'notice');

CPU

If your disk is not fast and you don't have enough RAM and haven't reconfigured the kernel to allow for larger shared memory segments and haven't reconfigured your databases to use additional memory, then go back and do those things first.

RT will run in a speedy manner on a 1GHz or better processor. CPU is not the primary bottleneck. Before you spend more money on a new CPU or motherboard, upgrade your disks, move to RAID 0+1 (RAID 1+0..., NEVER use the dangerous RAID 0+1--RJohnson) or buy more RAM. Time and money spent on disk and memory I/O improvements will give you significant performance gains.

RT

The single largest performance improvement made in 2004 to the RT code base, in my opinion, is the re-coding of the perl module DBIx::SearchBuilder. If you do not have the latest revision of this package - get it.

  1. perl -MCPAN -e shell cpan> install DBIx::SearchBuilder

Restart Apache when you're done.

Webserver

Consider using lighttpd which is lighter and faster than Apache. Read more about it in the FastCGI Documentation.

Apache

I'll work on filling this out later or someone else can enter some information about the various startup options in Apache regarding thread pools. This isn't normally where you're going to have a big performance issue.

For Apache the out of the box configuration file does not start up enough processes to handle the requests coming in for RT. There are four variables in httpd.conf that you can modify to suit your needs.

StartServers: the number of httpd processes waiting to respond to requests.
MinSpareServers: the number of active httpd processes ready as backup.
MaxSpareServers: if you need more than the min Apache will startup more as needed, up to this limit.
MaxClients: number of clients that can connect

SuSE (SLES9) Users: these values are controlled by the sysconfig utility that you can access from YaST. We chose a "Medium" size implementation and it set our values to the following:

StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150

HTML::Mason

Altering the caching parameters of HTML::Mason has really helped my RT installation move from 'just usable' to 'almost fast' with only 512mb of ram. Tested with 3-5 active users at any time, with a database of over 20,000 tickets. Surprisingly, using pure memory caches has not yet been a problem despite the limited available system memory. I put the following into my Apache2 configuration (when using mod_perl):

PerlSetVar MasonStaticSource true
PerlSetVar MasonBufferPreallocateSize 4096000
PerlSetVar MasonDataCacheDefaults "cache_class=> MemoryCache, cache_depth => 4"
PerlSetVar MasonPreloads '/Elements/*'