Slow querys - can I speed this up?

RT Users
1

RT frequently acts sluggish (this may or may not be related to my
other thread about mailgate timing out) so I’ve turned on the slow
query log in mySQL, and am getting this error quite frequently:

Query_time: 160.388061 Lock_time: 0.000103 Rows_sent: 1

Rows_examined: 930035
SET timestamp=1245189464;
SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL
AND main.Content != ‘’) AND (main.Parent = ‘964022’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;

I’ve put an index on the Parent and ContentType columns (it’s not
letting me do it on the Content column because it’s a BLOB) but that
doesn’t seem to be speeding it up. We just passed 30,000 tickets and
the Attachment table is huge (2.4 GiB) Any ideas?
Jon Baker
Information Technology
Willie George Ministries
1003 N 129th E Ave
Tulsa OK 74116
(918) 234-5656

2

mysql 5.1.x? report into mysql’s bug tracker.On Wed, Jun 17, 2009 at 2:02 AM, Jon Bakerjbaker@wgm.us wrote:

RT frequently acts sluggish (this may or may not be related to my
other thread about mailgate timing out) so I’ve turned on the slow
query log in mySQL, and am getting this error quite frequently:

Query_time: 160.388061 Lock_time: 0.000103 Rows_sent: 1

Rows_examined: 930035
SET timestamp=1245189464;
SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL
AND main.Content != ‘’) AND (main.Parent = ‘964022’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;

I’ve put an index on the Parent and ContentType columns (it’s not
letting me do it on the Content column because it’s a BLOB) but that
doesn’t seem to be speeding it up. We just passed 30,000 tickets and
the Attachment table is huge (2.4 GiB) Any ideas?

Jon Baker
Information Technology
Willie George Ministries
1003 N 129th E Ave
Tulsa OK 74116
(918) 234-5656

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

3

Have you tried tuning MySQL with mysqltuner? We had a similar problem when
our attachments table grew over 2.2gb. I was able to resolve the issue we
had by increasing settings based on the recommendations from mysqltuner. We
frequently experienced ticket load times of upto 14 - 30 seconds but now the
majority of tickets will load and display in under 1 second.

Had to add more RAM to the server also, MySQL was choking.

Currently running;
Debian Lenny 64Bit
4Gb RAM
Apache2 - Modperl
MySQL 5.0.51a-24

Regards,

*Aaron Guise
027 212 6638
aaron@guise.net.nzOn Wed, Jun 17, 2009 at 10:02 AM, Jon Baker jbaker@wgm.us wrote:

RT frequently acts sluggish (this may or may not be related to my
other thread about mailgate timing out) so I’ve turned on the slow
query log in mySQL, and am getting this error quite frequently:

Query_time: 160.388061 Lock_time: 0.000103 Rows_sent: 1

Rows_examined: 930035
SET timestamp=1245189464;
SELECT main.* FROM Attachments main WHERE (main.Content IS NOT NULL
AND main.Content != ‘’) AND (main.Parent = ‘964022’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;

I’ve put an index on the Parent and ContentType columns (it’s not
letting me do it on the Content column because it’s a BLOB) but that
doesn’t seem to be speeding it up. We just passed 30,000 tickets and
the Attachment table is huge (2.4 GiB) Any ideas?

Jon Baker
Information Technology
Willie George Ministries
1003 N 129th E Ave
Tulsa OK 74116
(918) 234-5656

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

4

Dear RT Developer,
i’m looking for a way for a special custom RT Page to bypass the Homepage Refresh interval. I copy and pasted from other pages the following:

<& /Elements/Header, Title => loc(“Request for Change: User Account”), Refresh => $session{‘home_refresh_interval’} &>

But now the Homepage Refresh Interval of the user (if set) takes place. How can i explicit diasable it for this page, also if a user has set a homepage refresh? Is it enough to remove the Refresh Part of the line above?

Torsten

Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Dirk Blesius (Stellv.), Alfred Manke, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne