mysql 大量更新请求 updating 状态

[sujin190]

每秒大概 100 个更新， 200 万左右数据，每次都是有索引更新，每次更新一条记录，大量处于 updating ， mysql 更新操作有这么慢么？还好多都更新等待超时了，或者该怎么找出哪的问题么？

[msg7086]

SSD ？

[peter999]

是事务表吗？

[Lpl]

你查查目前更新的语句，是不是卡在某个 sql 上了？

[sujin190]

@msg7086 普通磁盘，磁盘 io 不高啊

[sujin190]

@peter999 事务表？

[sujin190]

@Lpl 没有，只是每个都很慢，经常等待几十秒的

[lj0014]

innodb 还是 myisam

[sujin190]

@lj0014 innodb

[lecher]

索引重建的成本太高？建个测试环境试试删除索引后的更新耗时。看看具体的 SQL 和索引。
简单粗暴 SSD ，应该效果拔群。

[noahzh]

索引更新指的是更新索引,还是通过索引更新?

[sujin190]

@noahzh 通过索引更新，更新的不是索引字段

[sujin190]

@lecher 根据有索引的字段更新，但跟更新的不是索引字段，有索引的字段 insert 之后就没有更新过了，这种情况除了 io 问题之外还有可能是其他问题么？更新的那个字段默认是 null ，更新之后有值，这种情况有有限么？

[cevincheung]

postgresql 。

[sujin190]

@lecher varchar 类型，更新之后值不超过 64 字符

[lecher]

那只能拆解问题了。
update 毕竟分两步，先查后改。如果每次只是根据索引更新一条，试试拆成查询看看查询消耗。

如果不是需要重建索引，感觉问题很可能出现在查询这块的消耗上。用 EXPLAIN 分析一下操作的影响行数，如果建的索引用得好，查询的影响行数估计就只有几个。


之后才是测试取单行的标识 id 直接做更新操作， varchar 是变长字段，改动是会影响行数据的长度的。但应该不会出现那么大的耗时。

[sujin190]

@lecher 根据索引更新，是唯一索引，所以每次肯定也只影响一行，其实平常是正常的，只是并发稍高一些的时候会慢，但看 cpu 和磁盘 io 似乎并不高，每秒大概两三百写， cpu 10%左右，这样应该不算高吧？

[wuyadong]

这种情况，我一般先全部 delete 掉，然后在 insert ，最后重建索引， update 很慢。

[sujin190]

@wuyadong 可是不能删。。

[likuku]

InnoDB 相关设定，都默认的么？

[likuku]

机器硬件信息，请提供:
CPU/核数量，内存大小，硬盘类型，文件系统类型

mysql 的运行时参数值：
innodb_buffer_pool_size
innodb_log_file_size
innodb_flush_method
innodb_flush_log_at_trx_commit

可参考：
http://mp.weixin.qq.com/s?__biz=MzA4Nzg5Nzc5OA==&mid=206762682&idx=1&sn=1233ed1496d7fd059d247329f3d3a183&scene=1&key=c76941211a49ab587d35d0d840a84ff2e3948510bca7698783e134b95c3e8ad0a30d1f83897e9c764e289a1011c565db&ascene=0&uin=Mjk1ODMyNTYyMg%3D%3D&devicetype=iMac+MacBookPro9%2C2+OSX+OSX+10.10.3+build(14D136)&version=11020012&pass_ticket=GJi1zStfd3iFy8nPiSm73eDDCYHehF3pnzgmnpzERbyqm7LlmMscbVsBrAMC%2FPt3

[sujin190]

@likuku buffer 调大了

[sujin190]

@likuku
innodb_data_home_dir = /data/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 5G
innodb_additional_mem_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90

8 核 12g 机械硬盘

[huigeer]

主键是自增的么

[sujin190]

@huigeer 是的，但不是根据主键更新的

[yangqi]

目测是表的索引坏了，看过 show index from table 了么？ Cardinality 那一列

[billgreen1]

可能是 sql 语句有问题，我昨天做查询的时候，一个字段是股票代码， 600000 这样的，数据库里是文本格式，我查询的时候却用了数字格式，可以查的出来，但是要耗时 160+秒，后来发现这个问题，查询降到了 1 秒以下。

[sujin190]

@yangqi 什么样算坏的呢？
1964190 BTREE 索引

[sujin190]

@billgreen1 貌似不是这个问题

[yangqi]

@sujin190 看 cardinality 那列，如果有 null 的话就需要优化了。另外把 update 语句换成 select 然后 explain 一下看看。

[jwdstefanie]

注意吧 where 后面的字段设置索引 速度嗷嗷的

[sujin190]

@yangqi 没有
mysql> explain select id from order where order_id='201603010140117550200039';
+----+-------------+--------------+-------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | order_201603 | const | order_id | order_id | 72 | const | 1 | Using index |
+----+-------------+--------------+-------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
不过需要更新的那个字段是 varchar 类型，默认为 NULL 更新为 30 个字符左右的值，影响么？

[yangqi]

SET profiling=1;

然后执行 update 语句

最后 show profile;

[lecher]

如果 varchar 的长度原先为空， update 一个 30 字符的值进去， mysql 要做的是开辟新的空间把整行数据迁移过去，这个过程会锁表，这个并发高起来，性能都消耗在计算空间申请新空间上了，其实还不如 insert 。

要优化，可以考虑将 varchar 字段改成定长，这样在行记录创建的时候，就会预留固定长度的位置， update 的时候就不需要重新开辟新的空间，执行 update 的消耗就与 select 基本一致，付出的代价就是这个表的文件大小会变大，因为预留的空字符串长度，等于空间换时间。

[yangqi]

另外看下你的 key_buffer_size, 还有索引大小， order_id 是 varchar 索引应该不小，如果不能放在 ram 里面的话就只能从 disk 上读写，每次 update 操作都要更新索引，肯定会慢。

[sujin190]

@lecher 改成 char 型默认空字符串行么？

[sujin190]

@yangqi key_buffer_size 512M 索引大小为 400M ，这个 key_buffer_size 是每个 db 单独分配的么？需要更新有两个 db 的两张表，数据差不多大
mysql> SHOW STATUS LIKE 'key_read%';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Key_read_requests | 1242558 |
| Key_reads | 639 |
+-------------------+---------+
2 rows in set (0.00 sec)

[realpg]

@sujin190
1. 本地服务器 or 云服务器 or RDS 服务？
2. 看你透露出来的表结构，有唯一自增主键，同时 orderid 有唯一索引，根据 orderid 去更新非索引列，这种模型对吧？那么， update 没问题，有问题的是其他查询导致的锁表，然后 update 就进队列了，发生三五十次，因为 update 非常频繁，堆积就很严重了，检查 slowlog 吧。

[sujin190]

@realpg 云服务器，通过看 show processlist;似乎不是查询锁表，而且平时是正常的，只是并发稍高的时候会这样

[lecher]

把线上数据导一份到本地测速一下便知。

[noahzh]

@sujin190 这是索引区分度的问题,key_buffer_size 只是对 myisam 有效,对 innodb 无效,这明显是并发锁的问题,索引对应列过多,加锁了.很正常.重新设计一个索引.

[sujin190]

@noahzh 只有四个索引，其中两个都是不重复的

[noahzh]

@sujin190 你根本没有懂,我问你个问题一个表有 1000 列,索引能区分出 900 列,这个索引还有意义吗?

[sujin190]

@noahzh 索引不是区分度越大越好么？

[realpg]

@sujin190
云服务器，你确定不是 IO 的问题？感觉可能性极大……
另外发个 show create table XXX 上来让大家看看吧，感觉也可能是其他联合索引更新导致的锁
你这个 update 操作密度比较大， 100 次每秒，一旦一个其他导致锁的操作阻塞，你的 update 进队列，一秒就堵 100 个查询，导致阻塞的操作的频率，决定了累计程度，时间长就这样了，也是很可能的。

[noahzh]

@sujin190 一个表 100 条记录,一个索引能对应 99 条记录,你说这个索引有意义吗?

[pubby]

@realpg 如果其他因为锁表阻塞到这种程度， show processlist 应该一眼能看出来了。

感觉还是 io 问题， swap 严重不？

[realpg]

@pubby
不一定会的。导致锁表那个操作并不一定执行频率很高，未必能 show processlist 准确抓到。
卡住以后，堵了太多 update ，然后当那个锁释放的时候，这个待执行 update 队列就变态的大了， show proceslist 只能抓到正常的高密度 update 。

[sujin190]

@noahzh 哦，我们这个索引的这个字段是唯一索引，每行是不重复的

[sujin190]

@realpg 今天正常了，看起来可能是云服务器所在的物理机 io 比较高，准备换 ssd 主机试试

[Infernalzero]

最好不要拿 varchar 当索引，特别是长度比较长的
一般是把对应字段转换下， md5 后的值转换成整型存储，然后 md5 后的字段作为索引

[sujin190]

@pubby 12g 内存，用了不到一半。。 io 每秒 200 多， 300KB 左右，这应该不算高吧，因为是云主机，可能是云主机所在物理主机 io 高

[sujin190]

@Infernalzero 恩，固定 24 字符的，换成 char 会不会好点？

[likuku]

@sujin190 干嘛不用 RDS ？自动 HA ，还有自动快照备份， OPS/TPS 也足够高。

[sujin190]

@likuku 这个嘛数据早期是直接从物理机房的机器服务器上直接 copy 过来的，也没有对 rds 测试，所以暂时还没有使用 rds

[sujin190]

@lecher
@likuku
@yangqi
@realpg

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show profile;
+----------------------+------------+
| Status | Duration |
+----------------------+------------+
| starting | 0.000134 |
| checking permissions | 0.000033 |
| Opening tables | 0.000035 |
| init | 0.000050 |
| System lock | 0.000083 |
| updating | 121.503612 |
| end | 0.000096 |
| query end | 0.000028 |
| closing tables | 0.000029 |
| freeing items | 0.000074 |
| cleaning up | 0.000029 |
+----------------------+------------+
11 rows in set, 1 warning (0.00 sec)

这个看起来怎么这么奇怪啊？ updating120 秒

[yangdehua]

楼主执行下 show full processlist; 贴上来
把 update 的 sql 贴出来;
把表结构贴上来;

[sujin190]

@yangdehua 好吧，终于发现了，其实是表有唯一索引， insert 之后没有 commit ，只有高并发之后才会出现异常，死锁了，不过这种情况下能不能看出是哪个 sql 没 commit 么？

[yangdehua]

@sujin190 如果你是 innodb 引擎，可以看看 show engine innodb status\G 或者是 select * from information_schema.innodb_trx\G