Question about manual polling

[TeddiO]

Just a query about the switch to manual polling and the "trust me bro" about it being faster - If you're traversing to the C state from lua, you're going to get an inherent slowdown there anyways. Surely you'd want to take advantage of multithreading at the C layer?

Appreciate there might be something I'm missing in the grander picture here.

[KingofBeast]

The databases are all already multithreaded, each one gets its own io worker, so the "work" is already being done in the fashion you suggest. Polling is only done for callbacks to Lua, which has to converge to a single thread anyway. There are ways to keep that performant and do it from C, but it also allows less explicit control by the coder over how that process happens. For example if you wanted to rate limit polling on a specific database while keeping the others on each tick, that would be difficult to accomplish if 100% of the polling behavior was done from C.

[TeddiO]

I've done some testing and good news and tldr is generally I see a > 90% speedup on average which is fantastic. Certainly can't complain about that!

However because polling is done from lua, it becomes super easy to lock up the lua thread. You can emulate this with DO SLEEP(10); as a query, which will lock everything up pretty promptly. It doesn't matter if it's a prepared statement or via a generic query either and I'm essentially doing what Dash does via db_obj.Handle:Poll().

This wasn't behaviour that I've found on the previous tmysql4 (Blackawps edition).

Any pointers?

[KingofBeast]

Could you shoot over a full script to reproduce what you're seeing?

[TeddiO]

I think I'll revise a few of my comments, as I've found that it's not every time, it only seems to (usually) happen with the very first prepared statement - but I'm finding more unstable behaviour. Standard Query() usage doesn't actually seem to contribute towards the issue (though I'll do some more verificaiton on that in a bit).

For general debugging benefit:

Protocol version 24
Exe version 2023.06.28 (garrysmod)
Exe build: 17:28:13 Jul 24 2024 (9391) (4000)
GMod version 2024.07.31, branch: unknown
Windows 32bit Dedicated Server

Sample script:

require("tmysql4")

local db, err

local strHost = "host"
local strUser = "user"
local strPassword = "password"
local strDbSchema = "schemas"

local function dbSuccess()
    print("Connected to database")
end

local function CreateDatabaseConnection(p, c, a)
    db, err = tmysql.Connect(strHost, strUser, strPassword, strDbSchema, 3306, dbSuccess, tmysql.flags.CLIENT_MULTI_STATEMENTS)
    if !db then
        print(Format("Database failed to connect because: %s", tostring(err)))
        return
    end

    hook.Add("Think", "dbthink", function()
        db:Poll()
    end)

end
concommand.Add("db-connect", CreateDatabaseConnection)

concommand.Add("db-disconnect", function(p, c, a)
    hook.Remove("Think", "dbthink")
    db:Disconnect()
end)

concommand.Add("query-test-timeout", function(p, c, a)
    db:Query("DO SLEEP(10);", function(tblReturnData)
        print("Standard timeout completed")
        PrintTable(tblReturnData)
    end)
end)

concommand.Add("query-standard", function(p, c, a)
    db:Query("SELECT 1 + 1;", function(tblReturnData)
        print("Standard query completed")
        PrintTable(tblReturnData)
    end)
end)

concommand.Add("query-prepared", function(p, c, a)
    local preparedStatement = db:Prepare("SELECT 1 + ?")

    preparedStatement:Run(1, function(tblReturnData)
        print("Prepared completed")
        PrintTable(tblReturnData)
    end) 
end)

concommand.Add("query-prepared-timeout", function(p, c, a)
    local preparedStatement = db:Prepare("DO SLEEP(10);")

    preparedStatement:Run(function(tblReturnData)
        print("Prepared timeout completed")
        PrintTable(tblReturnData)
    end) 
end)

concommand.Add("query-multi-standard", function(p, c, a)
    db:Query("SELECT 1 + 1; SELECT 2 + 2;", function(tblReturnData)
        print("Standard query completed")
        PrintTable(tblReturnData)
    end)
end)

I've been testing the above on srcds, gamemode is sandbox with the script thrown into lua/autorun/server.

So as mentioned, it seems to generally kick off for the first prepared statement, however there seems to be some sort of variable to if it'll lag, and how much by. For example, I originally thought I could get it to occur every time with DO SLEEP(10);, but I mislead myself somewhat in the sense that the duration of that first statement was taking about that long.

I find if I run the query-prepared concommand after connecting to the database, there's a slim chance it'll act as described above, however if I run query-prepared-timeout first I can pretty much guarantee a thread pause. Sometimes I need to follow it up with query-prepared. Subsequent executions after the thread lock don't seem to cause it again. Sometimes I've even managed to somehow buffer the thread pause, but that's incredibly inconsistent.

What I have found is that there are times after using the above prepared statements and I issue a disconnect command or changelevel, again the thread hangs or in two cases so far, I've had a crash.

I'll try to collate more examples with consistent behaviour in a bit.

[TeddiO]

The following is able to cause it every time without fail, just throw it to the bottom of the script I provided previously:

concommand.Add("full-example", function(p, c, a)

    db, err = tmysql.Connect(strHost, strUser, strPassword, strDbSchema, 3306, dbSuccess, tmysql.flags.CLIENT_MULTI_STATEMENTS)
    if !db then
        print(Format("Database failed to connect because: %s", tostring(err)))
        return
    end

    hook.Add("Think", "dbthink", function()
        db:Poll()
    end)

    local preparedStatement = db:Prepare("DO SLEEP(10);")

    preparedStatement:Run(function(tblReturnData)
        print("Prepared timeout completed")
        PrintTable(tblReturnData)
    end) 

    local preparedStatement = db:Prepare("SELECT 1 + ?")

    preparedStatement:Run(1, function(tblReturnData)
        print("Prepared completed")
        PrintTable(tblReturnData)
    end) 
end)

[TeddiO]

So I've noticed some more interesting behaviour which leads me to suspect that PrepareStatement may not be working as intended.

With only using tmysql4.5 and making the bare minimum changes (essentially tmysql.initialize -> tmysql.Connect) in the original code and using explicitly Query(), everything works as expected, no thread holdup. However those obscene speedups that I was seeing essentially evaporated to something more in the 10-15% range which is still better than previous, but probably should have suggested something was glaringly wrong.

So to recap: A base upgrade module-wise and connection init is just fine. Preparing statements, somewhat a little less fine!