Performance: Direct DB or Local Caching?

[Mithon]

Primer: http://www.nwnx.org/phpBB2/viewtopic.php?t=17&postdays=0&postorder=asc&highlight=benchmark&start=0

Asmodae aptly points out that SQL is a disk-based system. It is robust, crash-resistant. Local variables is a memory-based system. You'll experience data loss on a server-crash. Obviously, accessing data in main memory is a lot faster than accessing data on disk (even when disregarding the transfer from the SQL-server to NWN.

To me, these are the two basic approaches that appeals the most. For both approaches we'd load all non-spacious data (like xp, debt etc) (unlike loot-tables etc) OnClientEnter. When this data is changed you can either...

1) Store the data directly to the DB when it's changed.

2) Store the data locally, and update the DB according to a time-interval, significance of the change or other heuristic, once it's changed.

Approach 1 makes your data very robust, but is a bit slower than approach 2. Is very clean and simple.

Approach 2 is very fast, while still keeping some robustness, but can cause data-loss on server-crash. Though if you have a good write-to-DB heuristic, shouldn't cause significant data-loss.

So, when should I worry about the loss of performance for approach 1? After all, in terms of heavy calculations, disk speed vs memory speed is very important, but nwn scripts are hardly that calculation intensive. It's rather about a lot of transactions (in some cases). But how much is a lot?

If there's 3 parties of 5 each nuking groups of mobs, causing 24 deaths each, every combat round (6 secs), that'd add up to (24 / 6) * 5 * 3 = 60 calls to myGiveXP which updates the pending xp value of my pc's. So, how fast is writing 60 times to the database compared to writing to local variables? Will I feel this on performance?

[xaltos]

60 calls/ 6 sec just for xp ?

Is this really necessary ? Sure the player will not loose any xp in the case of a server crash , but this kind of information can be stored once / min or all 5 min and the DB don’t need an update each sec.

But anyway, I think you will not notice the extra work for the DB.
10 calls each sec should be doable without bigger problems.
_________________
xaltos

NWN2_Audolo

[Mithon]

Actually 60 calls every second. Note that the scenario I made was highly extreme and would likely never happen except for testing purposes.

Even so, the scenario was made to illustrate the problem. And the problem is basically this, do I do what you suggest, basically using approach 2, or do I rely on hcr2's h2_SetExternalInt function, which utilizes approach 1, and would cause those 60 calls.

My gutt-feeling told me I should go with approach 2 like you suggest, so that's what I've started implementing my systems like. But I had to wonder if I was wasting my time since there was no support for this in hcr2, and people were speaking so highly of DB efficiency, and getting from local variables being insignificantly faster than getting from the DB. Hence this thread.

As for hcr2, Binary tells me they did approach 1 simply because they don't have any scenarios like this in their core system nor in any of their current subsystems.

So, unless somebody tells me I'm making a mistake, I'm going to stay on target, and continue implementing frequently used stuff like xp stored cached locally.

[Grinning Fool]

Caching locally makes the most sense. We keep about 15-20 pieces of information about our PCs stored on them as local variables; and we save that to DB every two minutes or so, or upon area entry (whichever is first), or upon major event (such as PC death). If a crash happens, at most two minutes of info are lost -- no major deal.
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[FunkySwerve]

Pretty much what Grinning Fool said. I don't store so much that needs updating, but I do write all the stuff we do track from db to locals for faster access on login. When a var is written (infequent compared to what you are doing) it's written to both db and local.
Funky

[Mithon]

Agreed. Thanks for feedback.

I'll stick to what I'm doing then. Hmm.. might need to streamline the system by some more general calls and such maybe. Well.. thanks.

[Disco]

We count area use in our server. Some areas are used a lot, others barely get a visit. It's not really enlightening to know if an areas is used 3000 times a week or 4000, but it's interesting to know if it's 12 times, 50 times, or 3000 times.

For this I made a little script that counts the first 10 visits, and from that moment on it caches every next visit until another 10 visits is reached. Those are stored and the cache is emptied. This way I get a reliable count of low use areas, and less database hits for areas that get a lot of use anyway.

This is just a hint for other uses of local caching, mind you.