RT 3.6.4 poor query performance

Jesse_Vincent · March 18, 2008, 4:28pm

[Please keep ccing the list. Free support is a lot more valuable when
the community’s involved]

What version of mysql is this again?

My explain output looks like this:

You’ll note that it’s starting on the Groups table rather than the
Users table, which I suspect is a lot less expensive (and yes, we have
a smaller RT database than you)

I sort of wonder whether that “member1” key is causing your RT to make
a bad call about join ordering.On Mar 18, 2008, at 12:21 PM, Richard Ellis wrote:

Database changed
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_4 JOIN
→ Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
→ CachedGroupMembers CachedGroupMembers_2 ON (
→ CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups
Groups_3
→ ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
→ (Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type)
→ AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType =
‘User’)
→ AND (ACL_4.RightName = ‘OwnTicket’) AND (Groups_3.Domain =
→ ‘RT::Queue-Role’) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR
→ (ACL_4.ObjectType = ‘RT::System’)) ORDER BY main.Name ASC;
±—±------------±---------------------±-------
±----------------------------------±--------±--------
±---------------------------------±-----
±---------------------------------------------+
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows |
Extra |
±—±------------±---------------------±-------
±----------------------------------±--------±--------
±---------------------------------±-----
±---------------------------------------------+
| 1 | SIMPLE | main | range |
PRIMARY,Users3 | PRIMARY | 4 |
NULL | 1378 | Using where; Using
temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY
| PRIMARY | 4 | rt3.main.id | 1 |
Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
DisGrouMem,GrouMem,group1,member1 | member1 | 5 |
rt3.Principals_1.id | 1 | Using where;
Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 |
ACL1 | 54 | NULL | 296 | Using
where; Using index; Distinct |
| 1 | SIMPLE | Groups_3 | eq_ref |
PRIMARY,Groups1,Groups2 | PRIMARY | 4 |
rt3.CachedGroupMembers_2.GroupId | 1 | Using where;
Distinct |
±—±------------±---------------------±-------
±----------------------------------±--------±--------
±---------------------------------±-----
±---------------------------------------------+
5 rows in set (0.01 sec)

mysql>

Is this giving you a clue where the problem is? Didn’t think their
were 15 million rows of data, their are only 10,000 tickets in total.

Jesse Vincent wrote:

So, you have a query that ran for 400+ seconds an examined fifteen
million rows. That seems…wrong. What does this say?

EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4
JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups
Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
(Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type) AND (Principals_1.id != ‘1’) AND
(Principals_1.PrincipalType = ‘User’) AND (ACL_4.RightName =
‘OwnTicket’) AND (Groups_3.Domain = ‘RT::Queue-Role’) AND
((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType =
‘RT::System’)) ORDER BY main.Name ASC;

On Tue, Mar 18, 2008 at 04:10:19PM +0000, Richard Ellis wrote:

Hi Jesse,

The output of the slow query log for the last 23 hours:

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

use rt3;
SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’,
3600);

Time: 080317 12:17:03

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’,
3600);

Time: 080317 12:17:47

User@Host: rt_user[rt_user] @ localhost

Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’,
3600);

Time: 080317 13:07:11

User@Host: rt_user[rt_user] @ localhost

Query_time: 411 Lock_time: 0 Rows_sent: 0 Rows_examined:

15418603
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON
( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups
Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
(Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type) AND (Principals_1.id != ‘1’) AND
(Principals_1.PrincipalType = ‘User’) AND (ACL_4.RightName =
‘OwnTicket’) AND (Groups_3.Domain = ‘RT::Queue-Role’) AND
((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType =
‘RT::System’)) ORDER BY main.Name ASC;

Time: 080317 13:07:13

User@Host: rt_user[rt_user] @ localhost

Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551’,
3600);

User@Host: rt_user[rt_user] @ localhost

Query_time: 346 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551’,
3600);

Thanks

Richard

Jesse Vincent wrote:

Sounds like someone previously changed the config file without
restarting.

On Mon, Mar 17, 2008 at 05:22:52PM +0000, Richard Ellis wrote:

Looks like theres a problem with the logfile

080317 10:04:58 mysqld started
InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of
different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 16777216 bytes!
080317 10:04:59 [Note] /usr/local/mysql/bin/mysqld: ready for
connections.
Version: ‘5.0.51a-log’ socket: ‘/tmp/mysql.sock’ port: 3306
MySQL Community Server (GPL)
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ACL.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ACL.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attachments.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attachments.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attributes.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attributes.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFields.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFields.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Groups.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Groups.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Links.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Links.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ObjectCustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ObjectCustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Principals.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Principals.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Queues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Queues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripActions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripActions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripConditions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripConditions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Scrips.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Scrips.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Templates.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Templates.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Tickets.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Tickets.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Transactions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Transactions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:16:25 [Note] /usr/local/mysql/bin/mysqld: Normal
shutdown

080317 10:16:27 [Note] /usr/local/mysql/bin/mysqld: Shutdown
complete

080317 10:16:27 mysqld ended

Jesse Vincent wrote:

Note that mysqlcheck WILL report corruption if the database is
being accessed.

On Mar 17, 2008, at 1:13 PM, Richard Ellis wrote:

Restarted to apply that change and now it looks like the
database has gone bang.

gpsummit# /usr/local/mysql/bin/safe_mysqld &
[2] 6970
gpsummit# Starting mysqld daemon with databases from /usr/
local/mysql/data

gpsummit# mysqlcheck rt3
rt3.ACL
Error : Incorrect information in file: ‘./rt3/ACL.frm’
error : Corrupt
rt3.Attachments
Error : Incorrect information in file: ‘./rt3/
Attachments.frm’
error : Corrupt
rt3.Attributes
Error : Incorrect information in file: ‘./rt3/Attributes.frm’
error : Corrupt
rt3.CachedGroupMembers
Error : Can’t find file: ‘CachedGroupMembers’ (errno: 2)
error : Corrupt
rt3.CustomFieldValues
Error : Incorrect information in file: ‘./rt3/
CustomFieldValues.frm’
error : Corrupt
rt3.CustomFields
Error : Incorrect information in file: ‘./rt3/
CustomFields.frm’
error : Corrupt
rt3.GroupMembers
Error : Can’t find file: ‘GroupMembers’ (errno: 2)
error : Corrupt
rt3.Groups
Error : Incorrect information in file: ‘./rt3/Groups.frm’
error : Corrupt
rt3.Links
Error : Incorrect information in file: ‘./rt3/Links.frm’
error : Corrupt
rt3.ObjectCustomFieldValues
Error : Incorrect information in file: ‘./rt3/
ObjectCustomFieldValues.frm’
error : Corrupt
rt3.ObjectCustomFields
Error : Can’t find file: ‘ObjectCustomFields’ (errno: 2)
error : Corrupt
rt3.Principals
Error : Incorrect information in file: ‘./rt3/Principals.frm’
error : Corrupt
rt3.Queues
Error : Incorrect information in file: ‘./rt3/Queues.frm’
error : Corrupt
rt3.ScripActions
Error : Incorrect information in file: ‘./rt3/
ScripActions.frm’
error : Corrupt
rt3.ScripConditions
Error : Incorrect information in file: ‘./rt3/
ScripConditions.frm’
error : Corrupt
rt3.Scrips
Error : Incorrect information in file: ‘./rt3/Scrips.frm’
error : Corrupt
rt3.Templates
Error : Incorrect information in file: ‘./rt3/Templates.frm’
error : Corrupt
rt3.Tickets
Error : Incorrect information in file: ‘./rt3/Tickets.frm’
error : Corrupt
rt3.Transactions
Error : Incorrect information in file: ‘./rt3/
Transactions.frm’
error : Corrupt
rt3.Users
Error : Incorrect information in file: ‘./rt3/Users.frm’
error : Corrupt
rt3.sessions

I am so pooched now. No idea if this is recoverable or not.

Rik

Jesse Vincent wrote:

On Mar 17, 2008, at 12:26 PM, Richard Ellis wrote:

mysql> SELECT * from ACL, CachedGroupMembers, Groups where
ACL.RightName = ‘OwnTicket’ and ACL.PrincipalId = Groups.id
and Groups.id = CachedGroupMembers.GroupId;

Ok. Those results tell me there should be 290 names in your
“SelectOwner” drop down. (That’s a lot, but not enough that
you should see 400s perf

Do you have mysql logging slow queries? If not, can you?

–

Richard Ellis
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

PGP.sig (186 Bytes)

Richard_Ellis · March 18, 2008, 4:31pm

Hi Jesse,

We are using 5.0.51a at the moment, because 5.0.34 was reported as
having issues in several posts on the forum and an upgrade recommended.

Richard

Jesse Vincent wrote:

What version of mysql is this again?

My explain output looks like this:

±---------------------±-------±----------------------------±--------±--------±------------------------------±-----±----------------------------------------------------------+

| table | type | possible_keys |
key | key_len | ref | rows |
Extra |
±---------------------±-------±----------------------------±--------±--------±------------------------------±-----±----------------------------------------------------------+

| Groups_3 | ref | PRIMARY,Groups1,Groups2 |
Groups1 | 65 | const | 108 | Using
where; Using index; Using temporary; Using filesort |
| CachedGroupMembers_2 | ref | DisGrouMem,GrouMem,MemberId |
GrouMem | 5 | Groups_3.id | 1 | Using
where; Using index |
| Principals_1 | eq_ref | PRIMARY |
PRIMARY | 4 | CachedGroupMembers_2.MemberId | 1 | Using
where |
| ACL_4 | range | ACL1 |
ACL1 | 50 | NULL | 36 | Using
where; Using index |
| main | eq_ref | PRIMARY,Users3 |
PRIMARY | 4 | Principals_1.id | 1
| |
±---------------------±-------±----------------------------±--------±--------±------------------------------±-----±----------------------------------------------------------+

You’ll note that it’s starting on the Groups table rather than the
Users table, which I suspect is a lot less expensive (and yes, we have
a smaller RT database than you)

I sort of wonder whether that “member1” key is causing your RT to make
a bad call about join ordering.

Database changed
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_4 JOIN
→ Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
→ CachedGroupMembers CachedGroupMembers_2 ON (
→ CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups
Groups_3
→ ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
→ (Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type)
→ AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType =
‘User’)
→ AND (ACL_4.RightName = ‘OwnTicket’) AND (Groups_3.Domain =
→ ‘RT::Queue-Role’) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR
→ (ACL_4.ObjectType = ‘RT::System’)) ORDER BY main.Name ASC;
±—±------------±---------------------±-------±----------------------------------±--------±--------±---------------------------------±-----±---------------------------------------------+

| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows |
Extra |
±—±------------±---------------------±-------±----------------------------------±--------±--------±---------------------------------±-----±---------------------------------------------+

| 1 | SIMPLE | main | range |
PRIMARY,Users3 | PRIMARY | 4 |
NULL | 1378 | Using where; Using
temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY |
PRIMARY | 4 | rt3.main.id | 1 | Using
where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
DisGrouMem,GrouMem,group1,member1 | member1 | 5 |
rt3.Principals_1.id | 1 | Using where;
Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 |
ACL1 | 54 | NULL | 296 | Using
where; Using index; Distinct |
| 1 | SIMPLE | Groups_3 | eq_ref |
PRIMARY,Groups1,Groups2 | PRIMARY | 4 |
rt3.CachedGroupMembers_2.GroupId | 1 | Using where;
Distinct |
±—±------------±---------------------±-------±----------------------------------±--------±--------±---------------------------------±-----±---------------------------------------------+

5 rows in set (0.01 sec)

mysql>

Is this giving you a clue where the problem is? Didn’t think their
were 15 million rows of data, their are only 10,000 tickets in total.

Jesse Vincent wrote:

So, you have a query that ran for 400+ seconds an examined fifteen
million rows. That seems…wrong. What does this say?

EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4
JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups
Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
(Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type) AND (Principals_1.id != ‘1’) AND
(Principals_1.PrincipalType = ‘User’) AND (ACL_4.RightName =
‘OwnTicket’) AND (Groups_3.Domain = ‘RT::Queue-Role’) AND
((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType =
‘RT::System’)) ORDER BY main.Name ASC;

Hi Jesse,

The output of the slow query log for the last 23 hours:

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

use rt3;
SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’,
3600);

Time: 080317 12:17:03

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’,
3600);

Time: 080317 12:17:47

User@Host: rt_user[rt_user] @ localhost

Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’,
3600);

Time: 080317 13:07:11

User@Host: rt_user[rt_user] @ localhost

Query_time: 411 Lock_time: 0 Rows_sent: 0 Rows_examined: 15418603

SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups
Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
(Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type) AND (Principals_1.id != ‘1’) AND
(Principals_1.PrincipalType = ‘User’) AND (ACL_4.RightName =
‘OwnTicket’) AND (Groups_3.Domain = ‘RT::Queue-Role’) AND
((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType =
‘RT::System’)) ORDER BY main.Name ASC;

Time: 080317 13:07:13

User@Host: rt_user[rt_user] @ localhost

Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551’,
3600);

User@Host: rt_user[rt_user] @ localhost

Query_time: 346 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551’,
3600);

Thanks

Richard

Jesse Vincent wrote:

Sounds like someone previously changed the config file without
restarting.

Looks like theres a problem with the logfile

080317 10:04:58 mysqld started
InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of
different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 16777216 bytes!
080317 10:04:59 [Note] /usr/local/mysql/bin/mysqld: ready for
connections.
Version: ‘5.0.51a-log’ socket: ‘/tmp/mysql.sock’ port: 3306
MySQL Community Server (GPL)
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ACL.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ACL.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attachments.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attachments.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attributes.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Attributes.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFields.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/CustomFields.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Groups.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Groups.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Links.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Links.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ObjectCustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ObjectCustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Principals.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Principals.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Queues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Queues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripActions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripActions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripConditions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/ScripConditions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Scrips.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Scrips.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Templates.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Templates.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Tickets.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Tickets.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Transactions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Transactions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect
information in file: ‘./rt3/Users.frm’
080317 10:16:25 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

080317 10:16:27 [Note] /usr/local/mysql/bin/mysqld: Shutdown
complete

080317 10:16:27 mysqld ended

Jesse Vincent wrote:

Note that mysqlcheck WILL report corruption if the database is
being accessed.

Restarted to apply that change and now it looks like the
database has gone bang.

gpsummit# /usr/local/mysql/bin/safe_mysqld &
[2] 6970
gpsummit# Starting mysqld daemon with databases from
/usr/local/mysql/data

gpsummit# mysqlcheck rt3
rt3.ACL
Error : Incorrect information in file: ‘./rt3/ACL.frm’
error : Corrupt
rt3.Attachments
Error : Incorrect information in file: ‘./rt3/Attachments.frm’
error : Corrupt
rt3.Attributes
Error : Incorrect information in file: ‘./rt3/Attributes.frm’
error : Corrupt
rt3.CachedGroupMembers
Error : Can’t find file: ‘CachedGroupMembers’ (errno: 2)
error : Corrupt
rt3.CustomFieldValues
Error : Incorrect information in file:
‘./rt3/CustomFieldValues.frm’
error : Corrupt
rt3.CustomFields
Error : Incorrect information in file: ‘./rt3/CustomFields.frm’
error : Corrupt
rt3.GroupMembers
Error : Can’t find file: ‘GroupMembers’ (errno: 2)
error : Corrupt
rt3.Groups
Error : Incorrect information in file: ‘./rt3/Groups.frm’
error : Corrupt
rt3.Links
Error : Incorrect information in file: ‘./rt3/Links.frm’
error : Corrupt
rt3.ObjectCustomFieldValues
Error : Incorrect information in file:
‘./rt3/ObjectCustomFieldValues.frm’
error : Corrupt
rt3.ObjectCustomFields
Error : Can’t find file: ‘ObjectCustomFields’ (errno: 2)
error : Corrupt
rt3.Principals
Error : Incorrect information in file: ‘./rt3/Principals.frm’
error : Corrupt
rt3.Queues
Error : Incorrect information in file: ‘./rt3/Queues.frm’
error : Corrupt
rt3.ScripActions
Error : Incorrect information in file: ‘./rt3/ScripActions.frm’
error : Corrupt
rt3.ScripConditions
Error : Incorrect information in file:
‘./rt3/ScripConditions.frm’
error : Corrupt
rt3.Scrips
Error : Incorrect information in file: ‘./rt3/Scrips.frm’
error : Corrupt
rt3.Templates
Error : Incorrect information in file: ‘./rt3/Templates.frm’
error : Corrupt
rt3.Tickets
Error : Incorrect information in file: ‘./rt3/Tickets.frm’
error : Corrupt
rt3.Transactions
Error : Incorrect information in file: ‘./rt3/Transactions.frm’
error : Corrupt
rt3.Users
Error : Incorrect information in file: ‘./rt3/Users.frm’
error : Corrupt
rt3.sessions

I am so pooched now. No idea if this is recoverable or not.

Rik

Jesse Vincent wrote:

mysql> SELECT * from ACL, CachedGroupMembers, Groups where
ACL.RightName = ‘OwnTicket’ and ACL.PrincipalId = Groups.id
and Groups.id = CachedGroupMembers.GroupId;

Ok. Those results tell me there should be 290 names in your
“SelectOwner” drop down. (That’s a lot, but not enough that
you should see 400s perf

Do you have mysql logging slow queries? If not, can you?

–

Richard Ellis
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

Ruslan_Zakirov1 · March 18, 2008, 9:02pm

Hello, Richard.

Jesse asked me to look into this problem. Could you please run SQL
commands from the attachment. It’s a list of COUNT and EXPLAIN
queries. I hope that will help us identify problems.On Tue, Mar 18, 2008 at 7:31 PM, Richard Ellis Richard.Ellis@sun.com wrote:

Hi Jesse,

We are using 5.0.51a at the moment, because 5.0.34 was reported as having
issues in several posts on the forum and an upgrade recommended.

Richard

Jesse Vincent wrote:

What version of mysql is this again?

My explain output looks like this:

±---------------------±-------±----------------------------±--------±--------±------------------------------±-----±----------------------------------------------------------+
| table | type | possible_keys | key |
key_len | ref | rows | Extra
|

±---------------------±-------±----------------------------±--------±--------±------------------------------±-----±----------------------------------------------------------+
| Groups_3 | ref | PRIMARY,Groups1,Groups2 | Groups1 |
65 | const | 108 | Using where; Using index; Using
temporary; Using filesort |
| CachedGroupMembers_2 | ref | DisGrouMem,GrouMem,MemberId | GrouMem |
5 | Groups_3.id | 1 | Using where; Using index
|
| Principals_1 | eq_ref | PRIMARY | PRIMARY |
4 | CachedGroupMembers_2.MemberId | 1 | Using where
|
| ACL_4 | range | ACL1 | ACL1 |
50 | NULL | 36 | Using where; Using index
|
| main | eq_ref | PRIMARY,Users3 | PRIMARY |
4 | Principals_1.id | 1 |
|

±---------------------±-------±----------------------------±--------±--------±------------------------------±-----±----------------------------------------------------------+

You’ll note that it’s starting on the Groups table rather than the Users
table, which I suspect is a lot less expensive (and yes, we have a smaller
RT database than you)

I sort of wonder whether that “member1” key is causing your RT to make a
bad call about join ordering.

On Mar 18, 2008, at 12:21 PM, Richard Ellis wrote:

Database changed
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4
JOIN
→ Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
→ CachedGroupMembers CachedGroupMembers_2 ON (
→ CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3
→ ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
→ (Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type)
→ AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’)
→ AND (ACL_4.RightName = ‘OwnTicket’) AND (Groups_3.Domain =
→ ‘RT::Queue-Role’) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR
→ (ACL_4.ObjectType = ‘RT::System’)) ORDER BY main.Name ASC;

±—±------------±---------------------±-------±----------------------------------±--------±--------±---------------------------------±-----±---------------------------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra
|

±—±------------±---------------------±-------±----------------------------------±--------±--------±---------------------------------±-----±---------------------------------------------+
| 1 | SIMPLE | main | range | PRIMARY,Users3
| PRIMARY | 4 | NULL | 1378 | Using where;
Using temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY |
PRIMARY | 4 | rt3.main.id | 1 | Using where;
Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
DisGrouMem,GrouMem,group1,member1 | member1 | 5 | rt3.Principals_1.id
| 1 | Using where; Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1
| 54 | NULL | 296 | Using where; Using
index; Distinct |
| 1 | SIMPLE | Groups_3 | eq_ref |
PRIMARY,Groups1,Groups2 | PRIMARY | 4 |
rt3.CachedGroupMembers_2.GroupId | 1 | Using where; Distinct
|

±—±------------±---------------------±-------±----------------------------------±--------±--------±---------------------------------±-----±---------------------------------------------+
5 rows in set (0.01 sec)

mysql>

Is this giving you a clue where the problem is? Didn’t think their were 15
million rows of data, their are only 10,000 tickets in total.

Jesse Vincent wrote:

So, you have a query that ran for 400+ seconds an examined fifteen
million rows. That seems…wrong. What does this say?

EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON
( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled
= ‘0’) AND (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id !=
‘1’) AND (Principals_1.PrincipalType = ‘User’) AND (ACL_4.RightName =
‘OwnTicket’) AND (Groups_3.Domain = ‘RT::Queue-Role’) AND
((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType = ‘RT::System’))
ORDER BY main.Name ASC;

On Tue, Mar 18, 2008 at 04:10:19PM +0000, Richard Ellis wrote:

Hi Jesse,

The output of the slow query log for the last 23 hours:

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

use rt3;
SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’, 3600);

Time: 080317 12:17:03

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’, 3600);

Time: 080317 12:17:47

User@Host: rt_user[rt_user] @ localhost

Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-ce4e206474839cb7dd09a5216f86ce9e’, 3600);

Time: 080317 13:07:11

User@Host: rt_user[rt_user] @ localhost

Query_time: 411 Lock_time: 0 Rows_sent: 0 Rows_examined: 15418603

SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals
Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id )
JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId )
WHERE (Principals_1.Disabled = ‘0’) AND (ACL_4.PrincipalType =
Groups_3.Type) AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType
= ‘User’) AND (ACL_4.RightName = ‘OwnTicket’) AND (Groups_3.Domain =
‘RT::Queue-Role’) AND ((ACL_4.ObjectType = ‘RT::Queue’) OR (ACL_4.ObjectType
= ‘RT::System’)) ORDER BY main.Name ASC;

Time: 080317 13:07:13

User@Host: rt_user[rt_user] @ localhost

Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551’, 3600);

User@Host: rt_user[rt_user] @ localhost

Query_time: 346 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT GET_LOCK(‘Apache-Session-9f2f1478e69cd6a6381f8ef9b98f7551’, 3600);

Thanks

Richard

Jesse Vincent wrote:

Sounds like someone previously changed the config file without
restarting.

On Mon, Mar 17, 2008 at 05:22:52PM +0000, Richard Ellis wrote:

Looks like theres a problem with the logfile

080317 10:04:58 mysqld started
InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of different
size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 16777216 bytes!
080317 10:04:59 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.0.51a-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL
Community Server (GPL)
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:11 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:05:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:06:36 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:47 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:49 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:50 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:51 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:07:59 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ACL.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ACL.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Attachments.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Attachments.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Attributes.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Attributes.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/CustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/CustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/CustomFields.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/CustomFields.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Groups.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Groups.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Links.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Links.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ObjectCustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ObjectCustomFieldValues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Principals.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Principals.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Queues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Queues.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ScripActions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ScripActions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ScripConditions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/ScripConditions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Scrips.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Scrips.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Templates.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Templates.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Tickets.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Tickets.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Transactions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Transactions.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:09:06 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information
in file: ‘./rt3/Users.frm’
080317 10:16:25 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

080317 10:16:27 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

080317 10:16:27 mysqld ended

Jesse Vincent wrote:

Note that mysqlcheck WILL report corruption if the database is being
accessed.

On Mar 17, 2008, at 1:13 PM, Richard Ellis wrote:

Restarted to apply that change and now it looks like the database has gone
bang.

gpsummit# /usr/local/mysql/bin/safe_mysqld &
[2] 6970
gpsummit# Starting mysqld daemon with databases from /usr/local/mysql/data

gpsummit# mysqlcheck rt3
rt3.ACL
Error : Incorrect information in file: ‘./rt3/ACL.frm’
error : Corrupt
rt3.Attachments
Error : Incorrect information in file: ‘./rt3/Attachments.frm’
error : Corrupt
rt3.Attributes
Error : Incorrect information in file: ‘./rt3/Attributes.frm’
error : Corrupt
rt3.CachedGroupMembers
Error : Can’t find file: ‘CachedGroupMembers’ (errno: 2)
error : Corrupt
rt3.CustomFieldValues
Error : Incorrect information in file: ‘./rt3/CustomFieldValues.frm’
error : Corrupt
rt3.CustomFields
Error : Incorrect information in file: ‘./rt3/CustomFields.frm’
error : Corrupt
rt3.GroupMembers
Error : Can’t find file: ‘GroupMembers’ (errno: 2)
error : Corrupt
rt3.Groups
Error : Incorrect information in file: ‘./rt3/Groups.frm’
error : Corrupt
rt3.Links
Error : Incorrect information in file: ‘./rt3/Links.frm’
error : Corrupt
rt3.ObjectCustomFieldValues
Error : Incorrect information in file:
‘./rt3/ObjectCustomFieldValues.frm’
error : Corrupt
rt3.ObjectCustomFields
Error : Can’t find file: ‘ObjectCustomFields’ (errno: 2)
error : Corrupt
rt3.Principals
Error : Incorrect information in file: ‘./rt3/Principals.frm’
error : Corrupt
rt3.Queues
Error : Incorrect information in file: ‘./rt3/Queues.frm’
error : Corrupt
rt3.ScripActions
Error : Incorrect information in file: ‘./rt3/ScripActions.frm’
error : Corrupt
rt3.ScripConditions
Error : Incorrect information in file: ‘./rt3/ScripConditions.frm’
error : Corrupt
rt3.Scrips
Error : Incorrect information in file: ‘./rt3/Scrips.frm’
error : Corrupt
rt3.Templates
Error : Incorrect information in file: ‘./rt3/Templates.frm’
error : Corrupt
rt3.Tickets
Error : Incorrect information in file: ‘./rt3/Tickets.frm’
error : Corrupt
rt3.Transactions
Error : Incorrect information in file: ‘./rt3/Transactions.frm’
error : Corrupt
rt3.Users
Error : Incorrect information in file: ‘./rt3/Users.frm’
error : Corrupt
rt3.sessions

I am so pooched now. No idea if this is recoverable or not.

Rik

Jesse Vincent wrote:

On Mar 17, 2008, at 12:26 PM, Richard Ellis wrote:

mysql> SELECT * from ACL, CachedGroupMembers, Groups where ACL.RightName =
‘OwnTicket’ and ACL.PrincipalId = Groups.id and Groups.id =
CachedGroupMembers.GroupId;

Ok. Those results tell me there should be 290 names in your “SelectOwner”
drop down. (That’s a lot, but not enough that you should see 400s perf

Do you have mysql logging slow queries? If not, can you?

–

Richard Ellis
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–
Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

–

Richard Ellis
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM

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.

search_possible_owners.mysql.sql (1.53 KB)

Richard_Ellis · March 19, 2008, 8:49am

Hi Ruslan,

Really appreciate the help on this. I’d love to find out why we are
seeing such odd results:

298 ticket owners when their are only 88 active users
1.5 million rows of data when we only have 9983 ticks as of this morning.

Really odd

Thanks

Richard

Ruslan Zakirov wrote:

Search results.sql (5.01 KB)

Mathew_Snyder3 · March 19, 2008, 11:39am

The vast amount of data could be due to spam. I set up a script which
regularly (daily) searches for spam and the users automatically created
by it. It then runs Shredder on the list I built. This is made easier
by creating a spam only queue into which all incoming spam is placed.

Additionally, we’ve instituted a method of preventing non-authorized
users from creating tickets. We have a script which pulls the email
addresses of our customers employees from our customer database. It
then builds a procmail script.

If the email address is listed in the procmail script the incoming email
is passed to rtx-mailgate. If the email address doesn’t exist in the
list they get a bounce back. This eliminated about 75% of our spam problem.

The other 25% was from our other public facing queue to which security
and abuse issues are reported. These emails are placed in our _SPAM
queue on which the above clean-up script runs. It doesn’t always get
everything because it isn’t configured to handle Bcc and Cc addresses
but the requestor address is always groomed. The emails in that queue
are marked as deleted and Shredder then grooms out all deleted emails.On another note, we just installed a Barracuda system which has been a blessing. The clean-up script went from a daily scrubbing of between 150-250 emails and users to between 0 and 20. Mathew Richard Ellis wrote:

Hi Ruslan,

Really appreciate the help on this. I’d love to find out why we are
seeing such odd results:

298 ticket owners when their are only 88 active users
1.5 million rows of data when we only have 9983 ticks as of this morning.

Really odd

Thanks

Richard

Keep up with me and what I’m up to: http://theillien.blogspot.com

Ruslan_Zakirov1 · March 19, 2008, 3:04pm

Ok, I have an idea how to fix that problem

Here is new file for testing that will give me more info to find the
best way to fixing this. We’re really close.

You can run it using:
mysql -t -u root -ppassword rt3 <…/search_possible_owners.mysql.sql >test.res

As a first step to fix it you can create the following index on Groups table:
CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);

Please, run commands from the attachment twice before indexing and after.

Thank you for the feedback.On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis Richard.Ellis@sun.com wrote:

Hi Ruslan,

Really appreciate the help on this. I’d love to find out why we are seeing
such odd results:

298 ticket owners when their are only 88 active users
1.5 million rows of data when we only have 9983 ticks as of this morning.

Really odd

Thanks

Richard

[snip]

Best regards, Ruslan.

search_possible_owners.mysql.sql (2.64 KB)

Richard_Ellis · March 19, 2008, 4:22pm

Hi Ruslan,

here’s the two sets of results.

Thanks

Richard

Ruslan Zakirov wrote:

test.res (7.88 KB)

testafter.res (8.44 KB)

Jesse_Vincent · March 19, 2008, 4:28pm

Hi Ruslan,

here’s the two sets of results.

FWIW, from your response to ruslan, it does look like your hand-added
“group1” index was messing up the query planner. It’s on GroupId, while
we already had an index on GroupId, MemberId.

Richard_Ellis · March 19, 2008, 4:38pm

Hi Jesse,

Thanks. To the best of my knowledge nobody has added any indexes to the
database on anything except what RT patches apply on each upgrade. This
DB was originally 3.0 and has been upgraded more times than I want to
think about over the years to 3.6.6 now

Richard

Jesse Vincent wrote:

Jesse_Vincent · March 19, 2008, 4:39pm

Hi Jesse,

Thanks. To the best of my knowledge nobody has added any indexes to the
database on anything except what RT patches apply on each upgrade. This
DB was originally 3.0 and has been upgraded more times than I want to
think about over the years to 3.6.6 now

That index doesn’t follow RT’s standard index naming/capitalization scheme. Someone may have gone behind your back

Ruslan_Zakirov1 · March 19, 2008, 4:42pm

Jesse, I know that they both have index on CachedGroupMembers table
that starts from ‘MemberId’ column. And it does mess up optimizer and
doesn’t matter if it’s one column or multiple like in (MemberId,
GroupId, Disabled) index (Jeff created such thing). We really need
such index in the core on CGM table, otherwise people have problems
with searches by watchers (like in “Requestor is XXX” search or “More
about XXX” box). It’s very sad that mysql can not deal with that. Fix
I’ve implemented in 3.6.6 helps people on setups with few ACL records
and few queues, but not in these two cases.On Wed, Mar 19, 2008 at 7:28 PM, Jesse Vincent jesse@bestpractical.com wrote:

On Wed, Mar 19, 2008 at 04:22:46PM +0000, Richard Ellis wrote:

Hi Ruslan,

here’s the two sets of results.

FWIW, from your response to ruslan, it does look like your hand-added
“group1” index was messing up the query planner. It’s on GroupId, while
we already had an index on GroupId, MemberId.

Best regards, Ruslan.

Jesse_Vincent · March 19, 2008, 4:54pm

Jesse, I know that they both have index on CachedGroupMembers table
that starts from ‘MemberId’ column. And it does mess up optimizer and
doesn’t matter if it’s one column or multiple like in (MemberId,
GroupId, Disabled) index (Jeff created such thing). We really need
such index in the core on CGM table, otherwise people have problems
with searches by watchers (like in “Requestor is XXX” search or “More
about XXX” box). It’s very sad that mysql can not deal with that. Fix
I’ve implemented in 3.6.6 helps people on setups with few ACL records
and few queues, but not in these two cases.

Got it.

Ruslan_Zakirov1 · March 19, 2008, 5:02pm

Hey, Rechard, the latest results suggest me that we’ve saddled this
beast at least that what explain says and I hope it’s correct.

You can check that query again and it should be fast. Wanna try?

You can use SELECT SQL_NO_CACHE … to make sure it’s reproducible and
is not cache hit.On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis Richard.Ellis@sun.com wrote:

Hi Ruslan,

here’s the two sets of results.

Thanks

Richard

Best regards, Ruslan.

Ruslan_Zakirov1 · March 19, 2008, 5:25pm

Jeff, always Cc the list.

Version of your mysql server?

As far as I can see you suffer from mysql bug, output from your server
is equal in both cases what is really wrong and mysql must use new
index in those test queries I sent to the list.

There are several options:

Delete any indexes on CachedGroupMembers table which starts from
MemberId column, but that will slowdown other queries and may be
terribly, depends on proprotions of your DB.
Upgrade to mysql 5.0.45 or greater and create index I suggested in
this thread earlier.
I have another idea how we can improve that in the code, but that
needs more investigation with a lot of users’ feedback and a lot of
mine and users’ time.

As long as MySQL 4.x has ended its life time and 5.0.x is stable
version then I think it’s fair enough to recommend recent versions
instead of continuose refactoring of the code to make all those broken
mysqls happy.On Wed, Mar 19, 2008 at 6:22 PM, Jeff Voskamp javoskam@uwaterloo.ca wrote:

Ruslan Zakirov wrote:

Ok, I have an idea how to fix that problem

Here is new file for testing that will give me more info to find the
best way to fixing this. We’re really close.

You can run it using:
mysql -t -u root -ppassword rt3 <…/search_possible_owners.mysql.sql >test.res

As a first step to fix it you can create the following index on Groups table:
CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);

Please, run commands from the attachment twice before indexing and after.

Thank you for the feedback.

On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis Richard.Ellis@sun.com wrote:

Hi Ruslan,

Really appreciate the help on this. I’d love to find out why we are seeing
such odd results:

298 ticket owners when their are only 88 active users
1.5 million rows of data when we only have 9983 ticks as of this morning.

Really odd

Thanks

Richard

Since we were also having problems here’s our output.
spw.out is before. spw.out2 is after.

Jeff Voskamp
University of Waterloo

±—±------------±---------------------±-------±-------------------------------------------------------------±---------±--------±--------------------------------------±-----±---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------------------±-------±-------------------------------------------------------------±---------±--------±--------------------------------------±-----±---------------------------------------------+
| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 4138 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1a | 67 | const | 630 | Using where; Using index; Distinct |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY,Principals4 | PRIMARY | 4 | rt3_inst.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,MyCGM1 | MyCGM1 | 10 | rt3_inst.main.id,rt3_inst.Groups_3.id | 1 | Using where; Using index; Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index; Distinct |
±—±------------±---------------------±-------±-------------------------------------------------------------±---------±--------±--------------------------------------±-----±---------------------------------------------+
±--------------±----------+
| PrincipalType | COUNT(id) |
±--------------±----------+
| Cc | 1 |
| Group | 372 |
±--------------±----------+
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
±-------------------+
| COUNT(Groups_3.id) |
±-------------------+
| 72 |
±-------------------+
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
| 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 | Using where; Using index |
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
| 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 | Using where; Using index |
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
| 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 | Using where; Using index |
±—±------------±---------±------±------------------------------------------±--------±--------±------±-----±-------------------------+
±-------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±-------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| CachedGroupMembers | 0 | PRIMARY | 1 | id | A | 897772 | NULL | NULL | | BTREE | NULL |
| CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled | A | 897772 | NULL | NULL | | BTREE | NULL |
| CachedGroupMembers | 1 | MyCGM1 | 1 | MemberId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | MyCGM1 | 2 | GroupId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | MyCGM1 | 3 | Disabled | A | 897772 | NULL | NULL | | BTREE | NULL |
±-------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+

±—±------------±---------------------±-------±-------------------------------------------------------------------------±---------±--------±--------------------------------------±-----±---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------------------±-------±-------------------------------------------------------------------------±---------±--------±--------------------------------------±-----±---------------------------------------------+
| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 4138 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1a | 67 | const | 630 | Using where; Using index; Distinct |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY,Principals4 | PRIMARY | 4 | rt3_inst.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,MyCGM1 | MyCGM1 | 10 | rt3_inst.main.id,rt3_inst.Groups_3.id | 1 | Using where; Using index; Distinct |
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index; Distinct |
±—±------------±---------------------±-------±-------------------------------------------------------------------------±---------±--------±--------------------------------------±-----±---------------------------------------------+
±--------------±----------+
| PrincipalType | COUNT(id) |
±--------------±----------+
| Cc | 1 |
| Group | 372 |
±--------------±----------+
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
±-------------------+
| COUNT(Groups_3.id) |
±-------------------+
| 72 |
±-------------------+
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
| 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | const | 630 | Using where; Using index |
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
| 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | const | 630 | Using where; Using index |
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
±—±------------±------±------±--------------±-----±--------±-----±-----±-------------------------+
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
| 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | const | 630 | Using where; Using index |
±—±------------±---------±------±------------------------------------------------------±--------±--------±------±-----±-------------------------+
±-------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±-------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| CachedGroupMembers | 0 | PRIMARY | 1 | id | A | 901467 | NULL | NULL | | BTREE | NULL |
| CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled | A | 901467 | NULL | NULL | | BTREE | NULL |
| CachedGroupMembers | 1 | MyCGM1 | 1 | MemberId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | MyCGM1 | 2 | GroupId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
| CachedGroupMembers | 1 | MyCGM1 | 3 | Disabled | A | 901467 | NULL | NULL | | BTREE | NULL |
±-------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+

Best regards, Ruslan.

Richard_Ellis · March 19, 2008, 5:35pm

Hi Ruslan,

You are a genius. Response time for the Query Builder is now back to 4
seconds which is good enough for me :0.

Thanks to all your team for all the efforts to work out what was wrong.

Thanks

Richard

Ruslan Zakirov wrote:

Jeff_Voskamp · March 19, 2008, 5:50pm

Ruslan Zakirov wrote:

Jeff, always Cc the list.

Version of your mysql server?

As far as I can see you suffer from mysql bug, output from your server
is equal in both cases what is really wrong and mysql must use new
index in those test queries I sent to the list.

There are several options:

Delete any indexes on CachedGroupMembers table which starts from
MemberId column, but that will slowdown other queries and may be
terribly, depends on proprotions of your DB.

Upgrade to mysql 5.0.45 or greater and create index I suggested in
this thread earlier.

I have another idea how we can improve that in the code, but that
needs more investigation with a lot of users’ feedback and a lot of
mine and users’ time.

As long as MySQL 4.x has ended its life time and 5.0.x is stable
version then I think it’s fair enough to recommend recent versions
instead of continuose refactoring of the code to make all those broken
mysqls happy.

I’ll try to remember to “reply all” from here on in.

We’re on Mysql-5.0.22 as packaged by RedHat for Enterprise Linux 5.1.

Dropping indexes for now. Can re-instate later. Then I can also drop my
coding hacks.

Will look into getting a shiny new MySQL.

jeff Voskamp

Jeff_Voskamp · March 19, 2008, 5:02pm

Jesse Vincent wrote:

Jesse, I know that they both have index on CachedGroupMembers table
that starts from ‘MemberId’ column. And it does mess up optimizer and
doesn’t matter if it’s one column or multiple like in (MemberId,
GroupId, Disabled) index (Jeff created such thing). We really need
such index in the core on CGM table, otherwise people have problems
with searches by watchers (like in “Requestor is XXX” search or “More
about XXX” box). It’s very sad that mysql can not deal with that. Fix
I’ve implemented in 3.6.6 helps people on setups with few ACL records
and few queues, but not in these two cases.

Got it
It’s a rename of one of the suggested indices in
RTx::Shredder - Cleanup RT database - metacpan.org
(see the Notes section).

CREATE INDEX SHREDDER_CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);

Jeff

Ruslan_Zakirov1 · March 20, 2008, 4:40pm

4 seconds is still slow, but better than 100-400.

About your indexes. You can and I really suggest to delete the
following indexes on CGM table:

DROP INDEX GrouMem ON CachedGroupMembers;
DROP INDEX group1 ON CachedGroupMembers;
DROP INDEX member1 ON CachedGroupMembers;

And instead create indexes:
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, ImmediateParentId);

Both will be part of 3.8’s schema update.On Wed, Mar 19, 2008 at 8:35 PM, Richard Ellis Richard.Ellis@sun.com wrote:

Hi Ruslan,

You are a genius. Response time for the Query Builder is now back to 4
seconds which is good enough for me :0.

Thanks to all your team for all the efforts to work out what was wrong.

Thanks

Richard

Ruslan Zakirov wrote:

Hey, Rechard, the latest results suggest me that we’ve saddled this
beast at least that what explain says and I hope it’s correct.

You can check that query again and it should be fast. Wanna try?

You can use SELECT SQL_NO_CACHE … to make sure it’s reproducible and
is not cache hit.

On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis Richard.Ellis@sun.com wrote:

Hi Ruslan,

here’s the two sets of results.

Thanks

Richard

Best regards, Ruslan.

Thomas_Hecker · March 25, 2008, 7:21am

Hi Everybody,

well fist of all - thanks for this topic. You all made my day, because for
the moment my performance problem seems to be fixed. I had the same problem
with the owner-dropdown getting bigger and bogger ower time.

In my case the problem was the inside the main queue we use for first level
support the unprivileged user group had the “own ticket” permisson right
(don’t ask me why). So my mistake was, that when i was looking for wrong
userpermissions i only looked at the users and the groups, not the queues.

So mayby some of you won’t need to do some quick hackings, simply check the
permission of your queues.

Greetings
ThomasAm 17.03.2008 12:19 Uhr schrieb “Mathew” unter mathew.snyder@gmail.com:

You shouldn’t have had to write a patch to fix the immense user drop
down. I’ve created queues with matching groups and assigned the own
ticket right to only the groups that correspond to each queue.

The Everyone group only has CreateTicket on two public facing queues
(all others are available for correspondence but not ticket creation),
Priveleged Users has all the major rights which all users require across
all queues and Unprivileged Users has only rights which customers would
need to interact with a ticket.

This has provide more than enough lock down to keep users created by
spam out of our drop down.

Mathew

Ham MI-ID, Torsten Brumm wrote:

Hi Mathew, Richard,
I tried also this weekend to upgrade to 3.6.6 and gave it up yesterday
evening (rolled back to 3.6.5).

To your Problem:

If you open the Search Builder menu, it takes a long time to build the
page.?!? Have a loko into the owner dropdown menu. Did you find there more
people as expacted? In my case i find a lot of people there, more than have
the rights to own tickets in the queues.

I have NOT SET the OwnTickets right globally !!! And now it will be very
strange at my Live systems (and test box too).

Inside the owner dropdown, i find also NOT PRIVILEGDED USERS!!!

OK, what i have tested:

Logged in as normal user with rights to 3 Queues.

This queues have per queue 5 people with the right to own a ticket here. (so
i looked for 15 people inside the owner dropdown) but i got a list of round
about several thousands!!!

OK to fix it fast:

Here is my diff to the /share/html/Search/Elements/PickBasics

root@bruchtal-www3:/opt/rt3/local/html/Search/Elements# diff
/opt/rt3/local/html/Search/Elements/PickBasics
/opt/rt3/share/html/Search/Elements/PickBasics
111,112c113
<
< %#<& /Elements/SelectOwner, Name => “ValueOfActor”, ValueAttribute =>
‘Name’ &>

<& /Elements/SelectOwner, Name => “ValueOfActor”, ValueAttribute => ‘Name’
&>

OK, it’s replacing the SelectOwner Dropdown, which is not working well here
with a noremal input box.

This speeds up the Searchbuilder a lot!

Btw. This “Problem” with the Owner Dropdown inside the searchbuilder we have
since RT 3.4.x and it is not working well since this time.

Hope this helps.
Torsten

Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.),
Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark
Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen,
Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich
haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg
Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne

-----Ursprüngliche Nachricht-----
Von: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Mathew
Gesendet: Montag, 17. März 2008 11:06
An: Richard Ellis
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] RT 3.6.4 poor query performance

Well, in that case, I recommend witchcraft.

Mathew

Richard Ellis wrote:

Hi All,

We have upgraded to 3.6.6 over the weekend and also run some
optimisation against the database but performance is still very poor.

I have looked at RTx::RightMatrix and Everyone definately does not
have OwnTickets rights unless that is lying to me, which I doubt.
I’ve used the tuning-primer.sh script to do some tuning and
performance has improved somewhat, as query builder now only take 300
seconds average to load instead of 400, but it is still unusable which
is frustrating the users. It’s going to take another 36 hours before I
can check how the optimisation is going.

I couldn’t get the MySQLTuner to run, but I’ll take a look at the perl
this week if I get a chance.

If anyone else has any ideas at all, I’m open to suggestions,
including witchcraft

Richard

–
Keep up with me and what I’m up to: http://theillien.blogspot.com

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

Richard_Ellis · March 25, 2008, 9:14am

Hi Ruslan,

I have changed those indexes this morning and will monitor the results
today. I created the second new index as CGM2 to prevent the system
complaining about duplicate keys.

Thanks

Richard

Ruslan Zakirov wrote:

4 seconds is still slow, but better than 100-400.

About your indexes. You can and I really suggest to delete the
following indexes on CGM table:

DROP INDEX GrouMem ON CachedGroupMembers;

DROP INDEX group1 ON CachedGroupMembers;

DROP INDEX member1 ON CachedGroupMembers;

And instead create indexes:
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, ImmediateParentId);

Both will be part of 3.8’s schema update.

Hi Ruslan,

You are a genius. Response time for the Query Builder is now back to 4
seconds which is good enough for me :0.

Thanks to all your team for all the efforts to work out what was wrong.

Thanks

Richard

Ruslan Zakirov wrote:

Hey, Rechard, the latest results suggest me that we’ve saddled this
beast at least that what explain says and I hope it’s correct.

You can check that query again and it should be fast. Wanna try?

You can use SELECT SQL_NO_CACHE … to make sure it’s reproducible and
is not cache hit.

Hi Ruslan,

here’s the two sets of results.

Thanks

Richard

Sun.com http://www.sun.com * Richard Ellis *
Technical Developer, .Sun eBusiness

Sun Microsystems, Inc.
Phone x(70) 24727/+44-1252-424 727
Fax +44 1252 420410
Email richard.ellis@Sun.COM
sun.com

RT 3.6.4 poor query performance

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 12:17:03

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 12:17:47

User@Host: rt_user[rt_user] @ localhost

Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 13:07:11

User@Host: rt_user[rt_user] @ localhost

Query_time: 411 Lock_time: 0 Rows_sent: 0 Rows_examined:

Time: 080317 13:07:13

User@Host: rt_user[rt_user] @ localhost

Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

User@Host: rt_user[rt_user] @ localhost

Query_time: 346 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 12:17:03

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 12:17:47

User@Host: rt_user[rt_user] @ localhost

Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 13:07:11

User@Host: rt_user[rt_user] @ localhost

Query_time: 411 Lock_time: 0 Rows_sent: 0 Rows_examined: 15418603

Time: 080317 13:07:13

User@Host: rt_user[rt_user] @ localhost

Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

User@Host: rt_user[rt_user] @ localhost

Query_time: 346 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 12:17:03

User@Host: rt_user[rt_user] @ localhost

Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 12:17:47

User@Host: rt_user[rt_user] @ localhost

Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

Time: 080317 13:07:11

User@Host: rt_user[rt_user] @ localhost

Query_time: 411 Lock_time: 0 Rows_sent: 0 Rows_examined: 15418603

Time: 080317 13:07:13

User@Host: rt_user[rt_user] @ localhost

Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

User@Host: rt_user[rt_user] @ localhost

Query_time: 346 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

root@bruchtal-www3:/opt/rt3/local/html/Search/Elements# diff /opt/rt3/local/html/Search/Elements/PickBasics /opt/rt3/share/html/Search/Elements/PickBasics 111,112c113 < < %#<& /Elements/SelectOwner, Name => “ValueOfActor”, ValueAttribute => ‘Name’ &>

root@bruchtal-www3:/opt/rt3/local/html/Search/Elements# diff
/opt/rt3/local/html/Search/Elements/PickBasics
/opt/rt3/share/html/Search/Elements/PickBasics
111,112c113
<
< %#<& /Elements/SelectOwner, Name => “ValueOfActor”, ValueAttribute =>
‘Name’ &>