1057373 - Onboard Bugzilla[78].db.scl3

Status: RESOLVED WONTFIX

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

Description

[Brandon Johnson [:cyborgshadow]]

This bug is to track the onboarding of bugzilla 7 and 8 servers in scl3.

So far the servers are built and have been hot copied over to with replication and ssl setup and working. They've also been puppetized.

Todo:
- add them to the load balancer
- add them to nagios
- add them to newrelic

Comment 1

[Brandon Johnson [:cyborgshadow]]

Added to nagios. making an alert page on bugzilla8 to confirm.

Comment 2

[Brandon Johnson [:cyborgshadow]]

Added to newrelic.

Comment 3

[Brandon Johnson [:cyborgshadow]]

Nagios alerting confirmed. Once bugzilla8 catches up I'll add these to the LB and move forward.

Comment 4

[Brandon Johnson [:cyborgshadow]]

By "catches up" I mean buffer pool loading.

Comment 5

[Brandon Johnson [:cyborgshadow]]

Holding off the onboarding of these servers for now.

It seems innodb full text indexing is severely crippled on these servers.

It might be because of either the RAM difference, the version difference (5.6.12 to 5.6.17), or the disk difference (SSD vs Fibre connected SAN ??)

This query takes subsecond on the physicals and is taking 3-7 minutes on the virtuals. Note that ALL fulltext queries are extremely slow on the virtuals, and in many cases would timeout before returning a page to bugzilla users, so I've also pulled bugzilla5 from the pool.

SELECT bugs.bug_id AS bug_id, (MATCH(bugs_fulltext_1.comments_noprivate) AGAINST('\"set|set&set\"' IN BOOLEAN MODE) + MATCH(bugs_fulltext_1.short_desc) AGAINST('\"set|set&set\"' IN BOOLEAN MODE)) AS relevance   FROM bugs LEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id LEFT JOIN bugs_fulltext AS bugs_fulltext_1 ON bugs.bug_id = bugs_fulltext_1.bug_id  WHERE bugs.creation_ts IS NOT NULL    AND security_map.group_id IS NULL    AND (  bugs.product_id IN (95)  AND ( MATCH(bugs_fulltext_1.comments_noprivate) AGAINST('\"set|set&set\"' IN BOOLEAN MODE) OR MATCH(bugs_fulltext_1.short_desc) AGAINST('\"set|set&set\"' IN BOOLEAN MODE) ) ) GROUP BY bugs.bug_id ORDER BY relevance DESC LIMIT 500;

Comment 6

[Brandon Johnson [:cyborgshadow]]

Note: Bugzilla5 crashed by OOM-killer because of fulltext searches running on it. This fact makes my leading bet for what's causing these issues be the RAM difference.

Comment 7

[Brandon Johnson [:cyborgshadow]]

From the slow query log, fulltext searches have been slow since July on bugzilla5. 

I'll have the RAM bumped up on a virtual to test if it's RAM. I should be able to tell pretty instantly with the query in commen 5.

If that fails, I'll downgrade MySQL to 5.6.12 on a virtual and test.

Comment 8

[Brandon Johnson [:cyborgshadow]]

cknowles upgraded bugzilla8 to 36GB RAM to match the physicals. I scanned the my.cnf file and compared config. The only differences were innodb buffer pool size and key buffer size. 

I matched those two variables, started mysql, waited for the innodb buffer pool load to be complete, and tested on bugzilla8.

It took 5 minutes and 34 seconds. After checking profiling it again spent all the time in "FULLTEXT initialization" state.

I checked the innodb engine status and nothing seemed off or different, even while running that query. I'm fairly certain it's a version issue at this point.

Comment 9

[Brandon Johnson [:cyborgshadow]]

After further work into this Monday evening and checking almost everything that could have been an issue, it appears it's either disk or cpu related, which we can't improve for the virtual servers.

Deal summary was submitted for physical servers to replace current scl3 physical servers. Closing this and filing a bug to shutdown bugzilla[78].