存档

文章标签 ‘MySQL’

MySQL的统计数值问题

2017年6月22日 没有评论

典型的明细与统计数值表问题:
以用户好友关系系统举例:用户的好友有个明细列表,还有一个用户好友个统计数值,便于直接返回好友数,不用每次都count。

问题:
如果增减好友时,用++或–来操作统计值表,容易出现不一致的情况,而且会一直错下去。

解决方案:
更新统计数值的时候,不是+1或-1,而且count后更新。

看似解决了问题,实际上带来了新的问题:
当用户数不多的时候,比如几百或几千,效率很高。但当好友数达到百万时,每次count会耗时两三秒,数据库的负载就会出现严重问题。

最终的解决方案:
当好友数在万以下时,采用count更新;过万以后,用+1或-1来操作。
因为当好友超过一万以后,已经没法人工去数了。有误差也不会影响体验了。

分类: 工作 标签: ,

MySQL的max_user_connections限制

2016年3月11日 没有评论

用了某山云服务的RDS服务,其实就是MySQL服务。扩容时碰到连接超限的错误。

User xxx already has more than 'max_user_connections' active connections

查到的结果是总连接限制数是200,单用户限制为20

mysql> select @@max_user_connections;
+------------------------+
| @@max_user_connections |
+------------------------+
|                    20 |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               200 |
+-------------------+
1 row in set (0.00 sec)

用show processlist;查到的当前用户连接数已经超出了20,所以受限了。
更多内容…

MySQL的InnoDB大表建索引

2015年11月11日 没有评论

对250G的大表新建索引,等了24小时没结束。注:innodb_buffer_pool_size大小为20G
按自己的理解,应该会生成一个略大于原表的临时表,替换原表。
在大表的同目录下只发现一个文件名以#开始的小文件,未见大文件。

-rw-rw---- 1 mysql mysql         8923  Nov   9 01:14 #sql-4220_ble924.sql

/tmp下面也没见大文件,用du -sh查到的结果也只有20k。
用lsof -p查到一批文件,发现打开了/tmp下两个30G的文件,但标记为已删除

# lsof -p 16928
...
mysqld  16928 mysql  txt    REG                8,1     41662738    671857 /data/software/mysql-5.5.8/bin/mysqld
...
mysqld  16928 mysql  263u   REG                8,1  30028070912  25591817 /tmp/ibZMaeaz (deleted)
mysqld  16928 mysql  268u   REG                8,1  30028070912  25591818 /tmp/ibRE7pZq (deleted)

用iostat -x 1查看,其所在磁盘的ioutil为100%,主要为r,少量w。
因为磁盘的空余空间不足,只好kill了。kill以后,ioutil依然是100%,读入速度没变,写入速度达到读取速度。
kill之前process状态为

| 11659556 | root | localhost | quad | Query | 84097 | manage keys | ALTER TABLE T1 ADD INDEX I1 (C1, C2) |

kill之后变成

| 11659556 | root | localhost | quad | Killed | 84097 | manage keys | ALTER TABLE T1 ADD INDEX I1 (C1, C2) |

传言回滚会耗费两倍以上的时间(24小时*2=48小时),实测发现十分钟内搞定。完成后lsof未见那两个30G的文件,df查到磁盘空间已经释放。

后又用17G的中表进行测试,半个小时生成的tmp文件200M,所此估算约需要42小时,放弃之。

下面是建议:
更多内容…

MySQL一主多从废主升一从为主

2015年11月10日 没有评论

简要说一下流程:
1、流量全切到主上。
2、停所有的从:stop slave
3、查所有从库的状态,找到数据最新的那个,其它从库使用START SLAVE UNTIL同步到跟他一致。

START SLAVE UNTIL MASTER_LOG_FILE = 'log-bin.00xxxx', MASTER_LOG_POS = ****;

4、在新主上找最大的log-bin文件,并用mysqlbinlog查到最后一个序号。长的是这个样子的:

# at 31013843

5、其它从库的主切到这个从上,并启动各从库的同步。

CHANGE MASTER TO MASTER_HOST='xx',MASTER_USER='xxx',MASTER_PASSWORD='xx',MASTER_LOG_FILE='xxx', MASTER_LOG_POS=xxx;
start slave;

6、启动新主的同步。同步完成后,就可以把流量切到新的主从上了。确认流量安全切完后,新主可以stop slave,停老主库。

分类: 工作 标签: , ,

MySQL崩溃之utf8mb4的like非左匹配

2015年7月8日 没有评论

线上数据库崩溃,怀疑是utf8mb4编码导致的,目前已经重现,并找到具体的解决办法。

原因:
当支持utf8mb4的字段中出现emoji表情时,用like ‘%xx%’方式全搜索时,崩溃。
应该是MySQL的bug,怀疑是因为emoji的编码长度是4,而utf8_unicode_ci是3,MySQL的like非左匹配操作时,碰到占4字节的utf8mb4时出现问题(猜测)。

解决办法:
用instr函数取代like。不崩溃且效率高。instr(‘abcd’,’ab’)值为1,instr(‘abcd’,’ca’)值为0。即大于0为命中。

总结:在like左匹配的时候,用原生like靠谱。如果是两边匹配的时候,like支持复杂语法,所以效率较低。普通的%xx%类型的搜索,直接instr即可搞定。
另外:instr的大小写匹配跟like一样,默认是大小写不敏感的。

以下是完整的异常信息。
更多内容…

分类: 工作 标签: , , , ,

MySQL动态调整索引一例

2015年5月18日 没有评论

缘起:线上发现一条慢查询日志,耗时32秒,对用户来说此次访问直接失败。

原因:用户长期未上线,拉群离线消息时扫描了几千万行数据。

现状:主键为消息唯一编号msgId,用户拉消息时,使用msgId > xxxx来拉,正常用户间隔较近,基本上耗时为毫秒。间隔越大,时间越长。

修改:当前msgId差值小于200w时,用主键索引。超出时,强制使用群编号groupId索引。msgId差值200w时用主键索引耗时为5秒左右。差值4000w时强制使用groupId索引,耗时600毫秒左右。

结论:用默认方案hold绝大多数的case,当特例出现时,强制使用另一个索引,确保用户可用。

分类: 工作 标签: , ,