Page 1 of 1

MySQL SELECT last long time on new server

Posted: Mon Apr 08, 2019 3:25 pm
by cz_mawa
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:

Code: Select all

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...

Its up to date 3.2.5 version, no mods installed except custom style. Any idea what is happening ?

Image

Image

Re: MySQL SELECT last long time on new server

Posted: Mon Apr 08, 2019 4:35 pm
by canonknipser
The main difference between the two results is the number of rows examined, as you already mentioned.
Did you check
  • number of columns in each table
  • the table engines on both sides (innodb, myisam ...)
  • the presence or absence of indices on the relevant tables
  • other differences in the table structure (eg. default values, nullable columns etc.)
  • mysql parameter and setting differences

Re: MySQL SELECT last long time on new server

Posted: Mon Apr 08, 2019 8:02 pm
by cz_mawa
Hi,

so I should check tables phpbb_acl_groups, phpbb_user_group, phpbb_groups, phpbb_acl_roles_data and phpbb_acl_options, is that correct ?

Also, what exactly should that part of code do ? Maybe if it is nothing important I could comment it for now and move the phpBB to that new server, and solve this problem later.

Re: MySQL SELECT last long time on new server

Posted: Mon Apr 08, 2019 9:02 pm
by canonknipser
cz_mawa wrote:
Mon Apr 08, 2019 8:02 pm
is that correct
Yes, that's correct

cz_mawa wrote:
Mon Apr 08, 2019 8:02 pm
Also, what exactly should that part of code do ?
As far as I read that fragment, it is part of the permission checks for moderator permissions.


Wait - did you empty the cache directory of phpBB on the new server after your move? If not - do that first.

Re: MySQL SELECT last long time on new server

Posted: Tue Apr 09, 2019 11:54 am
by cz_mawa
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. Or better said I tested it now on localhost and it works. Will test it later on the server.

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

Re: MySQL SELECT last long time on new server

Posted: Tue Apr 09, 2019 1:31 pm
by Mick
Have a look on a site that deals with mysql, they should know.

Re: MySQL SELECT last long time on new server

Posted: Tue Apr 09, 2019 2:35 pm
by david63
I doubt that phpBB and certainly not phpBB 3.2.5 will have been tested with mySql 8.0.15 as 3.2.5 was released prior to mySql 8.0.15.

It appears that there is a performance issue with mySql 8.x with binary logging which need to be turned off.

Re: MySQL SELECT last long time on new server

Posted: Tue Apr 09, 2019 8:04 pm
by cz_mawa
Well if 8.x branch of MySQL is not compatible with phpBB, it should be stated somewhere. Right now on the phpbb.com webpage is as required MySQL version written

Code: Select all

MySQL 3.23 or above (MySQLi supported)
. Not a single word about no compatibility with 8.x branch.

Re: MySQL SELECT last long time on new server

Posted: Wed Apr 10, 2019 1:55 am
by JoshyPHP
My money is this: you disabled indexes to import your backup and you didn't re-enable them.

Re: MySQL SELECT last long time on new server

Posted: Wed Apr 10, 2019 2:24 am
by Lumpy Burgertushie
mysql 8 is pretty new. the current version of phpbb came out way before that version of mysql came out. not the mention since the last time the documentation was updated.

robert

Re: MySQL SELECT last long time on new server

Posted: Wed Apr 10, 2019 4:10 pm
by cz_mawa
JoshyPHP wrote:
Wed Apr 10, 2019 1:55 am
My money is this: you disabled indexes to import your backup and you didn't re-enable them.
I performed standard mysql dump from old server using mysql workbench, and used same version of mysql workbench to import it to new server. No special options.