RT slow inserts and updates

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

<LocationMatch “/NoAuth”>

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;

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

Time: 100212 4:16:12

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

Query_time: 11.784373 Lock_time: 0.000005 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944572;

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

Time: 100212 4:16:23

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

Query_time: 10.882671 Lock_time: 0.000165 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944583;

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

Time: 100212 4:16:34

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

Query_time: 10.798826 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944594;

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

Time: 100212 4:16:45

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

Query_time: 10.834359 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944605;

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

Time: 100212 4:16:56

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

Query_time: 11.038873 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944616;

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

Time: 100212 4:17:07

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

Query_time: 10.916713 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944627;

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

Time: 100212 4:17:18

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

Query_time: 10.637657 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944638;

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

Time: 100212 4:17:28

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

Query_time: 10.628150 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55118

SET timestamp=1265944648;

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

Time: 100212 5:30:50

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

Query_time: 36.636542 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949050;

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

Time: 100212 5:31:08

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

Query_time: 17.914245 Lock_time: 0.000007 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949068;

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

Time: 100212 5:31:19

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

Query_time: 11.108912 Lock_time: 0.000007 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949079;

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

Time: 100212 5:31:30

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

Query_time: 11.019829 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949090;

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

Time: 100212 5:31:41

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

Query_time: 11.070368 Lock_time: 0.000166 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949101;

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

Time: 100212 5:31:53

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

Query_time: 11.139404 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949113;

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

Time: 100212 5:32:04

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

Query_time: 11.035979 Lock_time: 0.000007 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949124;

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

Time: 100212 5:32:15

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

Query_time: 11.301442 Lock_time: 0.000157 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949135;

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

Time: 100212 5:32:26

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

Query_time: 11.136719 Lock_time: 0.000150 Rows_sent: 1

Rows_examined: 55123

SET timestamp=1265949146;

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

Time: 100212 9:52:37

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

Query_time: 58.189971 Lock_time: 0.000106 Rows_sent: 1

Rows_examined: 55129

SET timestamp=1265964757;

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

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

Query_time: 51.502213 Lock_time: 0.000869 Rows_sent: 1

Rows_examined: 55129

SET timestamp=1265964757;

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

Time: 100212 9:52:50

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

Query_time: 12.880213 Lock_time: 0.000111 Rows_sent: 1

Rows_examined: 55129

SET timestamp=1265964770;

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

Time: 100212 9:53:03

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

Query_time: 12.213462 Lock_time: 0.000006 Rows_sent: 1

Rows_examined: 55129

SET timestamp=1265964783;

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

Time: 100212 10:00:48

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

Query_time: 17.086619 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55134

SET timestamp=1265965248;

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

Time: 100212 10:05:06

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

Query_time: 13.199653 Lock_time: 0.000006 Rows_sent: 0

Rows_examined: 55134

SET timestamp=1265965506;