December News from the Mozilla Database Team

Being the end of the quarter, there has been some planning going on this month about goals for Q1 2013 as well as meeting our goals for Q4 2012. Our biggest goal was to stop using MySQL 5.0, which we … Continue reading

Being the end of the quarter, there has been some planning going on this month about goals for Q1 2013 as well as meeting our goals for Q4 2012. Our biggest goal was to stop using MySQL 5.0, which we have successfully done. We only have one server left on MySQL 5.0, and that has a compatible MySQL 5.1 server waiting for a few developers to get back from their well-deserved vacations to migrate off. In December, we finished upgrading 2 servers to MySQL 5.1.

– Looked at the top 30 Bugzilla queries and started to give optimization tips for MySQL.
– Did our regular purge/defrag of TinderBox PushLog.
– Worked on integrating our datazilla code with features.
– Helped change the data model for datazilla.
– Moved some Bugzilla tables to a different partition when the data partition filled up. There is a plan to upgrade but we had an immediate need for the move.
– Upgraded one of the Bugzilla slaves to MariaDB 5.5.
– Refreshed the support staging database with production data.
– Added grants for metrics users to support new Bugzilla custom fields.
– Did some research on whether SSDs were good enough for the addons database or if we really needed Fusion I/O. (conclusion: SSDs are good enough! The driver for this was cost of larger Fusion I/O disks, and starting to worry about space on the database systems.)
– Found a bug in new build code for the builder that builds Firefox, that would effectively stop updated builds from being recorded in the builder database. The bug was found in development, the code itself is not in production yet, but there were several hours of database debugging to figure out the problem.
– Built a new database server for backups that does not depend on NFS.
– Implemented checksum checking on several more MySQL clusters to ensure the data on the master and slaves match.
– Created databases for Game On.
– Optimized a query for a Firefox build system component (clobberer).
– Installed larger disks on our production Postgres failover server. We will be testing failover and adding more disks to the current master server in Q1.
– Created a database cluster for the main Mozilla website for failover.
– Cleaned up replication on a cluster after a power problem caused the master to crash.
– Added a Nagios check that uses pt-config-diff to all our MySQL servers to ensure that we know whenever the running MySQL configuration does not match the /etc/my.cnf file.
– Dealt with a set of queries breaking replication due to not being inside a transaction.
– Dealt with a schema change for Bugzilla taking a long time, and took slaves out of the load balancer one at a time to let the ALTER TABLE complete without read queries getting locked and causing slow responsiveness on the database servers.
– Created read-only database logins for the administrators of Air Mozilla so they can better debug problems.
– Imported some data for Graphs.
– Audited the Persona/BrowserID databases to get them ready for prime time (these databases are not normally managed by the DB team).
– Did a security review for our Engagement team to get reports of Mozillians emails for sending out information to registered and vouched Mozillians.
– Added documentation for 11 Nagios checks related to MySQL and Postgres.
– Researched the Zero Day Exploits for MySQL to see if Mozilla was affected.
– Puppetized the postgresql.conf files for all our postgres servers.
– Puppetized our datazilla database servers.
– Puppetiezed database servers for web development and for internal tools.
– We sized MySQL database machines for the Platform as a Service (PaaS) platform that the webops team will be implementing. The next step is ordering the hardware!

Under planning we have done a lot in 2012 to stabilize our MySQL environment and have a good, sane centralized puppet configuration for control of MySQL packages, configuration files, scripts and backups. 2013 will be the year we do the same with Postgres:
– Stabilizing Postgres
– Streamlining Postgres configuration and installation and upgrading with puppet
– Reconfiguring Postgres logging
– Stabilizing Postgres backups

There are plenty of great things that will happen in 2013 from the Mozilla Database Team for both MySQL and Postgres databases!