Urgent: MySQL Spiking

[Kryptix]

I run a RPG online game which is very database hungry and sends queries to the database on pretty much everything you do. This has run brilliant for a while but sometimes it 'spikes' for no apparent reason. I've checked the SQL logs and don't see anything un-usual, but sometimes my servers CPU will sit on 3-10% fine, and then randomly spike to 50%+.

 

I'm on a Intel Core 2 Duo 1.86 running Windows Server 2003. I have XAMPP that installs MySQL (5.0.51b) and PHP (PHP 4.4.9).

 

Someone mentioned about MySQL not being configured properly for the two cores. Apparently there's lots of tweaks you can do to improve performance, but I have no experience in that area at all.

 

The same source-code is being run on several private servers with no issues, so I'm pretty sure it's a configuration issue.

 

The XAMPP version is like 16 months old, do you think I should install the latest version instead? It's really important and I'm loosing a lot of players due to this, but I'm struggling to figure it out.

 

Is there anything I can do in Windows to improve MySQL's performance?

 

I'm averaging at about 10-20 queries per second, but this has run fine for a while and like I said, many other servers use the same code and manage to run it off their home PC.

 

Do you think it'd be wise to switch to Linux instead?

[Kryptix]

I pin-pointed it to spiking when a player logs in:

 

SELECT ban_time FROM `rscd_players` WHERE `user`=4681476
SELECT r.pass,r.ban_time, r.banned, r.owner, u.group_id, b.id AS b_id FROM `rscd_players` AS r INNER JOIN `users` AS u ON u.id=r.owner LEFT JOIN `bans` AS b on (b.username LIKE u.username OR b.ip LIKE '71.20.63.202') WHERE `user`=4681476
SELECT ban_time FROM `rscd_players` WHERE `user`=4681476
SELECT r.pass,r.ban_time, r.banned, r.owner, u.group_id, b.id AS b_id FROM `rscd_players` AS r INNER JOIN `users` AS u ON u.id=r.owner LEFT JOIN `bans` AS b on (b.username LIKE u.username OR b.ip LIKE '71.20.63.202') WHERE `user`=4681476
UPDATE `rscd_players` SET `online`=1 WHERE `username`="Brown"
SELECT r.*, u.username AS owner_username, u.group_id, u.sub_expires FROM `rscd_players` AS r INNER JOIN `users` AS u ON u.id=r.owner WHERE `user`=4681476
SELECT * FROM `rscd_experience` WHERE `user`=4681476
SELECT * FROM `rscd_curstats` WHERE `user`=4681476
SELECT * FROM `rscd_invitems` WHERE `user`=4681476 ORDER BY `slot` ASC
SELECT * FROM `rscd_bank` WHERE `owner`='1559' ORDER BY `slot` ASC
SELECT * FROM `rscd_friends` WHERE `user`=4681476
SELECT * FROM `rscd_ignores` WHERE `user`=4681476
SELECT owner, block_private, x, y FROM `rscd_players` WHERE `user`=4681476
SELECT user FROM `rscd_friends` WHERE `friend`=4681476
INSERT INTO `rscd_online`(`user`, `username`,`x`,`y`,`world`, `ip`) VALUES('4681476', 'Brown', '214','447','1','71.20.63.202')
INSERT INTO `rscd_logins`(`user`, `time`, `ip`) VALUES('4681476', '1256529994', '71.20.63.202')
UPDATE `rscd_players` SET login_date=1256529994, login_ip='71.20.63.202' WHERE user=4681476
UPDATE `rscd_players` SET loggedin=1, world='1' WHERE user=4681476
SELECT p.user FROM `rscd_friends` AS f INNER JOIN `rscd_players` AS p ON p.user=f.friend WHERE p.block_private=0 AND f.user=4681476

 

Something in there is causing my CPU to hit the roof everytime they login.

 

I think it has something to do with ban_time personally, as that's the only modification my friend has done to the source-code which works fine. Anyone got any ideas?

[trq]

Are you trying to tell us all those queries are executed on each login?

[Mchl]

Do you have slow query log enabled?

[Kryptix]

Do you have slow query log enabled?

Nope, what's that?

 

And yes, but it's not too much. There's a problem somewhere but it should handle that easily (and normally does).

[Mchl]

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

 

Will help you find out which of these are most time consuming.

[gizmola]

The standard solution to this is to move load off the database and into an in-memory cache.  The most frequently used cache is memcached. 

 

As to your current conundrum, as Mchl already indicated, you need to figure out what query(s) are bogging down, although this can sometimes be misleading when it is related to contention or locking.

 

If you do find it's a basic query, then you will want to get very familiar with mysql's explain, as well as understanding how to optimize queries.  The main thing you want is to make sure that you aren't doing table scans.

 

Luckily there are a lot of good resources on the use of EXPLAIN and how to optimize mysql queries.

[gevensen]

why are you running xamp on 2003

doesnt 2003 let you make its own server??

i use xaamp (wamp actually ) of win x64 because i dont 2003

[trq]

why are you running xamp on 2003

doesnt 2003 let you make its own server??

 

Windows Server 2003 is an operating system not a http server. Like allot of Windows versions however it does ship with IIS (which is MS's http server).

 

There are many reasons to choose Apache over IIS, but lets not go there.

[Kryptix]

I've since upgraded the server to a Intel Quad Core with 4gB of RAM. The hosting company said I could store lots of queries in the memory to make the CPU under less load. Does anyone have any information on that?

 

Also, would switching to Linux help at all if it's configured the same?

[Kryptix]

PS. I have 5,100 entries in 'rscd_players', 'rscd_experience' (etc).

 

I have a question actually. We had a database crash which mean we had to roll back, but during the rollback some duplicated entries got added to 'rscd_experience' and 'rscd_curstats'

 

For example, some players (around 100 players) have 2-5 entries in 'rscd_experience' (etc). Would this cause a spike like that? I doubt it would but I'd sooner ask.

[kickstart]

Hi

 

No comments on server setup, and I agree with the above comments on delving through using explain.

 

However I would expect doing a JOIN with a LIKE statement might well be quite inefficient and 2 of your queries do that

 

SELECT r.pass,r.ban_time, r.banned, r.owner, u.group_id, b.id AS b_id

FROM `rscd_players` AS r

INNER JOIN `users` AS u ON u.id=r.owner

LEFT JOIN `bans` AS b on (b.username LIKE u.username OR b.ip LIKE '71.20.63.202')

WHERE `user`=4681476

 

I presume the IP address comes from a previous query.

 

All the best

 

Keith

[Mchl]

I've since upgraded the server to a Intel Quad Core with 4gB of RAM. The hosting company said I could store lots of queries in the memory to make the CPU under less load. Does anyone have any information on that?

 

As usual MySQL manual does

http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

[Kryptix]

Thanks a lot for your on-going help.

 

Can you tell me what I should enter for 'query_cache_size'? My RAM is 4gB. I can easily allow 2gB to be used for just caching.

 

This seems to help a LOT when logging out, but not so much when logging in. Still, it helps!

[Mchl]

Can you tell me what I should enter for 'query_cache_size'? My RAM is 4gB. I can easily allow 2gB to be used for just caching.

 

If this server runs Apache as well as MySQL I would set query cache to 1GB for starters. Then observe how much it's actually used.

[Kryptix]

Alright, thanks, but how do I define that? In Bytes? KB? MB? GB?

[gizmola]

There's actually 3 params you need to look at:

 

query_cache_type=1

 

Will turn on the query cache and cache select statements.  This should be the default.

 

query_cache_size = Bytes in multiples of 1024. 40k needed for query structures.  Defaults to 0, which essentially disables cache.

 

query_cache_limit= Bytes.  This is the maximum result set size that will be cached.