MySql CPU-100％帮助！ [英] MySql CPU - 100% HELP!

问题描述

大家好，我对使用"top"(linux)的MYSQL有很高的CPU问题，显示cpu峰值为90％.

我试图查找问题的根源，打开了常规日志和慢速查询日志，慢速查询日志没有找到任何内容.

Db包含一些小表和一个包含近10万行的大表，数据库引擎是MyIsam.我注意到一个奇怪的事情，在大表上，选择，插入非常快，但更新需要0.2-0.5秒.

已经使用过的优化和修复功能，没有改进.

这是表结构:

Hello everyone i have a high CPU problem with MYSQL using "top" ( linux ) shows cpu peaks of 90%.

I was trying to find the source of the problem, turned on general log and slow query log, The slow query log did not find anything.

The Db contains a few small tables and one large table that contains almost 100k rows, Database Engine is MyIsam. strange thing i have noticed that on the large table, select, insert are very fast but update takes 0.2 - 0.5 secs.

already used optimize and repair and no improvement.

this is the table structure:

CREATE TABLE IF NOT EXISTS `customers` (
  `CustFullName` varchar(45) NOT NULL,
  `CustPassword` varchar(45) NOT NULL,
  `CustEmail` varchar(128) NOT NULL,
  `SocialNetworkId` tinyint(4) NOT NULL,
  `CustUID` varchar(64) character set ascii NOT NULL,
  `CustMoney` bigint(20) NOT NULL default ''0'',
  `LastIpAddress` varchar(45) character set ascii NOT NULL,
  `LastLoginTime` datetime NOT NULL default ''1900-10-10 10:10:10'',
  `SmallPicURL` varchar(120) character set ascii default '''',
  `LargePicURL` varchar(120) character set ascii default '''',
  `LuckyChips` int(10) unsigned NOT NULL default ''0'',
  `AccountCreationTime` datetime NOT NULL default ''2009-11-11 11:11:11'',
  `AccountStatus` tinyint(4) NOT NULL default ''1'',
  `CustLevel` int(11) NOT NULL default ''0'',
  `City` varchar(32) NOT NULL default '''',
  `State` varchar(32) NOT NULL default ''0'',
  `Country` varchar(32) NOT NULL default '''',
  `Zip` varchar(16) character set ascii NOT NULL,
  `CustExp` bigint(20) NOT NULL default ''0'',
  PRIMARY KEY  (`CustUID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

基于表的键在该表上的任何更新语句都是很慢的.例如:

Any update statement on that table based on the table''s key is slow. for example:

UPDATE customers SET CustMoney = 1 WHERE CustUID = ''someid''

再次，我不确定这是否是导致CPU使用率过高的原因，但在我看来，更新语句花费这么长时间是不正常的. (0.5秒)

该表目前每秒最多可更新3次，将来它还会更频繁地更新.

我该怎么做才能改善此状况?

Again im not sure that this is the cause for the high CPU Usage but it seems to me that its not normal for an update statement to take that long. ( 0.5 sec)

The table is being updated up to 3 times in a sec at the moment and in the future it will update even more frequently.

What can i do to improve this?

推荐答案

`CustUID` varchar(64) character set ascii NOT NULL,

我通常避免将varchar用作唯一标识符-您可能有某些原因，但是通过执行以下操作无法达到相同的结果

*为新的唯一用户标识符创建一个auto_increment int字段
*对CustUID创建一个唯一约束(我假设这是某种客户简称，每个客户必须唯一吗?)

然后使用
执行更新

I generally avoid using varchar for unique identifiers - you may have some reasons for this, but couldn''t you achieve the same result by doing the following

* Create an auto_increment int field for a new unique user identifier
* Create a unique constraint over CustUID (which I assume is some sort of customer short name, that must be unique per customer?)

then perform your updates using

UPDATE customers SET CustMoney = 1 WHERE WhateverYouCallThisField = 123

我想看看是否有兴趣提高性能，对此很有兴趣.

看看在此线程中 [ ^ ]的类似问题

Interested to see if this improves performance, I suspect it will.

Have a look at this thread[^] for a similar question

在CustUID上创建非簇索引.

Create non clustor index on CustUID .

这篇关于MySql CPU-100％帮助！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！