How can I eliminate this lag on my server?

I am trying to pin point what the issue is with my server. When players try to change weapons it takes 5 mins years to register the change. It slowly feels like it is breaking down right before it decides to crash and needs to restart for it to play again.

Things I think could be causing it:
-The MySQL functions to save data on my server
-Maybe I used to many user-messages or something
-Maybe I am overusing the callback functions for saving data in the MySQL database
-Could my MySQL database be to large for this and is causing a huge delay in lag? There are 898 pages of information in my database. That is 26940 worth of info
-I have sourcebans on my server along with ulx mysql on it

Addons on my server - http://steamcommunity.com/sharedfiles/filedetails/?id=225661705

Net-graph information -
http://cloud-4.steampowered.com/ugc/532871246149935719/FBD3BA2A845D9C3B2958EE2F7C71ED8BDC20E0CD/[/t] [t]http://cloud-4.steampowered.com/ugc/532871246149973383/A54915A6B3425857A37D519C606E2FCB4E252120/

The MySQL code uses call back functions to send and receive the data.

Main code for mysql funciton Credit to everyone who helped me with this like robotboy and everyone else:



function testget(self,key, default, callback)
    local retVal = default
	
    insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")
    insertQ.onError = DBError
    insertQ.onData = function(self, data)
        retVal = data.value or default
	end
	insertQ.onSuccess = function(self)
		callback(retVal);
    end
	
	
    insertQ:start()


end


The server is running Linux.

a) How many times are you running this
b) How big it’s your db and the data that you’re sending
c) Why are you using onData while you writed onSuccess inside

I know this is WAAAAY off-topic, and I may probably get banned or get many dumb ratings, but what’s that thing with some graphics on the corner of the screen called?

netgraph 4

A) The MySQL functions are called every time someone is killed

B) I put the size of my database in the original post. It is 898 pages of information in my database. That is 26940 rows.

C) The last time I messed with this, that was how I got it to work correctly.

That is called netgraph. Type “net_graph 3” in console.

Thank you guys(i expected to recieve alot of dumb ratings instead of replies xD)

Is the table properly indexed?

Cache UniqueID, keep information you need to update on the player and query the database every few minutes or before a map change (if the players data is dirty)

  1. UniqueID is a bad way to index players, using SteamID64 would be better (collisions and such)
  2. Your MySQL table @ 26k rows is tiny. It can handle scores more.
  3. Make sure your indexing an actual integer and not a string.

In MySQL run SHOW CREATE TABLE tablename for the tables that are in question, and I will tell you what you can do to fix any errors if you have them.

First off, using UniqueID has nothing to do with your problem and you can keep using it without any problems (SteamID64 is still better). The performance difference between caching and not caching is 30% and is therefore negligible (0.15 ms compared to 0.2 ms for 100 uniqueids), CRC32 is pretty fast.

So the main questions you need to answer us:
Is your mysql server on the same machine as your srcds? If not how far away is it/how high is the ping?
How many queries are being ran per second (on average)?
Try timing your queries using SysTime() so we can see exactly how long they are taking to execute.

It’s not his problem, but SteamID64 is still a better alternative. I hope no one took that as it was the source of lag, that would be stupid to think that.

We still need to see the structure of his tables that he is querying though in order to see how his keys/indexes are setup, along with his datatypes.

It is on the same server.

Also I setup my database like this:
http://i.gyazo.com/211c0145cdcb1c044063d5fc4b7e7852.png[/t]

And here is the cpu chart I just got: [t]http://i.gyazo.com/d63fe932ff17174ae6ea5dc4cb03c558.png

Also on every kill it saves the stats of the persons kills, kills with that weapon, and Head-shots with that gun.

I am gonna try changing things around in my code tomorrow or tonight after school work. I think I am just gonna set a variable on player join after it gets the players stats and on leave or endgame it will just save it all then. Although i might still run into the issue of overloading the query I feel.

That doesn’t tell me anything. Please give the results from the SHOW CREATE TABLE tblname query so we can actually tell what the structure of the table is.

Also, I am logging every shot that gets made in my server. I just benchmarked it with 12 bots shooting the fastest weapon I have all at the same time, the server never even stuttered.

When does the lag actually occur?
It’s amusing how everyone is focussing on mysql when that might not even be the cause.

Are those netgraphs of when the lag occurs?
The server is still running close to 66 tick at the time so it’s not really overloaded, and there’s no complete freezes from the looks of the graph.

You do have loss and choke though, does anyone else get that?

tl;dr
When does it lag?
What are players doing at the time?
Does it affect everyone?
What kind of lag is it?

There’s lots of red in the net graph, so that is probably more something along the lines of something really heavy in a hook, or something else causing problems within the code. IIRC, MySQL hangs should show as a blank spot in the netgraph, not under the entities category (but don’t quote me on that).

Still, we need to see how you’re calling the queries from Lua and the structure of the table they are used on. 9/10 it’s probably something related to queries with MySQL (ie, called too often, or in a weird place in Lua).

MySQL queries are async, so they don’t block the server at all (no lag).
What I think his problem is is that the queries take a long time to execute and the time actually increases as longer the server runs. If that is not the case then please OP tell us what exactly you mean.
I think his problem is that he has too many queries that just fill up the queue because they come in faster than they are finished. Which means that the queue gets bigger and bigger over time creating a higher delay between starting the query and it being executed.

Depends how the code is written… they can be made synchronous.

I’ve seen a lot worse in ttt and in my prop hunt server, the red isn’t really that much of an issue unless you know the baseline is supposed to be lower than that.

The gaps do look like stutters, but that can be a local issue, not guaranteed to be server related.

Edit: Just double checked, you get red on the bottom edge of the screen where the solid blue is if the server freezes and doesn’t send anything to the client.

As someone who’s been using mySqloo for a fair while, I can’t fathom to think of why that would ever be a good idea.

On GM Shutdown, allow queries to run before allowing shutdown sequence to complete, otherwise they’d start but the Shutdown sequence would be completed before sql queries had a chance to be processed.

Are the weapons or anything else by chance using DTVars?
In this current version of Garry’s Mod, usage of those vars will cause spikes in the net graph like that, at least when setting them.
Kilburn fixed it in the dev version though if I recall correctly.