Copying to tmp table with usermgr.php

[xplicit]

Allthough I more like to answer questions, I now have one myself I can't get my finger on.

While using the usermgr.php file the server gets sort of timed out.

I've used several versions of usermgr.php (latest 1.9 comming from cpg 1.3.5) but they all give the same error on 1 server (others are doing fine). php version 4.3.10 Zend Optimizer v2.5.7,

I tried it on several servers and my own testing server, localserver and other hosting server are responding OK (with a copy of the complete sql table) it's just my main server which suddenly started to give errors, this happend when it reached I guess 4500 users. Here is what I tried to solve the problem:

* Check table
* Analyze table
* Repair table
* Optimize table

Only thing that I couldn't do was to flush the table (I was not allowed to)

I also searched the board and found a few familiar things but I think they are solved in latest versions like

http://forum.coppermine-gallery.net/index.php?topic=7379.0
http://forum.coppermine-gallery.net/index.php?topic=5738.0

Also used older versions usermgr.php but all the same result

Further everything was OK.

I also used a page time out function to see if the script timed out and well it did. So I focussed on the processes

When inspecting the processes I noticed that the query on line 99 was giving the problem since it took a very long time to copy I will attach the screenshot

(https://coppermine-gallery.com/forum/proxy.php?request=http%3A%2F%2Fpicserver.student.utwente.nl%2Fi%2FOFN8610E4600&hash=b8f1dca04d0d04493990fea43432c2d678a34955)

Don't let the cpg131 indication mislead you since it's only there cause I hardcoded (stupid) this indication in several modification files and added files so thats no problem it's not 135

The number of users are high so I try to changed the script a little bit to reduce the sql execution time

Code Select Expand


//  $result = db_query("SELECT count(*) FROM {$CONFIG['TABLE_USERS']} WHERE 1");
  //  $nbEnr = mysql_fetch_array($result);
  //  $user_count = $nbEnr[0];
  //  mysql_free_result($result);

   // if (!$user_count) cpg_die(CRITICAL_ERROR, $lang_usermgr_php['err_no_users'], __FILE__, __LINE__);

//   $user_per_page = 25;
//   $page = isset($HTTP_GET_VARS['page']) ? (int)$HTTP_GET_VARS['page'] : 1;
  //  $lower_limit = ($page-1) * $user_per_page;
  //  $total_pages = ceil($user_count / $user_per_page);

    $sql = "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, ".
           "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
           "FROM {$CONFIG['TABLE_USERS']} AS u ".
           "INNER JOIN {$CONFIG['TABLE_USERGROUPS']} AS g ON user_group = group_id ".
           "LEFT JOIN {$CONFIG['TABLE_PICTURES']} AS p ON p.owner_id = u.user_id ".
           "GROUP BY user_id ".
           "ORDER BY " . $sort_codes[$sort] . " ".
           "LIMIT 1, 1;";


But even then it's timing out

Seems there is a server setup thing not correct, (everything else is working fine)  database users is about 512KB

I see some differences when using the phpinfo file between this server and the others but which thing do I have to look at or suggest to adjust by my hosting company?

Anybody a suggestion otherwise perhaps to get this fixed?

[xplicit]

Ok I found a solution for my problem which is removing a big part of the sql

Code Select Expand

$sql = "SELECT user_id, user_name, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active, ".
           "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
           "FROM {$CONFIG['TABLE_USERS']} AS u ".
           "INNER JOIN {$CONFIG['TABLE_USERGROUPS']} AS g ON user_group = group_id ".
           "LEFT JOIN {$CONFIG['TABLE_PICTURES']} AS p ON p.owner_id = u.user_id ".
           "GROUP BY user_id ".
           "ORDER BY " . $sort_codes[$sort] . " ".
           "LIMIT $lower_limit, $user_per_page;";`

replaced by

Code Select Expand

$sql = "SELECT user_id, user_name, user_website, user_gender FROM {$CONFIG['TABLE_USERS']} ORDER BY $sort_codes[$sort] LIMIT $lower_limit, $user_per_page";

I don't mind to have removed a few statements codes so I just removed all the parts I don't use but could it be that for instance unsupported script codes could have caused it? And if so which are most likely to do so?

I really have no clue otherwise?

---
Apart from this is the $user_per_page;"; a mistype ? with the ;"; in version 1.3.5?

[Stramm]

the only idea I have is that the tmp_table_size is to small... therefore mysql has to create a tmp file on disk and swap instead of using mem for tmp.

Check that variable, default is 32mb... and it is sufficient for much more than 10k users (at least for me on a FreeBSD box)

edit:
to show created tmp files, tables: SHOW STATUS LIKE 'Created_tmp_%';
to show variables: SHOW VARIABLES;

[xplicit]

Thnx!

So there is the tmp where the process is copying to

I did as you said and this was the output:

Created_tmp_disk_tables 38778
Created_tmp_tables 110810
Created_tmp_files 1120

with the statement SHOW STATUS LIKE 'open%tables%' I got

Open_tables 64
Opened_tables 810045

SHOW STATUS LIKE '%key_read%'

Key_read_requests 84597171
Key_reads 2100272

so a aprox ratio of 40:1 looks like the key buffer size should be higher...

unfortunately I haven't permission to do the  SHOW VARIABLES LIKE '%query_cache%' or SHOW STATUS LIKE '%qcache%' of SHOW STATUS LIKE '%tmp_table_size%';

[andrez1]

While using the usermgr.php file the server gets sort of timed out.

I've used several versions of usermgr.php (latest 1.9 comming from cpg 1.3.5) but they all give the same error on 1 server (others are doing fine). php version 4.3.10 Zend Optimizer v2.5.7,

I tried it on several servers and my own testing server, localserver and other hosting server are responding OK (with a copy of the complete sql table) it's just my main server which suddenly started to give errors, this happend when it reached I guess 4500 users. Here is what I tried to solve the problem:

I have the same issue on a cpg 1.3.5, (XP, sp2, amd64 athlon) and the problems come with a little over 100 users.  turning on debug gives

                 "Page generated in 32.902 seconds - 9 queries in 32.85 seconds - Album set :"

-as last line. Usermgr.php is unaltered version 1.9. from cpg135. So I belive the problem could be related to something else than '4500 users'.


Your http://timecatchers.no-ip.org respond on a

                 telnet timecatchers.no-ip.org 80

(+ ctrl+c + enter)

With:

          [...]
          Apache/2.0.54 (Win32) mod_ssl/2.0.54 OpenSSL/0.9.8 PHP/5.0.5 mod_autoindex_color

no problem with that one?



A server running cpg1.3.5 (XP, sp2, AMD sempron) i look after have the  issue with usermgr.php slow respond:

           Apache/2.0.54 (Win32) mod_ssl/2.0.54 OpenSSL/0.9.8 PHP/5.0.4

But:

Check that variable, default is 32mb... and it is sufficient for much more than 10k users (at least for me on a FreeBSD box)

- are not likely to have '(Win32)' popping up.

I use the http://www.apachefriends.org/index-en.html , version '1.4.15 (lite)'.  (and will test a new version) anything of  interest? Phpinfo? php.ini? my.cnf?

(looking for patterns..)


Mvh Andrez1

[xplicit]

I use the http://www.apachefriends.org/index-en.html , version '1.4.15 (lite)'.

Also using Xampp here on the timecatchers url

But production sites are running on "real servers"

I haven't tried it on my back up server (a  Compaq Proliant ML330e G2 (with 4 gig RAM))  but I guess this will tried in a few weeks when it's going to replace my old Compaq server. But the only spare time I have to do this is in the christmass week 

[andrez1]

Also using Xampp here on the timecatchers url

But production sites are running on "real servers"

If you can dump the database from your "real server"  into a "look-alike  server" running vanilla cpg135 with no pictures; i think you will be abel to reproduce the problem even on your sofar unproblematic "look-alike server"   

I'v done som tests:


debug turned on. running on copy, fresh cpg135 (with imported data from production-server),
fresh xampplite 1.5.0 (for windows) with default my.cnf



   # Example MySQL config file for small systems.
   #
   # This is for a system with little memory (<= 64M) where MySQL is only used
   # from time to time and it's important that the mysqld daemon
   # doesn't use much resources.


[...]

   key_buffer = 16K
   max_allowed_packet = 1M
   table_cache = 4
   sort_buffer_size = 64K
   read_buffer_size = 256K
   read_rnd_buffer_size = 256K
   net_buffer_length = 2K
   thread_stack = 64K
   
[...]

   [isamchk]
   key_buffer = 8M
   sort_buffer_size = 8M

   [myisamchk]
   key_buffer = 8M
   sort_buffer_size = 8M

   
   

139 users, less than half active, only admin has pictures. starting usermgr.php with debug enabled:

51000 pic in 30.sec
51000 pic in (optimized all tables) 29.sec

reduce from 51000 pic to 8967

8967 pict in 14.sec
8967 pict in (optimized all tables) 4.8 sec

reduce from 8967 pic to 5252

5252 pict in 3.7 sec
5252 pict (optimized all tables) in 2.9 sec. 





New test:

   key_buffer = 64M
   max_allowed_packet = 1M
   table_cache = 64
   sort_buffer_size = 1M
   read_buffer_size = 1M
   read_rnd_buffer_size = 1M
   net_buffer_length = 32K
   thread_stack = 1M
[...]
   [isamchk]
   key_buffer = 8M
   sort_buffer_size = 8M

   [myisamchk]
   key_buffer = 8M
   sort_buffer_size = 8M


(I even tried to double to 16M on all values on  [isamchk] [myisamchk], make no difference.)


139 users, less than half active, only admin has pictures. starting usermgr.php with debug enabled:

51000 pic in 27.sec
51000 pic in (optimized all tables) 27.sec

reduce from 51000 pic to 8967

8967 pict in 13.sec
8967 pict in (optimized all tables) 4.1 sec

reduce from 8967 pic to 5252

5252 pict in 3.3 sec
5252 pict (optimized all tables) in 0.98 sec. 



So it helps with more memory to mysql. (prodution-server run with "key_buffer = 64M".)  It helps to optimize all tables.

But many pictures make the script very heavy. It is not only a matter of number of users.

Many pictures in one album also makes modifyalb.php heavy when it produce a heavy listbox to choose which image is to represent the album.  Would have been better with that function split out

Maybe the problem is the same here. As long as picturemanagement is linked closely to usermanagement; usermanagement gets heavy with a high number of pictures. 

I dont know if the problem is

users + pictures, users x pictures, or only pictures, or only users (doubt that).



Mvh Andrez1