Remote MySQL causes severe lag

[netherfoam]

Hi, remote mysql connections are causing severe lag for my server whenever I switch to them. I wish to use mysql because flatfile simply does not work well enough (Bad performance with thousands of players)

However, you lag out the server whenever someone interacts with the iConomy DB on a remote host. as far as I know this is because iconomy uses the same thread to fetch mysql results as the one everything else runs on, which means everything else comes to a halt while iconomy waits for a response from the mysql server. So say I purchased 5 things in a row from a shop individually in 5 transactions... Then I've effectively frozen the server, as it tries to contact the db 5 times.

Can you fix this by say... Using a different thread or the scheduler to access the database instead of the main one? I haven't looked at how you've coded iConomy, but this seems to be the issue.

It's a huge thing to ask, but not being able to use remote mysql is even worse.

Thanks; -Nether.

[netherfoam]

...I'd like to add my mysql server is a paid one...

[RytisRytisRytis]

It's because your connection is too bad or the MySQL Server is too slow. When the Minecraft Server queries to a MySQL Server, it just simply "holds" the server for a few miliseconds, normally.

[ChrisLane]

@Rytis110 It's not, I had the latest iConomy version and a very fast MySQL separate server that was connected by a 10 meter cable. All my other plugins work absolutely fine on the MySQL.

[RytisRytisRytis]

But I've got the same iConomy version as you and it's working fine :P

[netherfoam]

At risk of sounding rude, you're completely missing my point here. The server shouldn't have to pause waiting for the server to respond to a query. It should be multithreaded to prevent the server waiting.

E.g. Server should load the account balance into a variable of sorts from the database when the player logs in, then when it's modified it should change the value in RAM (very quick) then schedule a new async task(Read up on them if you haven't... it's multithreading basically) to update the database. This would mean the whole server doesn't pause while it's waiting for a mysql response.

Any unnecessary wait such as this is incredibly wasteful - The server isn't thinking or using resources, it's waiting for a response. Even 10-20 milliseconds is terrible lag in this kind of scenario, and totally avoidable. And, if you fixed that 10-20ms, you would also fix the issue I have with having MySQL in a separate data center as the Minecraft host.

-Nether.

[ChrisLane]

@Rytis110 Ok then, what's your server IP?

[RytisRytisRytis]

IP: survival.rytiscraft.com @ the economy world.

[ChrisLane]

Hmm that is indeed odd that yours works without lagging much, do you only have iConomy running on that mysql? You seem to be the minority not having the problem.

There is a small amount of delay but I guess it's not a huge problem.

[RytisRytisRytis]

On the same Server where MySQL is, is an Mailserver, Webserver, MySQL Server, TeamSpeak3 Server, FTP Server and a Counter-Strike: Source server running. On the MySQL Server are the following databases running: for a small website that my friend is programming (almost no traffic), 3x HLStatsX:CE (very high traffic), forum (mid traffic), some other PHP applications (mid traffic), and two databases for my two servers. On the survival server are running the following plugins with MySQL: Statistics (http://stats.survival.rytiscraft.com/), HawkEye, iConomy, Jobs, LWC, MailBox, Mcft plugin (it's awesome: http://dev.bukkit.org/server-mods/mcftprofiler/ :D) and Towny. So I think that a lot that is running on my VPS with 2 Gig of RAM :P

[RytisRytisRytis]

Btw the server is in Germany and Dynmap is since today in the morning rendering HD Maps for 4 worlds :P

[me4502]

I completely agree with this, I have the same issues

[RytisRytisRytis]

How is it with flatfile, does it lag with flatfile?

[ChrisLane]

Flatfile is very speedy. I wiped my whole mysql database to switch to flat file and am very glad I did now.

[me4502]

I was using FlatFile, but it kept getting erased... so i switched to MySQL.