[Duckington]

Hi there,

I'm running a website at the moment (a game website) and I've run into a slight...issue, mostly of my own fault through lack of thought and design.

Basically, I have my system log every action a player does (pretty much anything they click) so that in case there is a weird bug I can't work out, or incase they try to cheat in some way, I can look back at what they have been doing...however in doing this my log table is already up to 80,000 rows and about 10MB in space after the game only being live for about 32 hours... So obviously as days and weeks progress this is going to get bloody massive and take longer and longer each night to backup..

I do have a script which goes through and deletes old records after a while, but with so many records after only 1 day, i'm not really sure how to progress with it. Obviously I want to keep logs for a while in case i need to go back..

I could truncate the table every night, since i'll have saved a backup copy, but then if i need to go through it all, i'd have to go through each backup which would be a pain... I'm not sure at the moment.


Basically i'm just looking for some advice some people who have had a similar issue and what you think the best way to deal with it is?

Cheers.

[JackOfAllTrades]

80000 rows doesn't seem too bad. I think I might schedule the cleanup for that on a weekly basis.

[AdaHacker]

At the site I work for, we solved this very problem by using a circular logging table. Basically, it stores only a given number of rows and just overwrites old ones when new ones are added. That allows us to always have the most recent rows and requires no cleanup. The technique is described here.