Stopping an expensive calculation temporarily freezing the server

[MattJeanes]

So I've got some mysqloo database things which run every minute or so but they cause tiny freezes every time. I know coroutines can help with this but I've not got the slightest clue how to use them. I have researched but I still don't get it.

[Acecool]

Make sure you're not forcing the module to "wait" until the query is finished. MySQLOO and TMySQL both work asynchronously meaning they spread themselves out over x frames ( like how co-routines work ) so unless you specify in the query for it to wait, it should be fine...

[EvacX]

Why do you need some queries to run every few minutes in the first place?

[EvacX]

And saving values periodically is better than saving the values as soon as they change why?

[MattJeanes]

I'm using it to sync evolve playerinfo to an external database, and PlayTime for example updates it every minute, so I figured a 60 second timer would be a good interval, but I could experiment with setting them as soon as they change, not sure it would help the lag however.

Some current code:

for k,v in pairs(diff) do
	local q
	local t=evolve.PlayerInfo[v]
	if t then
		--print("Updating "..t.Nick..".")
		local values=""
		for a,b in ipairs(fields) do
			local val=t[b]
			if tonumber(val) or val==true then
				values=values..db:escape(tostring(val))
			else
				values=values.."\""..db:escape(tostring(val)).."\""
			end
			if a~=#fields then
				values=values..","
			end
		end
		local qs="replace into playerinfo ("..table.concat(fields,",")..") values("..values..")"
		--print(qs)
		q=db:query(qs)
	else
		--print("Removing ID: "..v)
		q=db:query("delete from playerinfo where UID="..v)
	end
	if q then
		function q:onError( err, sql )
			print(err)
			print(sql)
		end
		q:start()
	end
end

diff is a table of UIDs that have changes, the entire database is downloaded prior to this to check changes. That's my sort of 'master sync' function, though maybe I could have a separate one just to update values as they come in (by adding it to evolve:SetProperty and Player:SetProperty).

Do you think that it's the downloading database or the queries that cause the freezes? I've noticed that the spike is heavier with more players online (and therefore more queries) which indicates that the queries are causing the lag somehow.

[UselessGhost]

I ran into this before with MySQLOO. Very bad performance. I tried wrapping my statements with START TRANSACTION and COMMIT and that relieved some of the hit but I still had reports of lag spikes coming from players. I'd suggest tmysql instead.

[MattJeanes]

Should I be disconnecting and reconnecting to the database each time or keep a persistent connection?

[Willox]

[QUOTE=MattJeanes;46361024]Should I be disconnecting and reconnecting to the database each time or keep a persistent connection?[/QUOTE]

Keep a persistent connection, but be able to handle loss of connection.

[andreblue]

[QUOTE=MattJeanes;46361024]Should I be disconnecting and reconnecting to the database each time or keep a persistent connection?[/QUOTE]

I Also suggest trying to run it as a single query instead of per player.
Mysql has a multivalue updater.

Table Before the query
[IMG]http://andreblue.com/sharex/uploaded/2014-10-30_01:13:01.png[/IMG]
After the query
[IMG]http://andreblue.com/sharex/uploaded/2014-10-30_01:18:30.png[/IMG]

You mark per case where it should be and what to. You also specify a default if you want else you can remove the ELSE 'value' before the end.

UPDATE test SET
    rank = CASE WHEN ids=1 THEN 'owner' ELSE 'user' END,
    rank = CASE WHEN ids=2 THEN 'user' END
WHERE ids IN (1,2)

[MattJeanes]

Alright I've fixed the issue based on experiments and your feedback so thank you. Basically gave the code a reshuffle and changed how it works, and now it seems to run async where it didn't before somehow. Full code for anyone interested (uses mysqloo):

-- MySQLoo

require( "mysqloo" )

local lost=false

function Abyss:SQL(callback)
	if not Abyss.DB or lost then
		if Abyss.DB then
			Abyss.DB=nil
		end
		--Abyss:Print("New database")
		local db = mysqloo.connect( -redacted- )
		db:connect()
		function db:onConnected()
			Abyss.DB = db
			callback(Abyss.DB)
		end
		function db:onConnectionFailed( err )
			Abyss:Print("Failed to connect to database: "..err)
		end
	else
		callback(Abyss.DB)
	end
end

function Abyss:Query(qs,callback)	
	local db=Abyss:SQL(function(db)
		local q=db:query(qs)
		q:start()
		function q:onSuccess(data)
			--Abyss:Print("Query successful: "..qs)
			if callback then
				callback(data)
			end
		end
		function q:onError(err,sql)
			Abyss:Print("Query failed: "..err)
			local status=db:status()
			if status==mysqloo.DATABASE_NOT_CONNECTED or status==mysqloo.DATABASE_INTERNAL_ERROR then
				Abyss:Print("Query failed: Database connection lost")
				lost=true
				Abyss:Query(qs,callback)
			end
		end
	end)
end

function Abyss:Escape(str)
	return sql.SQLStr(str,true) -- Why not
end

function Abyss:TestQuery()
	Abyss:Query("select 1+1", function(data)
		PrintTable(data)
	end)
end