/tg/Station 13

If you don't know shit about SQL please do not bother to post.

So we keep running up against this issue of SQL being slow as hell. Reality is SQL dll is slow as shit. The SQL is quite fast.

Probably something that gets missed here a lot is what is actually slow about our dll.

Consider the following:

a.) A query that returns 1 row from the table SS13_Bans.
b.) A query that returns 500 rows from the table SS13_Bans
c.) A query that returns 1 row from the table SS13_Bans and is run 500 times total.

Which one takes the longest time?
a? b? c?

In practice a and b take the same amount of time to run via the SQL DLL.
c however would take tremendously longer to complete. It is just the inconvenient truth of how SQL and DLLs work on BYOND. Closing and opening 500 connections is SLOW.

So how do we make SQL work with SS13 and not have it suck?


Have a separate program handle SQL connections for writes.
We need the following:
1.) Script to read from a text file and hit the DB.
2.) Modify SS13 to write SQL updates to a text file.

Why?
By having the SQL updates be in a text file we can have another program (Python script) take care of the opening and closing of the DB connection. This would prevent Dream Daemon from blocking which would reduce server lag when admins are serving bans.



Cache Data at RoundStart
The second level to this. We need to cache all relevant data for the round at roundstart.
1.) Cache Library data (Books, Titles, Authors) (We can also cache the content if it isn't too much data)
2.) JobBan information
3.) Releveant ban DB information.

This gives us about 2-3 scripts at the start of the round and eliminates all SQL lag during the round.

Thoughts?

edit: Never mind it looks like it is already using one connection only, it's a shame it is so laggy then
edit2:IT would be interesting to log the establish db connection proc to see if it's working correctly, the lag from the DB connections is pretty astounding to me.

edit3: I would check also that mysql is configured to not close a connection if the client doesn't speak to it for a while - make sure that is set to some reasonable value
I believe the value is connect-timeout in the mysql conf

I'm new to the codebase, so forgive me if I'm pointing out the obvious or talking about irrelevant things, but I know that a lot of embedded databases, such as MS Access and SQLite don't actually only retrieve one record when you ask for one record. They retrieve EVERY record, and then sort through it afterwards. This is most likely the case in your dll.

I am confident that this is why a and b take the same time (they're both getting 500 records anyway, and looping through them to find one takes barely any time) and why c takes much longer (it's getting 500 records 500 times).

So it has come to my attention that the database is hosted on a seperate server than the game.

They should really be hosted in either the same datacenter or ideally the same server.

If the database is on the same network then it doesn't really matter.

They're not on the same network.

Yeah I should've clarified but from what I understand the DB is located at scaredy's house


The network delay is going to contribute to lag, so it's an easy win to move it to the server.