• While adding a custom field to Bugzilla to track the newest SeaMonkey version, the script ran into a lock wait timeout and aborted. Some of the data needed to be manually inserted to finish adding the custom field.
• We then needed to add database grants so our metrics team could access the new fields.
• Added access so the Autoland staging server
• We added the DBAs to what gets paged for our new backup server.
• This seemed to be the week that a few of machines started having disk issues, though all of them were one-offs (as opposed to having to set expire_logs_days). I did run into a fascinating issue where binary logs for a machine were 7G even though the maximum size was supposed to be 1G.
• This was also the week that some cron jobs did not get run, because we “sprung ahead”. Monday was a fun day, but luckily everything was easy to fix. Lesson learned: do NOT run anything via cron from 0200 to 0259 because if your server is set to a time zone that observes Daylight Saving Time, it will run twice in October/November and zero times in March.
• The mozillians.org team wanted some data about group names so they could optimize their searching, so we gave them a data export.
• We removed some company-sensitive comments from a bugzilla bug.
• Due to machines being moved around from the old data center to the new one, we had a new location for the developers to pick up their nightly exports of the support.mozilla.com database.
• Did you know I co-host a weekly podcast about MySQL? It’s called OurSQL Cast. You can find it on Feedburner and iTunes. Episode 83 is up, called “The NewSQL World”, and we interview Ori Herrnstadt, the CTO of Akiban.
• We got several new database nodes kickstarted in our new data center.
• We are preparing to upgrade MySQL on Bugzilla’s staging server, which will happen on Sunday.
  

The talks for PGDay NYC 2012 have been announced. The full lineup of talks for the one-day conference on Monday, April 2, 2012, is available at http://pgday.nycpug.org/schedule/.

There is a $50 discount on tickets using the code PGMEETUP (http://pgdaynyc2012.eventbrite.com/?discount=PGMEETUP) from now through March 16th.

PGDay NYC is a one day PostgreSQL conference in NYC featuring both well-known PostgreSQL community members as speakers and local users sharing their practical experience of using PostgreSQL as a key part of their infrastructure. PGDay NYC 2012 is part of the “PG Corridor Days” series of one-day conferences to help promote PostgreSQL usage in their locales.

The other conferences are PGDay Austin on Wednesday, March 28 http://www.postgresql.org/about/event/1379/ and PGDay DC on Friday, March 30 (http://pgday.bwpug.org/). These are all non-profit events organized by local users.

Once again, this year there will be a community dinner at Pedro’s. Pythian is organizing this pay-your-own-way, informally fun way to meet and re-connect with colleagues and friends, old and new. It is open to everyone, and is right after the opening reception Percona is throwing (that’s from 4:30 to 6:30).

So on Tuesday, April 10th, meet at 6:30 pm in the lobby of the Hyatt to walk over (about 1 mile), or meet at 7 pm at Pedro’s – 3935 Freedom Circle, Santa Clara, CA. If you want to come, please RSVP by leaving a comment on the Pythian post, so they have an accurate headcount.

This week I am in Mountain View to help with some physical data center moves. It is nice to get to see where the magic happens, and our new data center is pretty awesome. Our data center technicians, Derek Moore and Erica Muxlow, are really doing a stellar job, as are their interns.

At the office:
- Visited the San Francisco office. What a great view!
- A dependent subquery that had been running fine for weeks started acting up, causing 1000% more load than normal on a machine that hosted many sites, including the Firefox Flicks $10,000 Contest website and Tinderbox Push LoadLog [whoops, Freudian slip!]. We changed it to a join and added an appropriate index, and the query now runs much faster and is not causing any load.
- Created a user and database for the development site of Mozilla Labs
- Pointed our webdev team to some newer exports for some data they were looking for
- Enabled MySQL access for another buildbot host
- Cleaned up some older binary logs that were hanging around after a binlog name change in January, also set expire-logs-days for the same server
- Converted some data from latin1 to utf8 on a staging machine and debug’d a cron job that was duplicating data
- Helped assemble 32 new SSD drives for new database blades (and thanks to the rest of the systems team for installing the drives and kickstarting the machines!)

At the data center:
- Racked 16 blades, including scanning in and updating the inventory database with serial numbers and asset tags
- For 6 of those 16, had to physically dig out the toe tags that had the serial numbers on it (due to a manufacturing problem I’m sure)
- Attached rails to several new servers

Next week I will be in Mountain View to help with the data center move we are doing. My responsibilities will mostly be remote-style work anyway, but it will be nice to see some of my team members in person. So as I close out this week and reflect about what got done in the Mozilla DB world, I also wanted to make clear why I write these. These posts help answer the questions “What does a DBA do?” and “What does Mozilla use databases for?”

This week in Mozilla databases:

• A database with some tables with a latin1 charset and some tables with a utf8 charset was converted so that all the tables used utf8. This involved exporting using mysqldump --default-character-set=utf8, dropping the table and re-creating it with a utf8 charset, and importing the data.
• The default configuration for MySQL databases at Mozilla has been changed to set the default character set to utf8, and existing configurations have been changed to set the default charset to utf8. We use puppet to set up and maintain configuration files.
• A plan to upgrade our Bugzilla databases has been made, approved, and is in progress. And before anyone asks – yes, it is fully my intention to work on query optimization so that future versions of Bugzilla will have awesome queries; however, there is plenty of internal work to do at Mozilla first!
• Files were generated with the general log and prepared with pt-log-player for benchmarking a system with SSD’s.
• In preparation for our data center move, we promoted a machine in another data center to be the master for our download.mozilla.org service.
• We worked with our Infrastructure Security team gathering data on a security issue.
• Created a new slave for a development web environment
• Currently our physical backups are cold backups where we shut down our backup instance of MySQL and copy files. We have started to work on implementing Xtrabackup for these cases. This is a more long-term project, as backups work fine for now, but every step on the way to using Xtrabackup is a good one. We also use mysqldump for logical backups.
• Created new databases for the ci.mozilla.org service
• Added the custom field “cf_blocking_fennec10″ to the Bugzilla database
• Updated data and schema for the development and staging databases for CaseConductor, a litmus replacement for the QA team
  Last week’s theme was:
  
• There were some tweaks to be made with scripts that use the backups for ETL and updating data for development environments so they would function properly.

The OurSQL Podcast did an episode on some of the lesser-known but very useful tools in the Percona Toolkit. pt-show-grants is one of those tools that I use pretty frequently. While the manual page has an explanation of all the features and a few examples, you don’t really see the output, and often you decide whether or not to use a tool based on what it gives you as output.

So here is a small example of an actual command I did today using pt-show-grants. I wanted to find the grants for a particular user. To do that without pt-show-grants, I’d have to login to MySQL, run

mysql> SELECT host FROM mysql.user WHERE user='aus4_dev';

And then use that host information in a SHOW GRANTS statement:

mysql> SHOW GRANTS FOR aus4_dev@HOST;

But I would have to do this for each HOST – if there were 2 hosts, I’d have to run the SHOW GRANTS command twice.

Happily, pt-show-grants has an option called –only, which will show you all user@host combinations for the username you specify. I have login information stored in a .my.cnf on this particular dev machine, and except for the password and host, this is an exact copy/paste of what I typed and the output:

[scabral@dev1.db ~]$ bin/pt-show-grants --only aus4_dev
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.1.52-log at 2012-03-01 08:52:01
-- Grants for 'aus4_dev'@'10.0.0.1'
GRANT USAGE ON *.* TO 'aus4_dev'@'10.0.0.1' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'10.0.0.2';
-- Grants for 'aus4_dev'@'10.0.0.2'
GRANT USAGE ON *.* TO 'aus4_dev'@'10.0.0.2' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'10.0.0.2';


By default, if I did not put in the –only, it would show me all the users that I was allowed to see. There is also an –ignore option, so if you want to show all users except a particular username, you can do that as well.

Being able to find all user@host users and their grants given a particular username is very handy and eliminates the need to go into the database to find the hostnames.