For the past few days, I have been upgrading a few servers. We are going from Percona’s patched MySQL 5.1 to MariaDB 5.5 (the subquery optimization is excellent, and we have lots of subqueries). Our process involves upgrading a slave first, and letting it replicate for a while, and if everything’s good, update more slaves, then the master.

This has served us well in the past. And now that we are checking data integrity between masters and slaves with pt-table-checksum, the process involves checking before we start that there is data integrity. This is easy, as we checksum twice daily and have a Nagios alert if there are any discrepancies. After the upgrade, we checksum again, to be sure no data has been changed/corrupted in the process of doing a mysqldump export and import.*

Much to my surprise, after importing the data on one of our …

I saw a post on profiling memory usage today and this reminds me of several discussions I have had with different people.

Why would you want to profile the memory usage? Usually to see where memory is allocated and for what purposes, and usually you only care when memory usage is higher than expected. That is a DBA normally wants to use all available memory on a server for mysqld, whatever that size may be.

Configuration parameters may be inappropriate and need adjusting, so having a way to determine the range of memory usage based on those parameters would be most helpful.  However, the configuration parameters as a whole put no limit on memory used, so different workloads can quite easily lead to memory being insufficient on the OS, triggering swapping or perhaps the …

I am happy and proud to announce that there will be an Open Database Camp held at this year’s Northeast LinuxFest! The venue is at Harvard University in Cambridge, MA (“our fair city”), and will take place Saturday, March 16 and Sunday, March 17, 2013.

Northeast LinuxFest and Open Database Camp are both free, but there is no reciprocal membership. To register for Open Database Camp, just sign up with Eventbrite. We are also soliciting session ideas ahead of time, and attendees will choose sessions during the Saturday morning planning session, as usual for Open DB Camp.

If you are interested in …

I was surprised to find on a customer MySQL server this new syntax for Master_host in SHOW SLAVE STATUS.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: testdb1.xxx.com or 10.XXX.XX.XXX
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db1-354215-bin-log.000005
          Read_Master_Log_Pos: 1624
               Relay_Log_File: db2-354214-relay-log.000001
   

Is this a fancy new Percona Server feature? No. It’s operator error.

We read a little further to find.

mysql> SHOW SLAVE STATUS\G
...
             Slave_IO_Running: Connecting

...
                Last_IO_Errno: 2005
                Last_IO_Error: error connecting to master 'repl@ testdb1.xxx.com or 10.XXX.XX.XXX' - retry-time: 60  retries: 86400

MySQL MHA by Oracle ACE Director Yoshinori Matsunobu is an excellent open source tool to help in providing HA with native MySQL replication. The installation however is dependent on some Perl packages and to the untrained eye this may be an issue if you are using Percona Server as your choice of MySQL implementation.

The MHA Node page requires the perl-DBD-MySQL package to be installed. The installation on RedHat/CentOS/Oracle Linux look like this:

$ sudo yum install perl-DBD-MySQL
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.anl.gov
* extras: mirror.anl.gov
* updates: mirror.anl.gov
Setting up Install Process
Resolving Dependencies
--> Running transaction check …

This would have to be one of the most common MySQL error messages that is misleading to the end user developer. The MySQL Manual page confirms the broad range of possible conditions, but offers little to a PHP developer that does not speak MySQL Geek. I am commonly asked to help solve this issue from a developer.

The problem is that there are several conditions that can cause this error, and a more meaningful explanation to the end user would help in addressing the issue. In general terms, this actually means “Your SQL statement has failed because the connection to the database has been disconnected because of ???”.

Here are a few common situations and how to check for what “???” is.

1. Your MySQL server really did go away.

We can easily check this by looking at the server uptime and the server error log.

$ …

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 chart.io features.
- Helped change the data model …

I’m not a wizard with infographics, but I can do a few pie charts. I copied the data to the right of the pie charts for those that want to see the numbers. Overall, there are almost 400 databases at Mozilla, in 11 different categories. Here is how each category fares in number of databases:

Here is how each category measures up with regards to database size – clearly, our crash-stats database (which is on Postgres, not MySQL) is the largest:

So here is another pie chart with the relative sizes of the MySQL databases:

I’m sure I’ve …

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 …

Back in the summer, I heard about how Gene Babon of Boston’s PHP Meetup Group was brilliantly helping folks learn web programming through PHP Percolate, jQuery Jam and HTML5 Brunch. While he was explaining this, I thought to myself, “this needs to be done for MySQL!”

And so it will be. Starting in just over a month, January 1st, 2013, the first MySQL Marinate group will happen. We already have over 50 people signed up for the event itself, and as of right now there are 85 people in the meetup group itself, with over 30% of …