mysql-async high tick

[MohamedAmrMahdy]

running mysql-async 3.2.0

[NoyWasTaken]

Having the same problem, also running gcphone, I saw in the server logs that it causing high sql ms. Tell me if you got anything

[ArmoredSoldiers]

Same problem

[NoyWasTaken]

Are you running OneSync 64/128 slots ? @ArmoredSoldiers @MohamedAmrMahdy

[MohamedAmrMahdy]

Are you running OneSync 64/128 slots ? @ArmoredSoldiers @MohamedAmrMahdy

yes this issue happens when players get over 45 players online

[NoyWasTaken]

Me too [But only when 50+], have you found any solution or something to help it ?

[NoyWasTaken]

Have you tried to disable the gcphone ? @MohamedAmrMahdy

[MohamedAmrMahdy]

Have you tried to disable the gcphone ? @MohamedAmrMahdy

no but i think it is not the main reason because others recourse queries get same lag

[NoyWasTaken]

I debugged the mysql, I saw that the gcphone has slow query [high ms], I've checked myself and with some of my programmers [in my server] and we saw that the phone itself built all on mysql [always query the sql and not saving data on the server/client script]

[NoyWasTaken]

I'm gonna make some tests to see if it does the gcphone who is causing the problem, I'll let you know. @MohamedAmrMahdy

[NoyWasTaken]

I've noticed to this thing in my console [on server startup]:

[mysql-async] Database server connection established.

Authenticating with Nucleus...

server thread hitch warning: timer interval of 3040 milliseconds

[NoyWasTaken]

Btw: I've tried to disable the gcphone and there's still high mysql tick that is causing a delay.

[ArmoredSoldiers]

Samuray, yes, i running 75 slot with 256 player loops, and high tick up to 40-45 player up... i using OneSync plus, and use gcphone.

[NoyWasTaken]

Currently I'm working on finding the problem in my server. I've updated some resources and removed ones I don't need. I'll update if there's any change.

[ghost]

Same here. Tried everything and disabling resources.

[SaadGustavo]

Still no solution?

[LittleTin]

running mysql-async 3.2.0

Hello, I want to know what is this analyzing tool?

[Tinky124]

Still problem :) Nothing fixed yet

[CruelAlpha]

Recurring issue for us from time to time aswell, not exactly sure.

[Local9]

Just a small question but... wouldn't changing your for loops to use the GetActivePlayers() native be much better than looping 1-256?

    for _, player in ipairs(GetActivePlayers()) do
        local ped = GetPlayerPed(player)
        -- do stuff
    end

[CruelAlpha]

Just a small question but... wouldn't changing your for loops to use the GetActivePlayers() native be much better than looping 1-256?

    for _, player in ipairs(GetActivePlayers()) do
        local ped = GetPlayerPed(player)
        -- do stuff
    end

Are you refering to other plugins or the mysql plugin?

[CruelAlpha]

Has anyone tried messing around with mysql variables?

[Local9]

Just a small question but... wouldn't changing your for loops to use the GetActivePlayers() native be much better than looping 1-256?

    for _, player in ipairs(GetActivePlayers()) do
        local ped = GetPlayerPed(player)
        -- do stuff
    end

Are you refering to other plugins or the mysql plugin?

Anything that maybe using it, is there a case that a script maybe doing something unintended which is looping up to 256 instead of the number of players connected which could cause a high tick rate. Its just a general question as I'm looking to step back tom GHMatti's C# MySQL resource that I'm using (because I like things being referenced in my C# Projects using ADO.NET as exports raise concerns for me) and use something that is more up to date like this resource as it looks to be widely used.

[CruelAlpha]

After more research, some of the lag can be fended off by improving the MySQL variables, but at around ~64 people the DB queries usually has over 1000ms sometimes when there is too much it racks up to 100k+ ms (until all queries are caught up). Most of our issues during lower pop (~50) was fixed by experimenting with variables.

All our tables are properly indexed as per needed I wonder if GHMatti can offer a solution or provide tips to look at to investigate it

[ghost]

@CruelAlpha how many queries per second are you running, which ones are slow?

[ProCcEeD]

where im putting this code? for _, player in ipairs(GetActivePlayers()) do local ped = GetPlayerPed(player) -- do stuff end

and how i change the loop to 256? thnx!!!

[Antontonov]

Hi, its because you have an server loop like this:

for i=1, #players, 1 do
     -- MySQL.Async - Stuff
end

My solution to remove the high hitch time was this:

for i=1, #players, 1 do
     doMySQLStuff(players[i])
end

function doMySQLStuff(player)
    -- your MySQL.Async function stuff here
    Citizen.Wait(50)
 end

Info: When you put the Wait inside an for loop the server send an error

Try it and pease send feetback ^^ I hope it works on your servers to!!!

[martink1337]

@Antontonov , you mean something like this ?

Before : `

MySQL.Async.fetchAll('SELECT owner FROM owned_vehicles WHERE owner = @owner AND plate = @plate', {
    ['@owner'] = identifier,
    ['@plate'] = plate
}, function(result)
    if result[1] then
        cb(result[1].owner == identifier)
    else
        cb(false)
    end
end)

end)`

After : `

MySQL.Async.fetchAll('SELECT owner FROM owned_vehicles WHERE owner = @owner AND plate = @plate', {
    ['@owner'] = identifier,
    ['@plate'] = plate
}, function(result)
    if result[1] then
        cb(result[1].owner == identifier)
    else
        cb(false)
        -- test
        Citizen.Wait(50)
    end
end)

end)`

[Antontonov]

Hi, First Citizien.Wait(msec) than cb() But for only one request you dont need any wait ... i ll do that for my mysql functions inside "for"-Loops It works fine for me and i haven t any big fat hitch warnings anymore.

Edit: And in your function the poor Citizen have to wait 50 msec longer to store/get his veh. or other stuff ^^

[martink1337]

@Antontonov Interesting. I'll try to find out + I make the same thing but in client-side, which also works nice. The idea is simple. When someone uses an function from Ui menu or anywhere, he will have like 2-3 seconds cooldown, so the function will be triggered after that amount of time (the server-side code). This also works good, what you think about it ?

[CYXNNN]

@Antontonov thanks for sharing this, looks interesting! Do you mind explaining, in what way the wait call decreases the hitch time?

[SteveBaggins]

Hi, First Citizien.Wait(msec) than cb() But for only one request you dont need any wait ... i ll do that for my mysql functions inside "for"-Loops It works fine for me and i haven t any big fat hitch warnings anymore.

Edit: And in your function the poor Citizen have to wait 50 msec longer to store/get his veh. or other stuff ^^

Can you give an example? I think shitty sql queries are crashing my server. My memory goes way up till about 20 gb and crashes the server. Player count, and time running doesn't matter just seems to be random over time.

[Puntherline]

I know that this is an older thread, but I've had the same issue just a few moments ago and fixed it. Here's some example code that would fetch and print user data every second, but increase the CPU msec with every time it runs:

Citizen.CreateThread(function()
    while true do
        MySQL.Async.fetchAll(
            "SELECT * FROM players WHERE name = @name",
            {
                ["@name"] = "Puntherline"
            },
            function(result)
                print(json.encode(result))
            end
        )
        Citizen.Wait(500)
    end
end)

By moving the MySQL function to it's own function we eliminate the high CPU msec:

Citizen.CreateThread(function()
    while true do
        print(SomeRandomFunctionToGetStuff())
        Citizen.Wait(500)
    end
end)

function SomeRandomFunctionToGetStuff()

    -- Creating the "retval" local so we can set it in just a moment
    local retval = nil

    -- Actually running the SQL code here
    MySQL.Async.fetchAll(
            "SELECT * FROM players WHERE name = @name",
            {
                ["@name"] = "Puntherline"
            },
            function(result)
                retval = json.encode(result)
            end
        )

    -- Waiting for the "retval" local to be set
    while retval == nil do
        Citizen.Wait(10)
    end

    return retval
end

The more SQL related functions you have in your code, the more work this would be, however I'd just start with the ones that are a major cause of high CPU msec and keep fixing it until you're happy with the results.