MySQL CPU Usage – Constant Usage with No Connections or Queries

MySQL

I'm using mysql on a windows development machine, and all was good for a long time.

But today my PC was running slowly, and I noticed mysqld.exe is using between 25% and 40% CPU.

I've tried rebooting and giving it some time, but the CPU usage is fairly constant even when idleing on the desktop.
I've noticed the hard drive is working quite hard too, probably because of mysqld

I'm certain nothing is using the server as the only thing that would connect to it is the application on my local machine that I am developing, and that is not running.

But, just to be sure I ran the following statements

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p status
Enter password: **********
Uptime: 995  Threads: 1  Questions: 2  Slow queries: 0  Opens: 67  Flush tables: 1
Open tables: 60  Queries per second avg: 0.002

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p processlist
Enter password: **********
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 2  | root | localhost:1775 |    | Query   | 0    | init  | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

which also seem to show the sever isn't doing anything.

So, what gives? What is mysqld doing?

Edit: I did recently kill an update statement that was taking a long time. Could this have anything to do with it?

Best Answer

It's probably good that you added that afterthought, because that's very likely what you're experiencing... the server is rolling back the work it had already done before you killed the large update.

If a big transaction is slowing down server performance, rolling it back can make the problem worse, potentially taking several times as long to perform as the original DML operations.

Even after a reboot? Yes...

Killing the database process does not help, because the rollback starts again on server startup.

^{— http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-transaction-management.html}

InnoDB saved the data in the rows it was changing, and queries against that changes that are still being rolled back should be answered transparently using the data that's still in the undo log.

You'll probably also find the large amount of disk activity is associated with mysqld:

$ sudo apt-get install iotop
$ sudo iotop

There is a way to dispense with a runaway rollback, also explained at the link I've cited for the quotes, above... but your safest bet is to just let it churn for a while.

Related Solutions

Mysql – How to prevent high memory, CPU and time consumption by MySQL restore

I think the second point more or less shows you where your bottleneck is. I would be willing to bet that if you look at top or equivalent on your platform you will see high I/O wait time.

In essence you are probably waiting for your hard disks as tables are rewritten. Also innodb has some oddities regarding locks here so it isn't clear that these are all sequential writes.

I can't tell you about expected MySQL performance other than that you seem to be hitting write performance bottlenecks. I can tell you that a 700MB dump won't restore super-fast on any db with ordinary hard drives, but also that 25 min does seem a bit high based on my experience on PostgreSQL.

Mysql – CPU usage on RDS instance monotonically increasing with no change to query volume

I have some queries for you regarding table sizes that you can run in MySQL during these spikes

Database size in terms of StorageEngine (MB)

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Database size in terms of Databases (MB)

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT
IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Database size in terms of Database/StorageEngine (MB)

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(REPLACE(FORMAT(
B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Pay attention to certain markers

Innodb_buffer_pool_pages_dirty
Innodb_data_reads
Innodb_data_writes

I recommend downloading MySQL Administrator (I know, it's old but I still you it for quick and dirty "I WANNA SEE STATS NOW" moments of day) and set it up. I customized my own graphs to watch the size of the InnoDB Buffer Pool and its dirty pages. You could also just use the Connection Health tab.

Best Answer

Related Solutions

Mysql – How to prevent high memory, CPU and time consumption by MySQL restore

Mysql – CPU usage on RDS instance monotonically increasing with no change to query volume

Related Question