[rt-users] RT slow inserts and updates

Fri Feb 12 05:26:28 EST 2010

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 :

<VirtualHost rt.office.local:80>

   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/

<Directory />

    SetHandler perl-script

    PerlHandler RT::Mason

      AuthName RADIUS

      AuthType Basic

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

      AuthXRadiusTimeout 5

      AuthXRadiusRetries 4

      Require valid-user

      AuthBasicProvider xradius

   </Directory>

</VirtualHost>

<LocationMatch "/NoAuth">

    Satisfy Any

    Require valid-user

</LocationMatch>

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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at Host: rt_user[rt_user] @ localhost []

# Query_time: 13.199653  Lock_time: 0.000006 Rows_sent: 0
Rows_examined: 55134

SET timestamp=1265965506;

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100212/c8ea6c9d/attachment.htm>