TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL

来源：懂视网责编：小采时间：2020-11-09 20:03:47

TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL

TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL:As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I

推荐度：

点击下载本文 文档为doc格式

导读TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL:As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I

As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I read Matt Lord’sblog postabout the document relevancy rankings used for InnoDB full-text searches I knew to expect some differences when sorting by relevancy, so I want to focus on getting the same set of rows back, mostly ignoring the order in which the rows are returned.

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=0to 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:

pt-upgrade --read-only /--database flite /--type rawlog /tmp/proddb-slow.log.raw /h=testdb33.flite.com /h=testdb47.flite.com

I used the--read-onlyflag sopt-upgradewould only executeSELECTstatements, 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 rawloginstead 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-upgradeit exposed several significant discrepancies. I will document those discrepancies and how I fixed them in a future post.

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL

推荐度：

点击下载本文 文档为doc格式

标签： in mysql index

热门焦点

TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL

TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL

TestingMySQLFULLTEXTindexesinInnoDBusingpt-upgrade_MySQL

最新推荐

猜你喜欢

热门推荐