Testing MySQL FULLTEXT indexes in InnoDB using pt-upgrade_MySQL
Percona toolkit has a tool calledpt-upgradethat works well for this purpose. I used 2 test servers with a copy of my production database. On one of the servers I left the tables in MyISAM, and on the other I converted the tables to InnoDB. I copied a slow query log from a production host running withlong_query_time=0
to get the query set for testing. Since I was only interested in queries on a few tables, rather than running the entire slow query log against the servers I just extracted the specific queries I was interested in and ran them as a raw log.
Here’s the command I used:
|
I used the--read-only
flag sopt-upgrade
would only executeSELECT
statements, and not any statements that modify data.
Since I extracted the SQL queries from the slow query log instead of using the full slow query log, I used--type rawlog
instead of the default of--type slowlog
.
For the two hosts I compared, testdb33 is using FULLTEXT on InnoDB, and testdb47 is using FULLTEXT on MyISAM.
When I ranpt-upgrade
it exposed several significant discrepancies. I will document those discrepancies and how I fixed them in a future post.