Question about saving to DB (check if update is needed or not)

[SYJourney]

Hello Ragezone,
so I was wondering while modifying stuff, does it make sense to save in a boolean wether to update certain DB stuff or not? Can this make my server more stable or is the difference neglegible?

For example:

if (updateOmok) {
                ps = con.prepareStatement("UPDATE minigamestats SET playedomok = ?, omokwins = ?, omokties = ?, omoklosses = ? WHERE accid = ?");
                ps.setBoolean(1, playedOmok);
                ps.setInt(2, OmokStats.get(StatType.WINS));
                ps.setInt(3, OmokStats.get(StatType.TIES));
                ps.setInt(4, OmokStats.get(StatType.LOSSES));
                ps.setInt(5, accid);
                ps.executeUpdate();
            }

and:

case OMOK:
                if (playedOmok) {
                    oldStat = OmokStats.get(type);
                } else {
                    playedOmok = true;
                }
                
                OmokStats.put(type, oldStat + 1);
                updateOmok = true;
                break;

I was wondering about this because you wouldn't naturally assume that a player has played omok/matchcards everytime they change channel or log off. So why program it that way?

Another thing was that I recently read on a forum (MapleRoyals, the server I play) that they recently have lots of issues with lag mainly because of the database. Alot of newer players joined and now there's too many queries happening at once (altough it mainly seems to be merchant-related).

Well anyways, would be nice if someone could give some advice on this.

Edit: Forgot, I naturally also have:

public void saveMinigameStats(int accid) {
        if (!updateOmok && !updateCards)
            return;
        
        try {
            Connection con = DatabaseConnection.getConnection();

Edit2: Another thing, in connection with this. I've read on some sites (while looking for this) that another problem is not doing rs.close() and ps.close() after accessing the DB. I've noticed that the source (Moople) I'm using is actually missing the close functions in alot of places. So is it a good idea to add these everywhere or is there a reason why it's not used?

[MentaL]

[Linkerzz]

Hello Ragezone,
so I was wondering while modifying stuff, does it make sense to save in a boolean wether to update certain DB stuff or not? Can this make my server more stable or is the difference neglegible?

With the primary goal of being stable in mind, this could either:
Cause a ton of crashes, outweighing the pros of the final product. (people not saving correctly, reporting dc, channel/server crash from recv/send of minigame packets..)
Or it could work fine, and in theory this concept would reduce the memory being used on the host machine. Regardless, of my opinion..
I cannot say for sure whether it would even be a notable difference.

I suggest you to read through these two threads:
https://forum.ragezone.com/f428/sour...-leaks-642562/
https://forum.ragezone.com/f428/perf...-guide-782839/

I was wondering about this because you wouldn't naturally assume that a player has played omok/matchcards everytime they change channel or log off. So why program it that way?

Another thing was that I recently read on a forum (MapleRoyals, the server I play) that they recently have lots of issues with lag mainly because of the database. Alot of newer players joined and now there's too many queries happening at once (altough it mainly seems to be merchant-related).

This is a very interesting question you've brought up, although the difference could be so small it would only make an appearance if people were.. say.. spamming the register page, creating/deleting many characters in game at once, people spamming packets or what have you..
Private servers were not meant to have many players online at once.. Many of us download sources for the simple reason of testing functionalities, and to learn the code behind it.
To support many players, maplers have had to code the functionality to supporting it.. On top of the current source instead of reinventing the wheel every time.. Many many MapleStory emulators die off long before they even see multiplayer support.. /ramble
https://forum.ragezone.com/f427/fixi...tions-1060664/

Might have something to do with with memory leaks, might not.. their database structure might be totally fucked.. Their source could have huge portions of commented out code, where the admin simply didn't know what to do.. It's best not to compare your source to other servers unless you have the actual code to compare with.

[SYJourney]

Thanks for linking the two threads. I've read and applied the memory leaks guide, but didn't know about the 'how to measure memory' one.
The thing about the whole saving to DB everytime a player changes channel...it's not only omok/matchcards. The full list of things that are very likely not upgraded after ccing could be extended too:

- Telerocklocations
- Savedlocations
- Skillmacros
- Skills
- Playerstats
- Buddylist
- Areainfo
- Keymap

So yeah, you can probably tell why I'm so sceptical about saving everything after every cc/log off.

[Fraysa]

So why program it that way?

Because OdinMS. Unfortunately, over the years, people mostly cared about updating their servers rather than putting time into fixing anything similar to this. Therefore, there are tons of cases of inconsistency, memory leaks, etcetera. I wouldn't spend time on it if I were you.

[SYJourney]

Because OdinMS. Unfortunately, over the years, people mostly cared about updating their servers rather than putting time into fixing anything similar to this. Therefore, there are tons of cases of inconsistency, memory leaks, etcetera. I wouldn't spend time on it if I were you.

So, I get the first part of your post, but why shouldn't I spend time on it?

Edit: Just to make it clear, I'm being serious with that question, is there any reason why I shouldn't spend time on it?

[DancesWithOdin]

Thanks for linking the two threads. I've read and applied the memory leaks guide, but didn't know about the 'how to measure memory' one.
The thing about the whole saving to DB everytime a player changes channel...it's not only omok/matchcards. The full list of things that are very likely not upgraded after ccing could be extended too:

- Telerocklocations
- Savedlocations
- Skillmacros
- Skills
- Playerstats
- Buddylist
- Areainfo
- Keymap

So yeah, you can probably tell why I'm so sceptical about saving everything after every cc/log off.

In terms of this, I can see many situations where if you CC/relog that some of those listed will need to be updated more frequently than not. I'll elaborate a bit here for each:
-> Teleport rock: this is probably one of the only one on your list that doesn't really need to be saved since literally no one in private servers uses teleport rocks.
-> Saved location: this is important since if you CC in the Free Market or any other map that stores this (very frequent), it uses the saved location and if it cannot be found, it will use a default, incorrect warp (which I believe is Perion) or it just won't do anything.
-> Skill macros: this is probably less common, but still required/advised to save since it can be changed every time a new skill is gained.
-> Skills change very frequently and it would be silly to not update them.
-> Player stats change even more frequently compared to skills since a handful change pretty much constantly (hp, mp, exp even if they don't necessarily level; and, if they do level, it's far more).
-> Buddy Lists are probably a little less frequent but they definitely fluctuate more than you would expect.
-> Area Info is useful for other things that require it to be saved. I don't think private servers really use this information that much so it is far less critical.
-> Key maps change about as frequently as skills do since when you gain a new skill, it usually gets attached to your hotkeys immediately.

When you have a service running with many people online doing really different things at a time, you will have to update pretty much everything and anything they could be doing at frequent intervals. Keeping track of this requires way more resources than just doing a uniform update process especially in the large scale. Imagine having about 20-30 people online doing very different things. If you have some sort of mapping system that keeps track of things that have changed, you'll eventually find that over the course of time, you will have that group of players be required to update at least 70% of those listed when they relog/CC. So you end up with something that's fairly redundant if not designed for very specific data that is incredibly costly to save (which is actually completely irrelevant since the amount of data transactions this software makes to the database is pretty small).

Also, you could effectively ignore saving on CC if you serialized the entire character and sent it to the target channel instead of fresh loading it every time the player CC'd, but the cost would probably be similar if not more expensive and usually not worth bothering with. In most Odin-based sources, this is negated since it is generally as easy as just handing it off to another channel instance (since they aren't necessarily separate entities). You also have to implement a fallback system if they disconnect during changing channels if you want to do something like this.

More relating to your original post:
Even if you haven't played Omok ever on the official server, you still have 2000 points, 0 wins, and 0 losses:

Spoiler:

So the data is still saved regardless of whether or not you've played Omok before. It'd be pretty silly not to since it doesn't really require much space and the cost isn't much at all.

I think the underlying thing I need to say is that systems that check to see if something absolutely needs to be updated into the SQL database are usually more expensive to the server software in the long run than just doing a full update.

Regarding your experience on Royals, latency itself has many causes and is generally not database related. The database isn't actually bad for default Odin; and, the only problem that has come up regarding the database since Odin is the way people decided to handle connections timing out with autoReconnect. The thread link was provided above by Linkerzz regarding the fix by BlackRabbit as well as some good input from Snakeday.

If you want to fix something that's fairly critical, you'll want to debug TimerManager and why the backing STPE stops executing since that's definitively the biggest reason why Odin-based software cannot scale incredibly well. Also, JVM tuning is something to look at as well as well if you want to minimize latency due to garbage collection pauses.

@ Most recent post: If you want to work on it, do it. He recommends against it since it's a huge time sink regardless if you're experienced or not. If this doesn't bother you, then just do it.