MySQL Forums
Forum List  »  Performance

Re: Older version of MySQL server are faster than newer Versions
Posted by: Peter Brawley
Date: May 04, 2021 01:06PM

The my.ini file that comes with the installation contains useful general recommendations.

For specific issues, you need to analyse the load. I have a script that'll produce such estimates if you'll ...

(i) post 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;

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

show variables;

show global status;

select * from sys.memory_global_total;

select 
  substring_index(event_name,'/',2) as code_area, 
  sys.format_bytes( sum(current_alloc) ) as current_alloc 
from sys.x$memory_global_by_current_bytes 
group by substring_index(event_name,'/',2) 
order by sum(current_alloc) desc;

select 
  concat(format(a.num * 100.0 / b.num,2),"%") bufferpoolfullpct 
from 
  (select variable_value num from performance_schema.global_status
  where variable_name = 'innodb_buffer_pool_pages_data') a,
  (select variable_value num from performance_schema.global_status
  where variable_name = 'innodb_buffer_pool_pages_total') b;

... and look for runaway mem use in the results of ...

select * 
from performance_schema.memory_summary_global_by_event_name
where event_name like 'memory/performance_schema/%';

(iii) review swapping in the Memory tab of the Windows Task Manager or Resource Monitor

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Older version of MySQL server are faster than newer Versions
84
May 04, 2021 01:06PM
80
May 06, 2021 02:17PM
122
May 09, 2021 11:28AM
78
May 09, 2021 12:27PM
68
May 10, 2021 01:42AM
78
May 10, 2021 10:17AM
73
May 10, 2021 11:15AM
91
May 10, 2021 02:39PM
93
May 11, 2021 06:58AM
64
May 11, 2021 11:42AM
92
May 11, 2021 01:14PM
70
May 11, 2021 02:05PM


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.