Saving players stats on changes or in timer? [MySQL]
#1

Hi guys,

I want to start an discussion about saving player stats.What you think,is better to save player stats immediately(When there is any change in player stats) or in timer(For example every 10 minutes to save all player stats).

1) Save accounts in timer
Code:
UPDATE `users` SET `column1` = '%i',`column2` = '%i', `column3` = '%i', `column4` = '%i',`column5` = '%i' WHERE `ID`='%i'
2) Save column on which we have changes..
Code:
UPDATE `users` SET `column1` = '%i' WHERE `ID`='%i'

UPDATE `users` SET `column2` = '%i' WHERE `ID`='%i'

UPDATE `users` SET `column3` = '%i' WHERE `ID`='%i'

UPDATE `users` SET `column4` = '%i' WHERE `ID`='%i'

UPDATE `users` SET `column5` = '%i' WHERE `ID`='%i'
Now I'm use both option :/ .Half of player stats I save in timer(for example money which changes more often) and half I save on changes(Which changes rarely;for example Level which changes very rarely).
I have Roleplay mode.

What you use? (Sorry for my bad english)
Reply
#2

I use a timer which saves players stats every 10 minutes with the UPDATE query. I prefer to do in this way.

I don't care if something changes every second or very rarely, you can never know when server/player will crash (if he will). Also i don't see any increase of memory usage while doing this, so, it's ok for me.
Reply
#3

Using a timer is inefficient. Imagine a timer running every 10 minutes on a 100 pb server.

Save when you change something in the script.
Reply
#4

Save spontaneously.
Reply
#5

Quote:
Originally Posted by Dignity
View Post
Using a timer is inefficient. Imagine a timer running every 10 minutes on a 100 pb server.

Save when you change something in the script.
it would be better to have per player timers instead of looping through everyone connected. timers are not resource hogging features lol they work well and you can have a lot of them. heck, thousands of timers doesn't have much of a performance hit.
Reply
#6

The best way is to just save the results immediately when you change the value of any stats (money, weapons, etc) via a threaded query that doesn't cache any results of the query, so the script doesn't have to 'wait' to finish the query function or even 'watch' it through a callback.

Quote:
Originally Posted by Dignity
View Post
Using a timer is inefficient. Imagine a timer running every 10 minutes on a 100 pb server.

Save when you change something in the script.
If you are doing it the right it way, using threaded queries without caching the results, it doesn't matter that you sync 100 players every 10mins with just one loop.

Quote:
Originally Posted by Garr
View Post
it would be better to have per player timers instead of looping through everyone connected. timers are not resource hogging features lol they work well and you can have a lot of them. heck, thousands of timers doesn't have much of a performance hit.
It doesn't matter if you loop through them when you use threaded queries without caching the results, as i also stated above.
Reply
#7

Why don't you save the data once OnPlayerDisconnect is called? I don't get the point of all this..
Reply
#8

Quote:
Originally Posted by TheToretto
View Post
Why don't you save the data once OnPlayerDisconnect is called? I don't get the point of all this..
Well, it would be horrible if you lose money or any other stats when the server crash... With this method the change of losing data is minimized. Also stats are more up-to-date on your stats pages or your User Control Panel, if you have one of those running.
Reply
#9

Just use this..

pawn Code:
public OnGameModeExit()
{
   for(new i; i < MAX_PLAYERS; i++)
        OnPlayerDisconnect(i, 1);
    return 1;
}
Reply
#10

Quote:
Originally Posted by TheToretto
View Post
Just use this..

pawn Code:
public OnGameModeExit()
{
   for(new i; i < MAX_PLAYERS; i++)
        OnPlayerDisconnect(i, 1);
    return 1;
}
That won't work when the server crash or you force close the server, OnGameModeExit won't be called.

Also described on the wiki of OnGameModeExit (https://sampwiki.blast.hk/wiki/OnGameModeExit)
Quote:

This callback will NOT be called if the server crashes or the process is killed by other means, such as using the Linux kill command or pressing the close-button on the Windows console.

Reply
#11

Well your server isn\'t going to crash the whole day, just add a command for the server owner to save all players\' stats. Without kicking them or calling OnPlayerDisconnect, then... Shutdown your server;

If your server is really really unstable, just put a global timer, not per player, which saves players data every 20 minutes, or less. Who give a fuck if you lose 10$ because you bought a condom in the 24/7, I mean, you won\'t lose much data, you can\'t get $2.000.000 in 10/20 minutes...
Reply
#12

Quote:
Originally Posted by TheToretto
View Post
Well your server isn\'t going to crash the whole day, just add a command for the server owner to save all players\' stats. Without kicking them or calling OnPlayerDisconnect, then... Shutdown your server;


If your server is really really unstable, just put a global timer, not per player, which saves players data every 20 minutes, or less. Who give a fuck if you lose 10$ because you bought a condom in the 24/7, I mean, you won\'t lose much data, you can\'t get $2.000.000 in 10/20 minutes...
Lottery.



Regarding the topic:

I would create a ten minute timer after the player logs in and also update the database whenever it\'s necessary.

i.e. The player unlocked an achievement. Or he got into a new job. Or he wins the lottery.
Reply
#13

Quote:
Originally Posted by Ermanhaut
View Post
Lottery.



Regarding the topic:

I would create a ten minute timer after the player logs in and also update the database whenever it\'s necessary.

i.e. The player unlocked an achievement. Or he got into a new job. Or he wins the lottery.
The lottery isn\'t ran every minute, at a maximum it\'ll be twice a day, regarding the script you\'re using, plus, for the lottery, it\'s something obvious, so if you connect, it\'ll be the first thing that you notice, then ask for a refund. Concrete things may need queries.
Reply
#14

Quote:
Originally Posted by TheToretto
View Post
Well your server isn\'t going to crash the whole day, just add a command for the server owner to save all players\' stats. Without kicking them or calling OnPlayerDisconnect, then... Shutdown your server;


If your server is really really unstable, just put a global timer, not per player, which saves players data every 20 minutes, or less. Who give a fuck if you lose 10$ because you bought a condom in the 24/7, I mean, you won\'t lose much data, you can\'t get $2.000.000 in 10/20 minutes...
I would, and alot of other players.

Because in 10 minutes, you could buy a house, apply a different interior, buy some furniture and place it, buy a house-vehicle, repaint it and apply tuning components.

Whenever that\'s done and something happens before your timer saves the data, all that is lost.

By the time you relog, someone else might have bought that house.

I would never come back to your server because I know playing there doesn\'t guarantee proper data-saving.

Players don\'t know if you save every 10 minutes, only on disconnect or if you save whenever data changes.

They DO care if they lose data and some will be furious and never come back, even if you reimburse them.

They just see that some data hasn\'t been saved properly and will distrust your server because of unreliable saving functions.



In every MMORPG I\'ve played, data is saved whenever something changes.

I\'ve never lost alot of progress, even when timing out, server crashes, lagging followed by a disconnect, whatever reason, simply because they save whenever data changes.

You could gain a level or 2, do some quests or turn them in, go to another map, all that in less than 10 minutes.

I\'ve never respawned in the previous map, having to turn in quests for the second time or regain my 2 levels I gained right before the crash/timeout/whatever.



Also, only saving when the player disconnects will also guarantee you\'ll lose players.


Some players play for hours at a time and if something unexpected happens, his entire progress hasn\'t been saved.

A stupid hacker can mess up your server, making it crash and you\'ll have no data saved for hours on end for multiple players.


Even a timer which saves everything at once is usually not done.

This timer would save EVERYTHING, even sex, class, skin, name (if you allow name-changes), email, ... (things that very rarely change, if they ever do).

Would you really generate a huge query with hundreds of values that must be saved, even when the possibility exists that some players afk for hours? Then you would send a huge query to MySQL which in the end does absolutely nothing, because there is nothing to be updated/changed.


This is a pure waste of processing power, both for your server which must construct the entire query, but mostly for MySQL, which must break down the entire query, find every value in the database, check if the provided value is valid (you can\'t store an int in a float column so MySQL checks that), and eventually overwrite the value one by one (it won\'t overwrite the value if it hasn\'t changed).


Surely pc\'s nowadays have fast processors, but that\'s no excuse to be a lazy programmer.

Creating a huge query that covers all values and put it in a timer seems a smart thing to do (it\'s at least convenient), but it\'s just being lazy.


Having alot of smaller queries which are executed whenever something changes save you alot of trouble.


One bug in your huge query and NOTHING gets saved.

One bug in one small query only messes up that single query while keeping everything else intact.


One huge query is also alot more difficult to bug-fix and find the problem.


Also, saving only when something changes prevents queries to be sent when they\'re not even needed, saving alot of processing power.


A game\'s render engine also only renders whatever is being displayed on the screen.

What if that engine would render the entire game world at once, from which only 5% would be shown on your screen? It would also be convenient for the programmer and it would guarantee the required models would be rendered, but it\'s not the proper way to create a game.

Your game would lag immensely and is impossible to play.


Only do whatever needs to be done at the time it needs to be done.

Don\'t do unneccessary stuff.

This applies for everything you do, not only programming.




Do you paint your living room every month just in case there "might" be a spot, crack, some dirt, whatever on your wall, wasting expensive paint and alot of time in the process?

Or do you just repaint your living room when it\'s really time to paint it (you don\'t like it anymore, or there are too many spots/cracks/whatever), which would be every 10 years or so?


Do you dismantle your car\'s engine every month to prevent wear and tear, applying new grease to every moving part to prevent anything happening soon, even when nothing\'s wrong?

Or do you only do the required maintenance as told by the manufacturer (change oil every year) and go to the garage whenever something\'s broken?


If your teacher requires you to read page 55 (in a 200-page book), do you read the entire book, including page 55, just to be sure you\'ve read it, even when the class for that day only handles page 55?

Or do you simply do whatever she wants you to do, and read only page 55?


Do you go to the toilet every 10 minutes and sit down, waiting for 2 minutes just in case you would need to build a huge pile of brown stuff, making you sit on the toilet over 100 times a day without even the need to do poopoo?

Or do you only go to the toilet when nature calls?


In all cases, I\'m pretty sure you do the second option.

Why would you do the first option in programming?
Reply
#15

Damn, he said it all, don\'t use Timers, I mean they\'re bad atleast for me and he said everything ( the post above )
Reply
#16

Now, instead of saving player data only when player disconnects, why not save it whenever the player DIES.


I mean, in most servers, player\'s data is significantly changed when they die (maybe not for RP servers). So when the player dies, save their data.

pawn Code:
public OnPlayerDeath(playerid, killerid, reason) {
    // add kills and change variables\' data

    // save data in the end
    mysql_tquery(...);

    return 1;
}


Moreover, for security reasons its better to have a interval check between two deaths, so if a Cheater spams your OnPlayerDeath for fake kills/deaths, your server won\'t crash from MySQL query overload!

gettime() would do it for you. Before saving data, check if last save was not very recently and then forward.

pawn Code:
new lastSaveTimestamp[MAX_PLAYERS];

public OnPlayerDeath(playerid, killerid, reason) {

    new currentTimestamp = gettime();
    if (currentTimestamp - lastSaveTimestamp[playerid] >= 600) { // 10 minutes interval

        // save data if last update was 10 minutes atleast
        mysql_tquery(...);

        lastSaveTimestamp[playerid] = currentTimestamp; // update player\'s last save time to current timestamp
    }

    return 1;
}


But the above case is only for data which is significantly changed, other data like "AdminLevel" doesn\'t require to be updated in OnPlayerDeath. You should update AdminLevel whenever its changed in script, i.e. Update query for AdminLevel in your /setadmin command.


The whole idea about using Timer to save stats is nonsense.
Reply
#17

my opinion it better to save data when value is modified
Reply
#18

Usually you want to save on each change, even money. But some things like online time, i would save on timer basis and call single query (well a few queries are not problem as long they are not for each player at same time).
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)