RT query for test InnoDB performance

I have got RT 3.4.1 with the DB on a mysql server (4.0.20-max) with
InnoDB tables.
Since few months, i perceive a sensible performance degradation. Mysql
Support suggest me to do an “ALTER TABLE table ENGINE=InnoDB;” (Null
alter table) to defragment the table and recalculate index statistics.

I want to test with a RT real and most frequently SQL query (for example
to search tickets) before and after “ALTER TABLE” operation, to measure
the improvement. Can anyone suggest a typical RT SQL query?

Thank in advance.
Regards, Francesco.

Francesco Dalla Ca’
Settore Gestione Sistemi CINECA
Via Magnanelli 6/3 40033 Casalecchio di Reno (BO)
Bologna, Italy
http://www.cineca.it

I have got RT 3.4.1 with the DB on a mysql server (4.0.20-max) with
InnoDB tables.
Since few months, i perceive a sensible performance degradation. Mysql
Support suggest me to do an “ALTER TABLE table ENGINE=InnoDB;” (Null
alter table) to defragment the table and recalculate index statistics.

RT should alway be run with InnoDB tables if you’re running on MySQL.
Running with the default “MyISAM” table type is dangerous and should be
avoided.

Jesse

I have got RT 3.4.1 with the DB on a mysql server (4.0.20-max) with
InnoDB tables.
Since few months, i perceive a sensible performance
degradation. Mysql
Support suggest me to do an “ALTER TABLE table
ENGINE=InnoDB;” (Null
alter table) to defragment the table and recalculate index
statistics.

RT should alway be run with InnoDB tables if you’re running
on MySQL. Running with the default “MyISAM” table type is
dangerous and should be avoided.

I think he meant that MySQL support suggested he do that to force MySQL
to sort itself out, not to actually change the table type.

I want to test with a RT real and most frequently SQL query (for
example
to search tickets) before and after "ALTER TABLE"
operation, to measure
the improvement. Can anyone suggest a typical RT SQL query?

Why not perform a query and look in the MySQL logs?

lee

Goddard Lee wrote:

Why not perform a query and look in the MySQL logs?

lee

I have slow_query_log enabled and long_query_time set to 2 seconds, but
in the slow_query_log and i see only these kind of queries:

Time: 060116 15:47:48

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

Query_time: 14 Lock_time: 0 Rows_sent: 1 Rows_examined: 431514

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ( ( (Attachments_2.Content LIKE
’%asphi%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );

Time: 060116 15:48:20

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

Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

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

Time: 060116 15:48:51

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

Query_time: 14 Lock_time: 0 Rows_sent: 4 Rows_examined: 431347

SELECT DISTINCT main.* FROM Tickets main , Transactions Transactions_1,
Attachments Attachments_2 WHERE ((Transactions_1.ObjectType =
‘RT::Ticket’)) AND ((main.EffectiveId = main.id)) AND ((main.Status !=
‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( ( (Attachments_2.Content
LIKE ‘%asphi%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ) ORDER BY
main.id ASC;

I don’t use general log (for security reasons).
I’m searching for a tipical query that involves “Queues” and
"ObjectCustomFieldValues" tables. There’s a way to see the SQL from the
Query Browser?

Thank in advance.
Francesco.

Francesco Dalla Ca’
Settore Gestione Sistemi CINECA
Via Magnanelli 6/3 40033 Casalecchio di Reno (BO)
Bologna, Italy
http://www.cineca.it

Since few months, i perceive a sensible performance
degradation. Mysql
Support suggest me to do an “ALTER TABLE table
ENGINE=InnoDB;” (Null
alter table) to defragment the table and recalculate index
statistics.

RT should alway be run with InnoDB tables if you’re running
on MySQL. Running with the default “MyISAM” table type is
dangerous and should be avoided.

I think he meant that MySQL support suggested he do that to force
MySQL
to sort itself out, not to actually change the table type.

My understanding is that the above ALTER command does the moral
equivalent of a VACUUM FULL in postgres. ie, it cleans out the dead
tuples and reclaims the unused space from the table.

It is a travesty that there is no normal way to do that other then
incurring downtime in mysql.

Since few months, i perceive a sensible performance
degradation. Mysql
Support suggest me to do an “ALTER TABLE table
ENGINE=InnoDB;” (Null
alter table) to defragment the table and recalculate index
statistics.

RT should alway be run with InnoDB tables if you’re running
on MySQL. Running with the default “MyISAM” table type is
dangerous and should be avoided.

I think he meant that MySQL support suggested he do that to force
MySQL
to sort itself out, not to actually change the table type.

My understanding is that the above ALTER command does the moral
equivalent of a VACUUM FULL in postgres. ie, it cleans out the dead
tuples and reclaims the unused space from the table.
It’s not full vacuum, it only rebuilds indexes.

It is a travesty that there is no normal way to do that other then
incurring downtime in mysql.
Really, to get full vacuum on InnoDB you have to use dump&restore, this’s sad.

Best regards, Ruslan.

My understanding is that the above ALTER command does the moral
equivalent of a VACUUM FULL in postgres. ie, it cleans out the dead
tuples and reclaims the unused space from the table.
It’s not full vacuum, it only rebuilds indexes.

You’d think they’d call this command ‘REINDEX tablename’ instead,
then. But that’d be too obvious :frowning:

Vivek Khera wrote:

My understanding is that the above ALTER command does the moral
equivalent of a VACUUM FULL in postgres. ie, it cleans out the dead
tuples and reclaims the unused space from the table.

It’s not full vacuum, it only rebuilds indexes.

You’d think they’d call this command ‘REINDEX tablename’ instead,
then. But that’d be too obvious :frowning:

From mysql support:

ALTER recreates the table as if it was DROPped and CREATEd again it
updates all index statistics and “defragments” the table.

A “null” ALTER TABLE (ALTER TABLE x_y_z ENGINE=InnoDB;) is a very
expensive operation… but more effective than a ANALYZE+OPTIMIZE.

Regards, Francesco.

p.s.: Can anyone give me a sample sql query from “Query browser”?


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Download a free sample chapter of RT Essentials from O’Reilly Media at
http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at
http://bestpractical.com/services/training.html

Francesco Dalla Ca’ - Email: f.dallaca@cineca.it
Settore Gestione Sistemi CINECA
Via Magnanelli 6/3 40033 Casalecchio di Reno (BO)
Bologna, Italy Tel: 051/6171437
http://www.cineca.it

Vivek Khera wrote:

My understanding is that the above ALTER command does the moral
equivalent of a VACUUM FULL in postgres. ie, it cleans out the dead
tuples and reclaims the unused space from the table.

It’s not full vacuum, it only rebuilds indexes.

You’d think they’d call this command ‘REINDEX tablename’ instead,
then. But that’d be too obvious :frowning:

From mysql support:

ALTER recreates the table as if it was DROPped and CREATEd again it
updates all index statistics and “defragments” the table.

A “null” ALTER TABLE (ALTER TABLE x_y_z ENGINE=InnoDB;) is a very
expensive operation… but more effective than a ANALYZE+OPTIMIZE.
Hm, didn’t know that.

Regards, Francesco.

p.s.: Can anyone give me a sample sql query from “Query browser”?
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Links Links_1
WHERE ((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’))
AND ( ( (main.Status = ‘new’) OR (main.Status = ‘stalled’) OR
(main.Status = ‘open’) ) AND ( ( (Links_1.Type = ‘DependsOn’) AND
(Links_1.LocalBase = ‘119’) AND (main.id = Links_1.LocalTarget) ) )
);

SELECT main.* FROM Tickets main WHERE ((main.EffectiveId = main.id))
AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( (
( (main.Owner = ‘12’) ) AND (main.Status = ‘new’) OR (main.Status =
‘open’) ) ) ORDER BY main.Priority DESC LIMIT 10;

SELECT DISTINCT main.* FROM CustomFields main , ObjectCustomFields
ObjectCustomFields_1 WHERE ((ObjectCustomFields_1.ObjectId = ‘1’) OR
(ObjectCustomFields_1.ObjectId = ‘0’)) AND ((main.Disabled = ‘0’)) AND
((main.LookupType = ‘RT::Queue-RT::Ticket-RT::Transaction’)) AND
((main.Name = NULL)) AND ((main.id =
ObjectCustomFields_1.CustomField)) ORDER BY
ObjectCustomFields_1.ObjectId ASC, ObjectCustomFields_1.SortOrder ASC;


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Download a free sample chapter of RT Essentials from O’Reilly Media at
http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at
http://bestpractical.com/services/training.html

Francesco Dalla Ca’ - Email: f.dallaca@cineca.it
Settore Gestione Sistemi CINECA
Via Magnanelli 6/3 40033 Casalecchio di Reno (BO)
Bologna, Italy Tel: 051/6171437
http://www.cineca.it


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Download a free sample chapter of RT Essentials from O’Reilly Media at http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at http://bestpractical.com/services/training.html

Best regards, Ruslan.