Wednesday, 12th December, 2007

Scalability

geek bork code

There is (almost) nothing more wonderful than having a confusing and frustrating problem with something — a problem that threatens to derail almost everything you've worked on for over a year — then finding out eactly what's up, and fixing it.

The server that I host most of my more important things on — /seconds., our School site, SLeeds.org and (on a personal note) Currantly.org — has been playing silly buggers recently. I'd assumed that it was some kind of malicious thing initially — considering that the problems first started around about the time that Fasthosts had that data-leak malarky — but I'd been incresingly coming to the conclusion that the fault lay (natch) with a dodgy bit of coding on my part.

What was happening was that with an increasing frequency, all of the sites would go down, and I'd need to fire up the remote hardware control panel, and do a hard reset.

I suspected many many things — like the convoluted way I was handling video uploads while Erin was offline, or huge server-loads brought on by spambots attacking my Chatlog scripts — but no matter what I tinkered with, or disabled, the problem persisted.

Mostly, I just ignored it, and hit "reset" every time it happened, but on Friday I found myself in the unenviable position of trying to make sure that everything was up and running for that event that was going on.

I mean, how embarassing would it have been (and how much more stressy would it have made me) to have had to keep hitting reset in the company of a bunch of pretensious artists — who all think their work is the single most important thing ever to have grace the intertubes?

During my diagnostic checking (which consisted of banging my head against the keyboard, shouting "WHY?! FFS WHY?!" repeatedly, and staring at the server-stats page on my control panel with a blank look on my face), I noticed an odd thing: the memory usage of the server would start off at 5%, creep up to 100% within minutes, then the virtual memory would do the same.

At which the server would crash.

I didn't really have time on Friday to really look into it, but a quick edit of php.ini — dropping the maximum memory usage per script back down to its default of 8MB seemed to do the trick.

All was well, and the site stayed up throught the weekend with no problems.

Or, at least, no problems that I was aware of. Unbeknownst to me (because I didn't think to check) none of the School sites were working at all. All anyone would get when trying to log was a blank screen.

On Monday, I tried to find out what was going on — I was getting error messages complaining that the amount of memory had been exhausted, and things were Not OK™

I upped the maximum memory cautiously, to try and fix it, and was all chuffed with myself when things appeared to be back to normal. Until, that is, I got a series of emails from staff on the other course (who share our site) complaining that it still wasn't working for them.

I checked the code, and there were absolutely no differences between them — theirs being a complete duplicate of ours, just with different criteria for the database soas not to mix everything up.

And then it hit me — the only difference between the two courses was that there's had a shit-load more stuff in the database as ours.

...

I am not, I have a tendency to point out, a professional coder. I cobble things together, and if they work I pat myself on the back. I've never been trained, or even sat through a class on how to write code, so things like scalability and 'good coding practices' never really pop into my head when I'm sketching something out.

At the very top of the index.php page, was a simple line of code that pulled in all the stuff from the database, so as to form nice paginated results...

SELECT * FROM db WHERE (blah blah blah)

...which, on a database of (now) forty thousand pieces of work, was just a little bit too much for the server to cope with.

One quick hack later — a simple bit of SQL to find the total number of pieces without pulling in everysinglefucking bit of information about them into an array — and everything is fine again.

The new site I've been working on (which I will bore you to tears explaining when I've got it all working) doesn't rely on such shitty code. In fact, I've been almost good in the way that I've paginated things.

But, to cut a long story short¹, I'm becoming very aware of my limitations, and I'm going to have to go through the new one with a fine tooth comb to make sure things like that don't happen again.

¹ Too late, huh?