2DA to MySQL migration

[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 Get2DAString's throughput of 23-76 milliseconds.

UPDATE: Performed some additional file IO profiling (via Filemon) of NWNX4+NWN2 Server. Even with my modified global scripts the server still hits some of those zip files (under .\data) pretty damn often (1000s of times at startup). "Get2DAString" is one of those embedded, immutable engine functions that can't be overridden. So as much as I'd like my function to divert all 2DA look-ups, I don't see that happening. Also remember changes made to MySQL tables don't automatically modify the source 2DAs. You'll have to export your MySQL changes and put them into your server's .\override folder to catch those embedded calls. Probably would require a NWN2 Server restart as well.

So my function is great for homebrewed scripts that require speedy access to a lot of data. But if you're looking to just speed up native NWN2 Server functions you may be better off setting up a small RAMDisk and divert your .\override folder to it. Prior to starting your NWN2 Server I would copy the smaller of the .\data zips into it (ideally extracted).

Don't ask me why NWN2 Server hits files like Sounds.zip, Music.zip, NWN2_Materials.zip, VO.zip, etc., even after I've intentionally disabled sound in the server's INI. There appear to be 100s of MB of client-specific files, wholly unnecessary for server operation, being read in at server startup. Definitely do not copy those to RAMDisk. On my server I'm experimenting with the zips I can safely remove altogether to decrease file IO and footprint.

[Papillon]

Nice idea ! If someone with a little debugging knowledge would hook Get2DAString, you could get rid of the calls at startup as well.
_________________
Papillon

[chris421]

Wow, hadn't even considered a plug-in option for hooking Get2DAString.

You think it's possible?

I'm no developer. Wouldn't even know where to begin unfortunately. But I gather the premise would be to monitor the server process; await, intercept, and modify the request, returning the new result via some module stored string--to the original requesting script???

I guess that's what you guys do here. Cool.

[Papillon]

Yes, you would hook the function and then intercept all calls to it, or maybe even let the original function run if no row was found in the database. Usually, doing this takes between 2 and 4 days, so while it's not too hard to do, it's quite some work .
_________________
Papillon

[chris421]

There seem to be as many opinions as people regarding the "when", "how often" and "to what limits" 2DAs are cached at server startup (and afterward, per request). See Bioware thread here.

Assuming Bioware/Obsidian are currently employing some 2DA caching logic, if someone tackled this plug-in, hooking Get2DAString would also have the added benefit of reducing fragmented server process memory.

The 2DAs would already be in memory on the MySQL side. Allowing the additional NWN2 server-side caching would only defeat the purpose of having dynamically updateable tables.

Really wish I could contribute to this more.

[Gryphyn]

[Tenkawa]

Any interest in backporting this to NWNX2 if it works?

Tenkawa

[Grinning Fool]

Another alternative to consider -- a specific 2da processing plugin that handles it in the same fashion as the toolset: indexed arrays. Nearly all lookups are done by index (as opposed to value); this would mean even the largest files wou ld be readable faster than either a database, or native 2da can offer.
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.