MySQL Forums
Forum List  »  Performance

Re: High CPU usage in %user
Posted by: Rick James
Date: May 15, 2013 08:00PM

> I run optimize and repair in more important tables, so I think 46 fragmented table could unused dbs or because many tables are innodb.

Did it make any difference? (I'll bet it did not.)

Please provide
SHOW GLOBAL STATUS;
SHOW VARIABLES;
SHOW TABLE STATUS;
SHOW CREATE TABLE;

It sounds like you have NULL fields where you should have NOT NULL.

That one EXPLAIN points out that it will grab 44860 rows from one table, then at least that many from the other, the sort (ORDER BY) and deliver a few rows (LIMIT). Because you are filtering (WHERE) on fields in both tables, it is not possible to make it so that the ORDER BY and LIMIT can come first.

Akiban's 'group technology' can probably do an excellent job of that query.

Turn on the SlowLog. You may capture some useful statistics there.

Percona's XtraDB and Oracle's 5.6 can make better use of 24 cores. With plain 5.1, performance declines after about 8 cores. This _may_ be indicative of that issue:
> That query spent about 6 sec when the server are with high load, but only 0.72 sec when it is normal.

What version were you running on the old machine? Was it InnoDB then?

I see an UPDATE in "show engine innodb status" -- that will be conflicting with all the SELECTs, because all the selects must read the entire tables.

> Buffer pool hit rate 1000 / 1000
says that the buffer_pool is big enough.

Options: ReplyQuote


Subject
Views
Written By
Posted
55379
May 14, 2013 12:31PM
Re: High CPU usage in %user
5744
May 15, 2013 08:00PM
4647
May 16, 2013 02:22AM
4013
May 16, 2013 09:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.