Re: Bad performance on MySQL 8.0 compared to 5.7

[Raphael Yeung]

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)