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

[Tim]

Tim wrote:
>
>
> 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 =]
>
>
>
>

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__</a>/TITLE:#', 
'<a 
href=\"$RT::WebPath/Ticket/Display.html?id=__id__\">__Subject__</a>/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>

/
/