Bentobox JSON lags too much

LeBlach commented 1 month ago

Expected behavior

I'm probably experiencing a lot of lag because of the json, but when I want to migrate from JSON to SQLITE, I can't migrate because I get a lot of errors.

When I download from bentoboxworld site, I only get database locked error, but in jenkins version it can't even find the data of the players.

Observed/Actual behavior

The plugin is supposed to transfer the data correctly, but it fails to do so.

Steps/models to reproduce

Screenshot_1

BentoBox version

2.4.0 SNAPSHOT-b2645

Plugin list

I don't want to share.

Other

No response

tastybento commented 1 month ago

I see a mixture of things:

Looks like you have some lag happening due to the Limits addon. Do you really need the Limits addon? It's mostly designed to try to avoid lag by limiting things like hoppers, but if it's causing lag, then it's not doing what it is supposed to be doing. It'd be good to understand what settings you have for Limits
I doubt very much that using JSON is why the server is lagging. It's likely some kind of config of Limits, or some bug. Share the link to the timings report if you can.
Moving from JSON to SQLITE - that's not a good tradeoff. SQLITE is just a filesystem based database and not any more efficient that raw JSON. However, the fact you're getting errors is an issue. How are you doing the migration? Can you give me the steps you tried?

LeBlach commented 1 month ago

blocklimits: HOPPER: 200 SPAWNER: 1000 entitylimits: villager: 5

I removed the limit plugin and the lag was fixed.

Sorry, I don't have a timings report.
I shut down my server, I wrote JSON2SQLITE in bentobox config, then I turned on my server and got these errors.

BONNe commented 1 month ago

Tasty, I tried migration on 2.2.0 and it worked, but on 2.4.0-dev it stopped working. It as complaining about too many accessing at the same time to SQLite database.

Also I tried to migrate from SQLite to JSON and had the same issue.

Migrating form JSON to MariaDB also run into issues. Something with migration is not working anymore.

tastybento commented 1 month ago

Okay. Thanks @BONNe. It sounds like this needs some looking into then.

tastybento commented 1 month ago

JSON2MARIADB works okay for me. I don't have a huge database, but it moved everything. @BONNe what error did you see? I'll try SQLite.

>bbox migrate
[08:04:13] [Server thread/INFO]: BentoBox > Migrating addons
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bentobox.database.objects.Players
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.controlpanel.database.objects.ControlPanelObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bentobox.database.objects.Island
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.level.objects.IslandLevels
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.challenges.database.object.Challenge
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.magiccobblestonegenerator.database.objects.GeneratorBundleObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.warps.objects.WarpsData
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bank.data.BankAccounts
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.biomes.database.objects.BiomesBundleObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bentobox.database.objects.TeamInvite
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.cauldronwitchery.database.object.MagicStickObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.biomes.database.objects.BiomesObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.biomes.database.objects.BiomesIslandDataObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating com.wasteofplastic.invswitcher.dataobjects.InventoryStorage
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.aoneblock.dataobjects.OneBlockIslands
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.level.objects.LevelsData
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.challenges.database.object.ChallengeLevel
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bentobox.database.objects.IslandDeletion
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bentobox.database.objects.Ranks
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.magiccobblestonegenerator.database.objects.GeneratorTierObject
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.challenges.database.object.ChallengesPlayerData
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.bentobox.database.objects.Names
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.checkmeout.objects.SubmissionData
[08:04:13] [Server thread/INFO]: BentoBox > Migrated
[08:04:13] [Server thread/INFO]: BentoBox > Migrating world.bentobox.greenhouses.data.Greenhouse
[08:04:14] [Server thread/INFO]: BentoBox > Migrated
[08:04:14] [Server thread/INFO]: BentoBox > Migrating world.bentobox.limits.objects.IslandBlockCount
[08:04:14] [Server thread/INFO]: BentoBox > Migrated
[08:04:14] [Server thread/INFO]: BentoBox > Migrating world.bentobox.magiccobblestonegenerator.database.objects.GeneratorDataObject
[08:04:14] [Server thread/INFO]: BentoBox > Migrated
[08:04:14] [Server thread/INFO]: BentoBox > Migrating world.bentobox.likes.database.objects.LikesObject
[08:04:14] [Server thread/INFO]: BentoBox > Migrated
[08:04:14] [Server thread/INFO]: BentoBox > Migrating world.bentobox.warps.objects.SignCache
[08:04:14] [Server thread/INFO]: BentoBox > Migrated

tastybento commented 1 month ago

SQLite also moved fine. I'm guessing it may be something to do with he size of the database. Let me try and find one that is bigger...

tastybento commented 4 weeks ago

I had a look at the code and found the root cause:

The transition databases, e.g., SQLITE2JSON or JSON2SQLITE operate by shifting any loaded objects into the new database when they are loaded and then deleting them from the old database. This results in a gradual transition over time as the database is used organically. Servers can run in this mode perpetually.
The way that migration is forced to complete via the BentoBox migrate command is to simply force-load every known database, which causes all the data to be transferred over to the new database. The problem is that although the loading returns immediately, the new database is being saved to async, so if there are a lot of records, this can take a bit of time. When the next table is requested and that too kicks off saving, the prior table may not have finished saving yet, which results in the database throwing a BUSY/LOCKED error.
SQLITE does not handle simultaneous calls to itself and locks the database to prevent them. So if a call comes in while it is locked, the database action will be blocked. This makes transitioning to SQLITE particularly problematic.

Additional Problem (major):

While looking at the transition code, deletion of the old record could happen before the saving of the record completed and so if the saving did not complete for some reason, e.g., the SQLITE database was locked, then the record could be lost. Corrective Action:
Steps were taken to only delete records if saving had been successful.

For the main issue, there are two potential solutions:

Slow down the migration with a delay between each table so that each one can finish processing before moving onto the next one
Make the migration programmatically sequential, so that table processing cannot overlap

I really wanted to do 2, but it became very complicated, which for a function that should be run just once, and very rarely, made the lift too heavy. So, I just added a 1 second delay to each table in the migration command, which is more than enough to allow the transition to happen. It's not a perfect solution, but good enough. These changes apply to any transition.

So, transitions should be more robust now. I'd like to get feedback on whether any other issues are seen.

BentoBoxWorld / BentoBox