What are theses mysql requests ?

[WarnDa]

Minecraft's Version

1.19.3 pufferfish

Plugin's Version

2023.1

Describe the bug

I checked mysql logs and saw that you send a lot of query about each island members, it makes 1 request per player online and per second, I don't think it's normal, and maybe it could be reduced

To Reproduce

enable log on mariadb

Additional Information

No response

[OmerBenGera]

SSB does not save the last update time if it's the same or set to -1, which is very odd.
Enable debugging on SSB using /is admin debug QUERY_DATABASE and see if you also see it printed on SSB's side.

[WarnDa]

SSB does not save the last update time if it's the same or set to -1, which is very odd. Enable debugging on SSB using /is admin debug QUERY_DATABASE and see if you also see it printed on SSB's side.

Yes, but seems not for everyone, only some islands

For example the island a4d515fb-54de-45c9-bf03-f4baa1ae4e24 update 9 times each second

[WarnDa]

Their island :

On my server I use "discord" as a lore system for island, maybe their discord is too long ?
Edit : changed their desc to "test" and still the same, it saves it 10x per second
One member on their island is online, and 3 are offline (but were online yesterday)

[OmerBenGera]

If you run /is admin debug show_stacktrace it will print what exactly called that. Run it, wait for an update to occur and send everything that was printed.
It will be q bit spammy, so disable it after that.

[WarnDa]

If you run /is admin debug show_stacktrace it will print what exactly called that. Run it, wait for an update to occur and send everything that was printed. It will be q bit spammy, so disable it after that.

https://pastebin.com/x9D4eWY2

[OmerBenGera]

Keep the stack trace enable, but also run /is admin debug SET_ISLAND_LAST_TIME_UPDATED
Send me the new logs through pastebin again

[WarnDa]

/is admin debug show_stacktrace

With query_database : https://paste.helpch.at/rehodegaha.php
Without : https://paste.helpch.at/acacicijaw.md

[OmerBenGera]

Check out latest dev build, let me know if it is better.

[WarnDa]

Check out latest dev build, let me know if it is better.

It's not possible to increase the cache delay and do not push update every ~2s if an island place or break a block ? Something like 1mn would be fine, I had the same issue with my quest plugins and had to add a longer cache
Because with ~100 islands loaded it makes a lot of requests to the database, and it makes me getting iowait on my disk because of 300Mb/s write

(Here it's just 1 island, the query are quite long)

[OmerBenGera]

Check out latest dev build, let me know if it is better.

It's not possible to increase the cache delay and do not push update every ~2s if an island place or break a block ? Something like 1mn would be fine, I had the same issue with my quest plugins and had to add a longer cache Because with ~100 islands loaded it makes a lot of requests to the database, and it makes me getting iowait on my disk because of 300Mb/s write

(Here it's just 1 island, the query are quite long)

The plugin updates block counts to DB only after enough interactions for the island - the amount depends on the amount of players on your server. The more players you'll have, the more interactions will be required to save the block counts.

[WarnDa]

It's fixed thanks !
.
.
.
But your plugin still does a lot of heavy requests to the database

It wouldn't be possible to push them less often and keep a longer cache ? (Here 100 players online)
Luckperms requests are way less heavy

[OmerBenGera]

LuckPerms tracks much less data?
It only updates the DB when you update the permissions of players, unlike SSB that tracks much more data.

Anyways, I will look into making it saving the block counts less frequent.

[OmerBenGera]

Check out latest dev build, hopefully that's better.

[WarnDa]

Check out latest dev build, hopefully that's better.

Here is only 50 players online but it sends requests every seconds that are pretty heavy (I'm on latest dev build)

Here is 3 seconds of mysql :

You can't just add an option in config to increase the cache delay ? So I can set it to 1-2mn ?

[OmerBenGera]

It sends them for different islands tho.
I will make all these updates cached & sent with delay.
How often do you see the dirty chunks update? It is more important performance-wise to be saved than the block counts. Therefore, I prefer to keep it updated when needed, but if it is spammed as well then I will cache as well.

[WarnDa]

It sends them for different islands tho. I will make all these updates cached & sent with delay. How often do you see the dirty chunks update? It is more important performance-wise to be saved than the block counts. Therefore, I prefer to keep it updated when needed, but if it is spammed as well then I will cache as well.

I didn't see a lot of dirty chunk requests, so seems fine for me on this, maybe just cache block counts and I'll check after the number of requests to see if it's fine

[OmerBenGera]

@WarnDa I added configurable block counts save threshold, where you can configure how often block counts will be saved to DB.
This makes it so each island needs to have enough block updates before it saves the block counts to DB.
I made the default threshold (the required amount of updates) to 100, hopefully it will be better.

Let me know if it's better.

[WarnDa]

@WarnDa I added configurable block counts save threshold, where you can configure how often block counts will be saved to DB.
This makes it so each island needs to have enough block updates before it saves the block counts to DB.
I made the default threshold (the required amount of updates) to 100, hopefully it will be better.

Thanks will try it this weekend.
What if an island only change 50 blocks and then all members disconnect ? It will save all theses islands on the server stop ?

[OmerBenGera]

@WarnDa I added configurable block counts save threshold, where you can configure how often block counts will be saved to DB.
This makes it so each island needs to have enough block updates before it saves the block counts to DB.
I made the default threshold (the required amount of updates) to 100, hopefully it will be better.

Thanks will try it this weekend. What if an island only change 50 blocks and then all members disconnect ? It will save all theses islands on the server stop ?

Of course.
It will save "pending saves" when the server stops (block counts and island chest)

[WarnDa]

Of course. It will save "pending saves" when the server stops (block counts and island chest)

Perfect, just hope I won't get too much data to save and it won't make data loss.
As I got ~600 daily players, and they won't always just place 100 blocks, but 60 or 130, so it will need to wait on server stop to save them

[OmerBenGera]

Of course. It will save "pending saves" when the server stops (block counts and island chest)

Perfect, just hope I won't get too much data to save and it won't make data loss. As I got ~600 daily players, and they won't always just place 100 blocks, but 60 or 130, so it will need to wait on server stop to save them

Until now it would only save block counts to the island that hit 50 block updates globally, which means it was like a round robin between islands.

[WarnDa]

(Still on superior b3 as newer version get issue on island creation)
But is it normal that it saves dirty_chunks as often as block_counts ?

More detailled query :

[OmerBenGera]

Every time that a block is placed at an empty chunk, it will update the DB. Players can also cause DB updates by spamming commands.
Therefore, I think your best solution will be to upgrade the hardware of the server your DB is hosted at.

[WarnDa]

Every time that a block is placed at an empty chunk, it will update the DB. Players can also cause DB updates by spamming commands.
Therefore, I think your best solution will be to upgrade the hardware of the server your DB is hosted at.

Better hardware ? Already got the best one with NVME Samsung 990 pro and 13900K, but even with the best hardware if you spam it with requests it decrease his performances as the disk spend too much time on the queries and get less performances for the server.

[OmerBenGera]

Every time that a block is placed at an empty chunk, it will update the DB. Players can also cause DB updates by spamming commands.
Therefore, I think your best solution will be to upgrade the hardware of the server your DB is hosted at.

Better hardware ? Already got the best one with NVME Samsung 990 pro and 13900K, but even with the best hardware if you spam it with requests it decrease his performances as the disk spend too much time on the queries and get less performances for the server.

You have many islands, it means many requests.
This will also happen if people spam a command that queries thr DB. We cant really avoid it.

[WarnDa]

Every time that a block is placed at an empty chunk, it will update the DB. Players can also cause DB updates by spamming commands.
Therefore, I think your best solution will be to upgrade the hardware of the server your DB is hosted at.

Better hardware ? Already got the best one with NVME Samsung 990 pro and 13900K, but even with the best hardware if you spam it with requests it decrease his performances as the disk spend too much time on the queries and get less performances for the server.

You have many islands, it means many requests. This will also happen if people spam a command that queries thr DB. We cant really avoid it.

Yes there is 140 players online so many islands loaded
By adding a cache for dirty chunks as you suggered before ?
But it would cause issues ?

[WarnDa]

Here is on b12 with your latest patchs, how can 2 islands are being pushed to database multiples times per second ?
my config : block-counts-save-threshold: 500

I checked the island in the database and saw nothing :

Is it because of their cactus farms, so it count for broken blocks ?

So I tried to set the randomtickspeed to 0 (so no cactus broken) but still the same :

So it doesn't come from here

[OmerBenGera]

You can use /is admin debug block_place and /is admin debug block_break to find out which blocks are placed/broken for that island.

[WarnDa]

Okay it was their automatic farms in the other dimensions (melon, sugar cane, other cactus)
But there is no fix except putting block-counts-save-threshold: 10000 ?
But it would still save the island every 2-3s, and putting 1000000 as a thresold isn't optimal

That's why an option to save islands every X second would be better than a block thresold :/

[OmerBenGera]

Okay it was their automatic farms in the other dimensions (melon, sugar cane, other cactus) But there is no fix except putting block-counts-save-threshold: 10000 ? But it would still save the island every 2-3s, and putting 1000000 as a thresold isn't optimal

That's why an option to save islands every X second would be better than a block thresold :/

You can remove the crops from having worth value or anything like that.
Crops will not have that affect anyways on the threshold as they are placed and break in a matter of seconds, unless the farms are so huge that they exceed the threshold in this period of time.

[OmerBenGera]

Also, having a timer will not fix the issue, as you'll deal with the same issue - islands will be saved all the time because they have block changes due to your farms all the time.

[WarnDa]

Also, having a timer will not fix the issue, as you'll deal with the same issue - islands will be saved all the time because they have block changes due to your farms all the time.

Yes but they would be saved only once every 5mn, not every thresold.

I tried to remove all useless farms blocks, will try
No issue if many islands are keeped in cache for many hours ?
A way to auto save these island would be optimal (if all members disconnect for example, not to get a large cache for nothing)

[WarnDa]

Edit : there is still an issue :

My threshold is at 1000 blocks, but it still update the islands way too much compared to the amount of block place/break
You can see on the pastebin that it has only 292 lines in 3s of debug (so less than thresold) but many push to database : https://pastebin.com/YREVMSfY

[OmerBenGera]

Try replicating the issue yourself.

[WarnDa]

Will check with a dev to change the cache to add a customisable delay instead of a threshold, that would be large better and avoid island being in cache for 10hr without change or islands to be pushed every 10s if it's too active...

[WarnDa]

Try replicating the issue yourself.

There is a reason why it's always the same single island.. or you could add a delay and islands can't being push to database unless a certain amount of time :/
I don't want to get another dev for this like I did for mob limit issue, it's expensive.. But this issue kills my disk and slow down performances

[OmerBenGera]

Try replicating the issue yourself.

There is a reason why it's always the same single island.. or you could add a delay and islands can't being push to database unless a certain amount of time :/ I don't want to get another dev for this like I did for mob limit issue, it's expensive.. But this issue kills my disk and slow down performances

Let me know how to replicate the issue myself.
As I said, try replicating the issue yourself, so we can understand what causes this.
Farms shouldn't affect that as the crops are "placed" and then "break", making the threshold counter not being affected by it.

[WarnDa]

Only thing that changed betweend the two requests is a single block with 4 differences.. So it doesn't come from the block count

Chunks are identical :

The last time updated seems good

If I recalc twice his island worth and level do not change :

If I set gamerule randomtickspeed to 0, it fixes the issue
So I got no way to find the issue except analyzing the code, there is nothing else than block count that trigger the recalc ?

[WarnDa]

Update : after hours searching the issue, it has something with saplings
Here I saw all theses sappling, and the debug database_query spammed this island refreshing

I just did a /cut on theses sappling, and no database queries

I reproduced it on my island by doing a worldedit of a lot of jungle sapling and placing blocks on top of them

.
.
.
.
.

Steps to reproduce :

• place sapling
• place block in top of them not for them to growth
• spam them with bone meal to try to growth them
• it will spam the database with queries

Note : In spigot.yml I got : sapling-modifier: 300

[OmerBenGera]

I managed to replicate the issue, good job.
I am gonna look into it straight away.

[OmerBenGera]

Grown structures will not save block counts anymore, only if the threshold was reached.
I also added to /is admin debug show_stacktrace so it will show original stack traces when switching between threads, therefore the /is admin debug query_database will better show what caused the query to be executed, so we can better understand the causes of such issues in the future.

Let me know if it is still an issue.

[WarnDa]

Seems fixed, no spamming today, if it happens again I'll open another issue :)
Thanks for the fix