It was the year 2001. I barely started out with PHP and eventually MySQL. A client had this wish, to log and analyze its web traffic (a substantial amount of it), and because of a clustered environment, and also lack of detail with existing solutions the only available solution then was to develop our own analytics package with php and mysql. While at first we didn’t have problems scaling, we have quickly grown the log data and eventually hit barriers as disk space availability, concurrency issues (MyISAM …) and then query speed itself for analyzing data (sum, count, avg …).

In the late 90’s, after 96-97, I was a part of the demo scene in Slovenia. This was a somewhat informal gathering of like-minded hackers, programmers, artists, musicians who liked to have fun. There was a vibrant BBS scene at that time, where at least three or four BBS were active, one of them my own. I’m not even sure why we had BBS now, but before IRC and demo parties it seemed to be a good way to get a C++ compiler and read several tutorials and e-zines.

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.

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:

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 dying 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.

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.