Kryptix

I'm on a Quad Core 2.4 dedicated server with 4gB RAM, 1tB HDD, 100mB/s Port and 50,000pps DDoS Protection... It's nothing to do with that, if there's anything it's MySQL settings/configuration... But I don't know enough about it to be certain.

I don't know what to title this really, but I own a online game that uses MySQL as it's back on which transfers hundreds of queries every minute. Sometimes the game will just drop for some reason, but the people online will remain online. It's just the login process (which uses the most queries). I'm using a bog-standard MySQL install, and whilst the CPU isn't overloaded I was wandering if there's anything in MySQL that would restrict me? Is there a certain limit that I have to up or something?

Anyone?

Anyone? I'm sure it's really simple for a lot of you...

I need a script, or a way to display IRC information on my website. I want to display how many people are in the channel, and their names. How would I go about doing this? Is there any service available, or would I need to have a bot sit in IRC gathering all the information?

That looks perfect, but I have just tried and the 'INSERT INTO' statement copied the entire table, even duplicate entries. I unique column is called 'user', there should only be one 'user' the same. The other 18+ rows could be the same as others, but I need to delete all duplicate entries where 'user' isn't unique. Mainly thanks for your help.

No, this isn't web-based. The 'bug' was my mistake. It's a online JAVA RPG that uses a database to store data. Anyway, regardless how it happened, I need to fix it but I don't know how without going through them one-by-one.

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

Does anyone have any ideas? :'(

Randomly this evening MySQL disconnected for no reason. I don't know why. I'm not too familiar with MySQL, how would I find out why it shut down? Is there any logs? It hasn't done it since I restarted MySQL, but still, it's only been 10 minutes. :'(

Nope. It has 'user' which is a hash, and then like 18 other columns storing their experience in each stat. As it's duplicated, when it does the UPDATE query it updates both of them, using resources I don't want it to. :-\

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!

I have a table called 'rscd_experience' In this table I have a field called 'user' which is a hashed username, as well as several other fields. I need a query to search for duplicate 'user' and then delete one of the entries. There's about 1,200 duplicates at the moment. I have no idea where to start with this. Could someone write a rough query so I could learn from it please?

I run a online game which uses MySQL to store everything. The players 'Hits' (health) is calculated by the experience in 4 other stats divided by 3. However, some players have found a way to not gain 'Hits' (health) which gives them an advantage, believe it or not. We have fixed the bug but now need to update the database with the new information. The table is called 'rscd_experience' In the table we have a field called 'user' which is a hashed username, followed by 'exp_attack', 'exp_defense', 'exp_strength' and 'exp_hits' exp_attack + exp_defense + exp_strength / 4 = exp_hits At the moment I have 6,520 rows in this table. What would be the best way to do this? Is there a direct query possible or would I need PHP?

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.

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?

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).

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?

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?

I'm running a RPG game server and when the CPU hits 50% usage, everything becomes very slow and takes ages to respond. Someone has just looked into this and said his guess is that MySQL is set to only use 1 Core, so 50% is actually 100%. Does that make any sense? I'm running Windows Server 2003. What should I do? Is there anyway to make it run on 2 Cores?

I run a game server that queries the database hundreds and thousands of times per minute, and for some reason we're crashing now and again. I'm thinking someone has found a way to spam SQL queries but I don't know how to view a logger of it. I've installed MySQL through XAMPP. Has anyone got any idea how I can view SQL queries as they come in?

Woo, thank-you so much! So is it just -30 DAY for a month?

Code is now: Still returns '0'

Oops! I forgot to declare $result. :'( Here's the code now: $result = $db->query('SELECT COUNT(*) FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()'); $stats['online24'] = $db->result($result); This returns '0' (after being number_format()'d).

I changed that. It's still displaying them all. It's entered into 'login_date' which is a int(10). It updates 'login_date' with time() when they manage to login. The exact code I have is: $db->query('SELECT COUNT(*) FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()'); $stats['online24'] = $db->result($result); <dd><?php echo 'Game Characters Online Today: <strong>'. number_format($stats['online24']) ?></strong></dd>