Mysql on an seperated Server

Discussion in 'Performance Tweaking' started by Floww, Jan 8, 2013.

  1. Hello,

    I have an dedicated Server hostet at hetzner for 2 minecraft-servers and one virtual Server for Website and Backups.

    I'm thinking about moving the mysql-server from the root to the virtual server. So there is full power for the minecraft-server.

    I'm Using 1x Logblock, 1x Coreprotect and 1x Hawkeye on Skyblock server.


    Are there any disadvantages?
     
  2. You can get the same gain from moving MySQL data to a seperate hard disk in the server. :)
     
  3. I only have 2 HDD's in Raid1.
     
  4. If the second server is in the same facility, you will probably see some performance improvement. However, if the second server is not in the same facility, internet latency will nullify all the potential performance improvements you can expect from the change.
     
    • Agree Agree x 1
  5. Is anyone of you using an external mysql?
     
  6. I am using external mysql server in the same facility, no problems since 2011.
    The MySQL server is located in another room in the same facility.
    Using local IP to connect to MySQL.
     
  7. My virtualServer isn't hosted at Hetzner ... So I'm thinking^^
     
  8. If your VPS is not in the same facility, then don't even bother thinking about it ;)

    Put in perspective of things, here's an example to help you understand why you shouldn't even bother.

    Assuming you have a plate based device, the average seek time to find a file is around 8.9ms for 7200rpm plate drives. So, if you tell CoreProtect to find some information from its flatfile setup, it will take approximately 8.9ms to find that file, and locate the relevant records. Assuming zero time from finding the file to understanding its contents and returning information into ram, the whole process is done in 8.9ms. Not a pleasant amount of time, especially if you have months and months of record built up, but it's not too bad.

    Compare that to MySQL, with proper indices (I've got one on cx-cz), I can run a fairly complex query:
    Code (Text):

    SELECT *  
    FROM `co_blocks`
    WHERE
    `user` LIKE 'huang%' AND
    ((cx > -10) AND (cx < 10)) AND
    ((cz > -10) AND (cz < 10));
    ... against my pretty large table with 1,209,008 rows in ~0.0002 sec (2ms; ran various similar queries several times, all ranging from 0.0001s to 0.0005s). This is much better than the 9ms from files.

    When your second server is in the same datacenter, the ping is usually < 1ms, so you can expect the response to be pretty similar to what you are getting normally from MySQL on a local machine. However, when you are in a different center, you need to add the ping time to your response time. That means if it takes 30ms to get a response from your VPS, then you will need 30ms on top of the normal query time; 75ms ping -> 75ms on top; etc. etc.. This is MUCH worse than local MySQL, and you are better off using flatfile instead.

    Hope this helps you understand why you shouldn't even bother if they're not in the same facility.