MySQL Forums
Forum List  »  Performance

Re: MySQL 5.7 performance issue with 300 GB Innodb
Posted by: Peter Brawley
Date: September 13, 2017 11:25AM

Is the system generally sluggish, or are particular queries slow? If the latter, have you run Explain on those queries and using that info to optimise them?

Is the system primarily MyISAM or InnoDB? Let's see the result of this query ...

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 3 pw) A  
ORDER BY TSize;

And let's see the result s of ...

show variables;
show global status;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.7 performance issue with 300 GB Innodb
646
September 13, 2017 11:25AM


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.