MySql CPU - 100% HELP!

Question

0.00/5 (No votes)

See more:

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'

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?

Posted 29-Sep-10 22:46pm

udikantz

Updated 29-Sep-10 22:52pm

v3

Add a Solution

Comments

Sandeep Mewara 30-Sep-10 8:50am

How many records are there in this table?

udikantz 30-Sep-10 11:44am

it ssys in the post 100K

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Answer 1 · 2011-04-20T00:21:00

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

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

SQL

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

RDBurmon · Answer 2 · 2011-04-20T00:03:00

Solution 1

Create non clustor index on CustUID .

Posted 20-Apr-11 0:03am

RDBurmon

Comments

Dylan Morley 20-Apr-11 6:14am

He's already got a clustered index on CustUID (PRIMARY KEY (`CustUID`)), so how would this help?

RDBurmon 20-Apr-11 6:16am

Sorry , That was my mistake , I coudn't see it.