中止 DELETE 查询后 Mysql 占用 50% cpu
我遇到了以前从未遇到过的问题。 我昨天正在清理客户端数据库和包含访问统计信息的表，其中包含 15GB 的数据，因此我决定删除 2 月 1 日之前的所有行，因为我们将所有数据存储在每日统计信息中，无需保留那些旧的单一统计信息。
我尝试运行 DELETE FROM sv WHERE datetime < ?（日期时间索引）和DELETE FROM sv WHERE id ? （id 上的主索引），但是两个查询都太慢，花费了超过 15 分钟。所以我刚刚通过 Ctrl+C 中止了这些查询。
然后我认为更好的想法是复制表结构和 2 月行并删除旧表，但今天我发现服务器上的 mysql 进程的 CPU 使用率高达 50%，而我们网站上的流量正常。
将 sv 表移出数据库文件夹（myIsam 表）后使用率仍高达 50%。
首先，我认为该表一定已损坏，但 phpmyadmin 没有显示它标记为崩溃。
我该如何调试这个？那里发生了什么？
I have problem I have never met before.
I was clearing yesterday clients db and table with stats of visits had 15GB of data, so I decided to delete all rows older than 1 Feb, because we have all that data stored in daily stats and there's no need to keep those old single stats.
I tried to run DELETE FROM sv WHERE datetime < ? (index on datetime) and DELETE FROM sv WHERE id < ? (primary index on id), but both queries where too slow, took more than 15 mins. So I have just aborted those queries by Ctrl+C.
Then I thought better idea would be copying table structure and February rows and remove old table, but today I found out that mysql process on server is running up to 50% cpu usage with normal traffic on our site.
After moving sv table out of database folder (myIsam table) usage is still up to 50%.
First I thought that table must have been corrupted, but phpmyadmin does not show it marked as crashed.
How I can debug this one? What have happened there?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于查询被终止，您的系统可能会重新索引整个表。如果它是在 INNODB 的事务中，您还需要系统重建表，然后重新创建索引。
我会查看 iostat 并查找磁盘争用情况。
Your system is likely re-indexing the entire table as a result of the killed query. If it was in a transaction in INNODB, you are also having the system rebuild the table then recreating the index.
I would look at iostat and look for disk contention.