Some more thoughts - what does “pt-online-schema-change D=database,t=table --alter “ENGINE=InnoDB”” actually do?
I had a look at the manual here [url]http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html[/url] but I don't completely understand what the --alter flag is doing. Don't I have to specify what to alter?
When I just run it with --alter I see output similar to this:
Starting a dry run. `drupal`.`workflow_scheduled_transition` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table drupal._workflow_scheduled_transition_new OK.
Altering new table...
Altered `drupal`.`_workflow_scheduled_transition_new` OK.
Dropping new table...
Dropped new table OK.
Dry run complete. `drupal`.`workflow_scheduled_transition` was not altered.
The new table `drupal`.`_workflow_scheduled_transition_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
I'm not a DBA and my experience with MySQL is quite limited, however in the past when I've used Microsoft SQL it was necessary to perform regularly scheduled maintenance (which I'm assuming may be similar to a mysql optimize?) which rebuilds indexes and reorganizes tables. A job like this typically runs once per week and keeps the database healthy. If you don't run a job like this over time performance will slowly degrade.
Again I assume the same would be true of performing mysql --optimize or pt-online-schema change? In other words if you hadn't run this maintenance in 6 months you might see a noticable improvement in performance but if you had been running it regularly you probably wouldn't see a big difference in performance.
This is a round about way of saying how do I know what this is doing and if its helping?
It sounds like if I had specified --execute it would have copied a bunch of rows, created some triggers, drop some triggers, swapped some tables and updated some foreign_keys. Other than trying to run a load test against the server which again may or may not show a meaningful change - how can I tell if this is "helping"?