Solved [MySQL] Caching / connection question

Discussion in 'Spigot Plugin Development' started by boolean, Feb 19, 2021.

Thread Status:
Not open for further replies.
  1. Hi there! Recently decided to buck up and learn how to use databases (specifically MySQL) to help with a plugin I'm in the process of making, though I had a few questions about its performance on a wide-scale.

    Plugin would look to use "experience" values for three main skills (mining, combat, fishing) that would be incremented on:
    • Block break (mining)
    • Entity damage / entity death (combat)
    • Fishing (fishing)

    My question surrounds how efficient it would be to continually read and modify these values from a MySQL DB/table, would it be better to cache them (on player join) and store locally (presumably through a custom Object in a HashMap, linked by player UUID) and write them to the database every 15-30 seconds? Or just straight up read and write them as and when needed. Cautious about how the continual use of a single Connection (specifically, using it on something like every block break) might impact the performance of the database itself or the server (I'm newer to database design/usage, but wanting to learn & expand my knowledge of the subject)

    #1 boolean, Feb 19, 2021
    Last edited: Feb 19, 2021
  2. Okay so there is a lot here that you have asked about:
    • Firstly, you shouldn't rely on a single connection. Each time you 'speak' to the database you should be opening a new connection opposed to using the same one over and over. Think of it as wearing the same pair of socks because it's easier to do this rather than change them, it's easier but not the best idea.

    • Continious reading from the database, even async, will take it's toll. Imagine players using a efficiency 5 pickaxe w/ haste 2 beacon, each block they break you are communicating with the database and more than likely after a while it will crash because there is so much going on.
      • You mention caching the data, YES DO THIS! Saving it every X seconds however could be risky but it might be better in some circumstanes. If the server crashes then nothing is lost really but there usually is no issue with having it save when a player quits. There was a thread all about data saving and crashes and I'll update this post if I find it :)

    • I'd suggest doing some research into JDBC connections as that is one of the most reliable ways to connect to MySQL, MariaDB, PostgreSQL, etc.
      • Connection pools are also very popular and reliable if setup correctly and a greate API I suggest for this is HikariCP. I promise you it isn't as confusing as it first looks.
  3. Yeah, I've noticed that a lot of MySQL "tutorials" seem to use a singular Connection and I didn't feel that it'd be a great idea to continually use, and keep open the same one. Thanks for clarifying!

    Caching and saving data every few seconds may be a little excessive, I'll play around with timings and do some research into how crashes affect MySQL/Hikari data saving.

    I'll take a look into Hikari, from first glances it seems to offer a lot of the things I'm going to need/want with this! Connection pooling is a concept I've read up on but never really, known how to implement effectively though Hikari seems to (atleast, at first looks) help with immensely. Thank you for the reccomendation!
  4. I recommend using a connection pool library like HikariCP to manage your connections. As for caching I recommend you load the data on async pre login (blocking) and cache it. Write changes instantly (async) to prevent losing data.
Thread Status:
Not open for further replies.