Sometimes it’s not about just optimizing CPU time away. Looking at the details I could optimize away a badly written SQL query along with some more trivial big-O problem in regard with sorting of video clips which I got out of serialized data. All from a few carefully placed calls. Reducing network bandwidth is just as important as CPU time.
Every once in a while you need to do a sanity check of your code, how it performs and what you can do to improve it. This is most apparent with a code-base that is developed and refactored over a period of several years. There can be several problems that negatively impact performance due to dependency issues or legacy code that should have been removed but was forgotten during a refactoring sprint.
I also held a talk about Redis and some experience we got with it at RTV Slovenia. The talk slides are up at Slideshare.
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.
