/images/avatar.png

MySQL optimization and diagnostics tactics

We have all been there. There’s that server, which has mysterious spikes in load, which come from nowhere and leave just as they came - quickly and unexplained. While sometimes these problems can be explained with slow queries, given enough time, development and optimization, you will run into scenarios that don’t register in the slow query log.

It is my opinion, that any kind of database load problem can be traced back to development. It’s a general extension of “You are writing unoptimized queries” to “You’re calling your optimized queries too often” or just maybe “The traffic you generate from the database is too much” or “The code you wrote does not scale to the number of users you have”. But picking up on those problems in production systems with high load and high availability is tricky.

Maintaining broken code

Time moves on. Technology improves, sometimes taking care of bad decisions in the past, making it necessary to fix things related to that. PHP changed over the years in small and subtle ways, from register_globals, short_tags, autoglobals, and will hopefully continue to improve in the years to follow. But some things are not a design fault in PHP. Some things are a direct cause of little to no thought in the process of using PHP. PHP itself is a tool, and a powerful one. Unfortunately, it is as good of a tool, as the person using it. And the person who wrote this, was definitely a tool:

The capacitor plague and wasted hardware

After being involved in computers for around 15 years now, every once in a while I experience one common occurrence and that is the failing of hardware. It’s been a few weeks since I had a failed disk. It’s been this week that I realized that three monitors died at our office in the last few years. Hardware seems to be dieing more recently than not. It’s a shame a lot of components seem to be usable, but are usually being discarded along with perfectly functioning hardware.

MySQL & replication "oh, right" moments

We have been running a replicated MySQL setup for many years now. It has been working great for us, but it does make you think of some things in a different way. Not because you’re working with a specific function set, but also because of the way the database daemon is constructed, from the ground up, and these little things that add up over time.

A recent “oh” moment was regarding MySQL replication lag. Replication lag is what occurs in certain cases … loss of network connectivity is one. And looking at our database server, that didn’t seem to be the case. The replication was working, the data was being replicated but we had an ever increasing replication lag.

Mysteries of system administration ...

You have two servers, you have the same scripts available on the two servers. The scripts are run via ‘crontab’, which has the same version on both servers. They have the same operating system. The scripts use ‘perl’, which is the same version on both servers.

Script works on both servers, but throws locale warnings on server B.

In the end, the solution was to edit /etc/environment (deprecated). Somehow crontab still uses this file, and the file was empty on the working server, and set to an old (also deprecated) charset on the other. The only difference was the time when the servers were installed - one was installed before the deprecation of /etc/environment, and the second one after that. And since /etc/environment is not part of the crontab package, and it wasn’t cleaned up during server upgrades, you’re left with different behaviour on two “identical” servers.

The Billion Dollar Session Cookie

Marissa Mayer is the VP of geographic and local services at Google. When speaking at the Velocity 2009 conference, she mentioned what was since described as “The Billion Dollar HTML Tag”. The presented problem was that with Google’s vast user base, an inefficiently implemented landing page feature might cost them millions of dollars of lost revenue somewhere down the line.

Change in perception is sometimes needed, to recognize something trivial as something you need to pay attention to.