Performance, which DB solution is fastest?

[Khamul85]

I'm going to make myself a new PW/RP server, and I wonder which database is best performance wise. The standar NWN buildt in database, MySQL, MySQL Lite (Correct me if I'm wrong but seems just to be a text file so can't be special fast??) or any other if there are. The database will mainly be used when a player logs on and off, when they're on will values be temporarly be saved in variables (Like quest status, or subrace to character), any unforseen bugs with this??

Using MySQL do you also have to nice feature of being able to use it with other programs supporting MySQL, like making a PHP page showing who's online.

[Grumalg]

The stock nwn database is notoriously slow on writes and subject to corruption.

With other databases it is much more a matter of how you use the database that controls the performance.

If you use the simple way (i.e. the get/set persistant stuff) you limit your own speed. This is because every SetPersistant call uses two trips to the DB and every GetPersistant call uses one. If you wind up useing several persisted values in a single script Get/Set Persistant costs a lot of DB calls. Since each DB call is quite slow compared to actual script instructions you want to use as few as possible.

If you know enough to use the SQL language and the low level nwnx functions you can do much better. For example, if you needed to store 5 values you can write a SQL command that can do that in one DB call. It would take 10 DB calls to do the same thing with SetPersistant (two per value stored). Of course, getting this better level of performance requires an understanding of SQL, DB table design, and proper indexing techniques.

SQLite is often touted as faster than MySQL, but in reality the differences are small and much more dependant on how you use it than absolute DB engine speed.

--- Grumalg ---

[Khamul85]

Thanks for the reply! Seems MySQL will do just nicly..

I already got a mysql DBMS running up on the server I'm running. I'd perfer having the control of select and insert settings myself so i can control how information gets stored and fetched.. So what I'm really looking for is if NWNX has libs and functions for doing SQL settings, I guess they have ..

Most of the persistent data will be fetched and written when a player logs off or on the server.. Perhaps it would also be wise to have a interval that saves persistant data to the DB like evry 10 min incase the server crashes so only 10 min at max gets lost..

[Grinning Fool]

It's also worth noting that MySQL was proven a significantly slower than the NWN native database for read operations; and significantly faster for write operations. So it may depend on what you need to do -- if you're just looking to store a few persistent values which you read far more often than you write, use native. If you do more writes than that, and/or need the flexibility of relational database system, then MySQL would be best.

(Sorry, I do not have a source at the moment -- it was in a benchmarking thread I was following a while back, when a couple of folks were running large numbers of reads/writes to determine this. )
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Papillon]

Significantly... that depends on what you'd call significant, but I'd say no. There are some measurements in the ODBC2 docs:

http://nwnx.org/index.php?id=doc_odbc2

Other than that: What Grumalg said. MySQL is ony slower, if you do not use the advantages of SQL. Retrieving one value with one statement is not what SQL is about, so the results are not really comparable. But even in the worst case, it's just 10ms vs. 22ms (500 calls).
_________________
Papillon

[Grinning Fool]

[FunkySwerve]

Plus, native bioware database explode after extended use, causing crashes when accessed. No serious PW should consider the Bio db, in my opinion.
Funky

[Papillon]

The way some people probably see it, the internal (Bioware) DB has it advantages for PWs: It needs no setup and is essentially perfect to get something going very quick and without risk.

I say, that serious PWs will be severly limited sooner or later by it. It might be ok to store a few quest variables here or there... but. Serious PWs do NOT store a FEW variables, they have a lot more in the DB.

Want to track possible exploits ? Simply create a log table that tracks certain player parameters like gold value or level and write one entry on each login. It's trivial to analyze data like this with SQL or tools like Excel and spot players that have unusual high spikes in those values.

Do THAT with the internal DB.

Serious PWs will have to split modules sooner or later. Multi-core processors are now the standard, and one module does not only suffer the 2GB per process limitation, it also uses only one CPU. But: You can not share the internal DB between two modules.

IMHO, serious PWs with NWN2 and without NWNX are even less feasible than they were with NWN1.
_________________
Papillon

[FunkySwerve]

[Disco]

On a related issue: how fast are server logfile writes compared to MySQL writes? I could throw this all through the profiler if nobody tried it before, but maybe somebody did already?