MySQL SELECT last long time on new server

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
cz_mawa
Registered User
Posts: 12
Joined: Tue Jun 30, 2015 6:59 pm

MySQL SELECT last long time on new server

Post by cz_mawa » Mon Apr 08, 2019 3:25 pm

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

User avatar
canonknipser
Registered User
Posts: 1965
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: MySQL SELECT last long time on new server

Post by canonknipser » Mon Apr 08, 2019 4:35 pm

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
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB

cz_mawa
Registered User
Posts: 12
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post by cz_mawa » Mon Apr 08, 2019 8:02 pm

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.

User avatar
canonknipser
Registered User
Posts: 1965
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: MySQL SELECT last long time on new server

Post by canonknipser » Mon Apr 08, 2019 9:02 pm

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.
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB

cz_mawa
Registered User
Posts: 12
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post by cz_mawa » Tue Apr 09, 2019 11:54 am

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

User avatar
Mick
Support Team Member
Support Team Member
Posts: 21078
Joined: Fri Aug 29, 2008 9:49 am
Location: Watching cricket - definitely

Re: MySQL SELECT last long time on new server

Post by Mick » Tue Apr 09, 2019 1:31 pm

Have a look on a site that deals with mysql, they should know.
"The more connected we get the more alone we become" - Kyle Broflovski

User avatar
david63
Registered User
Posts: 16096
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: MySQL SELECT last long time on new server

Post by david63 » Tue Apr 09, 2019 2:35 pm

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.
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

cz_mawa
Registered User
Posts: 12
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post by cz_mawa » Tue Apr 09, 2019 8:04 pm

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.

User avatar
JoshyPHP
Code Contributor
Posts: 998
Joined: Mon Jul 11, 2011 12:28 am

Re: MySQL SELECT last long time on new server

Post by JoshyPHP » 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 wrote the thing that does BBCodes in 3.2.

User avatar
Lumpy Burgertushie
Registered User
Posts: 66268
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: MySQL SELECT last long time on new server

Post by Lumpy Burgertushie » Wed Apr 10, 2019 2:24 am

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
I am going to be out of town and off line for a week . see ya when I get back.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

cz_mawa
Registered User
Posts: 12
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post by cz_mawa » Wed Apr 10, 2019 4:10 pm

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.

Post Reply

Return to “[3.2.x] Support Forum”