search

PyvesB / advanced-achievements

:fireworks: Popular plugin that adds unique and challenging achievements to Minecraft servers.
https://www.spigotmc.org/resources/83466
GNU General Public License v3.0
199 stars 215 forks source link

delay on my server #910

Closed nolberh2 closed 3 years ago

nolberh2 commented 3 years ago

when i connected plugin to mysql it started to have lag, however when i was using h2 it was working correctly

logros34

nolberh2 commented 3 years ago

The test was done with 6 players connected (I actually have 30-40 players)

PyvesB commented 3 years ago

Hello @nolberh2 ! 👋🏻

You've not filled in the information in the issue template, but I'll assume that you're using Advanced Achievements 6.7.2 and standard Spigot 1.16.4.

Sorry to hear that you're experiencing some lags. Advanced Achievements uses the exact same code and database queries regardless of the database type, if the plugin is performing well with H2 but not with MySQL, this suggests that something is wrong with your server's MySQL setup.

What version of MySQL are you using?

Is you MySQL database local to your server? If not, is it within the same virtual network as your server or is the connection to the database happening on public Internet?

nolberh2 commented 3 years ago

I use mysql from a host company, "Pebblehost" I think the datacenter is in Newyork and my server is in Miami

nolberh2 commented 3 years ago

is it better to use h2 or sqlite?

nolberh2 commented 3 years ago

If I use sqlite, is it possible to upload sqlite to mysql in the future?

PyvesB commented 3 years ago

I use mysql from a host company, "Pebblehost" I think the datacenter is in Newyork and my server is in Miami

That's probably part of the problem, if the database and the server are in two different locations and communicate with one another over public Internet (I would be surprised if they had a dedicated network link), there will be a delay on every single database query. Advanced Achievements does writes in parallel and caches as much player data as it can in a lazy fashion, but ultimately it will have to do some requests on the main server thread every now and again, which will cause lags if the communication with the database is slow.

Additionally, there seem to be quite a few threads on the Internet suggesting that Pebblehost has poor MySQL performance (here for example) and I've got a feeling they're using an old 5.x version of MySQL which won't help either.

Best to rule out MySQL and PostgreSQL given your use case. :)

is it better to use h2 or sqlite?

H2 is significantly faster and is used in the default configuration, that would be my recommendation. However, worth keeping in mind SQLite is more mature and has been around for longer, it may be easier to migrate from SQLite to MySQL than it is from H2 to MySQL. Up to you :)

If I use sqlite, is it possible to upload sqlite to mysql in the future?

Yes, though it's a manual process which you can't do from within the plugin itself.

PyvesB commented 3 years ago

@nolberh2 did you go for H2 in the end and is that working as expected? Or did you choose SQLite?

nolberh2 commented 3 years ago

I opted for h2 and the plugin is working correctly, thanks!

PyvesB commented 3 years ago

Great news! Will close this issue, feel free to reach out again in the future if you need any other help. 😉