Author: sheeri

You may have noticed that I stopped posting the weekly news from the Mozilla DB Team. After going through the Operations Report Card and applying it to DBAs in OurSQL Podcast numbers 111, 112, 114, 115 and 116, I started thinking that the updates were really more like metrics, and it would better serve my own purposes better to do the updates monthly.

The purposes of doing this type of blog post are:
0) Answering So what does a DBA do, anyway?
1) Answering DBA? At Mozilla? Does Firefox have a database? Why does Mozilla have databases, and what for?
2) Showing what the DB team does for Mozilla, so that folks will understand that just keeping things working is actually a lot of work. It also helps compile yearly reviews of accomplishments.

We are also starting to get some metrics information. This month we started easy number of MySQL and Postgres machines, number of unique databases (mysql, information_schema, performance_schema and test are ignored, and duplicates, like the same database on a master and 2 slaves, are ignored), and version information.

As of today, we have 9 unique databases across 8 Postgres servers in 4 clusters, with 6 being Postgres 9.0 and 2 being Postgres 9.2 we are currently upgrading all our Postgres databases to 9.2 and expect that by the end of December all servers will be using 9.2.

We have 427 unique databases across 98 MySQL DB machines in 20 clusters, with 3 being MySQL 5.0, 71 being MySQL 5.1 (mostly Perconas patched 5.1), and 24 being MariaDB 5.5.

And in the last week of October and the month of November, we have:

• Documented 4 more of our Nagios checks.
• Started to upgrade Postgres databases to Postgres 9.2
• Decommissioned a legacy database cluster for Firefox themes.
• Built a new database cluster (complete with monitoring and backups) for a new Sentry implementation.
• Upgraded 14 machines for general operating system updating purposes and to ensure that be2net drivers are up-to-date; out-of-date drivers can (and have!) caused servers to crash.
• Upgraded MySQL 5.0 to MySQL 5.1 across 3 clusters and 7 machines.
• Did a quarterly purge of Crash Stats data.
• Had to re-sync 6 slaves when a transaction rolled back on the master, but some of the tables modified were MyISAM. So the master had data in some tables that was out of sync with the slaves.
• Assisted in converting to use UTC timestamps in the Elmo database behind the Mozilla localization portal and the Bugzilla Anthropology Project, prompting a blog post on converting timezone-specific times in MySQL.
• Decommissioned a legacy production generic database cluster that had over 60 databases on it.
• Built a 5th database backup instance due to growing backup needs.
• Changed binary log format to MIXED on our JIRA installation due to JIRA requirements and an upgrade to MySQL 5.1 issuing warnings that MySQL 5.0 had not.
• Added checksums to the database cluster that runs Input and Firefox about:home snippets.
• Archived and dropped the database behind Rock Your Firefox.
• Exported Bugzilla data for a research project. Did you know if you are doing academic research, you can get a copy of Mozillas public Bugzilla data?
• Gave read-only database access to a developer behind the Datazilla project.
• Updated the email list for vouched Mozillians.
• Backfilled missing crash-stats data after some failed cron scripts.
• Cleared some junk data from the Datazilla databases.
• Added new custom fields to our implementation of Bugzilla for upcoming release versions: Firefox 20, Thunderbird 20, Thunderbird ESR 20 and seamonkey 217.
• Added 10 new machines to the Graphs database, and added new sets of machines for Mozilla ESR 17 and Thunderbird ESR 17.
• Gave read-only database access to the two main leads of the Air Mozilla project.
• Debugged and turned off a 10-second timeout in our load balancing pool that was causing Postgres monitors and processors to lose connection to their databases.
• Discovered that the plugins database actually does better with the query_cache turned on, and tuned its size.
• Tweaked tokudb_cache_size and innodb_buffer_pool_size on our Datazilla databases so that less swap would be used.
• Created 2 read/write accounts for 2 people to access the development database for Mozillians.
• Gave access to Datazilla databases for staging.

Wednesday, Nov 28th was my 1-year anniversary at Mozilla. Tomorrow is December! 2012 went by very quickly.

Twice last week, a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. Its typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, from time to time, gets errors when doing mysqldump backups (we do physical backups and logical backups, but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, Does this make sense? Would changing this actually free up any resources? and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

I was asked about slides for my talks at Northeast PHP, so I figured I would post them here so folks could benefit. I gave three MySQL talks. In the list below, the talk name links to the description on the conference website, and you can get the slides by clicking the PDF slides links.

Are You Getting the Best Out of Your MySQL Indexes PDF slides
Getting Rid of Scheduled Tasks Using MySQL Events PDF slides
Better JOINs and Subqueries PDF slides

Hope you enjoy them!