Mysql – high traffic thesql hosts

MySQL

I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user.

and for now I really only need to handle say, 10 users.

I know it's hard to say because it depends on the statements so maybe if I supply them it will be of help.

each user is calling :

33x per second 

SELECT Online, Xloc, Yloc FROM accounts WHERE AccountID = " + getlocI

and polling:

33x per second

UPDATE `accounts` SET `Online`='"+InitialStat.Onlineint+"', `Xloc`='"+Game.currentx+"',
`Yloc`='"+Game.currenty+"' WHERE (`AccountID`='"+UserVerify.clientID+"') LIMIT 1

with a larger UPDATE only once per minute:

UPDATE `accounts` SET `Online`='"+InitialStat.Onlineint+"',`Zone`='"+Game.currentzone+"',`Xloc`='"+Game.currentx+"', `Yloc`='"+Game.currenty+"', `Level`='"+InitialStat.PlayerLevel+""
                        + "', `XP`='"+InitialStat.XP+"', `Health`='"+InitialStat.health+"', `Magic`='"+InitialStat.magic+"', `Stamina`='"+InitialStat.stamina+""
                        + "', `Strength`='"+InitialStat.strength+"', `Defense`='"+InitialStat.defense+"', `Vitality`='"+InitialStat.vitality+""
                        + "', `Agility`='"+InitialStat.agility+"', `Intelligence`='"+InitialStat.intelligence+"', `Dexterity`='"+InitialStat.dexterity+""
                        + "', `Luck`='"+InitialStat.luck+"', `Gold`='"+InitialStat.gold+"' WHERE (`AccountID`='"+UserVerify.clientID+"') LIMIT 1

the larger UPDATE statement isn't too big of a deal since it happens far less frequently.

But the fastest I can get my other ones to go is somewhere around once per second.

Is there any way I can find out a ballpark of what I would need in a server to get those to function as fast as I'd like them to? I'm currently using a VH, I'd rather not go the dedicated server route yet because this is still in development, so spending a few hundred a month of the server is a bit much.

Any help would be greatly appreciated. If you need more info please let me know.

Best Answer

I think you are trying to solve the problem with the wrong tools: MySQL is a relational database, designed to store large collections of data for long term in a durable fashion. You seem to be storing in the database a small amount of data for synchronisation, that is very dynamic, a workload that (even it is possible for the load you propose) it is not designed for that. Your data seems to be very state, session-like, for which application memory (shared memory) is designed. Real-time data is stored on main memory, not on the disk datastore. Or -if you could not use that-, other kind of data stores that doesn't require such durability/locking like MySQL may work better, like memcached.

If you really need to use MySQL, which I don't recommend for this pattern, 300 IOPS is something that any dedicated server can do, but a virtual host may be very limited or constrained by the other users (and very variable performance, as it cannot guarantee the service quality). MyISAM will do table locks, InnoDB will have too much durability overhead. Memory may work if the table locks take less than intended TPS, if not you will also suffer the table locks. And all that, no to talk about the SQL layer overhead.

Related Question