mySQL saving taking very long

Discussion in 'Spigot Plugin Development' started by Hex_27, May 28, 2017.

  1. For my plugin, if I save with mySQL (130k objects) when the server stops, the server just kind of freezes for over 5-10 minutes before its done. Is it supposed to?

    Below is an example of how 1 of those objects looks like when saved in gson
    Code (Text):
    {"loc":{"world":"world","x":9,"z":2},"owner":"Seao","claimTime":1495586694858,"structure":{"type":"NEXUS","loc":{"world":"world","x":147,"y":77,"z":32}},"turrets":[{"type":"HEALING","loc":{"world":"world","x":149,"y":78,"z":34}},{"type":"FLAME","loc":{"world":"world","x":149,"y":78,"z":33}}],"signs":[],"name":"world , 9 , 2"}
  2. I would suggest trying a connection pool such as HikariCP and doing your MySQL updates asynchronously. However, that's a huge amount of objects to save, so I think you're going to experience some sort of freezing or slow downs trying to save those all at once. Maybe cut down the load that the server has to save when shutting down?
  3. How? Like periodically unload data throughout the server runtime?
  4. electronicboy

    IRC Staff

    Well, yes; the idea of using a storage mechanism like MySQL is that you don't have all of the data loaded into memory all the time.

    beyond that, the only way to gain extra performance out of mysql is via properly designed databases and proper queries/sane design (e.g. people do weird stuff like checking if a value is in the dabatase and running an update insert if it is, or an update otherwise; when mysql supports this via the insert statement, letting you control how to handle cases with dupes...)
  5. But my plugin is basically saving information to do with chunks, and a lot of chunks are loaded all the time. Any getaround?
  6. What are you trying to accomplish?

    Nevermind, I see. Just save changes to chunks as they happen. Don't save all the chunks at the end.
  7. electronicboy

    IRC Staff

    use a cache, query and pull data on chunk load, and remove and update on chunk unload with a periodic task to update the data for loaded chunks
  8. This sounds like another good idea.
  9. So I can basically just have a constant async task checking if the chunk is not loaded, then unload the data? Because sometimes fetching the chunk information doesn't necessarily have to happen by moving into the chunk
  10. electronicboy

    IRC Staff

    • Agree Agree x 1
  11. Like stuff like I can stand thousands of blocks away and fetch info of an unloaded chunk. ChunkUnloadEvent calls on unloaded chunks?
  12. ChunkUnloadEvent is called when a chunk is unloaded, yes.
  13. It sounds like you've done something terribly wrong because that's really slow. Surely you're only dealing with like 50MB of data in total?
  14. electronicboy

    IRC Staff

    ChunkUnloadEvent is fired when a chunk is unloaded from the server; if you wanna check the data of unloaded chunks in relation to your db setup, you probably wanna look into creating a method that allows you to pass a callback or other so that you can lookup data and query them from the db if needed before running your code (with the fetched data), or passing the work off async and making sure that your system is designed so that it will handle thread safety just fine.

    Guava caches are nice as they'll help with handling a fair chunk of this for you, however, have some downsides in that they're designed to auto-evict, which is somewhat a behavior you don't want and would have to workaround
  15. Then certain information would never be unloaded if I purely use chunkunloadevent. Doesn't that mean I need to constantly loop data to unload it?

    ~30-70. Land size really varies but yea.
  16. That's so little you could just keep it all in memory and if you already are then I don't know why you're using a database
  17. because it needs to persist over restarts?
  18. You don't have to use a database to save things to disk
  19. Then what shall I use?