Enhanced Database Connection (Australian Database Server)

[indio]

AL, the database I'm writing to, the beta server, where is that located?

More importantly, can we set one up in my half of the world as well as the existing, and have it send the data overseas to the central database *after* the in-game reads and writes?

Currently a quest state is updated over 2-3 seconds...in that time the player client literally suspends all else. That's the situation with a server hosted in Australia, a player client in Australia and a database server overseas (US or Europe, not sure).

When the situation becomes American player logging into an Australia-based server which is writing to a European database (or American), we're playing the internation ping pong with our data.

It would really help to have a locally hosted database, on the machine the game is hosted, for example, which then updates the central database (wherever that is) independently of the game.

Thoughts?

[zicada]

AL, the database I'm writing to, the beta server, where is that located?

More importantly, can we set one up in my half of the world as well as the existing, and have it send the data overseas to the central database *after* the in-game reads and writes?

Currently a quest state is updated over 2-3 seconds...in that time the player client literally suspends all else. That's the situation with a server hosted in Australia, a player client in Australia and a database server overseas (US or Europe, not sure).

When the situation becomes American player logging into an Australia-based server which is writing to a European database (or American), we're playing the internation ping pong with our data.

It would really help to have a locally hosted database, on the machine the game is hosted, for example, which then updates the central database (wherever that is) independently of the game.

Thoughts?

What's the IP to the server in question ?

[indio]

It's at home and I'm at work. It's the beta server that AL gave me details of 12 months ago...I think it was Patrice's server.

[zicada]

It's at home and I'm at work. It's the beta server that AL gave me details of 12 months ago...I think it was Patrice's server.

No I mean, the IP to your box, the one i .au.

I'm the IA mate, I host the SQL box, it's on a 100mbit in sweden currently.
If you get me your IP I can try and figure out what the problem is.

[indio]

I'll get you it once I'm home.

[AcadiusLost]

I'd guess you're connecting to alfa_beta on vault.alandfaraway.org, which should now be pointing you to the central DB server on Zicada's central hosting machine, which generally has faster responses from more of the world than the powweb host that Hialmar had been paying for.

With regard to beta testing with a faster database, it's easy to set up a local MySQL DB and connect to that instead for troubleshooting and early beta testing, though it limits the ability of people like me to open up the DB tables remotely and see what's going on in terms of persistency with your module. For Live (and arguably, mature/late Beta testing), it really defeats the purpose of having a central database if we have more than one of them that aren't 1:1 constantly synchronized somehow. What we could do is try to incorporate some local caching of mod-specific p-variables for things like the death and quest systems, whereby if you just wrote a queststate of "8" for a player 2 seconds ago, you don't need to call back to europe again to confirm that it's still at "8" for the next convo branch. Those sort of systems are always tricky to implement well, though.

I think with regards to the IP address, Zic may have been asking for the IP address of your hosting machine (from which you're hosting skullport) so he can check the route and ping times from his SQL-server.

[edit: some of this was clarified while I was writing the post]

[indio]

121.219.113.20

[indio]

The IP any good, zicada?

AL, since talking with you about it I got the clock involved, especially on reads.

Writes are pretty reliably 2-3 seconds.

Reads can blow out to 8-10. When starting a conversation node dependent on a quest state, it's always 5+, and often 8-10.

So good luck with that idea of yours. It's looking important to me

[zicada]

Code: Select all

traceroute to 121.219.113.20 (121.219.113.20), 30 hops max, 40 byte packets
 1  192.168.0.1 (192.168.0.1)  0.230 ms  0.270 ms  0.357 ms
 2  94.255.129.1 (94.255.129.1)  12.624 ms  13.311 ms  14.153 ms
 3  se-host-80-94-221-18.stadenistaden.net (80.94.221.18)  14.283 ms  14.465 ms  14.690 ms
 4  SE-HOST-80-94-221-22.stadenistaden.net (80.94.221.22)  14.826 ms  14.998 ms  15.122 ms
 5  SE-HOST-80-94-221-26.stadenistaden.net (80.94.221.26)  15.259 ms  15.394 ms  15.526 ms
 6  se-host-80-94-221-30.stadenistaden.net (80.94.221.30)  15.666 ms  3.556 ms  3.677 ms
 7  SE-HOST-80-89-163-89.stadenistaden.net (80.89.163.89)  4.114 ms  12.276 ms  12.412 ms
 8  SE-HOST-80-89-163-82.stadenistaden.net (80.89.163.82)  12.629 ms  12.765 ms  12.932 ms
 9  83.233.79.13 (83.233.79.13)  13.066 ms  13.202 ms  13.443 ms
10  82.209.165.146 (82.209.165.146)  13.573 ms  13.709 ms  13.844 ms
11  82.209.165.144 (82.209.165.144)  13.980 ms  14.248 ms  3.792 ms
12  82.209.178.234 (82.209.178.234)  3.952 ms  4.303 ms  12.252 ms
13  cr-se-sto-ksg23-1-vl1001-10ge.bredband2.net (82.209.176.38)  12.379 ms  12.514 ms  12.648 ms
14  64.210.13.73 (64.210.13.73)  17.149 ms  17.284 ms  17.418 ms
15  i-4-9.paix05.net.reach.com (134.159.62.97)  181.011 ms  182.203 ms  182.585 ms
16  static.net.reach.com (202.84.251.73)  183.276 ms  184.927 ms  185.904 ms
17  static.net.reach.com (202.84.140.209)  322.918 ms  324.482 ms  324.742 ms
18  TenGigE0-2-0-2.oxf-gw2.Sydney.telstra.net (203.50.13.41)  324.199 ms  324.378 ms  324.605 ms
19  Bundle-Ether1.chw-core2.Sydney.telstra.net (203.50.6.89)  328.306 ms  325.338 ms  325.549 ms
20  Bundle-POS1.exi-core1.Melbourne.telstra.net (203.50.6.14)  340.633 ms  339.318 ms  339.438 ms
21  TenGigabitEthernet9-1.lon42.Melbourne.telstra.net (203.50.80.52)  339.670 ms  341.322 ms  341.433 ms
22  telstr388.lnk.telstra.net (165.228.103.222)  341.630 ms  338.694 ms  339.755 ms
23  CPE-61-9-133-133.vic.bigpond.net.au (61.9.133.133)  341.700 ms  339.726 ms  340.427 ms
24  * * *
25  * * *

That's not very good to be honest.
I'm not sure where reach.com is, but it jumps like crazy from one hop 16 to 17.
14 to 15 is just scandinavia to .au i guess

Still though, this has nothing to do with anything you can count in seconds, its 0,34 seconds to get a packet to you and back, so this issue, whatever it is, is software based.

Guess AL might be able to look at the code.

[indio]

Tom, please let me know if I can help regarding this code. The last thing I want to be is a burden and certainly wouldn't be pushing it if it wasn't so overtly affecting the server.

[AcadiusLost]

Just finished testing on this, seems to be working well- you will still see a bit of lag with DB writes, and a little with the first time quest variables are checked for each PC, but should definitely help boost quest performance.

Switch over to this "pre-release" alfa_acr.hak by putting it in place of your prior one (rename or move the old one). Open your module in the toolset, recompile all scripts, and it should start working automatically.

http://download.alandfaraway.org/downlo ... re1.54.rar

Also included here (but not as well tested) is the requested change that allows "plot" flagged items to drop regardless of loot system rolls and creature CR.

I'll probably wait a few more changes before I update the alfa_acr.hak on the worldgate, so until then, consider this a "temporary" hak change.

[indio]

Will test both when I get home tonight.

Thanks mate.

[indio]

Haven't tested the Plot item drops as yet.

The database reads are unrecognisable, however.

My test NPC has 12 quests, each a separate node in a single conversation, each with a separate conditional variable.

He used to take 8-10 seconds each time I talked to him for him to check which node I should start on.

He now takes .5 of a second. Often less. Often it's just like a local variable.

Many thanks, Tom.

[AcadiusLost]

Sounds like the problem definitely was with too many persistency database calls happening right at the same time, then. It's always best to spread those calls out where possible as a general practice. For example, a dialog with many conditional branches (each of which calls one or more persistent variables) can be quite "expensive" lag-wise since they will all fire off in rapid succession whenever a PC arrives at the convo node that is parent to all those options. Improvements can be had by introducing more subtrees into the convo, so only a few options are being checked at a time for example (like Kadelion's convo having separate paths for "Scouting", "Raids", "Exploration" etc. On that one though, the "reward" side still has a long laundry list of conditionals which involve plenty of calls.

With the new hak though, those shouldn't be as bad as before. We still have one other acute recurrent cause of major DB lag in the PC Tools text macros, which query more than 40 p-variables at once the first time the tools are opened for each PC (if they have macros set up). I'll get to optimizing that one as well once things settle down around here.

May also be places to add similar caching in the player login process, which does currently involve several successive calls to the same p-variables that could be alleviated with proper caching. I've seen some significant lag on TSM with PC logins, but opening PC tools is definitely the big one.

Glad it's worked well for you! Looking forward to having a look around Skullport when I get some time free.

[Teric neDhalir]

*Minor Hijack* I have some spawns dropping plot-flagged items when assigned the standard acr_cre_ondeath script, so first indications are that that fix is working, AL. Not had chance to see what happens with creatures that might drop ACR loot as well, yet ...
Thanks for that,
Teric

Edit: Now seen creature drop one of my plot items and ACR item at the same time