Heim > Datenbank > MySQL-Tutorial > Hauptteil

Testing MySQL FULLTEXT indexes in InnoDB using pt-upgrade_MySQL

WBOY
Freigeben: 2016-05-31 08:47:02
Original
835 Leute haben es durchsucht

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:

<code>pt-upgrade --read-only /--database flite /--type rawlog /tmp/proddb-slow.log.raw /h=testdb33.flite.com /h=testdb47.flite.com</code>
Nach dem Login kopieren

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.

Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage
Über uns Haftungsausschluss Sitemap
Chinesische PHP-Website:Online-PHP-Schulung für das Gemeinwohl,Helfen Sie PHP-Lernenden, sich schnell weiterzuentwickeln!