2DA to MySQL migration yields 350+ avg improvement

[chris421]

Uploaded my ERF, my performance results, and my sql script for migrating NWN2's 2DA files to MySQL here.

My initial comments about achieving a 10-12 fold improvement over the standard Get2DAString didn't factor the overhead I incurred while spamming SpeakStrings to verify the resulting data. Removing the SpeakStrings showed the true throughput appears between 150-160 microseconds per look-up. This is in comparison to the standard Get2DAString throughput of 23-76 milliseconds. Note: my test server is only running PC3200 DDR. So greater throughput should be possible.

Not sure what can be said for my method vs. the performance/stability of in-game in-memory cached hashsets on objects. And if Obsidian currently is caching their 2DA.zip by default, they appear to be doing a horrible job of it. One clear advantage of moving 2DAs to MySQL though is absolute flexibility.

[chris421]

Well whatever was done to improve/increase the 2DA cache in NWN2 1.05 sure smokes my old NWNX4 / MySQL method.

The odd runs are 200 MySQL lookups, compared with the even runs, 200 native Obsidian Get2DAString's.

What I can't figure out is why my lookups got so much worse? The xp_time.dll changed, went from MySQL 5.0.27 to .41, the game obviously changed. Could NWNX4 1.08 have gotten this much slower?

NWN2 1.05 with NWNX4 1.08

[Gryphyn]

[chris421]

[Gryphyn]

[Papillon]

I wonder what your MySQL queries look like. 200 lookups with a duration of around 700 ms seems rather slow.

You should be able to do 200 lookups in about 3.5 ms, not 700! (cf. this thread). What could be the reason for this ?
_________________
Papillon

[Grinning Fool]

I know this is an old topic, but I was wondering if anyone has re-run these tests since early 2007, when Obsidian added 2da caching to the engine? I'd expect to see a slow load the first time, and a much faster one for subsequent reads.

If not... perhaps it's worth exploring a Get2DAString hook that uses DB or local hash, thus giving benefit to internal 2da use?
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[FunkySwerve]

I recently ran a bunch of tests posted on the bioboards comparing locals to 2das, which should prove informative. Given that Biowareeans posted asking questions in that thread, in which I basically said Get2daString was unuseable for practical applications, I suspect it was the reason they improved its caching in 1.69. Any tests comparing them should be run against that beta, since I would expect to see dramatically improved performance with the additional caching capability.

[Edit]
Oh, I should also mention that we use the hash plugin for storing much of the data in OldManWhistler's spells data setup, and for storing data for our item updater. It's nice and speedy, as well, and iirc I ran some compares against it. I'll try to hunt down the thread.

Funky

[Mithon]

I'd also be very interrested in seeing those results.. I would love to know what the local vs db vs 2da lookup times on current versions of nwnx4 and nwn2 are.

[FunkySwerve]

http://nwn.bioware.com/forums/viewtopic.html?topic=566175&forum=47&sp=0


Figures aplenty. Sorry, this had slipped from my mind.

Funky