Query takes too much time when acl_get_list is used

Discussion forum for Extension Writers regarding Extension Development.
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Query takes too much time when acl_get_list is used

Post by andreask »

Hello,

I use the code bellow to get a list of of user_ids that are administrators and moderators.
But the query takes 4-5 seconds to complete.
It is on a local virtual machine (2 cores 512 MB ram on a physical machine with ssd and 8 GB ram) of course but I think that this would still take too long for a query.
Is there a way to fix this issue? Or is there another way to get the list of admins and mods?

Code: Select all

		
		$administrators = $this->auth->acl_get_list(false, 'a_', false);
		$admin_ary = (!empty($administrators[0]['a_'])) ? $administrators[0]['a_'] : array();

		$moderators = $this->auth->acl_get_list(false, 'm_', false);
		$mod_ary = (!empty($moderators[0]['m_'])) ? $moderators[0]['m_'] : array();
Thanks!
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

The above code is present in at least 2 validated extensions. And those two array should be merged, btw.
Top Five (RMcGirr83) and TPOTM (3Di), It runs on my 3.2 live board since 1 year ago w/no issues and collected until now 1868 unique downloads, no one user told me about that.
I believe your issue lies in your main query, pls post - within the code tags - the full SQL query.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

Hi 3Di

Thanks for your help first of all!
Here is the query I am using during the installation of the extension to populate my table.
It is still "incomplete" (as you know) but the "slowness" started when started using acl_get_list() method.
Same method is also used on another query with the same slowness...
Before that any installations I've done are almost "instantaneous".
And I don't know if this would still be a problem if it was installed on a "physical" server or at least on a machine (virtual or not) with more resources.

Code: Select all

ignore_groups = $phpbb_container->get('auth');

$administrators = $ignore_groups->acl_get_list(false, 'a_', false);
$admin_ary = (!empty($administrators[0]['a_'])) ? $administrators[0]['a_'] : array();

$moderators = $ignore_groups->acl_get_list(false, 'm_', false);
$mod_ary = (!empty($moderators[0]['m_'])) ? $moderators[0]['m_'] : array();

$admin_mod_array = implode(', ', array_unique(array_merge($admin_ary, $mod_ary)));
// AND p.group_id NOT IN (1,4,5,6)

$sql = 'INSERT INTO ' . $this->table_prefix . $this->schema_name . ' (user_id, username)
SELECT user_id, username FROM `' . USERS_TABLE . '` p
WHERE from_unixtime(p.user_lastvisit) < DATE_SUB(NOW(), INTERVAL 30 DAY)
AND (p.user_type <> ' . USER_FOUNDER . ')
AND (p.user_type <> ' . USER_IGNORE . ')
AND (p.user_id > ' . ANONYMOUS . ')
AND (p.user_id NOT IN ('. $admin_mod_array .'))';
$result = $this->sql_query($sql);
Thanks again for your time!
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

Well, you query doesn't adhere to the coding guidelines, first of all (back-ticks, use of not DBAL compliant SQL -> DATE_SUB)

In any case, try this, if it works you should consider to re-think about your code though.

Code: Select all

$ignore_groups = $phpbb_container->get('auth');

$administrators = $ignore_groups->acl_get_list(false, 'a_', false);
$admin_ary = (!empty($administrators[0]['a_'])) ? $administrators[0]['a_'] : array();
$moderators = $ignore_groups->acl_get_list(false, 'm_', false);
$mod_ary = (!empty($moderators[0]['m_'])) ? $moderators[0]['m_'] : array();
$admin_mod_array = implode(', ', array_unique(array_merge($admin_ary, $mod_ary)));

$sql = 'INSERT INTO ' . $this->table_prefix . $this->schema_name . ' (user_id, username)
SELECT user_id, username FROM ' . USERS_TABLE . ' p
WHERE from_unixtime(p.user_lastvisit) < DATE_SUB(NOW(), INTERVAL 30 DAY)
	AND (p.user_type <> ' . USER_FOUNDER . ')
	AND (p.user_type <> ' . USER_IGNORE . ')
	AND (p.user_id > ' . ANONYMOUS . ')
	AND ' . $this->db->sql_in_set('p.user_id', $admin_mod_array, true, true) . ')';
$result = $this->sql_query($sql);
You should inject the $db object, and also the $auth object.
While using INSERT or UPDATE you need to make the use of $db->sql_build_array().
Your table should be stated into a config/parameters.yml file and used by config/services.yml.

Any way, let's see.
Last edited by 3Di on Mon Mar 13, 2017 9:19 pm, edited 1 time in total.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

Or even better

Code: Select all

$ignore_groups = $phpbb_container->get('auth');
$ignore_groups_extra = array(USER_FOUNDER, USER_IGNORE);

$administrators = $ignore_groups->acl_get_list(false, 'a_', false);
$admin_ary = (!empty($administrators[0]['a_'])) ? $administrators[0]['a_'] : array();
$moderators = $ignore_groups->acl_get_list(false, 'm_', false);
$mod_ary = (!empty($moderators[0]['m_'])) ? $moderators[0]['m_'] : array();
$admin_mod_array = implode(', ', array_unique(array_merge($admin_ary, $mod_ary)));

$sql = 'INSERT INTO ' . $this->table_prefix . $this->schema_name . ' (user_id, username)
SELECT user_id, username, user_type FROM ' . USERS_TABLE . ' p
WHERE from_unixtime(p.user_lastvisit) < DATE_SUB(NOW(), INTERVAL 30 DAY)
	AND (p.user_id > ' . ANONYMOUS . ')
	AND ' . $this->db->sql_in_set('p.user_type', $ignore_groups_extra, true, true) . ')
	AND ' . $this->db->sql_in_set('p.user_id', $admin_mod_array, true, true) . ')';
$result = $this->sql_query($sql);
To add, first do the SELECT and after do the INSERT using an array.

Edit: I lurked a bit at your repository and I saw you are well aware of what I am talking about. :roll:
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

WHERE from_unixtime(p.user_lastvisit) < DATE_SUB(NOW(), INTERVAL 30 DAY)
Why don't you use something along the line..
WHERE p.user_lastvisit < ' . (time() - 2592000) . '
The use of GROUP BY and/or ORDER BY could benefit your query as well.

Edit:
andreask wrote:
Mon Mar 13, 2017 8:12 pm
Here is the query I am using during the installation of the extension to populate my table.
Do you mean you are using it on a migration actually?
https://github.com/andreaskou/PHPBB_IUM ... p#L92-L105
https://github.com/andreaskou/PHPBB_IUM ... hp#L60-L81

Not sure if you need a multi-step process here
https://area51.phpbb.com/docs/dev/31x/m ... -processes
or an sql_transaction.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

I did some "debugging" and the problem lies on these two actions...

Code: Select all

$administrators = $ignore_groups->acl_get_list(false, 'a_', false);
$moderators = $ignore_groups->acl_get_list(false, 'm_', false);
Whenever they are not used the queries are done almost immediately.
But when auth is used it takes much more time.

Here are the queries that take the most time.
when auth is not used they are not in the sql report.
Image
Image

Of course I've done everything you have suggested me to do. (at least I think I did :mrgreen: )
If you want to check see the groups branch. It has the latest changes.

Once again thank you for all your support!
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

andreask wrote:
Wed Mar 15, 2017 7:30 pm
I did some "debugging" and the problem lies on these two actions...

Code: Select all

$administrators = $ignore_groups->acl_get_list(false, 'a_', false);
$moderators = $ignore_groups->acl_get_list(false, 'm_', false);
Whenever they are not used the queries are done almost immediately.
But when auth is used it takes much more time.

Here are the queries that take the most time.
when auth is not used they are not in the sql report.
http://image.prntscr.com/image/3b9ac98d ... 64e029.png
http://image.prntscr.com/image/730db402 ... 19ff46.png

Of course I've done everything you have suggested me to do. (at least I think I did :mrgreen: )
If you want to check see the groups branch. It has the latest changes.

Once again thank you for all your support!
Before to try and spend time on this I would like you to see the results of the same on my Boards:

http://prntscr.com/ekgzh5 (localhost - Win7 (a simple netbook) - easyphp devserver 16.1.1 - phpBB 3.1.10)
Before: 5.10429s | After: 5.11129s | Elapsed: 0.00700s

http://prntscr.com/ekh27u (Live phpBB 3.2.0 - PHP > 7)
Before: 4.58740s | After: 4.58769s | Elapsed: 0.00030s

I cloned just your branch to test it when I get some time
git clone https://github.com/andreaskou/PHPBB_IUM.git --branch groups --single-branch

In the meantime you could try and download the validated TPOTM and test it on your dev server, and report.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

I've installed TPOTM (3Di) on my local 3.2 board...
But I've run in some problems. After enabling it and going to my index page I get an error...

Code: Select all

General Error
SQL ERROR [ mysql4 ]

Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.p.post_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [1055]

SQL

SELECT u.username, u.user_id, u.user_colour, u.user_type, p.poster_id, p.post_time, COUNT(p.post_id) AS total_posts FROM phpbb_users u, phpbb_posts p WHERE u.user_id > 1 AND u.user_id = p.poster_id AND (u.user_type <> 3) AND u.user_id NOT IN (2, 53, 56, 81, 54, 363, 534, 836) AND u.user_id NOT IN (258, 262, 263, 264, 286, 390, 393, 396, 399, 400, 403, 407, 408, 409, 649, 777, 810, 852, 1442) AND p.post_time BETWEEN 1488326400 AND 1489787140 GROUP BY u.user_id ORDER BY total_posts DESC, p.post_time DESC, p.post_id DESC LIMIT 1

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 999
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysql.php
LINE: 191
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/mysql_base.php
LINE: 45
CALL: phpbb\db\driver\mysql->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 270
CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/ext/threedi/tpotm/event/listener.php
LINE: 159
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: (not given by php)
LINE: (not given by php)
CALL: threedi\tpotm\event\listener->display_tpotm()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 184
CALL: call_user_func()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 46
CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 60
CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 46
CALL: phpbb\event\dispatcher->dispatch()

FILE: [ROOT]/includes/functions.php
LINE: 4620
CALL: phpbb\event\dispatcher->trigger_event()

FILE: [ROOT]/index.php
LINE: 244
CALL: page_footer()
Though the ext clearly states it's for 3.1.9...
You say it works on 3.2?
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

https://tracker.phpbb.com/browse/PHPBB3-14637 ¿?

Which links to http://stackoverflow.com/questions/2392 ... 2#31058962

And yes, TPOTM works also for Rhea (my live board runs 3.2.0).
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
JoshyPHP
Code Contributor
Posts: 1167
Joined: Mon Jul 11, 2011 12:28 am

Re: Query takes too much time when acl_get_list is used

Post by JoshyPHP »

andreask wrote:
Sun Mar 12, 2017 11:43 pm
But the query takes 4-5 seconds to complete.
That seems way too much for that kind of query. Try OPTIMIZEing the tables used in those queries via phpMyAdmin or whatever SQL interface you usually use. Some indexes may be out of whack because the order tables are joined seems wrong.
I wrote the thing that does BBCodes in 3.2+.
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

To add, today has been released an extension (3.1 and 3.2) written by VSE
https://www.phpbb.com/customise/db/exte ... pair_tool/
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

Finally I was able to do some tests and it seems that my local sql OR vm is the problem.
Because on my live board TPOTM is working fine, while on my local/dev environment I have the same problems.
Unfortunately I cannot install my ext to my live board because of the 3.1.10 bug.
Unless there is a fix which I haven't found for it, and I cannot update to 3.2 for now because a lot of extensions are not updated to work on 3.2 so far :(

Once again, thank you all for your help!
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
pierredu
Registered User
Posts: 1112
Joined: Thu Nov 01, 2012 8:04 am
Location: Paris (France)

Re: Query takes too much time when acl_get_list is used

Post by pierredu »

andreask wrote:
Sun Mar 19, 2017 1:32 pm
Finally I was able to do some tests and it seems that my local sql OR vm is the problem.
Curious minds would like to know, whenever you can pinpoint the problem.
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

Pierre,

I would say it's mysql...
I really doubt that it's anything else.
I've tested the same machine with more resources with no luck.
With another vm (arch 64) I do not have the same problem.
Later I will paste here the conf files of mysql perhaps some of you could think of anything cause it's not my part of "expertise" if I have one! :)
This problem was frustrating as much as educating...
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
Post Reply

Return to “Extension Writers Discussion”