Advertisement
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

More efficient MySQL use ?

Comments in 'Plugin Development' started by Guillaume351, Jun 14, 2015.

  1. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
    Hello,
    I am using a database to know if a player is VIP, which kits does he own ...
    Each time a player connect, it check in the database if he is VIP, it get his kits...
    The problem is I have a lot of players trying to connect, and even when the server is full I need to check if they are VIP to bypass the server limit.
    It seems that when the server connect to the database, I have a TPS drop (Server waiting for the database to respond ?) : it make a lot of lag.

    Is there a way to "download" the database like every 30 minutes and check in the downloaded database instead of the online one ? Or is there a way to do it in a task to reduce lag when player connect ?

    Sorry for my english, I hope you can understand my problem :/
    RekkuzaRage likes this.
  2. iJoshuaHD
    Offline

    iJoshuaHD Notable Member Plugin Developer

    Joined:
    Nov 7, 2013
    Posts:
    1,201
    Plugins:
    4
    Minecraft User:
    iJoshuaHD
    Do MySQL queries using AsyncTask.
    iksaku and Guillaume351 like this.
  3. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
    I don't know how AsynTask work, I am going to try I keep you informed thanks
    Last edited: Jun 14, 2015
  4. aliuly
    Offline

    aliuly Notable Member Plugin Developer

    Joined:
    Feb 8, 2014
    Posts:
    1,086
    Plugins:
    17
    Probably you need to combine solutions. You should connect to the database once and just reuse the same connection.
    Guillaume351 likes this.
  5. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
    The problem is I don't know how :/
  6. PEMapModder
    Offline

    PEMapModder Notable Member Plugin Developer

    Joined:
    Oct 9, 2013
    Posts:
    7,325
    Plugins:
    11
    Minecraft User:
    PEMapModder
    Save the MySQL instance.
    It will still lag.
    If you don't have too many (like hundreds or thousands) VIPs, what about running a SELECT name FROM players where vip=1 query? (I made up those table and column names)

    The main trouble of using an AsyncTask is that you must always let the player join, and only kick when the query returns a result. Some interesting race conditions may occur in some special cases.

    I have an example here: https://GitHub.com/PEMapModder/LegionPE-Theta-Base
  7. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
    PHP:
    <?php
    namespace Server;

    use 
    pocketmine\Player;
    use 
    pocketmine\scheduler\AsyncTask;
    use 
    pocketmine\Server;
    use 
    pocketmine\utils\TextFormat;

    class 
    getData extends AsyncTask{
        public 
    $plugin;
        public 
    $link;
        public 
    $player;
        public function 
    __construct(Main $pluginPlayer $player){
            
    Server::getInstance()->broadcastMessage("Started, ".$player->getName());
           
    $this->player $player;
            
    $this->reloadDataBase();
            
    $this->plugin $plugin;
           
    Server::getInstance()->broadcastMessage("End construct");
        }

        public function 
    onRun(){
            
    Server::getInstance()->broadcastMessage("Start onRun");
            if(isset(
    $this->plugin->playerRanks[$this->player->getName()]) == false or isset ($this->plugin->playerRanks[$this->player->getName()]) == false){
                
    $data $this->getData($this->player);
                if(isset(
    $data["playerName"]) and $data["playerName"] != ""){
                    
    $this->plugin->playerRanks[$this->player->getName()] = $data["playerRank"];
                    
    $this->plugin->playerKits[$this->player->getName()] = $data["playerKit"];
                }else{
                    
    $this->plugin->link->query("INSERT INTO data (playerName) VALUES ('".$this->player->getName()."')");
                    
    $this->plugin->playerRanks[$this->player->getName()] = $data["playerRank"];
                    
    $this->plugin->playerKits[$this->player->getName()] = $data["playerKit"];
                }

            }
            if(
    $this->plugin->playerRanks[$this->player->getName()] == "vip" or $this->plugin->playerRanks[$this->player->getName()] == "vip+"){
                
    $this->player->setDisplayName(TextFormat::GREEN."[VIP] ".$this->player->getName());
                
    $this->plugin->vips[$this->player->getName()] = true;
                if(
    $this->player->getName() == "Guillaume351"){
                    
    $this->player->setDisplayName(TextFormat::RED."[Owner] Guillaume351");
                }

                
    $this->player->setNameTag($this->player->getDisplayName());
                
    //  $pos = new Vector3($this->player->getX(),$this->player->getY(),$this->player->getZ());
                // Server::getInstance()->getDefaultLevel()->addSound(new PopSound($pos));
                
    Server::getInstance()->broadcastMessage(TextFormat::GREEN.$this->player->getDisplayName() . " joined the game !");
            }



        }




        public function 
    reloadDataBase(){
            if(
    $this->link = @mysqli_connect("ip","id","password""db",port)){
            } else{
                
    $this->lock true;
                
    Server::getInstance()->broadcastMessage(TextFormat::RED."Error in database");

            }
        }

        public function 
    getData(Player $player){
            
    $name $player->getName();
            
    $result $this->link->query("SELECT * FROM data WHERE playerName = '".$name."'");
            if(!
    $result){
                return 
    false;
            }else{
                
    $data mysqli_fetch_array($result);
                return 
    $data;
            }
        }

    }
    I get error "Mysqli : Couldn't fetch ...". I don't have this error when I do this in the main plugin file, using exactly the same method.
    I try to put $name = "Guillaume351" directly, problem does not come from that. Moreover, plugin connect correctly to database, I checked.
    I call the AsyncTask using this in PlayerJoinEvent :
    $this->getServer()->getScheduler()->scheduleAsyncTask(new getData($this,$event->getPlayer()));

    Thank you once again for your help :/
  8. aliuly
    Offline

    aliuly Notable Member Plugin Developer

    Joined:
    Feb 8, 2014
    Posts:
    1,086
    Plugins:
    17
    This would not work. php threading is a share nothing model.
    Guillaume351 likes this.
  9. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
    If I do it in a non-delayed task, result will be the same ? Will it run on the same thread that main class ?
    Thanks
  10. PEMapModder
    Offline

    PEMapModder Notable Member Plugin Developer

    Joined:
    Oct 9, 2013
    Posts:
    7,325
    Plugins:
    11
    Minecraft User:
    PEMapModder
    Don't use Server::getInstance() from other threads!
    An AsyncTask is run at a separate thread. PHP has problems with static properties, and they do not work when you are on other threads (and you are discouraged to use them as well in main thread).
    AsyncTask and (normal) Task are completely different things. They have no relationship at all except being put at the same namespace with similar names. And no, AsyncTask is only run asynchronously if you use scheduleAsyncTask.

    Do all PocketMine API-related things in the main thread and pass them as variables of primitive data types (serialize them if they aren't) or objects that are instanceof \Threaded. Return all results in the same way to the main class and execute them in onCompletion() or wherever you like.
    iksaku, iJoshuaHD and Guillaume351 like this.
  11. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
    Thanks, but I think I don't have a good enough PHP level to do this. I don't understand everything, I will try to search around this.
  12. aliuly
    Offline

    aliuly Notable Member Plugin Developer

    Joined:
    Feb 8, 2014
    Posts:
    1,086
    Plugins:
    17
    This is what I do... In the constructor I pack all the information I need to perform the task and save it as class variables. All the data here should be serializable. If you are not sure, pass the variable to serialize.

    In the onRun function, I start doing whatever is needed. No static data can be accessed. Usually no PocketMine API functions are callable unless they are static functions (and only if they do not access static data). In other words, any input that you need should come from whatever you did in the constructor. One you are done, I save all the results using the setResult function. Whatever you put in setResults, should be serializable.

    In onCompletion, I get whatever was stashed and place it in wherever... Usually in a plugin, so for that I would do $server->getPluginManager->getPlugin to get the plugin and call whatever function the plugin uses to handle the new data.

    This is some example code: https://github.com/alejandroliu/poc.../LiveSigns/src/aliuly/livesigns/FetchTask.php
    Nothing to do with MySQL but more about how to use an AsyncTask.
    Guillaume351 likes this.
  13. PEMapModder
    Offline

    PEMapModder Notable Member Plugin Developer

    Joined:
    Oct 9, 2013
    Posts:
    7,325
    Plugins:
    11
    Minecraft User:
    PEMapModder
  14. Guillaume351
    Offline

    Guillaume351 Active Member Plugin Developer

    Joined:
    Oct 11, 2013
    Posts:
    150
    Plugins:
    1
    Minecraft User:
    Guillaume351
  15. Samueljh1H8sLag
    Offline

    Samueljh1H8sLag Active Member

    Joined:
    Jun 20, 2015
    Posts:
    168
    Minecraft User:
    Samueljh1_
    make the mysql instance static so it is kept the same between all instances of the asynctask
  16. Goerick
    Offline

    Goerick Active Member

    Joined:
    Jan 24, 2014
    Posts:
    252
    Minecraft User:
    goerick
    Extant mysql memory cache to like 60 mb.
  17. PEMapModder
    Offline

    PEMapModder Notable Member Plugin Developer

    Joined:
    Oct 9, 2013
    Posts:
    7,325
    Plugins:
    11
    Minecraft User:
    PEMapModder
    No that's not how it works. Each AsyncTask worker (something very PocketMine-internal that you can forget about it) has its independent instance. Unless the object is serializable or extends Threaded, you can't share it between different threads/workers.. As often said before, static object instances are strongly discouraged, and using them on different threads simply would NOT work. You have to use the thread store methods in AsyncTask like how I did in the LegionPE plugin and in the Basin plugin.
  18. Samueljh1H8sLag
    Offline

    Samueljh1H8sLag Active Member

    Joined:
    Jun 20, 2015
    Posts:
    168
    Minecraft User:
    Samueljh1_
    No have a static var inside the asynchronous class and set it on the first run inside of the asynchronous class checking if it is null first
  19. PEMapModder
    Offline

    PEMapModder Notable Member Plugin Developer

    Joined:
    Oct 9, 2013
    Posts:
    7,325
    Plugins:
    11
    Minecraft User:
    PEMapModder

Share This Page

Advertisement