MySQL Forums
Forum List  »  InnoDB

Re: HIgh cpu utilization
Posted by: Peter Brawley
Date: May 15, 2018 10:29AM

> I am using the link(https://dev.mysql.com/doc/dev/mysql-server/8.0.0/)
> and able to fetch only some of the information.

You said you're running MySQL 5.7.20. Use the dcs for the version you are running.

If it a multi-processor box, or multi-core cpu see https://www.tecmint.com/understand-linux-load-averages-and-monitor-performance/ for how to interpret top results.

While running top -H at a busy time press 1 to see high-use threads.

Usually the problem is poorly designed queries. Turn on the slow query log for a day, optimise the queries it reports by running Explain on them and using those results to guide improvements in their use of indexes.

The query cache is often a problem. It's deprecated in 5.7, removed in 8.0. Turn it off.

Failing all the above, post ...

(i) the value of innodb_buffer_pool_size, How much RAM the machine has, how much is used by other processes.

(ii) the result of ...

select engine,data,indexes,total
from (
  select 
    ifnull(engine,'TOTALS') as engine, 
    concat(data,' GB') as data, 
    concat(indexes,' GB') as indexes, 
    concat(tot,' GB') as total,
    if(engine is null,-1,tot) as ord
  from (
    select   
      engine,  
      round( sum(data_length)/1024/1024/1024, 2 ) as data,  
      round( sum(index_length)/1024/1024/1024, 2 ) as indexes,  
      round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
    from information_schema.tables
    where engine is not null and engine not in('information_schema','performance_schema')
    group by engine with rollup
  ) sums
) list
order by list.ord desc;

(iii) the results of running these queries in the mysql client program ...

show variables;

show global status;

(iv) from the OS ...

free -m

Options: ReplyQuote


Subject
Views
Written By
Posted
98
May 08, 2018 01:41AM
71
May 08, 2018 10:57AM
37
May 15, 2018 12:46AM
Re: HIgh cpu utilization
33
May 15, 2018 10:29AM


Sorry, only registered users may post in this forum.

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.