Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1] 2   Go Down

Author Topic: Usermanager takes ages to load, and eventualy time out.  (Read 25870 times)

0 Members and 1 Guest are viewing this topic.

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Usermanager takes ages to load, and eventualy time out.
« on: 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
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #1 on: 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...
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #2 on: 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]
USER:
------------------
Array
(
    [ID] => 3d93e364e02e05f924aeda7d7dd9e735
    [liv_a] => Array
        (
            [0] => 335
            [1] => 334
            [2] => 333
            [3] => 337
            [4] => 338
        )

    [liv] => Array
        (
            [0] => 784808
            [1] => 786542
            [2] => 786834
        )

    [am] => 1
)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => Admin
    [groups] => Array
        (
            [0] => 1
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 0
    [can_send_ecards] => 1
    [can_post_comments] => 0
    [can_upload_pictures] => 0
    [can_create_albums] => 0
    [has_admin_access] => 1
    [access_level] => 3
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [can_create_public_albums] => 0
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [allowed_albums] => Array
        (
        )

)

==========================
Queries:
------------------
Array
(
    [0] => SELECT name, value FROM cpg_config [include/init.inc.php:179] (0.33 ms)
    [1] => SELECT * FROM cpg_plugins ORDER BY priority [include/plugin_api.inc.php:52] (0.1 ms)
    [2] => SELECT user_id, time FROM `ysf_YSF2015`.cpg_sessions WHERE session_id = '8602437911c4df3d7976879a47ca1b9c' [bridge/coppermine.inc.php:279] (16.2 ms)
    [3] => SELECT user_id, user_password FROM `ysf_YSF2015`.cpg_users WHERE user_id = 1 [bridge/coppermine.inc.php:291] (0.26 ms)
    [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `ysf_YSF2015`.cpg_users AS u LEFT JOIN `ysf_YSF2015`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:70] (0.22 ms)
    [5] => SELECT user_group_list FROM `ysf_YSF2015`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/coppermine.inc.php:217] (0.17 ms)
    [6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0.24 ms)
    [7] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.15 ms)
    [8] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.12 ms)
    [9] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:330] (0.16 ms)
    [10] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:388] (6.83 ms)
    [11] => DELETE FROM cpg_mod_online WHERE last_action < NOW() - INTERVAL 10 MINUTE [plugins/onlinestats/codebase.php:122] (0.28 ms)
    [12] => REPLACE INTO cpg_mod_online (user_id, user_name, user_ip, last_action) VALUES ('1', 'Admin', '41.182.15.90', NOW()) [plugins/onlinestats/codebase.php:125] (0.15 ms)
    [13] => SELECT name, value FROM cpg_cpgshop_config [plugins/cpgshop/libs/cpgDB.class.php:24] (0.23 ms)
    [14] => SELECT * FROM cpg_copperad_config [plugins/copper_ad/include/init2.inc.php:20] (0.08 ms)
    [15] => SELECT home,login,my_gallery,upload_pic,album_list,lastup,lastcom,topn,toprated,favpics,search,my_profile FROM cpg_final_extract_config WHERE Group_Id=1 [plugins/final_extract/include/init2.inc.php:20] (0.31 ms)
    [16] => DELETE FROM cpg_banned WHERE expiry < '2015-03-23 08:39:22' [include/init.inc.php:444] (0.22 ms)
    [17] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '41.182.15.90' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.21 ms)
    [18] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2421] (690.04 ms)
    [19] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2310] (0.44 ms)
    [20] => SELECT COUNT(*) FROM cpg_plugin_newsletter_queue WHERE attempts <= 3 [plugins/newsletter/init.inc.php:195] (0.12 ms)
    [21] => SELECT COUNT(*) FROM cpg_plugin_newsletter_queue WHERE attempts > 2 [plugins/newsletter/init.inc.php:204] (0.05 ms)
    [22] => SELECT count(*) FROM `ysf_YSF2015`.cpg_users WHERE 1 [bridge/udb_base.inc.php:177] (0.28 ms)
    [23] => SELECT COUNT(*) FROM cpg_mod_online [plugins/onlinestats/codebase.php:151] (0.08 ms)
    [24] => SELECT COUNT(*) FROM cpg_mod_online WHERE user_id <> 0 [plugins/onlinestats/codebase.php:154] (0.21 ms)
    [25] => SELECT user_id AS user_id, user_name AS user_name FROM `ysf_YSF2015`.cpg_users ORDER BY user_id DESC LIMIT 1 [plugins/onlinestats/codebase.php:157] (71.82 ms)
    [26] => SELECT user_id, user_name FROM cpg_mod_online WHERE user_id <> 0 [plugins/onlinestats/codebase.php:160] (0.2 ms)
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
)

==========================
COOKIE :
------------------
Array
(
    [__atuvc] => 4|8,95|9,0|10,643|11,159|12
    [cpg15_data] => YTo0OntzOjI6IklEIjtzOjMyOiIzZDkzZTM2NGUwMmUwNWY5MjRhZWRhN2Q3ZGQ5ZTczNSI7czo1OiJsaXZfYSI7YTo1OntpOjA7aTozMzU7aToxO2k6MzM0O2k6MjtpOjMzMztpOjM7aTozMzc7aTo0O2k6MzM4O31zOjM6ImxpdiI7YTozOntpOjA7czo2OiI3ODQ4MDgiO2k6MTtzOjY6Ijc4NjU0MiI7aToyO3M6NjoiNzg2ODM0Ijt9czoyOiJhbSI7aToxO30=
    [cpg15_fav] => YTowOnt9
    [0c5865d75bd2fe954e3abf0823f79e67] => ade344e6d2d4e2d891719d3c631f6e2d
    [Horde] => b5ociqe7p1v4v8et2ikhte32m4
    [default_horde_view] => auto
    [__atuvs] => 550fca5658b1366a005
)

==========================
VERSION INFO :
------------------
PHP version        5.4.36 OK   
MySQL version      5.6.23 OK   
Coppermine version 1.5.28 stable

==========================
Module: GD
------------------
Exact version                    2.1.0                     
GD Version                       bundled (2.1.0 compatible)
FreeType Support                 1                         
FreeType Linkage                 with freetype             
T1Lib Support                                             
GIF Read Support                 1                         
GIF Create Support               1                         
JPEG Support                     1                         
PNG Support                      1                         
WBMP Support                     1                         
XPM Support                      1                         
XBM Support                      1                         
JIS-mapped Japanese Font Support                           

==========================
Key config settings
------------------
site_url             http://www.youth-sport-fotos.com/     
charset              utf-8                                 
allow_private_albums 0                                     
cookie_name          cpg15                                 
cookie_path          /                                     
impath               /usr/bin/                             
lang                 english                               
main_page_layout     breadcrumb/catlist/alblist/onlinestats
silly_safe_mode      0                                     
smtp_host                                                 
theme                water_drop                           
thumb_method         gd2                                   

==========================
Plugins
------------------
Name           Favorite Button                                                                                                               
Actions                                                                                                                                       
Filters                                                                                                                                       
--------------
Name           Who is online?                                                                                                                 
Actions        plugin_install, page_start, plugin_uninstall, plugin_cleanup, plugin_configure                                                 
Filters        plugin_block                                                                                                                   
--------------
Name           cpgShop                                                                                                                       
Actions        plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup, page_start, profile_display_form, profile_submit_form     
Filters        register_form_create, register_form_submit, admin_menu, gallery_header, gallery_footer, file_data                             
--------------
Name           Link target                                                                                                                   
Actions        page_start                                                                                                                     
Filters                                                                                                                                       
--------------
Name           News Letter                                                                                                                   
Actions        page_start, plugin_install, plugin_uninstall, plugin_cleanup, plugin_configure, gallery_footer                                 
Filters        admin_menu, register_form_create, register_form_submit, register_user_activation, profile_add_data, usermgr_form_list, sub_menu
--------------
Name           Copper ad                                                                                                                     
Actions        plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup, page_start                                                 
Filters        page_html                                                                                                                     
--------------
Name           Final Extract for cpg1.5.x                                                                                                     
Actions        plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup, page_start                                                 
Filters        page_html                                                                                                                     
--------------
Name           Add Lightbox Button                                                                                                           
Actions        page_start, theme_thumbnails_wrapper_start, theme_thumbnails_wrapper_end                                                       
Filters        thumb_caption                                                                                                                 
--------------

==========================
Server restrictions
------------------
safe_mode                                   
safe_mode_exec_dir                           
safe_mode_gid                               
safe_mode_include_dir                       
sql.safe_mode            0                   
disable_functions                           
file_uploads             1                   
include_path             .:/usr/local/lib/php
open_basedir                                 
allow_url_fopen          1                   
max_execution_time       30                 
max_input_time           -1                 
upload_max_filesize      2M                 
post_max_size            8M                 
memory_limit             128M               
suhosin.post.max_vars                       
suhosin.request.max_vars                     


==========================
Page (performance)
------------------
Parameter        Current   Peak     
Memory usage     4.80 MiB  4.93 MiB
Page generation  1001.6 ms 1001.6 ms
Page query time  789.51 ms 789.51 ms
Page query count 27        27       


==========================

« Last Edit: March 24, 2015, 12:15:17 am by Jeff Bailey »
Logged

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #3 on: 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.

Logged

Joe Carver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1545
  • aka 'i-imagine'
    • Home Page
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #4 on: 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

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #5 on: 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
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #6 on: 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
Logged

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #7 on: 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.

Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #8 on: 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" ';
        }
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #9 on: 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
Logged

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #10 on: 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
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #11 on: 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...
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #12 on: 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



Logged

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #13 on: 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']};";

Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #14 on: 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
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #15 on: 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
Logged

phill104

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4885
    • Windsurf.me
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #16 on: March 23, 2015, 10:06:30 pm »

I'll drop Strmm an email but not heard from him in a long time sadly.
Logged
It is a mistake to think you can solve any major problems just with potatoes.

JohannM

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 252
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #17 on: March 23, 2015, 10:13:29 pm »

Thanx Phill
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #18 on: 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??
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

phill104

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4885
    • Windsurf.me
Re: Usermanager takes ages to load, and eventualy time out.
« Reply #19 on: 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.
Logged
It is a mistake to think you can solve any major problems just with potatoes.
Pages: [1] 2   Go Up
 

Page created in 0.033 seconds with 20 queries.