Usermanager takes ages to load, and eventualy time out.

JohannM · March 21, 2015, 03:23:00 PM

Hi there

I have a problem. At the moment I am hosting about 1.6 million photos on my site, I am the only poster on the site, have 2761 users.

If I click on Users, it takes ages to loead, and then eventualy time out. Any idea why ?

Thanx in advance

gmc · March 23, 2015, 01:41:19 AM

At 1.6 million photos, I assume you have a decent ISP (or self hosted...)
If you can get the page to load with debug on (for admin only is fine) - the SQL query trace could show us where time is being spent...

If not, your ISP should be able to see if you are exceeding CPU time, memory, etc... And possibly raise the limits at least temporarily to see what is happening...

JohannM · March 23, 2015, 09:41:34 AM

Hi gmc (greg)

Sorry, I forgot to add my website: http://www.youth-sport-fotos.com

The script is still running, but I will copy and paste my sql query trace here.

I must say, I have a good server, running linux enterprice, 8 gb ram, 2 TB drives, Dual core i3 Pentium machine. I have controll over my limits and it was set to high. Even max execusion times was mut to 7200 etc.

Since I am the only one posting the pics, could it be that my user id as admin is causing this, e.g. counting all the images and size of my uploads ?

Pasting my sql here:

Code Select

JohannM · March 23, 2015, 10:06:54 AM

After rebooting the server again, I managed to get the user page at least open again.

Here are the sql results and notices:

* Too big to post, so I will attch them as files.

Joe Carver · March 23, 2015, 12:48:50 PM

Try turning off / disabling the Who's Online plugin.

(Can't look any closer, am on a mobile now)

gmc · March 23, 2015, 01:46:51 PM

That is where I was going... wanted the debug data to show where we are spending time...

Extracted from attachments - the top 2 (which huge difference between them...) - pretty much everything else is under 1ms..
[24] => SELECT user_id AS user_id, user_name AS user_name, user_email AS user_email, UNIX_TIMESTAMP(user_regdate) AS user_regdate, UNIX_TIMESTAMP(user_lastvisit) AS user_lastvisit, user_active AS user_active, COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota FROM `ysf_YSF2015`.cpg_users AS u INNER JOIN `ysf_YSF2015`.cpg_usergroups AS g ON u.user_group = g.group_id LEFT JOIN `ysf_YSF2015`.cpg_pictures AS p ON p.owner_id = u.user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25; [bridge/udb_base.inc.php:230] (448967.8 ms) <---- **OVER 7 MINUTES**

[26] => SELECT SUM(total_filesize) FROM cpg_pictures LIMIT 1 [usermgr.php:433] (813.35 ms)

One question.. Once usermanager displays the first set - does viewing the next set of users happen any faster??
(I'm guessing not as I look at the query... Seems we have an 'ORDER BY' on a non-indexed field that is going to need to generate the entire result set before applying the LIMIT. (Otherwise the initial queries would be much faster - as you stated you are the only poster - and I assume you don't have a recent registration date for your id...)

First thing I would try is adding an index... user_regdate is a low update field (only at registration) so index maintenance overhead is negligible - and should help MYSQL determine the correct result set.
**NOTE the SORT ORDER appears to be selectable - this will only work to help your current sort order... Likely why there isn't a normal index here - hard to index every possible combination - and some would be high maintenance like user_lastvisit**

Code Select

ALTER TABLE `databasename`.`cpg_users` ADD INDEX `user_regdate` (`user_regdate`) COMMENT 'non-std index to improve usermanager performance';

(Change databasename to your database and cpg_ to your prefix as appropriate)

My tables aren't big enough to validate a performance improvement - but it can't hurt. My expectation is that this will improve performance UNTIL you try to show your admin ID in the result set...

As an alternative - could attempt to exclude the 'Administrators' group from the display - but this code is a common function (get_users) that needs to work with all bridges - and may be called at other times... I'm sure I could come up with a 'band-aid' for your case - but not trivial to get something viable for a distribution... (maybe combination of a config var to show/hide admin group - making sure not bridged - and adding appropriate WHERE (using an ELSE on the 'if ($options['search']) {' statement...)
Not something I can dig into today...

Perhaps others have more/better ideas...
Greg

JohannM · March 23, 2015, 03:35:34 PM

Hi Greg

Thanx for getting back to me so quick.

Once usermanager displays the first set, yes, viewing the next set of users going a tiny bit faster, but not THAT faster

I altered the TABLE as instructed, but it still takes a lot of time to load.

Is it neccesary to include the admin/admin id or the number of files and the space used or quota in this ?
Is there a way to exclude the admin, or at least the number of files and the space used or quota in the query for usermanager ?

Regards

Johann

JohannM · March 23, 2015, 05:12:38 PM

I recon the problem is with this quiry in udb_base.inc.php

// Build SQL table, should work with all bridges
$sql = "SELECT {$f['user_id']} AS user_id, {$f['username']} AS user_name, {$f['email']} AS user_email, {$f['regdate']} AS user_regdate, {$f['lastvisit']} AS user_lastvisit, {$f['active']} AS user_active, "
. "COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota "
. "FROM {$this->usertable} AS u "
. "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
. "LEFT JOIN `{$C['dbname']}`.{$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} "
. $options['search']
. "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " "
. "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";

$result = cpg_db_query($sql, $this->link_id);

I recon this lines

. "COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota "
. "FROM {$this->usertable} AS u "

is causing the time to be so long.

gmc · March 23, 2015, 05:40:25 PM

OK... I'd like to see the debug for just that ONE statement (was index 24) to see if it made any change...

To exclude the 'Administrators' group from the display (and from that SQL!) - you can try this...
**Note that this is just a band-aid - and I don't expect this code will be added to CPG - meaning it is an update you will need to maintain for any upgrade... (Perhaps we can do something with a config variable to include/exclude - but as written this leaves you unable to maintain an admin user...)**
It will not have any effect in a bridged gallery (as we might be using the bridged applications groups - so bypass to avoid SQL failure.)
It will not have any effect if you don't have an 'Administrators' named group (as you are allowed to change these names... The update needed should be obvious.)
A direct search for the admin user will still work (takes the 'if' path below.)

I coded this and did a quick test at the 1.5.28 level you seem to be running... You should upgrade to 1.5.34.

In 'bridge/udb_base.inc.php'
Find: (line 215 at 1.5.28 - just before the query in your last post...)

Code Select


        // Build WHERE clause, if this is a username search
        if ($options['search']) {
            $options['search'] = 'WHERE u.'.$f['username'].' LIKE "%'.$options['search'].'%" ';
        }

and replace with:

Code Select


        // Build WHERE clause, if this is a username search
        if ($options['search']) {
            $options['search'] = 'WHERE u.'.$f['username'].' LIKE "%'.$options['search'].'%" ';
        } elseif (!$CONFIG['bridge_enable']) {
          //*GMC *TESTFIX* to eliminate administrator group from usermanager for large gallery with mostly admin loaded pictures
          //*GMC only used for non-bridged gallery - as bridged gallery may use CPG or Bridged groups...
          $options['search'] = 'WHERE group_name != "Administrators" ';
        }

JohannM · March 23, 2015, 06:39:10 PM

Greg

Thanx alot.

I made the changes as you suggested.

Here is the result on the sql quiry that takes a Lot of loading time:

[24] => SELECT user_id AS user_id, user_name AS user_name, user_email AS user_email, UNIX_TIMESTAMP(user_regdate) AS user_regdate, UNIX_TIMESTAMP(user_lastvisit) AS user_lastvisit, user_active AS user_active, COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota FROM `ysf_YSF2015`.cpg_users AS u INNER JOIN `ysf_YSF2015`.cpg_usergroups AS g ON u.user_group = g.group_id LEFT JOIN `ysf_YSF2015`.cpg_pictures AS p ON p.owner_id = u.user_id WHERE group_name != "Administrators" GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25; [bridge/udb_base.inc.php:234] (453262.16 ms)

Thanx in advance

Johann

JohannM · March 23, 2015, 06:43:27 PM

Greg

As I said, im not interested in the "pic count" or "disk usage" ... is there a way to get the query NOT to ask that ?

Regards

Johann

gmc · March 23, 2015, 06:55:58 PM

Find:

Code Select


        // Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} AS user_id, {$f['username']} AS user_name, {$f['email']} AS user_email, {$f['regdate']} AS user_regdate, {$f['lastvisit']} AS user_lastvisit, {$f['active']} AS user_active, "
               . "COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota "
               . "FROM {$this->usertable} AS u "
               . "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
               . "LEFT JOIN `{$C['dbname']}`.{$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} "
               . $options['search']
               . "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " "
               . "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";

and replace with:

Code Select


        // Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} AS user_id, {$f['username']} AS user_name, {$f['email']} AS user_email, {$f['regdate']} AS user_regdate, {$f['lastvisit']} AS user_lastvisit, {$f['active']} AS user_active, "
               . "'0' AS pic_count, '0' AS disk_usage, group_name, group_quota "
               . "FROM {$this->usertable} AS u "
               . "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
               . "LEFT JOIN `{$C['dbname']}`.{$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} "
               . $options['search']
               . "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " "
               . "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";

I forced the return of 0 for those fields - so the fields are still set in the result set... It will just show 0 file/0 space used...

JohannM · March 23, 2015, 07:19:59 PM

Hi Greg

I appreciate your efford.

Still have to wait for a long time to load ...

Herewith the sql results:

[24] => SELECT user_id AS user_id, user_name AS user_name, user_email AS user_email, UNIX_TIMESTAMP(user_regdate) AS user_regdate, UNIX_TIMESTAMP(user_lastvisit) AS user_lastvisit, user_active AS user_active, '0' AS pic_count, '0' AS disk_usage, group_name, group_quota FROM `ysf_YSF2015`.cpg_users AS u INNER JOIN `ysf_YSF2015`.cpg_usergroups AS g ON u.user_group = g.group_id LEFT JOIN `ysf_YSF2015`.cpg_pictures AS p ON p.owner_id = u.user_id WHERE group_name != "Administrators" GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25; [bridge/udb_base.inc.php:234] (447709.97 ms)

[26] => SELECT SUM(total_filesize) FROM cpg_pictures LIMIT 1 [usermgr.php:433] (487.95 ms)

*****************
Is there a way to delete this part and not to let the query ask the results, or would it influence ather pages ?

. "COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota "
. "FROM {$this->usertable} AS u "
. "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
. "LEFT JOIN `{$C['dbname']}`.{$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} "

or

. "'0' AS pic_count, '0' AS disk_usage, group_name, group_quota "
. "FROM {$this->usertable} AS u "
. "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
. "LEFT JOIN `{$C['dbname']}`.{$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} "

When I delete these parts, I get an database error.

My point is just that it seems that it does count the pics/pic count and disk space used by users or groups for instance, thats why it takes so long, but I dont feel it is needed.

Regards

Johann

JohannM · March 23, 2015, 08:14:45 PM

For example ...

(Not sure if my coding is correct, not sure where the " and . have to come in ...

// Build SQL table, should work with all bridges
$sql = "SELECT {$f['user_id']} AS user_id, {$f['username']} AS user_name, {$f['email']} AS user_email, {$f['regdate']} AS user_regdate, {$f['lastvisit']} AS user_lastvisit, {$f['active']} AS user_active, "
. " . $options['search'] . "
. "GROUP BY user_id "
. "ORDER BY " . $sort_codes[$options['sort']] . " " . "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";

gmc · March 23, 2015, 09:41:00 PM

Oops... when I changed the results to constants - I should have removed the join as well - joining with 1.6M rows is still being done... even though no longer retrieving any columns from them.

Replace:

Code Select


        // Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} AS user_id, {$f['username']} AS user_name, {$f['email']} AS user_email, {$f['regdate']} AS user_regdate, {$f['lastvisit']} AS user_lastvisit, {$f['active']} AS user_active, "
               . "COUNT(pid) AS pic_count, ROUND(SUM(total_filesize)/1024) AS disk_usage, group_name, group_quota "
               . "FROM {$this->usertable} AS u "
               . "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
               . "LEFT JOIN `{$C['dbname']}`.{$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} "
               . $options['search']
               . "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " "
               . "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";

With:

Code Select


        // Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} AS user_id, {$f['username']} AS user_name, {$f['email']} AS user_email, {$f['regdate']} AS user_regdate, {$f['lastvisit']} AS user_lastvisit, {$f['active']} AS user_active, "
               . "'0' AS pic_count, '0' AS disk_usage, group_name, group_quota "
               . "FROM {$this->usertable} AS u "
               . "INNER JOIN `{$C['dbname']}`.{$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id "
               . $options['search']
               . "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " "
               . "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";

You do want to leave the '0' constants - as the code will reference those array keys later...

See what that does for you...
Greg

JohannM · March 23, 2015, 09:56:09 PM

Wow Greg

Super fast now !!!

[24] => SELECT user_id AS user_id, user_name AS user_name, user_email AS user_email, UNIX_TIMESTAMP(user_regdate) AS user_regdate, UNIX_TIMESTAMP(user_lastvisit) AS user_lastvisit, user_active AS user_active, '0' AS pic_count, '0' AS disk_usage, group_name, group_quota FROM `ysf_YSF2015`.cpg_users AS u INNER JOIN `ysf_YSF2015`.cpg_usergroups AS g ON u.user_group = g.group_id WHERE group_name != "Administrators" GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25; [bridge/udb_base.inc.php:233] (0.1 ms)

[26] => SELECT SUM(total_filesize) FROM cpg_pictures LIMIT 1 [usermgr.php:433] (486.68 ms)

Your a star !!!

Btw, you know what happend to Stramm ? I need cpg_shop, the full version,but have been trying to get hold of him for more than 9 months now.

Thanx again for all your help.

Best Regards

Johann

phill104 · March 23, 2015, 10:06:30 PM

I'll drop Strmm an email but not heard from him in a long time sadly.

JohannM · March 23, 2015, 10:13:29 PM

Thanx Phill

gmc · March 23, 2015, 10:18:47 PM

Johann,
Glad it worked... and I actually like that solution better than eliminating the administrator group from the display...

@dev team - seems a way to bypass the pic count and size may be appropriate in usermgr - whether a config parm - or other means... (cPanel for example by default doesn't show me my disk usage - but gives me the option to click and wait if I want to...)

A config var would be easy to code - building the SQL based on the selection ('0's and no join with pictures if off - and the current code if on... Small galleries can handle the join - but that was a 7+ minute query on Johann's server...

Thoughts/ideas/comments??

phill104 · March 23, 2015, 10:25:21 PM

Sounds like a good idea Greg. Could possibly be added as a hidden config option for 1.5.x then included in 1.6. This is not the first report of speed issues here.

coppermine-gallery.com/forum

News:

Usermanager takes ages to load, and eventualy time out.

JohannM

gmc

JohannM

JohannM

Joe Carver

gmc

JohannM

JohannM

gmc

JohannM

JohannM

gmc

JohannM

JohannM

gmc

JohannM

phill104

JohannM

gmc

phill104