Some background info/discussion on it here
So you thought my post on networking a few days ago was boring? Get ready for database fun!
Normally I would just write about what worked, but in this case I’d rather trek through the foggy world of missteps I went through yesterday to make the various errors searchable, maybe my confusion and pain can be of help to some poor future googler.
Why do you want to directly talk to mySQL from a Unity app?
The first thing you normally see when searching online for help with integrating SQL and Unity is “you idiot, it’s too dangerous to ever connect directly to your server database!”
Well, for the CLIENT APP, that’s correct, it’s a horrible idea because anybody can steal your logon credentials and ruin your database. (I love you Team Meat, but … yeah)
Instead, you need a gateway of some kind (for high scores, this is usually a .php page on a website) that can do things like limit score additions from the same day from the same IP address and other types of ‘hardening’.
But for a game server that needs to access lots of data, a direct connection to a database from your Unity-based dedicated server is absolutely vital.
Step 1: Getting the MySQL Connector/Net files
I downloaded the files for “.NET & Mono” and drag and dropped the 4.5 branch files into my Unity project. (spoiler, this was dumb, I only needed one)
Note: Managed .dll files do NOT have to be dragged into any specific folder like C++ plugins do, anything inside of your Assets folder is ok, even sub-folders.
Step 2: Activating Unity’s 4.5 .Net support
From inside of Unity, I clicked on one of the newly added .dll files. Near the bottom of the property window (it’s easy to miss) it gave this message:
“Plugin targets .NET 4.x and is marked as compatible with Editor, Editor can only use assemblies targeting .NET 3.5 or lower, please unselect Editor as compatible platform.”
To fix this, I changed Unity’s scripting runtime version to ‘Experimental .NET 4.6 Equivalent’.
Step 3: Noticing I added the wrong files
I added ‘using MySql.Data;‘ to a .cs as a test. It compiled, but when I ran the game I got a “TypeLoadException” without any details. I installed Unity 2017.3.01f (beta) because I heard it gave better description of these kinds of errors, and it did:
TypeLoadException: Could not load type of field 'MySql.Data.Entity.MySqlMigrationCodeGenerator+<>c:<>9__4_0' (1) due to: Could not load file or assembly 'EntityFramework, Version=126.96.36.199,'
EntityFramework? Ohhh, so that zip had multiple versions of MySQL Connector and this one wants EntityFramework, whatever that is. I removed them all (and the .xml files too) and re-added MySql.Data.dll.
Note: You probably don’t need to update to a beta version like I did; this isn’t recommended usually. Obviously I just like to live dangerously.
Great, no errors and I can do a ‘using MySql.Data;‘ just fine.
Step 4: Adding support for System.Data
I added ‘using System.Data;‘ as we’ll need that too. Uh oh, got an error there. So I dragged and dropped <unity install dir>\Editor\Data\MonoBleedingEdge\lib\mono\4.6-api\System.Data.dll into my project. (I’m attempting to use the version that comes with Unity that matches the .NET version I’m using)
Well, it now compiles, but now I’m seeing ‘loading script assembly “assets/plugins/system.data.dll’ failed!’ error in the Unity log. Is this the wrong version?
The error goes away if I use <Unity dir>\Editor\Data\MonoBleedingEdge\lib\mono\2.0-api\System.Data.dll instead. Fine, I’ll use that. (spoiler, this causes problems later)
I wrote some test code using MySQL Connector, it works fine in the Unity Editor! I’m able to create/destroy/modify SQL databases. No warnings in Unity either! But uh oh, now I’m seeing this in Visual Studio:
warning MSB3277: Found conflicts between different versions of the same dependent assembly that could not be resolved.".
I guess Visual Studio is set to use a newer version and having the old System.Data.dll referenced is bad?
Not only that, but when I build a Win64 stand-alone version, when running it, I got this error:
NullReferenceException: Object reference not set to an instance of an object at MySql.Data.MySqlClient.MySqlPoolManager.GetKey (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00012] in <861c41359f7d4496a8fdf269ae744290>:0 at MySql.Data.MySqlClient.MySqlPoolManager.GetPool (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000] in <861c41359f7d4496a8fdf269ae744290>:0 at MySql.Data.MySqlClient.MySqlConnection.Open () [0x0016d] in <861c41359f7d4496a8fdf269ae744290>:0 at RTSqlManager.Init (System.String serverIP, System.String userName, System.String userPassword, System.String databaseName) [0x00083] in F:\Unity\SQLTest\Assets\RT\MySql\RTSqlManager.cs:51 at SQLTest.Start () [0x00030] in F:\Unity\SQLTest\Assets\_Script\SQLTest.cs:64
Uh… ok, I guess I used the wrong version of the .dll or something, I can see it’s being included but … when I look around, I can see other .DLL’s are coming from directory is actually from <Unity dir>\Editor\Data\MonoBleedingEdge\lib\mono\unityjit and not where I took them from. Using the System.Data.dll from this “unityjit” dir fixed the Visual Studio warning AND fixed the standalone versions (Win and Linux at least, that’s all I tested).
It works! So is it fast?
Hallelujah! Everything runs ok, both local in Windows (to test this, you need to setup a MySQL server on Windows) and on linux. (I’m actually using MariaDB, a MySQL fork, that works fine too)
Speed seems comparable to using the C++ version. As is my way, I wrote a Unity app with built-in Telnet server to test speeds, here are the results on a dedicated linux server:
Command > ? /? for help /create to create testtable /delete to remove testtable /add <count> to add a bunch of records /show <index> to look at a specific record /stats to show current status of testtable and record count /seektest <count> to benchmark loading <count> random accounts and reading ID and RandomNum from them /updatetest <count> to benchmark updating <count> random accounts with a new RandomNum and RandomIndexedNum /ranktest <count> Rank, benchmark, and view top <count> records Command > /updatetest 1000 Updating RandomNum and RandomIndexedNum for the first 1000 accounts, each in a different SQL statement Updated 999 accounts in 5.860773 seconds. (0.005860773 p/account) (time taken: `w5.869883 seconds``) Command > /seektest 1000 Reading ID and RandomNum for 1000 random accounts in 1000 different SQL SELECT statements... Read from 1000 accounts in 0.1809998 seconds. (0.0001809998 p/account) (time taken: `w0.1875515 seconds``) Command > /ranktest 1000 Ranking top 1000 random numbers... #1 ID: 50117 RandomNum: 1999986 #2 ID: 31319 RandomNum: 1999854 #3 ID: 5009 RandomNum: 1999835 #4 ID: 7211 RandomNum: 1999784 #5 ID: 40406 RandomNum: 1999772 #6 ID: 9686 RandomNum: 1999698 #7 ID: 18099 RandomNum: 1999644 #8 ID: 41235 RandomNum: 1999593 #9 ID: 12447 RandomNum: 1999505 #10 ID: 37578 RandomNum: 1999396 (not displaying the rest...)Took 0.02680588 to rank and get 1000 records. #1 ID: 35 RandomIndexedNum: 19954265 #2 ID: 552 RandomIndexedNum: 19942897 #3 ID: 161 RandomIndexedNum: 19898997 #4 ID: 874 RandomIndexedNum: 19872310 #5 ID: 484 RandomIndexedNum: 19859544 #6 ID: 453 RandomIndexedNum: 19777522 #7 ID: 649 RandomIndexedNum: 19776457 #8 ID: 851 RandomIndexedNum: 19769359 #9 ID: 488 RandomIndexedNum: 19741107 #10 ID: 727 RandomIndexedNum: 19711418 (not displaying the rest...)Took 0.0008430481 to rank and get 1000 indexed records. (time taken: `w0.02984619 seconds``)
To break it down (note: this database has 50,000 records):
Update a record: 5 ms
Seek and read from a random record: 0.18 ms
Sort 50k unindexed ints and return the top 1000: 27 ms
Sort 50k indexed ints and return the top 1000: 0.84 ms
Well, I probably should have created the table with ENGINE=MEMORY as I suspect the update record test was hampered by IO writeback (this system doesn’t have an SSD), but overall I’m happy with these speeds.
I wonder how the speeds would compare with SQLite? Is the fact that I’m using a beta version of Unity change/break anything I did? I don’t know, but it works fine now so whatever.
Seth, what the hell do you know about netcode, anyway?!
Woah, with the attitude already. Look, I’m all about multiplayer. Have been for a long time. I’ve gone from being excited to get two people walking on the same ANSI map in LORD2 in the early 90s to hitting the 70,000 concurrent user mark during busy times in Growtopia.
In addition to writing my own junk, I regularly play with Unity and Unreal and am constantly trying to see how they can fit my developing style – mostly I need them because it’s becoming a losing battle to try to maintain your own 3D engine if you also want to actually design and release games too.
What’s LLAPI, anyway?
LLAPI stands for Low Level Application Programming Interface. It’s what Unity’s HLAPI (High level) API is built on. I guess both of those can be called part of UNet. (Unity networking)
Why not just use the HLAPI like a normal person?
That might be ok for some projects, especially round-based networked games without too many players.
When you are targeting MMO level network performance where a server may have to track stats of millions of players and simulate a world for weeks instead of minutes, the first rule is:
Stay as low level as you reasonably can.
I don’t want someone else’s net code to even think about touching my gameobjects, I just want it to deliver reliable/unreliable packets that I write/read straight bytes to. That’s it. I want to handle things like prediction, dead reckoning, and variable serialization, myself!
What’s the deal with UDP, TCP, and WebSockets?
Both UDP and TCP are internet protocols built on top of IP.
TCP is a bidirectional socket based connection that insures your data is correct and sent in the right order.
It’s like calling Jeff, he picks up the phone and you can both talk until one of you puts the phone down. Oh, and after you say something, you make sure Jeff understood you correctly each time, so that eats up a bit of time.
UDP is is a stateless connection where single packets get sent. You write your message on a rock and throw it at Jeff’s window. While it gets there fast, it’s possible you’ll miss and Jeff will never see it. If dozens of people are throwing rocks, he needs to examine each one carefully to figure out which pile to put it with so the text is attributed to the right thrower. He might read the second rock you threw before the first one.
WebSockets are basically TCP with a special handshake that’s done over HTTP first. To continue this already questionable analogy, it’s as if you had to call Jeff’s mom for permission, and then she gave you his cell number to call.
In theory WebSocket performance could be similar to TCP but in practice they are much slower and unreliable, I don’t really know why but hey, that’s what we get. I suspect this will improve later.
For games, UDP is nice because you can send some of your packets fast with no verification that they were received, and others (more important packets, like dying) with verification which sort of makes those packets work like TCP.
That said, most games probably would be ok with a straight TCP steam as well and having a connected state can make things easier, like easily applying SSL to everything and not worrying about unordered packets.
Why use WebSockets instead of UDP or TCP? Just use UDP or TCP for everything, moran
What does LLAPI use?
It can read from both sockets (UDP) and WebSockets at the same time and route them so your game can let them play together. But how fast is it, and does it work?
This brings us to the second rule of netcode:
Use stress tests without game code to notice glaring problems early and more clearly
Which finally brings us to the point of this post. NetTest is a little utility I wrote that can:
- Run as a client, server, or both
- If you connect as “Normal client” you can all chat with eachother
- Run with a GUI or as headless (meaning no GUI, which is what you want for a server usually)
- Can open “Stress clients” for testing, a single instance can open 100+ clients sockets
- When stress mode is turned on, the stress clients will each send one random sentence per second. The server is set to re-broadcast it to ALL clients connected, so this generates a lot of traffic. (300 stress clients cause the server to generate 90K+ packets per second for example)
- Server can give interesting stats about packets, payload vs Unity junk % in the packets, bandwidth and server speed statistics
- Built in telnet server, any telnet client can log on and get statistics directly or issue commands
- Tested on Windows, linux, WebGL
- Supports serializing variables and creating custom packet types, although the stress test only uses strings
- Everything is setup to push performance. Things that might mess with the readings like Unity’s packet merging or modifying values based on dropped packets is disabled. All processing is done as fast as possible, no throttling or consideration to sleep cycles.
- Keep in mind a real game server is going to also being doing tons of other things, this is doing almost nothing CPU wise except processing packets. Testing things separately like this make it easier to see issues and know what the baseline is.
ABOUT TEST RESULTS
These tests are presented ‘as is’, do your own tests using my Unity project if you really want exact info and to check the settings.
My windows machine is a i7-5960X, the remote linux box is similar and hosted on a different continent, I get a 200 ping to it.
All packets in NetTest are being sent over the ‘reliable’ channel. No tests involve p2p networking, only client<>server.
config.AcksType = ConnectionAcksType.Acks32; //NOTE: Must match server or you'll get a CRC error on connection config.PingTimeout = 4000; //NOTE: Must match server or you'll get a CRC error on connection config.DisconnectTimeout = 20000; //NOTE: Must match server or you'll get a CRC error on connection config.MaxSentMessageQueueSize = 30000; config.MaxCombinedReliableMessageCount = 1; config.MaxCombinedReliableMessageSize = 100; //in bytes config.MinUpdateTimeout = 1; config.OverflowDropThreshold = 100; config.NetworkDropThreshold = 100; config.SendDelay = 0;
TEST 1: 300 CLIENT IDLE (LOCALHOST, WINDOWS BOX)
Ok, here I’m curious how many bytes LLAPI is going to use with clients doing nothing.
Server is set to localhost. I’ve got 4 NetTest’s running – the one on the bottom right has had “Start local host” clicked, and the three others each have added 100 stress clients.
- Around 30 bytes per stay alive packet per client. (23KB total over 7.74 seconds, sent 2.2 packets per client over 8 seconds, so roughly what I would expect for the 4000 ms ping timeout setting) Are stay alives sent over the reliable channel? Hmm.
- Adding and removing test clients is a blocking operation and gets progressibly slower as more are added – why does it get slower and cause my whole system to act a bit weird?
- While closing the instances looks normal, the instances are actually spending an additional 30 seconds or so to close sockets that were opened
- Side note: Having a client ping the host (at localhost) takes about 2 ms. (normally this would be bad, but given that it can’t check until the next frame, then once more to get the answer, this seems to match the framerate decent enough)
I’m not going to worry about the socket slowness, this may be related to a windows 10 resource thing. I’ll launch with less clients in future tests to help though. The server itself has zero speed/blocking issues with actually connecting/disconnecting though, so that’s good.
TEST 2: 200 CLIENT STRESS (LOCALHOST SERVER, WINDOWS BOX)
Ok, now we’re getting serious. I have 5 instances – 4 have 50 stress clients, 1 is the server. (I could have run the server on one of the stress client instances, but meh)
First I enabled 50 clients in “stress mode” – this should generate 10,000 packets (50*200) per second. The server gets 50 lines of text from clients, then broadcasts each one to each of the 200 clients. Huh, stats show it only sending around 6,000 packets per second, not 10,000. However, we can see all the lines of text are being delivered.
That seemed fine so I upped it to all 200 clients doing the stress test – this should cause the server to send 40,000 packets a second (and receive 200 from the clients).
The stats show only 24,549 packets and 5.3 MB per second being sent. About 15% is unity packet overhead, seems like a lot but ok. Server FPS is good, slowest frame was 0.02 (20 ms) so not bad.
Is unity combining packets even though I set config.MaxCombinedReliableMessageCount = 1; ? Oops, I bet that needs to be 0 to do no combining.
I also notice 4,346 per second packets being received by the server. 200 for my messages, and I assume the rest are part of the “reliable guarantee” protocol they are doing for UDP where the clients need to confirm they received things ok. Oh, and UNet’s keep alives too.
In the screenshot you can see an error appearing of “An abnormal situation has occurred: the PlayerLoop internal function has been called recursively… contact customer support”. Uhh… I don’t know what that’s about. Did I screw up something with threads? It didn’t happen on the server, but one of the four client instances.
I let it run at this rate for a while – a few clients were dropped by the server for no reason I could see. Not entirely stable at these numbers I guess, but possible it would be if I ran it on multiple computers, it’s a lot of data and ports.
TEST 2: 100 CLIENTS STRESS (REMOTE SERVER, HEADLESS LINUX)
As nice as it is to be able to turn on both the server (localhost) and the client directly from the same app in the Unity editor to test stuff, eventually you’ll probably want to get serious and host the server on linux in a data center somewhere. This test let’s us do that.
I’ve changed the destination address to the remote IP. (You can click the GameLogic object to edit them, I was too lazy to make a real GUI way))
I have a .bat file that builds the Unity project (on Windows) and copies it to the remote server, then triggers a restart. It’s run on Linux like this:
cd ~/NetTest chmod +x ./linux64headless ./linux64headless -batchmode -nographics -logFile log.txt
Here is a screenshot of the 100 clients (using one instance of NetTest) connecting to the linux server (which is also NetTest) (And no, that’s not the real port/ip, stop trying to hack me, Mitnick)
So let’s try to digest this data.
- NetTest Client: The 100 clients are self reporting receiving about 1,150,000 (1.15 MB) bytes of actual payload data per second. (10000 lines of text). Zero packets in the send queue
- Windows system: Windows says 12.1 Mbps per second, so 1.5 MB, close enough, who knows what else I’m streaming/downloading
- Linux system: iotop shows server sending 10.Mb (1.25 MB per second) so that seems right
- Linux NetTest: (using telnet) it’s reporting it’s sending 1.22 MB per second (only 3,458 packets per second, yeah, definitely some packet merging happening) The total stats went negative, I ran this a while, uh… I guess I need to be using larger numbers, probably rolled over.
- Linux: Server is running at 1100 FPS, so zero problems with speed. You’re probably thinking “Hey, it’s headless, what do you mean frames per second? What frames?”, ok, fine, it’s really measuring “update()’s per second”, but you know what I mean. Slowest frame out of the 90 second sample time was 0.0137 (13 ms)
- I don’t have it in the screenshot, but top shows the server is not too power hungry, its process seems to use 3% (no load) to 15% (spamming tons of packets). Keep in mind we aren’t actually DOING anything game related yet, but for just the networking, that isn’t bad
I tried 200X200 (4x the total bandwidth & packets, so 5 MB a second) but the outgoing packet queue started to fill up and I started dropping connections.
TEST 3: WebGL (REMOTE SERVER, HEADLESS LINUX)
I’m not going to bother making pics and somehow this post already got way out of control, but here is what seemed to be the case when I played with this earlier:
- No WebSocket stats are included in the stuff UNet reports – “the function called has not been supported for web sockets communication” when using things like NetworkTransport.GetOutgoingFullBytesCountForHost on it
- WebGL/WebSockets be slow. Real slow. If pushed the bandwidth to over 18KB per second (per Chrome tab), packets started to get backed up. I don’t know if things are getting throttled by the browser or what. The server eventually freaks out when the send queue gets too big and starts throwing resource errors.
- When creating stress test clients, a single WebGL tab seems limited to 14 WebSockets max (Chrome at least)
- I suspect there could be issues with a naughty WebSocket filling up the queue and not sending acknowledges of receives, your game probably needs to carefully monitor packets sent/received to disconnect people quick if they abuse the connection
- WebSocket queue/etc issues don’t affect or slow down normal connections, so that’s something
- Mixing WebSocket and normal Sockets (UDP) clients work fine, that’s a very handy thing to be able to do
All in all, I think directly using LLAPI in Unity 2017.1+ is promising. Previously, I assumed I’d need to write my own C++ server to get decent socket performance (that’s how I did the Unity multiplayer space taxi demo) but now I don’t think so, if you’re careful about C#’s memory stuff.
- How is it ok that Unity’s text widget/scroller can only show like 100 lines of text? (65,535 vert buffer errors if you have too much text, even though most of it is off screen) Maybe I’m doing something wrong
- Unity’s linux builds don’t seem to cleanly shutdown if you do a pkill/sigterm. OnApplicationQuit() does not get run, this is very bad for linux servers that could restart processes for reboots or upgrades. (if it’s a dedicated server you can work around it, but still!)
- WebGL builds can’t do GetHostAddresses() so I had to type the real IP in for it to connect (I should try switching to the experimental 4.6 net libs)
- To my great surprise, my telnet host contined to run and send answers even after stopping the game in the editor. How could that game object still exist?! Threads and references are confusing, I fixed it by properly closing the telnet host threads on exit.
- The Unity linux headless build worked perfectly on my CentOS 7.4 dedicated server, didn’t have to change a single thing
- It’s weird that you’ll get CRC errors if PingTimeout/AcksType/DisconnectTimeout don’t perfectly match on the client and server. I wonder why there is not option to just let the server set it on the client during the initial handshake as you want to be able to tweak those things easily server-side. I guess the client could grab that data via HTTP or something before the real connect, but meh.
Note: I left in my .bat files (don’t mock the lowly batch file!), thought they may be useful to somebody. They allow you to build everything for linux/win/webl, copy to the remote servers and restart the remote server with a single click. Nothing will work without tweaking (they assume you have ssh, rsync etc working from a dos prompt) but you could probably figure it out.
Source is released under the “do whatever, but be cool and give me an attribution if you use it in something big” license.
Dink Smallwood HD 1.89 has been released to the App Store (with iOS 11 support) as well as on Google Play and Windows!
- Now with correct aspect ratio
- Vastly improved DMOD compability
- Re-worked engine timing, now the same on all systems
- Improved shadows (contributed by Dan Walma)
- Can now browse/install/play over 300 DMODS (add-on quests) directly from inside the game, full integration with the Dink Network
- Open sourced. SVN checkout link: svn://rtsoft.com/rtsvn/projects/RTDink
- New price is
$2.99FREE on all platforms! (This is a permanent change – No ads or crap either!)
Or visit the main Dink Smallwood page.
I’ve been working on Dink HD to greatly improve it for a new release, you can help test it here! It’s been open sourced (on the RTsoft subversion server) as well so feel free to contribute patches. The plan is to release it free on all platforms soon as I can, until then, it won’t run on iOS 11.
Also a new interview about the making of Dink is here. Talks about its low sales and making games as an indie in general.