h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Finally! I've found and fixed the memory leak we've suffered since last November.

It wasn't a bad one, but it meant that every day, we'd see a handful of database connections left hanging, which indicates that somewhere in the code someone wasn't deleting a database object properly. We could see the connections, and also see what SQL command was last executed on it, and it is this information that has, in the past, made it possible to track down these leaks. Left unchecked, these orphaned connections would mount up and eventually make it impossible to get a new connection, and the web servers would then lose contact with the database, falling back to serving cached pages. Not nice.

This latest one was a puzzle, though. Usually, the last command to go through the connection was to fetch user details, something that *every* page does at some point, so no help there. Next possibility is that a particular page was causing an access violation, thus exiting the thread and leaving the connection hanging. Trouble is, that would leave an entry in the logs, but whenever I checked, no such error was logged.

Another problem was that, sometimes, the last query wasn't user details, but was more specific - but still didn't point to a bug in the code.

Then yesterday, I was doing the usual, but this time (luckily) there were two connections very close to one another. I looked in the logs, found possible offending queries and noticed they both went to the Info page. I called up the code, and immediately spotted the problem. A database connection was created, then the code checked the cache for recently cached info (we cache it for five minutes) and if that exists it would serve that instead of doing an expensive database query for every request. The bug was that the database connection wasn't released if the cached version was served. Moving the creation of the database connection below the cache test meant everything was fine.

The fact that the connection was created but never used explains why the last query run on it was variable - I was seeing the last query run before the connection was recycled and claimed by the info code. It's also clear why it wasn't happening that much - Info isn't used a huge amount, and the bug would only happen if two people requested the page within five minutes of each other.

It'll need monitoring, but I'm really pleased to have found this one, as it's the only memory leak that I'm aware of in the code, so its removal should mean the site is much more reliable.

Ah.

hmmmm...

This sounds like a case of MS SQL ....

Perhpas MySQL or Postgre would solve the problem???

Or, since I run all my servers as unix boxes, am I just being cheeky?


GB

(Not Anti-microsoft, just very careful...)

This can hardly be described as a SQL server problem - whichever database engine I used would have behaved in the same way - it was *our* code which was leaking the connections - nothing whatsoever to do with Microsoft. In fact, I've found SQL server to be one of the most reliable pieces of software I've ever used. Before we moved to the BBC, the SQL database had run for about 10 months without a reboot, and with no problems. We hadn't needed to restart SQL server once, and the only reason it had been restarted that April was because that's when we moved to a new database machine.

MS products work fine when you use them properly. It's far easier to program on the Windows platform (the dev tools are more accessible for one thing) so it's not surprising that there's more buggy software on Windows. Remember Sturgeon's law: 90% of *everything* is crap.

All too true!

On our database site we are using a php4/mysql combination which we have found very accomadating. Especially as my sysadmin has a good knowledge of perl and has found the move over to php very easy as a result.

Mind you, we use a lot of open source solutions and although have foundthem very good, have had to tighten up security immensly. One of the most common idiocies we come across is config files with DB password and username that are kept in plain text in the web rooot - ouch!

We run a 2000 server for part of our streaming, but we do find the remote administration much less friendly than Unix. Security wise we find problems on each, though finding a fix for unix is normally much easier.

As for programming, we use a mixture of workign directly on the server with Vi and working with various tools on Windows - I dont see a lot of difference, especially when 90% of our developement is essentially just hard coded. Mind you, we do tend to avoid Notepad, as it can create some interesting little format problems. TextPad and Xemacs are our mainstays.

Obviously, we are resource light compared to the beeb - we have two linked servers in the states running 4 disk raids - so we tend towards the open source solutions for cost reasons. Also, there is simply rather a lot out there to choose from!

Keep hacking.

We considered MySQL at the beginning, but its lack of stored procedures and transactional integrity put me off. SQL server doesn't cost a huge amount to deploy (especially compared to Oracle, for example) and is almost laughably easy to set up and configure. MySQL by comparison takes more work because it's not GUI driven - and being lazy I prefer to press buttons and use forms than type in reams of SQL commands to create my databases.

But we did write the site in perl originally. It was only after the private memory in use for a server which had only 16 connections to it was 800 Megabytes that I decided I'd rather move to a language and environment that actually released memory when it finished with it.

At least now, all our memory allocation bugs are our own problem. It's all about being in control.

Control is the issue all round.

If you do find yourself in a mysql role again, there are piles of GUIs around - like phpmyadmin etc. Sourceforge has quite a few listed.

Other than that, a few lines of perl would do it. What language are you using, btw?

C++