MySQL Forums
Forum List  »  Performance

Re: Bad performance on MySQL 8.0 compared to 5.7
Posted by: Raphael Yeung
Date: August 25, 2022 01:16AM

Hello Peter,

Thank you for your reply and sorry for late response.
Here're the results of the suggested queries.

(for the results on "show global status;", I will put it in the next post)

[8.0]
mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.00 sec)

mysql> 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;
Empty set, 1 warning (0.01 sec)

mysql> 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;
+-------------------+
| bufferpoolfullpct |
+-------------------+
| 0.52% |
+-------------------+
1 row in set (0.00 sec)

mysql> select engine,data,indexes,total
select
engine,
round( sum(data_length)/1024/1024/102 -> 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,
ine not in('information_schema','performance_schema')
group by engine with rollup
) sums
) list
order by list.ord desc; -> 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;
+--------+----------+---------+----------+
| engine | data | indexes | total |
+--------+----------+---------+----------+
| InnoDB | 32.74 GB | 2.43 GB | 35.17 GB |
| CSV | 0.00 GB | 0.00 GB | 0.00 GB |
| MyISAM | 0.00 GB | 0.00 GB | 0.00 GB |
| TOTALS | 32.74 GB | 2.43 GB | 35.17 GB |
+--------+----------+---------+----------+
4 rows in set (3.21 sec)


#====

[5.7]

mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.01 sec)

mysql> 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;
Empty set (0.01 sec)

mysql> 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;
+-------------------+
| bufferpoolfullpct |
+-------------------+
| 2.54% |
+-------------------+
1 row in set (0.01 sec)

mysql> 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;
+--------+----------+---------+----------+
| engine | data | indexes | total |
+--------+----------+---------+----------+
| InnoDB | 32.66 GB | 2.42 GB | 35.08 GB |
| CSV | 0.00 GB | 0.00 GB | 0.00 GB |
| MEMORY | 0.00 GB | 0.00 GB | 0.00 GB |
| MyISAM | 0.00 GB | 0.00 GB | 0.00 GB |
| TOTALS | 32.66 GB | 2.42 GB | 35.08 GB |
+--------+----------+---------+----------+
5 rows in set (3.40 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Bad performance on MySQL 8.0 compared to 5.7
29
August 25, 2022 01:16AM


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.