Strong Machine - Poor Performance...

[d1CK135]

Hi @ all, since a few weeks i my CPG 1.4.8 is getting slower and slower...

I turned off all features like logging, banlist, statistics, last added, random pics, user galleries... but still my Index.php needs about 15 secs to be generated and browsing the albums is awfull, too...

My Machine:
IBM e-server xSeries 226 Dual XEON 2.8 GHZ
4096 MB RAM, 1,2 Terrabyte U320 SCSI HDD´s
Windows 2000 Advanced Server incl. SP4
All Hotfixes & Updates / New drivers for all devices
All Windows Services which aren´t needed disabled

Webserver Environment:
    * Apache 2.2.2
    * MySQL 5.0.21
    * PHP 5.1.4
    * phpMyAdmin 2.8.1
    * FileZilla FTP Server 0.9.16c

Stats from/about my CPG (german):
15029 Dateien in 355 Alben und 48 Kategorien mit 0 Kommentaren, 17513 mal angesehen
Page generated in 4.927 seconds - 355 queries in 4.507 seconds - Album set : ; Meta set: ;
   

[Nibbler]

Please post a link. Did you edit out part of the debug output is that exactly as it was ?

[d1CK135]

A link won´t help, because the CPG / Server is intranet-based and not reachable from the WWW

I´ve looked over a few other threads in this forum, dealing with "performance / tweaking" - regarding to them
the number of querries is one of the most important factors, when it comes to performance problems...
Unfortunately I am miles away from being a good coder/progger so I really need advise what to do and how to
do...

Many THX and sorry...

[d1CK135]

Yes, I cut out the most because the 20.000 letter limit of the posting was exceeded...
If you tell me how can manage to create a longer posting I´ll post the complete version
immediately...

[Nibbler]

It's not an issue of tweaking, 355 queries on a page is not expected behaviour to begin with. Do you have all your albums visable on the main page? What is on your main page?

You can attach a text file to your post.

[d1CK135]

On the main page there are shown 48 Categories and about 60 Albums (incl. 1 Thumbnail per Album)...
There is no user defined header or footer... only the standard page with the Mac Ox X Theme ( the sites
behaviour is the same with other themes, too )

@Nibbler - Debug attached to first post of this thread...

[Nibbler]

Is that the debug output from the index page ?

Try disabling 'Show number of linked files'  in config.

[d1CK135]

yupp, thats the debug from the index page...

It became a bit faster after i disabled 'Show number of linked files'
Page generated in 2.772 seconds - 304 queries in 2.36 seconds - Album set : ; Meta set: ;

But at the moment I am trying in "offline mode"... Normally there are about 400 to 600 unique
users browsing the gallery... 15-20 secs are normal for the index page to show up... That´s
much to slow, all of my users and our servers in the farm are linked via GBIT/Fibre...

edit: With 10 users browsing ( tested a few secs ago ) it looks like that now...
Page generated in 22.415 seconds - 304 queries in 22.23 seconds - Album set : ; Meta set: ;

edit: When I refresh the browserwindows on my client or on my server the MySQL process jumps
up to about 50 - 60% CPU Usage (10 - 12 secs), then it goes down to "idle" again and the Apache
process jumps to 95 - 99& CPU Usage ( 10-12 secs)...

[Nibbler]

Many of those queries are from the batch add script, they shouldn't be on your index page atall. Have you modified your gallery?

[d1CK135]

No - Completely no mods, nu plugins, only the "Mac Ox X" & the "Hardwired" theme...

look @ attachment - remember - this is still in "offline mode" without any users on the site...

[d1CK135]

A vanilla CPG with the same settings and 1 Album with 100 pics on the same machine needs about 0.2 secs to show up.
Is it possible, that the performance problems are issued simply by the number of pics in my main gallery and that there
is no workaround ?!?

[Joachim Müller]

I can't understand where all those queries come from. This is part of my debug_output for a gallery that has a similar size as yours, some mods applied and a lot of meta albums in place, but resides on shared webhosting with even a less powerful server:

Queries:
------------------
Array
(
   

• => SELECT extension, mime, content, player FROM cpg140_filetypes; (0s)
      [1] => select * from cpg140_plugins order by priority asc; (0s)
      [2] => SELECT * FROM cpg140_bridge (0s)
      [3] => 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(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, 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, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg140_usergroups WHERE group_id in (101,112) (0s)
      [4] => SELECT group_name FROM  cpg140_usergroups WHERE group_id= 101 (0s)
      [5] => SELECT user_favpics FROM cpg140_favpics WHERE user_id = 30 (0s)
      [6] => SHOW TABLES LIKE 'cpg140_cms_config' (0s)
      [7] => SELECT * FROM cpg140_cms_config (0s)
      [8] => DELETE FROM cpg140_banned WHERE expiry < '2006-07-18 07:32:02' (0s)
      [9] => SELECT * FROM cpg140_banned WHERE (ip_addr='84.172.82.198' OR ip_addr='84.172.82.198' OR user_id=30) AND brute_force=0 (0s)
      [10] => SELECT cid, name, description, thumb FROM cpg140_categories WHERE parent = ''  ORDER BY pos (0s)
      [11] => SELECT aid FROM cpg140_albums WHERE category = 36 (0s)
      [12] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 36 (0s)
      [13] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8970' (0s)
      [14] => SELECT count(*) FROM cpg140_albums as a WHERE category = '36' (0s)
      [15] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=36 ORDER BY a.pos LIMIT 0,8 (0s)
      [16] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (213, 215, 212, 211, 214, 251, 267, 289)GROUP BY a.aid (0s)
      [17] => SELECT count(pid) AS link_pic_count
                        FROM cpg140_pictures
                          WHERE aid != 211 AND
                          keywords LIKE '%BauernFasching2005Bauernball%' AND
                          approved = 'YES' (0s)
      [18] => SELECT count(pid) AS link_pic_count
                        FROM cpg140_pictures
                          WHERE aid != 215 AND
                          keywords LIKE '%Raga2004HighlandGames%' AND
                          approved = 'YES' (0s)
      [19] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8969' (0s)
      [20] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8879' (0s)
      [21] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8782' (0s)
      [22] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8970' (0s)
      [23] => SELECT aid FROM cpg140_albums WHERE category = 27 (0s)
      [24] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 27 (0s)
      [25] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8690' (0s)
      [26] => SELECT count(*) FROM cpg140_albums as a WHERE category = '27' (0s)
      [27] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=27 ORDER BY a.pos LIMIT 0,1 (0s)
      [28] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (209)GROUP BY a.aid (0s)
      [29] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='8698' (0s)
      [30] => SELECT aid FROM cpg140_albums WHERE category = 3 (0s)
      [31] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 3 (0s)
      [32] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='7006' (0s)
      [33] => SELECT count(*) FROM cpg140_albums as a WHERE category = '3' (0s)
      [34] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=3 ORDER BY a.pos LIMIT 0,7 (0s)
      [35] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (11, 13, 12, 97, 120, 139, 182)GROUP BY a.aid (0s)
      [36] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='5437' (0s)
      [37] => SELECT aid FROM cpg140_albums WHERE category = 2 (0s)
      [38] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 2 (0s)
      [39] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='260' (0s)
      [40] => SELECT count(*) FROM cpg140_albums as a WHERE category = '2' (0s)
      [41] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=2 ORDER BY a.pos LIMIT 0,5 (0s)
      [42] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (31, 72, 49, 91, 20)GROUP BY a.aid (0s)
      [43] => SELECT aid FROM cpg140_albums WHERE category = 4 (0s)
      [44] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 4 (0s)
      [45] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='246' (0s)
      [46] => SELECT count(*) FROM cpg140_albums as a WHERE category = '4' (0s)
      [47] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=4 ORDER BY a.pos LIMIT 0,3 (0s)
      [48] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (17, 15, 16)GROUP BY a.aid (0s)
      [49] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='246' (0s)
      [50] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='93' (0s)
      [51] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='423' (0s)
      [52] => SELECT aid FROM cpg140_albums WHERE category = 5 (0s)
      [53] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 5 (0s)
      [54] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='5216' (0s)
      [55] => SELECT count(*) FROM cpg140_albums as a WHERE category = '5' (0s)
      [56] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=5 ORDER BY a.pos LIMIT 0,9 (0s)
      [57] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (69, 50, 22, 93, 124, 159, 177, 252, 307)GROUP BY a.aid (0s)
      [58] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='11134' (0s)
      [59] => SELECT aid FROM cpg140_albums WHERE category = 7 (0s)
      [60] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 7 (0s)
      [61] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='6054' (0s)
      [62] => SELECT count(*) FROM cpg140_albums as a WHERE category = '7' (0s)
      [63] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=7 ORDER BY a.pos LIMIT 0,15 (0s)
      [64] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (282, 281, 47, 88, 64, 217, 60, 85, 86, 48, 102, 105, 115, 119, 137)GROUP BY a.aid (0s)
      [65] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='2927' (0s)
      [66] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='4504' (0s)
      [67] => SELECT aid FROM cpg140_albums WHERE category = 10 (0s)
      [68] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 10 (0s)
      [69] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='3691' (0s)
      [70] => SELECT count(*) FROM cpg140_albums as a WHERE category = '10' (0s)
      [71] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=10 ORDER BY a.pos LIMIT 0,12 (0s)
      [72] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (33, 34, 87, 52, 71, 63, 24, 133, 141, 147, 169, 181)GROUP BY a.aid (0s)
      [73] => SELECT aid FROM cpg140_albums WHERE category = 6 (0s)
      [74] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 6 (0s)
      [75] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='3626' (0s)
      [76] => SELECT count(*) FROM cpg140_albums as a WHERE category = '6' (0s)
      [77] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=6 ORDER BY a.pos LIMIT 0,15 (0s)
      [78] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (140, 61, 65, 46, 45, 80, 39, 84, 95, 127, 134, 142, 146, 148, 160)GROUP BY a.aid (0s)
      [79] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='2953' (0s)
      [80] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='12302' (0s)
      [81] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='5345' (0s)
      [82] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='5790' (0s)
      [83] => SELECT aid FROM cpg140_albums WHERE category = 14 (0s)
      [84] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category = 14 (0s)
      [85] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='2684' (0s)
      [86] => SELECT count(*) FROM cpg140_albums as a WHERE category = '14' (0s)
      [87] => SELECT a.aid, a.title, a.description, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg140_albums as a LEFT JOIN cpg140_pictures as p ON a.thumb=p.pid WHERE category=14 ORDER BY a.pos LIMIT 0,9 (0s)
      [88] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg140_albums AS a  LEFT JOIN cpg140_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (53, 54, 55, 56, 57, 58, 59, 154, 190)GROUP BY a.aid (0s)
      [89] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='2836' (0s)
      [90] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg140_pictures WHERE pid='2819' (0s)
      [91] => SELECT aid FROM cpg140_albums as a WHERE category>=10000 (0s)
      [92] => SELECT count(*) FROM cpg140_pictures as p, cpg140_albums as a WHERE p.aid = a.aid AND category >= 10000 (0s)
      [93] => SELECT aid FROM cpg140_albums as a WHERE category = '0' (0s)
      [94] => SELECT count(*) FROM cpg140_albums as a WHERE 1 (0s)
      [95] => SELECT count(*) FROM cpg140_pictures as p LEFT JOIN cpg140_albums as a ON a.aid=p.aid WHERE 1 (0s)
      [96] => SELECT count(*) FROM cpg140_comments as c LEFT JOIN cpg140_pictures as p ON c.pid=p.pid LEFT JOIN cpg140_albums as a ON a.aid=p.aid WHERE 1 (0s)
      [97] => SELECT count(*) FROM cpg140_categories WHERE 1 (0s)
      [98] => SELECT sum(hits) FROM cpg140_pictures as p LEFT JOIN cpg140_albums as a ON p.aid=a.aid WHERE 1 (0s)
      [99] => SELECT COUNT(*) FROM cpg140_pictures WHERE approved = 'NO' (0s)
      [100] => SELECT * FROM cpg140_cms WHERE conid='0' AND type='0' ORDER BY cpos (0s)
      [101] => SELECT count(*) FROM cpg140_albums as a WHERE category = '0' (0s)
      [102] => SELECT COUNT(*) from cpg140_pictures WHERE approved = 'YES'  (0s)
      [103] => SELECT * FROM cpg140_pictures WHERE approved = 'YES'  ORDER BY RAND() LIMIT 5 (0.282s)
      [104] => SELECT count(*) from cpg140_comments where pid=5153 and msg_id!=0 (0.001s)
      [105] => SELECT count(*) from cpg140_comments where pid=6930 and msg_id!=0 (0s)
      [106] => SELECT count(*) from cpg140_comments where pid=9902 and msg_id!=0 (0s)
      [107] => SELECT count(*) from cpg140_comments where pid=6436 and msg_id!=0 (0s)
      [108] => SELECT count(*) from cpg140_comments where pid=11359 and msg_id!=0 (0s)
  )

There's not even half the amount of queries on my page. The only thing I could think of are really code changes (mods) - maybe your code source is to blame: where did you get your coppermine package from?

[d1CK135]

I got my CPG from your Sourceforge DL Page... same with the themes and the "downloadlink plugin"

The code of my CPG wasn´t modified at all...

by the way :-) LOL @ Bauernfasching :-) ( written in your debug log... )

Schwäbisch Allemannischer Fasching isch was scheners :-)