Alleviating some concerns with database record handling

[TeoTwawki]

Currently we are inconsistent when or even if we prune old records. For example the auction house records remain forever and delivery box records are removed as soon as both in game characters no longer have need for them so as a server operator its near impossible to look for an item players claim was lost in the mail.

In both these cases I think it would be more ideal to have a certain retention period, and then prune the oldest unused records. I doubt retail can find auction records from 5 years ago after they've been pushed off the visible in game history (if they are within the last 10 and thus visible, the records are still current).

I know it would take many players and very long time for the AH table to even get moderately large and that this wouldn't really be a problem in my lifetime basically, but might be nice to see some sort of uniform policy for a theoretically scalable "big server" eventually so old records would exist only in the backups* where they belong, instead of bloating the table indefinitely without manual intervention.


*Haven't got backups? You shouldn't even be running a server then.

[nasomi]

It'd be pretty simple to write a query to tack on to a successful bid to knock out anything more than 10 records, since the current history is 10. Otherwise it'd be fairly simple to write a one time thing you could run at your leisure. The only odwn side of that is that if it's large, it can lock the table and make the server hang until it finishes. My AH table is at 570k records, after 2.5 years. However, it's still performing at lightning speeds without issue.

On the contrast, char_inventory is 605k records. Partitioning the table would make sense. There's ways to optimize them that would prevent any slowdown.

[TeoTwawki]

AH just made a handy example. Right now we have no real rules about when we should or shouldn't retain things. Mnaual intervention to either prune or duplicate record that DSP normally obliterates instantly is far less than optimal.

[Delaide]

Like bcnms and "record is 1 second by !"?

[TeoTwawki]

Like bcnms and "record is 1 second by !"?

Thats actually because someone didn't invoke the correct parameter info in the BCNM script very fixable. retail always made me sad by wiping it out every conquest tally though.

[kjLotus]

i think we just don't save the clear time anywhere

[TeoTwawki]

i think we just don't save the clear time anywhere

Theres a column for it in bcnm_info that I think we haven't actually been using. I recall seeing someone store+read the clear times to a server variable in some of the bcnm scripts.