RT slow inserts and updates

Matt_van_der_Westhui · February 12, 2010, 10:26am

Hi all,

Having some (what seems like DB) issues when attaching files, or
replying to a certain transaction, and the associated queries, it takes
about a minute and a half to create a ticket with a 120 KB attachment,
and when replying to a transaction, fetching and inserting the
correspondence into the wysiwyg editor can take up to a minute. I have
turned on slow query logging and also optimized as much as possible,
using settings from our heavier DB servers as well as using
MySQLTuner… But still, the problem persists, details:

RT 3.8.6 on FreeBSD 7.1 VM (Had the same issue on a physical machine)
running on a hefty 64 bit host(Vista, VMware), 2G RAM, MySQL 5.1.3,
Apache2, mod_perl2.

Relevant httpd.conf :

ServerName rt.office.local

ServerAdmin #####@#####

DocumentRoot /usr/local/rt3/share/html/

AddDefaultCharset UTF-8

#PerlModule Apache::DBI

PerlModule Apache2::RequestRec

PerlModule HTML::Mason::ApacheHandler

PerlRequire /usr/local/rt3/bin/webmux.pl

Alias /NoAuth/images/ /usr/local/rt3/share/html/NoAuth/images/

SetHandler perl-script

PerlHandler RT::Mason

  AuthName RADIUS

  AuthType Basic

  AuthXRadiusAddServer "#####" "#####"

  AuthXRadiusTimeout 5

  AuthXRadiusRetries 4

  Require valid-user

  AuthBasicProvider xradius

Satisfy Any

Require valid-user

my.cnf:

[mysqld]

max_allowed_packet=10MB

query_cache_size=32M

tmp_table_size=64M

max_heap_table_size=64M

thread_cache_size=4

innodb_buffer_pool_size=300M

log-slow-queries=/var/log/mysql_slow_queries.log

RT_SiteConfig:

Set($rtname, ‘Alldata’);

Set($Organization, “########”);

Set($Webpath, “”);

Set($WebBaseURL, “http://##########”);

Set($SendmailPath , “/usr/sbin/sendmail”);

Set($SendmailArguments , “-oi -t”);

Set($Timezone , ‘Europe/Amsterdam’);

Set($CorrespondAddress, q{#####@#####} );

Set($CommentAddress, q{#####@#####});

Set($OwnerEmail, q{#####@#####});

Set($WebExternalAuth , 1);

Set($WebFallbackToInternalAuth , 1);

Set($WebExternalAuthContinuous, 0);

Set($WebExternalAuto , 1);

Set($AutoCreateFromExternalUserInfo, 1);

Set($MaxAttachmentSize , 10000000);

#Set($MaxInlineBody, 0);

Set($DontSearchFileAttachments, 1); (Doesn’t look like this works…)

Set($SuppressInlineTextFiles, 1);

1;

A few lines from slow query log:

SELECT DISTINCT main.* FROM Tickets main JOIN Transactions
Transactions_1 ON ( Transactions_1.ObjectId = main.id ) JOIN
Attachments Attachments_2 ON ( Attachments_2.TransactionId =
Transactions_1.id ) WHERE (Transactions_1.ObjectType = ‘RT::Ticket’)
AND (main.Status != ‘deleted’) AND ( ( Attachments_2.Filename IS NULL
AND Attachments_2.Content LIKE ‘%iPhone%’ ) AND (
Attachments_2.Filename IS NULL AND Attachments_2.Content LIKE ‘%IPHONE%’
) AND ( Attachments_2.Filename IS NULL AND Attachments_2.Content LIKE
‘%iphone%’ ) AND ( main.Status = ‘open’ OR main.Status = ‘new’ OR
main.Status = ‘stalled’ OR main.Status = ‘resolved’ ) ) AND (main.Type =
‘ticket’) AND (main.EffectiveId = main.id) ORDER BY main.id ASC;

Time: 100211 19:56:32

User@Host: rt_user[rt_user] @ localhost []

Query_time: 38.581619 Lock_time: 0.000478 Rows_sent: 0

Rows_examined: 55108

SET timestamp=1265914592;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68277’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:39:57

User@Host: rt_user[rt_user] @ localhost []

Query_time: 39.655915 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265927997;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:40:21

User@Host: rt_user[rt_user] @ localhost []

Query_time: 23.391029 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928021;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:40:33

User@Host: rt_user[rt_user] @ localhost []

Query_time: 11.936418 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928033;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:40:45

User@Host: rt_user[rt_user] @ localhost []

Query_time: 11.771741 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928045;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:40:57

User@Host: rt_user[rt_user] @ localhost []

Query_time: 11.982423 Lock_time: 0.000220 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928057;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:41:09

User@Host: rt_user[rt_user] @ localhost []

Query_time: 12.005457 Lock_time: 0.000007 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928069;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:41:21

User@Host: rt_user[rt_user] @ localhost []

Query_time: 12.057792 Lock_time: 0.000005 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928081;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:41:33

User@Host: rt_user[rt_user] @ localhost []

Query_time: 11.991430 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928093;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100211 23:41:45

User@Host: rt_user[rt_user] @ localhost []

Query_time: 12.007473 Lock_time: 0.000007 Rows_sent: 0

Rows_examined: 55113

SET timestamp=1265928105;

SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL AND
main.Content != ‘’) AND (main.Parent = ‘68280’) AND (main.ContentType =
‘text/plain’) ORDER BY main.id ASC;

Time: 100212 4:16:00

User@Host: rt_user[rt_user] @ localhost []

Query_time: 45.911922 Lock_time: 0.000005 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944560;