每秒大概 100 个更新， 200 万左右数据，每次都是有索引更新，每次更新一条记录，大量处于 updating ， mysql 更新操作有这么慢么？还好多都更新等待超时了，或者该怎么找出哪的问题么？
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 3847 days ago, the information mentioned may be changed or developed.
 |
1
msg7086 Mar 24, 2016
SSD ？
|
 |
2
peter999 Mar 24, 2016
是事务表吗？
|
 |
3
Lpl Mar 24, 2016
你查查目前更新的语句，是不是卡在某个 sql 上了？
|
 |
7
lj0014 Mar 24, 2016
innodb 还是 myisam
|
 |
9
lecher Mar 24, 2016 via Android
索引重建的成本太高？建个测试环境试试删除索引后的更新耗时。看看具体的 SQL 和索引。
简单粗暴 SSD ，应该效果拔群。 |
 |
10
noahzh Mar 24, 2016
索引更新指的是更新索引,还是通过索引更新?
|
 |
12
sujin190 OP @lecher 根据有索引的字段更新，但跟更新的不是索引字段，有索引的字段 insert 之后就没有更新过了，这种情况除了 io 问题之外还有可能是其他问题么？更新的那个字段默认是 null ，更新之后有值，这种情况有有限么？
|
 |
13
cevincheung Mar 24, 2016
postgresql 。
|
|
15
lecher Mar 24, 2016 via Android  1
那只能拆解问题了。
update 毕竟分两步，先查后改。如果每次只是根据索引更新一条，试试拆成查询看看查询消耗。 如果不是需要重建索引，感觉问题很可能出现在查询这块的消耗上。用 EXPLAIN 分析一下操作的影响行数，如果建的索引用得好，查询的影响行数估计就只有几个。 之后才是测试取单行的标识 id 直接做更新操作， varchar 是变长字段，改动是会影响行数据的长度的。但应该不会出现那么大的耗时。 |
|
16
sujin190 OP @lecher 根据索引更新，是唯一索引，所以每次肯定也只影响一行，其实平常是正常的，只是并发稍高一些的时候会慢，但看 cpu 和磁盘 io 似乎并不高，每秒大概两三百写， cpu 10%左右，这样应该不算高吧？
|
 |
17
wuyadong Mar 24, 2016
这种情况，我一般先全部 delete 掉，然后在 insert ，最后重建索引， update 很慢。
|
 |
19
likuku Mar 24, 2016
InnoDB 相关设定，都默认的么？
|
|
20
likuku Mar 24, 2016
机器硬件信息，请提供:
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 |
|
22
sujin190 OP @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 机械硬盘 |
 |
23
huigeer Mar 24, 2016 via iPhone
主键是自增的么
|
 |
25
yangqi Mar 24, 2016
目测是表的索引坏了，看过 show index from table 了么？ Cardinality 那一列
|
 |
26
billgreen1 Mar 24, 2016
可能是 sql 语句有问题，我昨天做查询的时候，一个字段是股票代码， 600000 这样的，数据库里是文本格式，我查询的时候却用了数字格式，可以查的出来，但是要耗时 160+秒，后来发现这个问题，查询降到了 1 秒以下。
|
|
28
sujin190 OP @billgreen1 貌似不是这个问题
|
|
29
yangqi Mar 24, 2016
@sujin190 看 cardinality 那列，如果有 null 的话就需要优化了。另外把 update 语句换成 select 然后 explain 一下看看。
|
 |
30
jwdstefanie Mar 24, 2016
注意吧 where 后面的字段设置索引 速度嗷嗷的
|
|
31
sujin190 OP @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 个字符左右的值，影响么？ |
|
32
yangqi Mar 24, 2016
SET profiling=1;
然后执行 update 语句 最后 show profile; |
|
33
lecher Mar 25, 2016 1
如果 varchar 的长度原先为空， update 一个 30 字符的值进去， mysql 要做的是开辟新的空间把整行数据迁移过去，这个过程会锁表，这个并发高起来，性能都消耗在计算空间申请新空间上了，其实还不如 insert 。
要优化，可以考虑将 varchar 字段改成定长，这样在行记录创建的时候，就会预留固定长度的位置， update 的时候就不需要重新开辟新的空间，执行 update 的消耗就与 select 基本一致，付出的代价就是这个表的文件大小会变大，因为预留的空字符串长度，等于空间换时间。 |
|
34
yangqi Mar 25, 2016 1
另外看下你的 key_buffer_size, 还有索引大小， order_id 是 varchar 索引应该不小，如果不能放在 ram 里面的话就只能从 disk 上读写，每次 update 操作都要更新索引，肯定会慢。
|
|
36
sujin190 OP @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) |
 |
37
realpg Mar 25, 2016 1
@sujin190
1. 本地服务器 or 云服务器 or RDS 服务？ 2. 看你透露出来的表结构，有唯一自增主键，同时 orderid 有唯一索引，根据 orderid 去更新非索引列，这种模型对吧？那么， update 没问题，有问题的是其他查询导致的锁表，然后 update 就进队列了，发生三五十次，因为 update 非常频繁，堆积就很严重了，检查 slowlog 吧。 |
|
39
lecher Mar 25, 2016 via Android
把线上数据导一份到本地测速一下便知。
|
|
40
noahzh Mar 25, 2016
@sujin190 这是索引区分度的问题,key_buffer_size 只是对 myisam 有效,对 innodb 无效,这明显是并发锁的问题,索引对应列过多,加锁了.很正常.重新设计一个索引.
|
|
44
realpg Mar 25, 2016
@sujin190
云服务器，你确定不是 IO 的问题？感觉可能性极大…… 另外发个 show create table XXX 上来让大家看看吧，感觉也可能是其他联合索引更新导致的锁 你这个 update 操作密度比较大， 100 次每秒，一旦一个其他导致锁的操作阻塞，你的 update 进队列，一秒就堵 100 个查询，导致阻塞的操作的频率，决定了累计程度，时间长就这样了，也是很可能的。 |
|
47
realpg Mar 25, 2016
@pubby
不一定会的。导致锁表那个操作并不一定执行频率很高，未必能 show processlist 准确抓到。 卡住以后，堵了太多 update ，然后当那个锁释放的时候，这个待执行 update 队列就变态的大了， show proceslist 只能抓到正常的高密度 update 。 |
 |
50
Infernalzero Mar 25, 2016
最好不要拿 varchar 当索引，特别是长度比较长的
一般是把对应字段转换下， md5 后的值转换成整型存储，然后 md5 后的字段作为索引 |
|
51
sujin190 OP @pubby 12g 内存，用了不到一半。。 io 每秒 200 多， 300KB 左右，这应该不算高吧，因为是云主机，可能是云主机所在物理主机 io 高
|
|
52
sujin190 OP @Infernalzero 恩，固定 24 字符的，换成 char 会不会好点？
|
|
55
sujin190 OP @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 秒 |
 |
56
yangdehua Mar 31, 2016 1
楼主执行下 show full processlist; 贴上来
把 update 的 sql 贴出来; 把表结构贴上来; |
Select Language