NWNx Performance question

[Darelius]

Hi, i use NWNx and used much database entrys per Character, because i use a monstercounter and some other statistics saved in the database.

Now i am scripting and thinking about "performance", because i have to make a decision between following options:

1. Database only
This option will use no "local" variables for characters, every data is requested or stored directly in the database.

2. Local variables
I save all 5 minutes the local variables (and on logout) of the character to the database, instead of permanent database handling.

Now i want to ask, if someone have experience with this matter of performance?

Normally i could imagine, that a direct database acces would use more performance than storing local variables, on the other side i could imagine, that storing local variables takes nearly same performance than direct database access, but i have no way to fine out, except to make a giant time loop with that...

Have anyone experience with that?
(yes, i know, tha database access uses a local variable, so it uses more performance, but how much more?)


...DX
_________________
Darelius, Keeper of the Elements

[virusman]

Local variables are much faster than MySQL.

[Asparius]

This is for WIndows server, I have very little experience with Linux, though I think both versions of nwserver are similar in this matter.

In my opinion - definitively local variables, especially if you use MySQL. On my machine typical MySQL query takes about 10 miliseconds (unless its buffered). Reading from local variables - about 3 microseconds + few tens of nanoseconds per each variable stored on object.

Try to store your data on different objects , so you won't have more than 400-500 local vars on each one. Avoid putting data on GetModule() - in most cases it will have much of them set already.

Set most accessed variables on object first, even if you won't use them from start of the module - and don't delete them, even if unused for some time. Delete only if vars wont be needed until server restart.

PS. Even 100 objects with 10.000 local vars on each didnt hinder server performance too much (the only difference I saw was increased memory usage).

[Disco]

I concur. We used to have a login script that checked for ban files, dm authorisation, etc by issuing individual SQL queries. I changed this to a a few calls in ModLoad that store all DM keys on a waypoint, all banned keys on another, etc. The only thing I store on Module are object references to the cache waypoints, so I can easily access those.

This change brought the number of login crashes down a lot, and made the cliententer script use a fraction of the cycles it did before.

[Darelius]

Hmm, thanks, that halps ;)

Normally i store the local variables on the place they belong to (NPC, PC or so) and the module specific variables on the module...

What i meant with "many" variables per Character is, that, for example, when he killed a monster, the kill direct into the database.

But with this information i will look to make a script, what saves all five minutes all local variables of all characters and the module...
(maybe that could case a mini-lag avery 5 minutes, i will bet on it, when there are more characters *g*


...DX
_________________
Darelius, Keeper of the Elements

[Zebranky]

If possible, design your code so that you have mostly vars that don't need to be persistent (use local vars) and a few vars that do need to be persistent (use the DB). This is the most elegant solution.

On the whole, as long as you're not running DB queries inside loops or unusually often, they won't be the main source of your lag. Don't be afraid to go directly to the DB, but do realize that local vars are orders of magnitude faster. The difference between "fast" and "really fast" usually doesn't matter, though
_________________
Win32 SVN builds: http://www.mercuric.net/nwn/nwnx/

<Fluffy-Kooshy> NWNx plugin is to this as nuclear warheads are to getting rid of fire ants.

<ThriWork> whenever I hear nwn extender, I think what does NWN need a penis extender for?

[virusman]

I also use localvars as cache for persistent data in advanced systems.

[Darelius]

Hmm, okay, so i can "explain" my system and maybe you cen help, what i have to use ;)

It should be persistent world, so the database is to save some data between server restarts and re-logins, what does not work with local variables ;)

Tha Datas are divided in some groups:
First a monster counter, so when a character kills a monster , this monstercounter is raised by one (the total server monster couner, the server monster countwer for this creature, the player monster counter total, the character monster counter total, the player creature counter fpor this type of creature and the character creature counter for this creature.

here where hundres of monsters (in planning) but only when the character kill one, the counters woll be raised.

Second the craft system read before a crafting check the crafting skill out of the database (the crafting system is an own designed) and after the check it raises the specific crafting exo and eventiually the specific crafting level, both in the database of only that character.

Third on Questings the quest the success level is saved so that a character can continue after a server restart.

Fourth the death statistic is saved that means, tha amount of death, amount of respawn and amount of xp loss is saved for server total, player total and indivifual character.

Fifth the main server quest and some special NPC variables must be saved for game purpuse (for example, if an NPC is murdered he qould be never respawn etc)

sixt in far planning a special monster growth system is planned, but that is primary local and only shoud be saved on server down.

In my opiniton, the quest objects should be directly saved in the database, the statistics "could" be stored locally BUT!!!
When i store local variables locally on the character, i have a problem:
When "onClientLeave" saves the player/xharacter datas, what happend, when the client gats a system crash / internet connecdtion loss? I think, than the character do not leave correctly!

And a save intervall all minutes for the variables could hang up the server, too..
(okay an intervall, what saves every five minutes for every specific player i could imagine...

But normally the access to the database is not much, only some data to request and some to write.

The whols Database content is only checked on "onLoad" when the module checks his database and create empty parts, if neccessary and on "onClientEnter" where the character and player is checked if they exist in database and create their entry, when neccessary.

So i thought normally, that the direct database acess could be "normally" okay. It makes a little more use, but is saver for the characters...

By the way, in my module i use hundreds or thousands of local variables on NPCs...


SWo what you think, is the performance okay with that little direct access? only on serverstart and clien enter ant leave it is a little more...


...DX
_________________
Darelius, Keeper of the Elements

[Zebranky]

[Disco]

I got a more elegant proposal for the monster count thing. Most people will kill a lot of them, so missing one or two doesn't really matter. So, what you do is this:

[Zebranky]

It sounds like he also wants to track how many times a particular monster dies, though, and a couple other things. In that case, saving it all right when the monster dies is the way to go. (I guess you could use additional counters on the module object or something, but... blech.)
_________________
Win32 SVN builds: http://www.mercuric.net/nwn/nwnx/

<Fluffy-Kooshy> NWNx plugin is to this as nuclear warheads are to getting rid of fire ants.

<ThriWork> whenever I hear nwn extender, I think what does NWN need a penis extender for?

[Darelius]

@Zebranky
i think, that is a very good idea, to get the "old" value from local and only update it local and in db, so i spare the read from the db

About the monster counter, what is count:
Every Monster is specific (monster[001-500])
And a total number is count
So total number and specific monster number for Module comoplete, player and Character (6 values) are saved.

Thats for "fun" statistic, how much monsters were slain totally on this server, by this player or character
Yes i make a difference between player and character, because if i have 8 Characters and play all 10 hours, this is count "together" in one of the lists...

@Disco
That would sound good, but some monsters are very seldom in encounter ( or only one existent) and when that is not saved, it is bad. otherwise you are right, it is nor important to know the absolute number every second, so i have to decide, what method i use.

On a massive combet, for example, that is important and i will test it (place 100 enemys and make a hellfire storm or how the epic spell is) and kill them all at once *g*

About the monster growth script i have to work on, that should be one of the best features in the module..
No ordenary "spawn" points, the monsters regrowth with the time and if they were killed "all" than the spawnpoints give no enemys anymore.
If the "Queen" (or other name for bosses) are killed, that race is entirely eleminated and never wil show up on the server and another race will take place of that strategic point...
But that script is not important now, first the module have to work correctly


...DX
_________________
Darelius, Keeper of the Elements

[Disco]

Make sure you use the mysql statement "INSERT [..] ON DUPLICATE KEY UPDATE [...]" for this, in combination with a unique combined key on character/monster/kills.

Also keep in mind that character names aren't really reliable for this, and combinations of account/character will fail if you allow for name changes on your server. You should create some stamp on each character with an unique ID. I simply place an undroppable item in each PC's inventory that has CDKEY_unixtimestamp as name. By definition unique, reasonable short, the ID can't be erased accidentally during a transfer (local vars can, unfortunately), and you can simply ask for the key if needed.

[Darelius]

Hmm, i am not shure, if i understand, what you mean.

Every Character and every Player has a unique ID in the database.

The Player IS is generated nor by "PC", its generated by the object GetModule() and dha ley is the Name of the Character and tha value is its ID.

On Server-based Chgaracters names cannot changed,

Whar you mean with the "insert"? I dont understand anything of the first sentence, sorry...

Every Player creates ist own "talbe" with player_[id]
There is on Player/cxharacter/monster/kills and
Module/monster/kills, so entry with "module" is the player total and the player/character is for character monster kills...

I dont want to put thins in the inventory, thats bad, because items can be dropped, lost or exchanged in some way, even it is nontradable etc.
And i dont work with CD-Key i work with the player account created on the Server..
When a player makes a second account, its his problem, not mine!


...DX
_________________
Darelius, Keeper of the Elements

[Disco]

Well, I'd give your db structure a good bit of thinking first. You don't want to waste space, I assume, and you don't want double info. You also want it to be easy to search in, otherwise it makes no sense to database stuff in the first place.

So, how could you best trace monster kills by a pc?

1. You need a way to make sure you can identify each PC. The easiest way in your case would be account + name. You need the combination of the two, not just one of them.

2. You want to know how many times PC X killed monster Y. This can be done by making one record for each combination. You can access this record by the unique combination of account + name + monster.

OK, how does such a table (let's call it "monster_kills") look like?

account, varchar(64) *
pcname, varchar(64) *
monster, varchar(16) *
kills, int(11)
updated, timestamp

Each record has a unique combination of the three * columns. Make a new index on those three columns with the 'unique' flag. This makes sure the database 'sees' these three colums as one identifier, or key. You can optimise your SQL query in NWN with this to one statement:

string sAccount = //player's account with SQL encoding
string sName = //player's PC name with SQL encoding
string sResRef = //monster's resref, doesn't need encoding

string sSQL = "INSERT INTO monster_kills VALUES ( '"+sAccount+"', '"+sName+"', '"+sResRef+"', 1, NOW() ) ON DUPLICATE KEY UPDATE kills = kills + 1, updated=NOW()";

When you issue this command it will look in your table and check if there's a known combination of account/name/monster, and update kills with one if so. If there's no record yet it will insert a new one (with kills=1, obviously). Keeping track of the date is always handy.

Hope this helps.