Alleviating some concerns with database record handling

Post Reply
User avatar
TeoTwawki
Developer
Posts: 523
Joined: Mon Jul 15, 2013 9:50 pm

Alleviating some concerns with database record handling

Post by TeoTwawki » Sat Apr 09, 2016 8:58 am

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.
Hi, I run The Demiurge server.

Code: Select all

PLS USE [code]CODE TAGS[/code] WHEN POSTING CODE
Image
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. - Martin Golding
DO NOT PRIVATE MESSAGE ME ABOUT BUGS

nasomi
Posts: 141
Joined: Wed Feb 13, 2013 8:51 am

Re: Alleviating some concerns with database record handling

Post by nasomi » Sat Apr 09, 2016 8:24 pm

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.

User avatar
TeoTwawki
Developer
Posts: 523
Joined: Mon Jul 15, 2013 9:50 pm

Re: Alleviating some concerns with database record handling

Post by TeoTwawki » Fri Apr 15, 2016 6:09 pm

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.
Hi, I run The Demiurge server.

Code: Select all

PLS USE [code]CODE TAGS[/code] WHEN POSTING CODE
Image
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. - Martin Golding
DO NOT PRIVATE MESSAGE ME ABOUT BUGS

Delaide
Posts: 472
Joined: Sat Jun 14, 2014 8:58 am

Re: Alleviating some concerns with database record handling

Post by Delaide » Mon Apr 18, 2016 1:03 am

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

User avatar
TeoTwawki
Developer
Posts: 523
Joined: Mon Jul 15, 2013 9:50 pm

Re: Alleviating some concerns with database record handling

Post by TeoTwawki » Sun Apr 24, 2016 10:27 pm

Delaide wrote: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.
Hi, I run The Demiurge server.

Code: Select all

PLS USE [code]CODE TAGS[/code] WHEN POSTING CODE
Image
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. - Martin Golding
DO NOT PRIVATE MESSAGE ME ABOUT BUGS

User avatar
kjLotus
Lead Developer
Posts: 1813
Joined: Sun Jul 22, 2012 2:16 pm

Re: Alleviating some concerns with database record handling

Post by kjLotus » Sun Apr 24, 2016 11:23 pm

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

User avatar
TeoTwawki
Developer
Posts: 523
Joined: Mon Jul 15, 2013 9:50 pm

Re: Alleviating some concerns with database record handling

Post by TeoTwawki » Thu May 12, 2016 5:11 am

kjLotus wrote: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.
Hi, I run The Demiurge server.

Code: Select all

PLS USE [code]CODE TAGS[/code] WHEN POSTING CODE
Image
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. - Martin Golding
DO NOT PRIVATE MESSAGE ME ABOUT BUGS

Post Reply