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

[Tim]

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 
CachedGroupMembers(MemberId)"

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

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
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).
old_passwords=1

# 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!
#
#skip-networking

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

# 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
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=12M

innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50

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


Any suggestions are most welcome =]