MySQL question

Discussion in 'Plugin Development' started by TheNewTao, Jul 18, 2016.

Thread Status:
Not open for further replies.
  1. Offline

    TheNewTao

    Hi,

    So I am creating this plugin where every user has a level. For example, Alexa will be level 1 and I could be level 2. That is an easy one to one relationship table I can set up in my mysql database.

    So my questions are:

    1. How often should I set up a connection from my plugin to the mysql database to update the player's level. For instance, let's say Alexa levels up to level 2, should I immediately update the information over to the mysql server? What I was also thinking that I could do was every time the plugin loads, I will load everything on a hash map and when the plugin disables I will update everything onto the mysql database. However, I might have like 500 users that have joined in total, so the hash map would be holding 500 keys. I don't know which way is more efficient?

    2. What do you guys use to set up a connection pool? I was previously using c3p0 but I would like to know what you guys think is more efficient?

    3. How often should I access the mysql database without affecting server performance? Can I do it every second?

    Thanks!
     
  2. Offline

    DarkenCake

    1) Use LevelChangeEvent ( No HashMap that takes a lot of RAM) & check if level is going up, then set table, and, you need to connect to MYSQL on plugin enabling.
    Code:
     public void onEnable() {
           // connect 
     
    Last edited: Jul 18, 2016
  3. Offline

    thapengwin

    Incorrect. You do NOT keep the connection alive for the duration of the server's session. You must connect ONLY when you need to update values and disconnect after about 30 seconds of no activity. Keeping the connection alive constantly, the server will time you out, forcing you to restart the plugin.
     
  4. Offline

    TheNewTao

    I am using a custom level system. I would set up the connection pool when I enable the plugin.


    My question is how often can I open up a connection without sacrificing efficiency? Like I need to retrieve a player's level from a mysql database every time a player joins, would that be okay?
     
  5. Offline

    thapengwin

    As I said, open the connection whenever needed, and close it once 30 seconds or 1 minute have passed after the last MySQL interaction. There will not be a significant speed decrease, especially not if you do this asynchronously.
     
  6. Offline

    TheNewTao

    I was told I should do these type of operations async?
     
  7. Offline

    thapengwin

    And that's what I reinforced, however with a double negative.
     
    TheNewTao likes this.
  8. Offline

    mythbusterma

    @TheNewTao

    Seriously consider just doing this in a YAML file. It's simpler, faster, human readable, and requires less set up.

    That being said,
    Oh man, gotta save that 1KB of RAM.

    Seriously, use whatever event you want, and as many data structures as you reasonably need to store your data. Preemptive optimisation of this level of ignorance is how you wind up with so many awful plugins. Do what makes the most sense and is the most readable.

    Why not? MySQL servers have a keep-alive parameter that you can change to be quite long, and running a query every 10 seconds on a connection isn't going to kill you. Plus you'll know quite quickly if the connection has died, and don't have to wait for a read or write. If you actually keep the connection alive, you have no reason to recreate it each time.

    There is a huge performance hit in creating a connection. It is expensive for the client and server, and can often take up to a second. This being said, you're likely not to notice it since these are being run asynchronously, but misinformation is still bad. If you're opening it for every query, you should close it after every query, by the way.

    Unless you want your server to stall indefinitely waiting for a response that may never come, yes.

    Now, on to your original post:

    As often as you feel is convenient. If you think this is going to be happening a bunch, then you probably ought to just have a single connection open. You could cache the data as you say, and dump it every now and then, but I'm not sure you're going to see any gains from that. Nothing wrong with it, though.

    Just stop worrying about this nebulous concept of "efficiency." Don't do anything outrageously stupid and your plugin will run fine. You don't need a connection pool for something this small, just have a single connection that you either open when you start and keep alive or recreate it each time. Worrying about efficiency like this is a slippery slope, to say the least.

    Since you're going to be running these queries asynchronous to the server's main thread, it really shouldn't have any impact on performance. Keep in mind these queries can take a while if there is changing network conditions.
     
    bwfcwalshy likes this.
Thread Status:
Not open for further replies.

Share This Page