Folks,
We’re in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some
“interesting” issues with the Attachments table.
Here’s what queries against Attachments looked like on our old RT instance:
previous RT install (3.6.4):
mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS
NOT NULL AND main.Content != ‘’) AND (main.Parent = ‘1208717’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
| 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 |
const | 2 | Using where; Using filesort |
1 row in set (0.00 sec)
mysql>
Now, here’s what (what should be the self-same queries…) is being produced
on our new RT host, running 3.8.2:
new RT host:
mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS
NOT NULL AND main.Content != ‘’) AND (main.Parent = ‘1208717’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
| 1 | SIMPLE | main | index | Attachments3 | PRIMARY | 4 |
NULL | 2199950 | Using where |
1 row in set (0.11 sec)
mysql>
Obviously, on the new host, it isn’t using the index on the Attachments
table - and that’s REALLY making things go slowly for some bits of RT.
Anybody got a clue how to fix this, or an idea of what we can do to coerce
it to use the proper index?
[We have quite a lot of tickets, and a fairly scary amount of spam has
leaked into our RT instance - I’m going to need to run the shredder on a
large number of deleted tickets, but the Attachments table is currently so
big that doing so is a bit daunting…]
This is on a new host, and we imported via a mysqldump and re-import, so the
data in the table should be defragmented… but the end result is clearly
problematic.
thanks in advance,
–elijah
Folks,
We’re in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some
“interesting” issues with the Attachments table.
What does mysqltuner say on each system?
What mysql version are you running on each?
What’s the configuration of each system?
Give us a bit to work with 
I believe you’re on mysql 5.1.x, it’s know issue with some 5.1.2x. I
can not say if it’s been fixed in recent release of 5.1. You should
file bug into mysql bug tracker.On Wed, May 27, 2009 at 1:16 AM, Elijah Wright elw@brandorr.com wrote:
Folks,
We’re in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some
“interesting” issues with the Attachments table.
Here’s what queries against Attachments looked like on our old RT instance:
previous RT install (3.6.4):
mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS
NOT NULL AND main.Content != ‘’) AND (main.Parent = ‘1208717’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;
±—±------------±------±-----±--------------±-------------±--------±------±-----±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
±—±------------±------±-----±--------------±-------------±--------±------±-----±----------------------------+
| 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 |
const | 2 | Using where; Using filesort |
±—±------------±------±-----±--------------±-------------±--------±------±-----±----------------------------+
1 row in set (0.00 sec)
mysql>
Now, here’s what (what should be the self-same queries…) is being produced
on our new RT host, running 3.8.2:
new RT host:
mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS
NOT NULL AND main.Content != ‘’) AND (main.Parent = ‘1208717’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;
±—±------------±------±------±--------------±--------±--------±-----±--------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
±—±------------±------±------±--------------±--------±--------±-----±--------±------------+
| 1 | SIMPLE | main | index | Attachments3 | PRIMARY | 4 |
NULL | 2199950 | Using where |
±—±------------±------±------±--------------±--------±--------±-----±--------±------------+
1 row in set (0.11 sec)
mysql>
Obviously, on the new host, it isn’t using the index on the Attachments
table - and that’s REALLY making things go slowly for some bits of RT.
Anybody got a clue how to fix this, or an idea of what we can do to coerce
it to use the proper index?
[We have quite a lot of tickets, and a fairly scary amount of spam has
leaked into our RT instance - I’m going to need to run the shredder on a
large number of deleted tickets, but the Attachments table is currently so
big that doing so is a bit daunting…]
This is on a new host, and we imported via a mysqldump and re-import, so the
data in the table should be defragmented… but the end result is clearly
problematic.
thanks in advance,
–elijah
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.
http://bugs.mysql.com/bug.php?id=37680 - here is bug report, you can
help debug issue.On Wed, May 27, 2009 at 1:32 AM, Ruslan Zakirov ruslan.zakirov@gmail.com wrote:
I believe you’re on mysql 5.1.x, it’s know issue with some 5.1.2x. I
can not say if it’s been fixed in recent release of 5.1. You should
file bug into mysql bug tracker.
On Wed, May 27, 2009 at 1:16 AM, Elijah Wright elw@brandorr.com wrote:
Folks,
We’re in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some
“interesting” issues with the Attachments table.
Here’s what queries against Attachments looked like on our old RT instance:
previous RT install (3.6.4):
mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS
NOT NULL AND main.Content != ‘’) AND (main.Parent = ‘1208717’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;
±—±------------±------±-----±--------------±-------------±--------±------±-----±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
±—±------------±------±-----±--------------±-------------±--------±------±-----±----------------------------+
| 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 |
const | 2 | Using where; Using filesort |
±—±------------±------±-----±--------------±-------------±--------±------±-----±----------------------------+
1 row in set (0.00 sec)
mysql>
Now, here’s what (what should be the self-same queries…) is being produced
on our new RT host, running 3.8.2:
new RT host:
mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS
NOT NULL AND main.Content != ‘’) AND (main.Parent = ‘1208717’) AND
(main.ContentType = ‘text/plain’) ORDER BY main.id ASC;
±—±------------±------±------±--------------±--------±--------±-----±--------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
±—±------------±------±------±--------------±--------±--------±-----±--------±------------+
| 1 | SIMPLE | main | index | Attachments3 | PRIMARY | 4 |
NULL | 2199950 | Using where |
±—±------------±------±------±--------------±--------±--------±-----±--------±------------+
1 row in set (0.11 sec)
mysql>
Obviously, on the new host, it isn’t using the index on the Attachments
table - and that’s REALLY making things go slowly for some bits of RT.
Anybody got a clue how to fix this, or an idea of what we can do to coerce
it to use the proper index?
[We have quite a lot of tickets, and a fairly scary amount of spam has
leaked into our RT instance - I’m going to need to run the shredder on a
large number of deleted tickets, but the Attachments table is currently so
big that doing so is a bit daunting…]
This is on a new host, and we imported via a mysqldump and re-import, so the
data in the table should be defragmented… but the end result is clearly
problematic.
thanks in advance,
–elijah
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.
Best regards, Ruslan.
This is on a new host, and we imported via a mysqldump and re-import, so the
data in the table should be defragmented… but the end result is clearly
problematic.
Wait a minute. Did you install 3.8.2 and load a dump into it from the older
version? Oy vey. The schemas don’t really line up.
You’d need to install the old version of RT on the new host, and then go
through the upgrade process. Otherwise you’re in for a lot of manual
discovery & cajoling to get things to work properly.
– ============================
Tom Lahti
BIT Statement LLC
(425)251-0833 x 117
http://www.bitstatement.net/
– ============================