RT 3.4.5 slow loading/large query for RT: At a Glance page]]

Tim wrote:

Jesse Vincent 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 =]

I forgot to include the contents of the MyRequests Element which can be
found below

<&|/Elements/TitleBox,
title => loc("[_1] highest priority tickets I requested…", $rows),
title_href => “Search/Results.html”.$QueryString &>
<& /Elements/TicketList,
Format => “’<a
href=”$RT::WebPath/Ticket/Display.html?id=id">id/TITLE:#’,
’<a
href="$RT::WebPath/Ticket/Display.html?id=id">Subject/TITLE:Subject’,
QueueName, ExtendedStatus, ‘Priority/TITLE:Pri’, OwnerName",
Query => $Query,
OrderBy => ‘Priority’,
Order => ‘DESC’,
ShowNavigation => 0,
Rows => $rows

   &>

</&>
<%init>
my $rows = $RT::MyRequestsLength;

my $Query = “Requestor.EmailAddress =
’”.$session{‘CurrentUser’}->EmailAddress."’ AND (Status = ‘new’ OR
Status = ‘open’ OR Status = ‘stalled’)";

my $QueryString = “”;
$QueryString = ‘?’ . $m->comp(’/Elements/QueryString’,
Query => $Query,
Order => ‘DESC’,
OrderBy => ‘Priority’) if ($Query);

</%init>

/
/