phpBB SELECT last long time on new server

Question

I'm setting up a new server, and want to move existing phpBB installation there. Everything went OK, except one SELECT is in SLOW log and eats CPU forever. I am not DB specialist, so I am lost.

Old server - Win2008 R2, MySQL 5.7.24, php 5.6.39, single i7 Quad Core CPU, 24GB RAM

New Server - Win2016, MySQL 8.0.15, php 7.1.5, dual Xeon CPUs, 96GB RAM

I tried to open every table and check for errors, performed mysql_upgrade with OK result. The problematic SELECT is:

SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
            FROM phpbb_acl_groups a, phpbb_user_group ug, phpbb_groups g, phpbb_acl_roles_data r, phpbb_acl_options ao
            WHERE a.auth_role_id = r.role_id AND r.auth_option_id = ao.auth_option_id 
                AND a.group_id = ug.group_id
                AND g.group_id = ug.group_id
                AND ug.user_pending = 0
                AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)


                AND ao.auth_option = 'm_';

On the old server the select is done immeditely. On the new server it last 25 - 30 seconds. See pictures - look a the "rows examined". But all tables looks OK...

New server

Old server

Old server my.ini looks like this:

max_connections=151
table_open_cache=2000
tmp_table_size=922M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=3G
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=1024M
innodb_log_file_size=128M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

New server my.ini looks like this:

max_connections=151
table_open_cache=2000
tmp_table_size=5G
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=10G
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=2048M
innodb_log_file_size=128M
innodb_thread_concurrency=24
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

Answer 1

So, I:

• compared every table data, columns, rows, indexes between old and new database. I didnt found any single difference.
• installed mysql 8.0.15 on different machine and tried it there - just in case there is something wrong on the new server.

With no luck.

So as last try, I downloaded current mysql 5.7.25 version and guess what - it works.

So is it possible that it is some bug in MySQL 8.x branch ? I dont see any other explanation...

Answer 2

This url https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ indicates the GA release date is 2019-02-01 for 8.0.15. Anytime you JUMP into a release with LESS THAN 90 days of GA, you are indicating your willingness to find/report bugs that were missed. You made a wise decision to go back to a 5.7 something with some practical in the field history of success. You may wish to establish 180 days AFTER General Availability to prevent finding/reporting or being exposed to bugs that have just not been corrected yet.