[ttasdasda]

After some testing and checking SM source code, I've come to the conclusion that database operations are single-threaded (they were confirmed to be so ages ago, but haven't seen any mention of it for the past few years). This can be seen here - https://i.imgur.com/zDRHYhL.png, notice how there's only one "SM SQL Worker".

This is a real performance killer, especially in my case, because a multiple-join query effectively blocks all other queries from executing for a few seconds. Takes over a minute for all stuff to load on mapstart, even though it's just a hundred or two of 0.01 - 0.5s queries, which would have taken less than a few seconds to execute had the queries been parallel. The thread is shared between all plugins and databases, meaning a plugin #1 running 1000 queries to database1 will block plugin #2 from running its single query to database2.

I'm sure SM developers have their reasons, however, I can't be the only person to be bottlenecked by this. There are people running heavily loaded servers with 50+ concurrent players (I'm looking at you, surfheaven), so there's gotta be some way of remedying this. Of course, I could offload the queries to some daemon, but that would be way too hacky and would require me to rewrite most of my code.

Any ideas?

[Skyy]

i have one. use threaded queries.
https://sm.alliedmods.net/api/index....d=show&id=364&

[ttasdasda]

Originally Posted by Skyy i have one. use threaded queries. https://sm.alliedmods.net/api/index....d=show&id=364&

I already do, as does every sane person. I even attempted to load-balance it by using several different database connection handles, but that doesn't help, since all the queries from all plugins are still run in a single thread. They do not freeze the game server instance, but they do freeze each other, because all the Worker Thread basically does is grab queries one-by-one using GetLikelyQueue(), then execute it and wait for response, rinse and repeat. And all SM does is keep pushing queries into the queue using AddToThreadQueue(), as long as the worker thread exists (if it doesn't, it simply executes it in the main thread).

I could probably rewrite the SM code to spawn a new worker thread for each query, but my C++ is way too rusty for this.

[ttasdasda]

Somewhat related: https://forums.alliedmods.net/showthread.php?t=313958

[sneaK]

If your queries are taking that long to execute, you should optimize your queries, database structure, and/or mysql installation.

[ttasdasda]

Let's say I have a bhop/surf server with 30 concurrent players, you're pretty familiar with those. On map start, I need to load all of the clients' VIP status. That's 30 queries alone. Then I need to load their surf/bhop ranks. That's 30 more queries. Then I need to load their stage cookie status to restore their run if needed, that's another 30 queries. Then I need to load their records for the map, that's 30 more queries on top of the existing 90. Then I need to get stuff like completed maps to show in the scoreboard. I could go on, but you get the idea.

Then there's additional backgrond stuff like grabbing ads from the database, fetching runs and zones, sql mapchooser...
Then keep in mind that SM worker thread sleeps for DEFAULT_THINK_TIME_MS ms, which atm is 20ms. That alone adds a lot of overhead, essentially meaning that even the fastest query will take at least 20ms to execute - https://i.imgur.com/7Fs2zvn.png, taken from query log.

But all of that doesn't matter THAT much. I could probably cut some of the features, combine user queries to fetch as much data as possible in a single query and introduce a 30-second warmup period. That would be counter-productive, messy from a coding point of view and would worsen the player experience, but would do the job. I could also recompile sourcemod and lower DEFAULT_THINK_TIME_MS. BAILOPAN called this limit "self-imposed", I'm sure I could lower it without consequences.

However, that wouldn't stop long queries from essentially freezing the query execution for 1-3 seconds. Yeah, increasing cooldowns and using DBPrio_Low does help, but only to a certain extent.

If your queries are taking that long to execute, you should optimize your queries, database structure, and/or mysql installation.

That's the problem. My local mysql installation is perfectly fine, it runs on a i9-9900k with more RAM than I'll ever need on a kernel that doesn't have Spectre mitigation; it's significantly faster than some of the DBaaS options that I considered. And since it's run locally, I get to enjoy the benefits of zero ping (keep in mind 20 ping would've doubled the execution time of most queries). I can handle WAY more load than that. All I need is to somehow get around the bottleneck.

[Skyy]

so, with a machine learning system integrated into my server, there are sometimes hundreds of queries being sent in a minute, sometimes in a second, if the server is really populated. I've never heard of anyone having delays like you've mentioned unless there's an issue with their code or the general structure. so...

Originally Posted by sneaK If your queries are taking that long to execute, you should optimize your queries, database structure, and/or mysql installation.

if you need help with this, this is an open source community and you can always share code blocks of excerpts and i am sure the community would be more than willing to help out.

[ttasdasda]

Well, your mileage may vary. I understand your use-case doesn't run into into that kind of limitation. Mine does. It's not like I'm asking for something out of this world, there's a reason why multithreading and thread pooling are there. It's 2019 already. Burst load of several hundred 0.001-0.1ms queries isn't unheard of, I'm sure we can all agree on that. My workload is just a tiny fraction of what, say, this forum experiences every second.

[ttasdasda]

as even 120 queries should not be causing a significant hit.

Like I said, they do not, as can be seen in this screenshot - https://i.imgur.com/7Fs2zvn.png. Most of the queries are almost instantaneous (if you substract the 20ms overhead).

This alone will drop your supposed 120 query count into just 4 (4 different pieces of data for 30 players).

That would be very hacky and not a good practice. The players do not join simultaneously, meaning I would have to do it ~30 seconds after the map has started, essentially leading to the same result (not getting necessary data in time). Fetching all the player's data using one query is not feasible, as the queries are spread out between several plugins.

And anyway, that doesn't help with the fact that even a DBPrio_Low will block any other queries from executing (if the query takes ~2 seconds or the connection suddenly times out).

I would appreciate if we discussed the way to make query execution multi-threaded or the way to somehow offload the query execution, instead of you guys making me explain myself. For the time being, I will recompile SM with lowered DEFAULT_THINK_TIME_MS, which will hopefully help reduce the delay during burst load.

[Peace-Maker]

This is certainly a desired feature request. You can start by opening an issue on the tracker. There was some talk about it 3 weeks ago on IRC between KyleS and dvander. To summarize:

We could start a new thread per connection, so you can manage your connection handles yourself and decide which query uses which one. In any case - you'd lose the linearity of queries. If you run two queries, the second one could finish first after such threading change. Right now you're guaranteed that your queries will execute in the order you send them and people probably rely on this behaviour. So we could add a new query priority like "as-fast-as-possible", where people opt in to not care about queries racing each other.

Heavy caching in the plugin can work around a lot of queries per player, since the data is there already and is loaded on mapchange while the players are still connecting.