Tuesday, September 15, 2009
“Okay, I'm deleting records over a certain age, so the time needs to be … ”
It's going slowly.
R called and said there was a problem with the current site—that the server was running out of memory trying to serve up a page with all the people who took the questionnaire. I took a look at the existing, undocumented overwrought PHP framework and it's doing a database query (which is returning at least 10,000 records) and using XSLT to transform XML and the database results into HTML.
All in memory.
So of course it's running out of memory.
We discuss the issue and there are two solutions: paginate the output, or delete enough records so the page can be built in memory. We both took one look at the options (diving into 300,000 lines of code and modifying it, or a few lines of SQL to prune the database) and said “delete the records!”
Now, the database doesn't use the SQL
DATETIME to store when the
questionnaire was filled out. No, that would be too easy. No, it uses an
INT to store the date, using the number of seconds since
January 1st, 1970—it being MySQL under Linux (it's a Unix thing). So
I have to figure out the number of seconds from January 1st, 1970
as of three months ago.
Fortunately, it's easy under Linux:
[spc]lucy:~>date -d '3 months ago' +%s 1245129383 [spc]lucy:~>
So anything older than that gets deleted.
mysql> DELETE FROM questionnaire WHERE answer_date > 1245129383; Query OK, 2344 rows affected (0.10 sec)
Um … I was expecting over 8,000 rows to be … oh XXXX! I got the conditional backwards! It's supposed to be less than that number of seconds, not greater, since it's a date!
Backups to the rescue.
I'll be glad when this is over.