Use SQLibrary to store the data-model instead of yml

[rlf]

We've been discussing this for ages, and we feel we need to move on it now.

We are seeing a lot of performance issues regarding the UUID/Name change support (Which was intruduced as a temporary hack).

Also, servers with a lot of players (many files in both the island and player folder), are seeing extreme lag when the renaming has to occur.

Solution: Introduce an SQLite database instead, and store PlayerInfo, IslandInfo and ChallengeCompletion in separate tables, using the UUID as foreign key for playerinfo, and keeping track of name, displayname only one place.

This will make "renaming" easy (just update one entry, with a fast UUID lookup), and also make the trust, ban and members for IslandInfo easy (just a relation-table of @OneToMany from IslandInfo to PlayerInfo).

[mcraftpete]

How long are we talking , will there be any other update to fix performance before this as its more then just a little bad right now ? My lag which is caused from uskyblock is becoming unbearable , i have done lots of things such as clearing lots of islands etc but it all comes down to the onnamechange event. Yes sql will definitely help this , but i have to make changes sooner rather then later.

[rlf]

The problem you are facing, is the combination of the name-change (due to the faulty UUID-Name model in USB), and the async file-loading introduced in HF6. The change from yml files to a relational datamodel (as SQLite), should fix a lot of the issues we are having, with data becoming "corrupt".

[rlf]

@mcraftpete The "latest working" version I think is the latest HF4 version, try that, I hope to have a new SQL based release within 1 - 2 weeks (pre-release ofc)

[mcraftpete]

Back to 4 ? I have been using recent versions will that cause me any issues if I was to go backwards ? , With dutchys help I do at least now have regions around all my islands and have been able to /usb island delete player to clear a few islands etc. Do you mean go back to this version ... https://github.com/rlf/uSkyBlock/releases/tag/v2.3-HF4e If I did will everything function is in players namechanges still recognized etc ?

[rlf]

It should... no guarantees though

[mcraftpete]

Hi, I was wondering how progression of the sql system was going , I have held back from changing my version and trying to bide my time in making changes that could make things worse rather then better :P

[mcraftpete]

Also are you plans to make it convert existing data on first run ? or how will that part of it work ?

[rlf]

Sry @mcraftpete I have been totally swamped the last couple of weeks - but my summer-vacation starts on Friday - and hopefully I can take a good stab at it then.

I plan on doing an auto-convert on first run, and also make it available as a /usb import yml2db import command.

I think the core of what you are experiencing, is based on the name 2 UUID conversion. With the number of islands and players you have, none of the solutions that come to mind (except the database solution) will be sufficient.

I am considering (how-ever), to keep the yml files, and just make it correctly UUID aware - but that will take quite some time to "convert", esp. for your setup.

[mcraftpete]

ok thast cool , just needed to know so I can decide what steps I make next , I have stuck with it in the hope it will eventually come good , keeping player numbers down currently by spreading to another similar style server on my network but with t eh hope once we are all good again I can do some promotional stuff on the skyblock server to bring back the players. In hindsight yes the number of islands and uuid name changes can be part of the problem , but I feel the lack of a fully working purge system is even more so , we wouldn't have the vast number of islands and player data in the first place if this was fully operational so I feel part of this fix process should be finding a way to purge properly , with each problem comes something new , like on name changes when they didn't work also came the issue that players were then creating new islands with new name knowing they had an island on their old name , so when you are trying to uuid convert it has gotten a little confusing. Most of the larger plugins for example essentials convert the player.yml to the uuid.yml , although in some ways I find this harder when trying to look fro player info etc it is also 1 less thing the system has to calculate in the plugin , is this not something that could possibly decrease any calculation processes onjoin ?

I am keen to stick with this as I have informed all my players that I am working to keep their large islands etc and do not want to have to fully restart everything from scratch.

If I can be of any help at anytime , let me know, I will only be too glad to try to work to a resolve so that we can have this running smoothly again :)

[rlf]

I think we might be able to look into both moving to SQLite, and perhaps a "faster?" fix for UUID.

[mcraftpete]

ok I am keen to see changes and help in any way I can , my players have been pretty good and understanding , its being caught in the middle where some players have spent months building spectacular islands but anything short of a reset/update will not fix the issues we have , I have tweaked every plugin I can to get optimum performance but it always comes back to I cant really do anything about the skyblock plugin. I was tempted to test a purge but I dare not , I know this can cause other problems , manual delete process does work but with thousands of islands is not practical , in fact I spent 3 hours manually deleting some and the new players were coming in quicker then I was deleting , they was indeed coming to the islands I had deleted which is a good thing as it made those islands fresh ,but second I stop deleteing they are creating new islands :P So yes very keen to see sql implemented and a way for it to recognize namechanges cleaner will definitely help our cause :) Having sql may help look at the option of purging cleaner which feels like an important factor , less old islands the better the system will handle it. I kinda compare the layout in many ways to plotme , it can smoothly delete 50 plots at a time without causing any lag and one of my servers has way way more plots then my skyblock server has islands.It uses sql and has no lag on namechanging etc so I feel it is the sql factor.

[rlf]

Ok, that is not the plotme I have, that one causes players to be disconnected on /plotme auto. :(

But yes, we are aware of it, unfortunately, the USB coding is done when we can find the time (and the energy for it). I have been totally swamped for a while now, and now my vacation-time starts - which means, perhaps some work the next couple of days - but then definitely off for a week to an island somewhere :)

[dutchy1001]

the UK is an island ;)

[mcraftpete]

ok I understand that everyone is busy irl etc as I am too , I just need to know if the major lag I have currently caused from usb will be resolved in the near future , obviously if sql is the fix then I am only asking how long before it is added so I can make the correct decisions moving forward. To be perfectly honest I am at that point where my skyblock server is dying of lag , solely caused from the skyblock plugin ,cant purge as its bugged , cant go back versions due to namechanges so im stuck and I have to change something , if I know the fix is coming soon I will wait , but I am looking at alternatives such as reset and changing plugin , I have to have all options open as I cannot continue in its current form. On a small server I am sure this functions fine , but once I hit 30 players mine dies and timings confirm everytime it is usb for the last 1 month doing daily reports and often checking 2-3 times a day , 3 months + ago before all the namechanges etc and updated wg I was hitting 100 players on this same server. My players want skyblock but they don't want it like this so I have to change it if it is not fixed , sorry its not having ago merely the question how long it will be for sql and hopefully fix for this.

[dutchy1001]

to combat the lag I would suggest the latest release on here (v2.3-HF6e). Yes there is a bug when players have changed their name and give an error in console, but the game play is not effected (as I seen on our test server). As we spoke before how to change owners for an islands manual is maybe a better option then having lag.

[mcraftpete]

Its more then a bug on that version as it doesn't update the newname unless you do it manually, it created me more issues as when it didn't change the name they started created new islands on new name so its worse then having lag.

[GingerGeek]

I'm from Cubecraft and we run quite a large instance of uSkyBlock, if not one of the largest. Recently we've been having issues with memory leaks which I suspect is to do with all the YAML files being loaded (millions of instances of Matcher, int[], char[]). Moving to a different backend I think would definitely help this.

[harrydevane]

@GingerGeek What version are you running?

[GingerGeek]

Latest verion from repo.

[GingerGeek]

Some experiments I've run: I'm running this on an overclocked i7 (5GHz) with 16GB or RAM. 10 of which is dedicated to the instance. We have around 34k islands and 80k registered players.

I refactored everything to getFileConfiguration() + allowing for a default file to get from resources to be set and then tried this:

• Putting all config through the cache - caused memory usage to get higher quicker and causes a crash as memory usage gets to 100% as GC goes crazy - GC Overhead Limit is reached
• Putting no config through the cache - this seems to positively impact the leak, it still slowly reaches 100% but it doesn't completely lag out, I'll usually just restart once it gets here.

Plugin wise we run: Multiverse/MV Portals. NCP,OpenInv,PeX,VanishNoPacket,Vault,WE,WG and two custom plugins which link into our network infrastructure.

[rlf]

This feature has been set on hold, after the refactoring and introduction of Guava caches in v2.4.

@GingerGeek I'd like to see the changes you've made, because the getFileConfiguration should only cache the config-files, not the player-files. If it does, we need to change ASAP.

[Muspah]

bump

I'd like to start moving towards a system where it would be possible to use different storage implementations. My idea is to start with creating abstraction layers for this (decouple island/player/etc models from storage), re-implement YAML storage in this system and add MySQL/MariaDB support via HikariCP. We could add additional popular storage systems, but I prefer to also add methods to the API to inject/register own storage implementations (with fallback to USB filestorage), especially for larger servers.

I'm thinking of starting with a (draft) implementation of a UUID map stored in a file (csv?) and additional in SQL.

[1stGlitch]

bump

I'd like to start moving towards a system where it would be possible to use different storage implementations. My idea is to start with creating abstraction layers for this (decouple island/player/etc models from storage), re-implement YAML storage in this system and add MySQL/MariaDB support via HikariCP. We could add additional popular storage systems, but I prefer to also add methods to the API to inject/register own storage implementations (with fallback to USB filestorage), especially for larger servers.

I'm thinking of starting with a (draft) implementation of a UUID map stored in a file (csv?) and additional in SQL.

I would just like to say that I would absolutely love to see this as a feature in the future, and I'm glad that you agree. If only I had the time, I would submit a PR for it myself. Looking forward to seeing this someday!