981496 - alter the engine type for bugzilla's bugs_fulltext table from myisam to innodb

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[:glob ✱]

cyborgshadow has identified bugs_fulltext's myisam engine as a major contributing factor to the database replication lag we've been experiencing.  there's some details on bug 981487.

after discussing the issue with him, it appears that we should be able to switch to innodb without downtime:
> remove it from pool, run the alter, replace into pool

looking at bugzilla's code, this shouldn't cause any issues while we're running, however there's a few minor changes that should be made (mostly moving bugs_fulltext updating code within transaction blocks).

Comment 1

[Sheeri Cabral [:sheeri]]

Done on bugzilla4, 5 and 6, though there are data inconsistences that have to be addressed (tomorrow, when we're fresh, at the latest).

Doing on bugzilla2 now:
mysql> select @@hostname; set sql_log_bin=0;  alter table bugs.bugs_fulltext engine=innodb;
+-------------------------------+
| @@hostname                    |
+-------------------------------+
| bugzilla2.db.scl3.mozilla.com |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Comment 2

[Sheeri Cabral [:sheeri]]

the table crashed on the master, so we had to failover. bugzilla3 is the current master, bugzilla1 is undergoing a repair and then an alter table now.

Comment 3

[Sheeri Cabral [:sheeri]]

bugzilla1 repair and alter complete.

bugzilla3 is the only remaining holdout and it's the current master.

Comment 4

[Sheeri Cabral [:sheeri]]

bugzilla1 repair and alter complete.

bugzilla3 is the only remaining holdout and it's the current master.

Comment 5

[Sheeri Cabral [:sheeri]]

had to fail back, as bugzilla3's bugs_fulltext table was having problems. Altering bugzilla3 and all the phx servers and backups to be innodb now.

Comment 6

[Sheeri Cabral [:sheeri]]

bugzilla3 and phx are done, just waiting for backups to complete.

Comment 7

[Brandon Johnson [:cyborgshadow]]

This is finished. Closing out.