Chinaunix首页 | 论坛 | 博客
  • 博客访问: 652733
  • 博文数量: 26
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 3182
  • 用 户 组: 普通用户
  • 注册时间: 2011-02-23 14:29
个人简介

7年游戏服务器开发,擅长c/c++,javesript,php;熟悉linux,mysql/redis,elasticsearch;开源爱好者.github : https://github.com/yuyunliuhen

文章分类

全部博文(26)

文章存档

2016年(1)

2015年(3)

2014年(3)

2013年(19)

分类: Mysql/postgreSQL

2013-04-22 20:00:50

    最近几天一直在做MYSQL的优化工作,其原因是游戏中的角色数据存盘时间过长,而且出现内存不断的增长,类似内存泄露,其后者与之前做的代码优化有关。        
    原因:
    4000+的机器人,平均每个25个物品,存盘时间超过200ms,将近占总时长的一半,根据现有的设计,进行存盘的时候,遍历每个物品,并将角色下所有的物品设置invlid,执行update/insert操作(如果存在部分关键字段相同,则update,否则insert新的物品);
    方案一:
    先从数据库查询结果,然后比较内存的数据,只存储变更的物品,减少插入的次数;因使用了查询缓存,query_cache_size设置的不够大,默认为2M,导致了在运行过程中内存不断增加,调整参数后能得到一定的缓解;
    方案二:    
    使用replace ...into 替代 insert,物品表使用了联合索引(charguid,packettype,packetpos),在使用了索引或者主键的情况下,如果存在相同的值,则会先删除,后插入;并使用批量插入,这样也达到减少操作次数的目的;物品的个数是不固定的,所以进行分页存储,每次存储固定个数,剩余的最后一次存储;
    结论:
    方案一没有带来实质性的改善(我想应该是在代码的某处存在问题),存盘时间还是比较长,甚至比优化前更长;    
    方案二改善比较大,时间为原来的1/10左右,也就是说提升了90%;进行优化时,总会考虑批量的执行insert,但没想到效果是这么明显;
    其他:
    运行时执行tunning-primer.sh,可得到一些mysql优化的建议;


其他:

1, mysql 中text类型的字段最大长度为2^16=65535,即16k

2, innodb存储引擎的默认格式是compact(redundant为兼容以前的版本),对于blob,text,varchar这样的大字段(8092),innodb只会存放768个字节在数据页中,而剩余的数据则会存储在溢出段中(发生溢出情况的时候适用);

3 ,innodb的块大小默认为16k,由于innodb的存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中至少有两行记录,也就是说innodb引擎每行数据不能超过8k;

4, mysql 在操作数据的时候,以page为单位,不管是更新,插入,还是删除,都需要将那行数据所在的page读到内存中,然后再进行操作,这样就存在一个命中率的问题,如果一个page中能够相对的存放足够多的行,那么命中率就会相对高一些,性能就会有提升;

已做过的优化:    
(1)分表
如下表:

点击(此处)折叠或打开

  1. CREATE TABLE `t_char` (
  2.   `aid` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `accname` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  4.   `charguid` int(11) NOT NULL,
  5.   `charname` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  6.   `title` varchar(50) NOT NULL,
  7.   `pw` varchar(15) NOT NULL,
  8.   `sex` smallint(6) NOT NULL,
  9.   `level` int(11) NOT NULL,
  10.   `enegry` int(11) NOT NULL,
  11.   `outlook` int(11) NOT NULL,
  12.   `scene` int(11) NOT NULL,
  13.   `xpos` int(11) NOT NULL,
  14.   `zpos` int(11) NOT NULL,
  15.   `menpai` smallint(6) NOT NULL,
  16.   `hp` int(11) NOT NULL,
  17.   `mp` int(11) NOT NULL,
  18.   `strikepoint` smallint(6) NOT NULL,
  19.   `camp` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  20.   `str` int(11) NOT NULL,
  21.   `con` int(11) NOT NULL,
  22.   `dex` int(11) NOT NULL,
  23.   `spr` int(11) NOT NULL,
  24.   `ipr` int(11) NOT NULL,
  25.   `points` int(11) NOT NULL,
  26.   `logouttime` int(11) NOT NULL,
  27.   `logintime` int(11) NOT NULL,
  28.   `createtime` int(11) NOT NULL,
  29.   `dbversion` int(11) NOT NULL DEFAULT '0',
  30.   `haircolor` int(11) NOT NULL,
  31.   `hairmodel` int(11) NOT NULL,
  32.   `facecolor` int(11) NOT NULL,
  33.   `facemodel` int(11) NOT NULL,
  34.   `vmoney` int(11) NOT NULL,
  35.   `isvalid` smallint(6) NOT NULL,
  36.   `exp` int(11) NOT NULL,
  37.   `pres` text NOT NULL,
  38.   `mdata` text,
  39.   `mflag` text,
  40.   `relflag` text,
  41.   `settings` text NOT NULL,
  42.   `shopinfo` text,
  43.   `carrypet` varchar(20) NOT NULL,
  44.   `guldid` int(11) NOT NULL,
  45.   `teamid` int(11) NOT NULL,
  46.   `headid` int(11) NOT NULL,
  47.   `erecover` int(11) NOT NULL,
  48.   `RMB` int(11) NOT NULL DEFAULT '0',
  49.   `vigor` int(11) NOT NULL,
  50.   `maxvigor` int(11) NOT NULL,
  51.   `BankRMB` int(11) NOT NULL DEFAULT '0',
  52.   `vrecover` int(11) NOT NULL,
  53.   `energymax` int(11) NOT NULL,
  54.   `pwdeltime` int(11) NOT NULL,
  55.   `pinfo` text,
  56.   `bkscene` int(11) DEFAULT NULL,
  57.   `bkxpos` int(11) DEFAULT NULL,
  58.   `bkzpos` int(11) DEFAULT NULL,
  59.   `titleinfo` text,
  60.   `dietime` int(11) NOT NULL,
  61.   `bankmoney` int(11) NOT NULL,
  62.   `bankend` int(11) NOT NULL,
  63.   `cooldown` text,
  64.   `rage` int(11) DEFAULT '0',
  65.   PRIMARY KEY (`aid`,`charname`),
  66.   UNIQUE KEY `Index_char_charguid` (`charguid`),
  67.   UNIQUE KEY `Index_char_charname` (`charname`),
  68.   KEY `Index_char_accname` (`accname`)
  69. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 将表中的非text字段分为一个表,text字段分为另外一个表,根据知识点2,表的平均行长度最好不要超过8k;也可以多分为几个表,字段长度差不多的分在一个表中;
    实践表明,100G左右的数据,经过分表之后,大小约为20G,而且备份数据的速度大大提升;
(2)加索引 
根据查询explain,分析表的必须访问的行数,根据其加表索引;基本上是根据select语句,为检索的字段加索引;
(3)优化insert语句
将多次insert改为一次插入多条记录;
(4)代码优化
优化数据库逻辑相关代码,将数据库的循环操作,特别是物品类,每个物品一条记录,每个玩家身上最大可能有270个左右的数据,也就是说在存盘的时候,需要循环270次,根据日志记录,在此消耗时间比较大;其方案是标记玩家更改的数据,存盘时只存变更的数据;
(5)配置相关优化
降低事务的隔离级别;
关闭二进制日志;
减少磁盘io,降低日志刷新频率;
加大查询缓存设置;
其他常规配置;
(6)其他
根据vmstat,iostat工具监测cpu,磁盘io的性能,根据参数确认服务器瓶颈

1, mysql 中text类型的字段最大长度为2^16=65535,即16k

2, innodb存储引擎的默认格式是compact(redundant为兼容以前的版本),对于blob,text,varchar这样的大字段(8092),innodb只会存放768个字节在数据页中,而剩余的数据则会存储在溢出段中(发生溢出情况的时候适用);
阅读(2006) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~