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
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 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?
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
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.
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
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
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”?
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
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
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;
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
