Speeding up your Server 101 (Windows)
Setup
Tools used in this 101
- UIforETW, we will use this profiler to generate a trace of the for the server.
-
mysql-async 3.0.7 or newer. Running with the
set mysql_slow_query_warning 50line in theserver.cfg - HeidiSQL, because it is visually well-designed for us to improve database structures.
Links
- randomasciis’s etw package This should install the Windows Performance and Assessment Toolkit.
- HeidiSQL, just to visually represent your database.
Finding the source of hitches
In this section we will deal with finding out which resources hitch your server. If you can script yourself, you will probably be able to find out why.
However, if you cannot script yourself, I would recommend posting an issue on Github or the respective topic in the FiveM forums, indicating that the resource hitches the server in a long calculation cycle, and describing under which conditions the hitches occured, e.g. a full server, full database, etc.
Recording hitches
Now for this your server is running, and you see those nasty hitch warnings, indicating your server freezing because the duration of one tick in a certain resource is longer than a server tick (50ms). This is not entirely correct, but it allows you to understand why it is happening.
To record the hitches we start up UIforETW.exe and leave everything on the default settings.
Press the Start Tracing button at your leisure, remember that long traces consume more disk space, so try and record just the hitches. A proper configuration will help reducing the trace size, but this is not part of this post.
Once the server has hitched, you can hit Save Trace and when the Trace was saved, you should Cancel Tracing.
You open then the trace by Right Clicking the Trace on the bottom left and click on Open it in 10.0 WPA. It should then look intimidating like this:
You will notice the FXServer.exe there, with the Provider Name Multi-Main. We click now on the arrow on Task Name >Block to expand it for the FXServer.exe, and then again expand the >End in the Opcode Name column.
You can now see the two columns of importance Description (Field 1) and Duration (ms) (Field 3). You can sort by Duration (ms) (Field 3), at best descending, which is indicated by a down arrow.
Now scroll up and you should see which resources are causing hitches as the Duration (ms) will roughly correspond to the time the server hitched. See the example image for a server without hitches.
The thing that took the longest for me apparently a fivem-map-hipster tick with 0.09ms, so nothing that could even start to hitch the server.
Anything over 50ms should be reported to the threads of the respective resources, or posted as issues on github with a description how the hitch happened, e.g. full server.
If you are okay enough with scripting you can try and find out which part of the code hangs yourself in that resource. If you do, do not forget to inform the people in the resource’s topic on the FiveM forums or on github, or on both.
Speeding up your MySQL queries
Now another issue that might slow down your server are badly indexed MySQL tables or badly written queries to these tables, or both. That is why I introduced the Slow Query Warnings in mysql-async 3.0.7.
Let’s take a look at a few examples of slow queries and their respective tables in HeidiSQL.
Examples
[MySQL] [Slow Query Warning] [es_extended] [794ms] UPDATE user_inventory SET count = 46 WHERE identifier = ‘steam:11000010e6axxxx’ AND item = ‘bread’
[MySQL] [Slow Query Warning] [es_extended] [939ms] UPDATE user_inventory SET count = 50 WHERE identifier = ‘steam:11000010e6axxxx’ AND item = ‘opium_pooch’
[MySQL] [Slow Query Warning] [es_extended] [1198ms] UPDATE user_inventory SET count = 0 WHERE identifier = ‘steam:11000010e6axxxx’ AND item = ‘opium’
These are single line update statements, and should not take 1s. Let’s take a look at the table in HeidiSQL.
Here we see that it only has a primary key. But identifier and item are in a WHERE clause so it should have an index there too. We are going to add the index in HeidiSQL by selecting the appropriate columns as rows, right clicking them, clicking on New Index and then clicking on the green KEY. This should pop up a green key symbol next to the column names.
Congratulations, you query is now faster.
There are a lot of nooks and crannies when optimizing queries, but since this is a 101, I’ll leave it with adding missing Indexes.
Do not forget to report missing Indicies in the respective resource topic on the FiveM forums or on Github, or both.
P.S. This post is not an indepth guide on how to fix stuff, more like a big hint on how to find what is wrong.
