Jump to content

Urgent: MySQL Spiking


Kryptix

Recommended Posts

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. :shrug:

 

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? :shrug:

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.