Mysql高更新选择导致滞后 [英] Mysql High UPDATE SELECT causing lag

问题描述

我有一个相当大的游戏，白天最多有 30-40-50 人.我们将有关他们坦克的信息存储到 mysql 数据库中，当他们开枪或失去健康时，我们会将其转储到数据库中.因此，我们看到了非常高的处理器和 HDD Spike，结果是游戏滞后.

I have a game that is fairly sizeable, 30-40-50 people in at most points during the day. We store the information about their tank into a mysql database and when they shoot or lose health we dump that to the database. We are seeing a very high processor and HDD Spike because of this and the result is lag in the game.

违规言论:

UPDATE MapData
SET Health = @1, X = @2, Y = @3,TotalPoints = @4
   , RankPoints = @5
WHERE MapID = @6
   AND TankID = @7
   AND Color = @8

我想知道我是否可以做一些事情来帮助解决延迟问题.

I want to know if there are some things i can do to help with the lag at all.

CREATE TABLE `mapdata` (
  `MapID` int(11) NOT NULL,
  `TankID` int(11) NOT NULL,
  `Color` tinyint(4) NOT NULL,
  `X` int(11) DEFAULT ''-1'',
  `Y` int(11) DEFAULT ''-1'',
  `Rank` tinyint(4) NOT NULL DEFAULT ''0'',
  `Health` int(11) NOT NULL DEFAULT ''1000'',
  `Armors` tinyint(4) NOT NULL DEFAULT ''0'',
  `Duals` tinyint(4) NOT NULL DEFAULT ''0'',
  `Missiles` tinyint(4) NOT NULL DEFAULT ''0'',
  `Homings` tinyint(4) NOT NULL DEFAULT ''0'',
  `Radars` tinyint(4) NOT NULL DEFAULT ''0'',
  `Beacons` tinyint(4) NOT NULL DEFAULT ''0'',
  `HasRankKill` bit(1) NOT NULL DEFAULT b''0'',
  `TotalPP` bigint(20) NOT NULL DEFAULT ''0'',
  `RankPP` bigint(20) NOT NULL DEFAULT ''0'',
  `KillCount` int(11) NOT NULL DEFAULT ''0'',
  `DeathCount` int(11) NOT NULL DEFAULT ''0'',
  `TimePlayed` time NOT NULL DEFAULT ''00:00:00'',
  `EnabledEquipment` tinyint(4) NOT NULL DEFAULT ''0'',
  `Prestige` tinyint(4) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`MapID`,`TankID`,`Color`),
  KEY `MapID` (`MapID`),
  KEY `TankID` (`TankID`),
  KEY `idx_mapdata` (`MapID`,`Color`,`TankID`),
  CONSTRAINT `mapdata_ibfk_1` FOREIGN KEY (`MapID`) REFERENCES `maps` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mapdata_ibfk_2` FOREIGN KEY (`TankID`) REFERENCES `tank` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

有什么办法可以让我将表保存在内存中而不是硬盘上，并让它每隔一段时间转储回磁盘?

Is there someway that I can persist the table in memory instead of on hdd and have it dump every so often back to disk?

推荐答案

有些事情你可以做，但你已经达到了一个难以通过的门槛.一个你有一个写入繁重的系统，但它的写入速度不够快.

There are things you can do but you have reached one of the hard thresholds to pass. The one where you have a write heavy system and it just doesn't write fast enough.

有一些提高性能的方法

1. 计算innodb_log_file_size
2. 将 innodb_flush_log_at_trx_commit 设置为 0 或 2. 请注意，1(默认值)是唯一符合 ACID 的值，但将其更改为 0 或 2 可提供更好的性能.

1. Calculate god value for innodb_log_file_size
2. Set innodb_flush_log_at_trx_commit to 0 or 2. Note that 1 (default) is the only ACID compliant value but changing it to 0 or 2 gives much better performance.

当然还有更多方法，但这两个很重要.

There are more way of course, but these two are important.

这篇关于Mysql高更新选择导致滞后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！