Speeding up your Server 101 (Windows)

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 50 line in the server.cfg
  • HeidiSQL, because it is visually well-designed for us to improve database structures.

Links

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.

38 Likes

seems to be worth of trying, ill try it.

Seems to be working good! Are there any values that is not good to index?

You index whatever is in your where, so it tries to only uses a single index for that query.

This is interesting! Thank you for posting!

I understand the basics of coding and trying (and successfully) to optimize a primitive code when I see one from other sources.

I don’t see any hitch warning on my server atm, so, my question is, does this worth the effort for me? Will the difference visible to me and others, when I am not in real need of changing things in this direction? I’ve found this topic by accident, rly. :stuck_out_tongue:

Don’t get me bad, I just don’t wanna touch anything that is not broken…

I’m assuming this looks quite bad:

Anyone know why is the duration so high or how would I go about fixing it. All of those resources are up-to-date except MySQL-aSync which was but I downgraded it to test if that is causing it. Happened also with that.

Either reporting to the github esx-drugs repo, or fixing it yourself and doing a PR. Some part of the code is just inefficient.

After adding indexes MySQL Slow Query still appears.

1 Like

it seems like yes, I did the test here that when I starto a smaller amount of scripts do not give

Hey, i got problem with etwpackage…
No files are created so i cant read records…
end of message says “System cant find specified file”.
unknown%20(20)

Hey, nice tutorial! <3

I have done this; https://gyazo.com/88e7b5d9a816d48b8d93c70188e8245d
But… I get this "error"after it too. Any1 know?

Hi! this is great, but how would i go about doing this in a linux server?

Thanks in advanced.

Unfortunately it’s not possible

When i try to open the trace, WPA instantly shuts down again. any idea why ?
Edit:
Fixed it by reinstalling WADK, and repairing .NET framework

[MariaDB:10.1.40-MariaDB] [esplugin_mysql] [48ms] UPDATE users SET bank=10200
how can I make fast it?

when I right click on the traces to open it, it close itself after 2seconds

Hey ! I’m not sure how to do the new index > green key on phpmyadmin, i can only connect to my BDD using PhpMyAdmin so if someone know, that will help me :slight_smile:

See + Indexes at the bottom? There

Got it!
If I did understand well ( Probably not because I’m french but I did my best).
I can index all columns that do not move? For exemple, an column “Identifier” where the steam ID of the player is in it should never be changed, can I index that?

any idea what could cause this? our indexes were fixed already

performance_badstuff