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.
Locked
cz_mawa
Registered User
Posts: 13
Joined: Tue Jun 30, 2015 6:59 pm

MySQL SELECT last long time on new server

Post 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
User avatar
canonknipser
Registered User
Posts: 2096
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 »

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: 13
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post 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.
User avatar
canonknipser
Registered User
Posts: 2096
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 »

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: 13
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post 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...
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26455
Joined: Fri Aug 29, 2008 9:49 am

Re: MySQL SELECT last long time on new server

Post by Mick »

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©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: MySQL SELECT last long time on new server

Post 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.
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
cz_mawa
Registered User
Posts: 13
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post 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.
User avatar
JoshyPHP
Code Contributor
Posts: 1288
Joined: Mon Jul 11, 2011 12:28 am

Re: MySQL SELECT last long time on new server

Post by JoshyPHP »

My money is this: you disabled indexes to import your backup and you didn't re-enable them.
I wrote the library that handles markup in phpBB 3.2+.
User avatar
Lumpy Burgertushie
Registered User
Posts: 69223
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: MySQL SELECT last long time on new server

Post 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
Premium phpBB 3.3 Styles by PlanetStyles.net

I am pleased to announce that I have completed the first item on my bucket list. I have the bucket.
cz_mawa
Registered User
Posts: 13
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post 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.
cz_mawa
Registered User
Posts: 13
Joined: Tue Jun 30, 2015 6:59 pm

Re: MySQL SELECT last long time on new server

Post by cz_mawa »

Hello everyone,

so after almost two years I am bumping this topic.

All I did this morning was that I changed some group permissions in administration panel.

After that, the forum died and it is is doing exactly the same thing as two years ago. Except that now it is still runinng 5.7.25 version of mysql, which worked all ok until two hours ago. So the problem probably was not 8.xx branch of mysql, but something else.

How to kill all group permissions, or reset them to some default settings and try that ? For now i will restore the database from tonight backup and I expect it will work again all ok, but its not long term solution, i need to change these group permissions...
oferlaor
Registered User
Posts: 9
Joined: Sat Dec 04, 2004 6:31 am

Re: MySQL SELECT last long time on new server

Post by oferlaor »

from what I can tell, this is a query that looks for all the m_ authorizations (a subset of permissions), then matches the acl list and group list that supports it for users who are group leads and have this permission. I think this is used for the list of admins per forum and forum group that appears on some forums at the very top.

For large forums, this is heavy because it is a cartasian multiplication between 5 tables. This grows dramatically when you have a large number of users, groups and forums.

The first time that this is called after a cache flush or when you change admins/rights/roles - it has to do this heavy calculation and then cache it.

After caching it's an instant reply, but when it's not cached, it's super heavy.

The query itself is very badly written. using subqueries and optimizations, it's not a very complex thing to write properly. I tried adding indexes but couldn't find something that accelerated it dramatically (only 20%, which is nothing - it doesn't solve the problem).

it's the "role specific" group settings in acl_raw_data, inside of auth.php
User avatar
HiFiKabin
Community Team Member
Community Team Member
Posts: 6669
Joined: Wed May 14, 2014 9:10 am
Location: Swearing at the PC, UK
Name: James
Contact:

Re: MySQL SELECT last long time on new server

Post by HiFiKabin »

After two years many things will have changed, both with your board and your server so bumping an old topic is of no help.

Please fill out the Support Request Template and post it in a new topic to enable us to assist you better.

Closing
Locked

Return to “[3.2.x] Support Forum”