Improve writing performance (MySql)

[teerialegends]

Hello,

My machine is currently running 3 game-servers which 2 happen to use MySql for storing in-game balance, now the problem is that on the most visited server with 80 people online daily (The game we run is Terraria) seems to be writing a lot of data per second. I already contacted the help service about this matter and they said there isn't really a way to improve the SSD disk it's running on. And I've currently set random options in MySql to have it able to use everything it needs to write fast, but I do not know the perfect setting for MySql and I could really use some help as our server keeps lagging by a lot once we have more players online.

I am willing to do everything to make my machine work faster and smoother again.

Image taken around 30 minutes ago:
http://i.gyazo.com/e4206754eec260a9fc4ecffe1ad06536.png

[soja]

Our 95 slot server has mysql hosted off-site(about 20ms away) on SSD and we don't see any issues.

If your addon isn't using "threaded" sql, your server can freeze while waiting for a query to finish. If this is the case, you will need to recode the addon so this doesn't happen.

Your disk queue length is measured at .05 for both disks. This would indicate it is not a storage problem.

[teerialegends]

Our 95 slot server has mysql hosted off-site(about 20ms away) on SSD and we don't see any issues.

If your addon isn't using "threaded" sql, your server can freeze while waiting for a query to finish. If this is the case, you will need to recode the addon so this doesn't happen.

Your disk queue length is measured at .05 for both disks. This would indicate it is not a storage problem.

Well the problem is.. I have been editing some settings in my MySql lately but I don't know how I can change it so it would use ultimate performace.. I can also try and reinstall the service to run as a dedicated server as it currently runs as VPS service.

[soja]

You are already writing to SSD. Any performance increases you usually see are from increasing memory usage, but this isn't as useful when you have a SSD.

You might be running into a CPU issue, it is never a good idea to run mysql on a machine with game servers, you are asking for performance problems there. Is what you posted considered normal usage?

[teerialegends]

You are already writing to SSD. Any performance increases you usually see are from increasing memory usage, but this isn't as useful when you have a SSD.

You might be running into a CPU issue, it is never a good idea to run mysql on a machine with game servers, you are asking for performance problems there. Is what you posted considered normal usage?

Well all I know is that Terraria runs on 1 CPU only due to the way it's coded by the Developer. And this is about normal usage yes, but whenever I seem to have let's say.. an event which allows people to gather more in-game currency/exp which obviously causes more and heavy writing then the server starts lagging really badly. If this event is not there (Which I had to cancel today due to the high traffic) then the server seems to be running fine with 80 people online, so I assumed it was a writing issue but it could always be a performance issue CPU/RAM wise (If I am right).

That is why i wish to edit the settings of my MySql to take full advantage and run smoothly so the server will not lag. And it would be a great idea to run the MySql server on a different machine but I cannot afford that, sadly.

[soja]

My apologies i completely blanked that you were running Terraria.

What is the average CPU usage of my mysql process? Could you try manually setting affinity, and allowing your terraria server access to 2 cores, and mysql the rest(but do not let them overlap)?

[teerialegends]

My apologies i completely blanked that you were running Terraria.

What is the average CPU usage of my mysql process? Could you try manually setting affinity, and allowing your terraria server access to 2 cores, and mysql the rest(but do not let them overlap)?

Both have their priority set to Realtime, which sometimes helps a bit. But I could always try and set the CPU's which they run on.. But my question is if this would affect the server's performance? I mean if it uses less cores (even though only 1 is assigned because it doesn't use multithreading) won't it lag more?

[soja]

If you assign the game server more than it needs, you shouldn't notice any performance loss. If it only uses 1 core usually, assigning it 2 or 3 should be fine.

[Edge100x]

I do not recommend setting a game server to real-time priority, as if it has problems, it can lock up the system entirely. If you need it to have a higher priority than other processes, just make sure that it has some higher priority than them, without going to the top.

As soja said, your problem does not appear to be I/O related.

Are you sure this is an issue with MySQL? Do you have specific MySQL queries that are taking a long time? If so, what is an example of one? You can find long-running threads with "show processlist" (many MySQL utilities will run this for you, as well).

From the graphs you posted, it appears that the game servers themselves are using entire CPU cores, and you may need to focus your optimization energies on them, instead.

[teerialegends]

I do not recommend setting a game server to real-time priority, as if it has problems, it can lock up the system entirely. If you need it to have a higher priority than other processes, just make sure that it has some higher priority than them, without going to the top.

As soja said, your problem does not appear to be I/O related.

Are you sure this is an issue with MySQL? Do you have specific MySQL queries that are taking a long time? If so, what is an example of one? You can find long-running threads with "show processlist" (many MySQL utilities will run this for you, as well).

From the graphs you posted, it appears that the game servers themselves are using entire CPU cores, and you may need to focus your optimization energies on them, instead.

I can see that whenever I switch on the event (which is x20 gainments) with 60~ people online the MySql queries go up from around 0p/s~40p/s to 0p/s~300p/s, the InnoDB writes seem to be hopping around 10~30, so that's not a big issue. But with 80+ people online, would probably make the queries go up to 500 if not 1000. And I also noted that whenever the event is on, the game-server use quite a bit more CPU than usual. And alright, I shall run my server on HIGH priority then, as we have had some blue screen kills before.

[teerialegends]

I'd also just like to know what would be the best setting for my MySql server to run smoothly, I have set some options but in most cases I didn't really knew what it meant as much.

[pro4never]

Full disclosure that I've never done any hosting with Terraria but in almost every case where you're running into issues with MySql it's going to be caused with how the server itself is handling the queries.

If you sync data to your database every single time it's modified then you're going to quickly run into issues. I'm not familiar with how Terraria manages its DB but it would shock me if saving character data to MySql every time it's modified is normal behavior... Maybe take a look through whatever mods you are using to see how its interacting with the database rather than trying to 'tweak' mysql settings for performance gains.