[rt-users] RT 3.4.5 slow loading/large query for RT: At a Glance page]

Tim kruz at chariot.net.au
Wed Feb 22 22:48:59 EST 2006

Jesse Vincent wrote:
> On Mon, Feb 20, 2006 at 02:37:33PM +1030, Tim wrote:
>> Hi All,
>> I was recently tasked with upgrading RT from 3.0.11 to 3.4.5 for my 
>> company, basicaly we use RT to handle all incoming requests from 
>> customers as well as internal requests between branches, so the database 
>> is very large 650k + tickets from a large number of individual users - 
>> pretty much anyone that emails the company on the specified support 
>> address.
>> We have a front end box with SearchBuilder 1.38, Apache 2.0.54, mod_perl 
>> 2,RT 3.4.5 (p4 2.8 ghz 1.5 gig ram) and a backend box, running the mysql 
>> RT database Fedora Core 4, Mysql 4.1.16 (p4 3 ghz 4 gig ram 10k rpm WD 
>> raptor drive. The upgrade process was all very smooth ( I dumped the old 
>> DB - transferred it to the new server, configured the appropriate my.cnf 
>> settings, imported the database and then ran the schema upgrades) and in 
>> initial testing everything seemed fine, however when all the support 
>> staff started logging on this particular query grinds the server to a 
>> halt by maxing out the CPU on the rt-sql server.
> Can you tell us how you've tuned and optimized your mysql instance?

Ok, basically the only extra index that we have that seems to speed 
things along is this one "create index mbowe_MemberId on 

An analyze was run on all tables.

Interestingly the query that I posted previously seems to be attached to 
the "25 Highest priority tickets that I have requested". By disabling 
this element from the At a Glance page performance is now quite snappy 
with only a small delay when loading the At a Glance page.

Below is the mysqld section of the my.cnf that was used on the new 
server before the database import

port            = 3306
socket          = /var/lib/mysql/mysql.sock
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!

# Replication Master Server (default)
# binary logging is required for replication

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/

# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high

# Set .._log_file_size to 25 % of buffer pool size


# Uncomment the next lines if you want to use them
#set-variable = innodb_thread_concurrency=5

Any suggestions are most welcome =]

More information about the rt-users mailing list