Hostgator have disabled my gallery, high CPU usage.

[dogwalks]

Hi

First post in this forum, my name is Rob, pleased to meet you all.

This is quite an urgent problem for me, it's a money making website and the gallery is very important. Here's what hostgator said when they took CPG down...

The issue is what appears to be a common query in the software is examining 55k rows of data every time it's ran which is almost the entire table:

dogwalks_joom
cpg14x_jos_pictures 58536 <----- total rows in the table:

| 1 | SIMPLE | cpg14x_jos_pictures | range | aid_approved,pic_aid | aid_approved | 5 | NULL | 55310 <----- rows examined on the below query| Using where; Using filesort |

mysql> explain SELECT * FROM cpg14x_jos_pictures WHERE approved = 'YES' AND aid IN

What follows that is a very long list of numbers.

Hostgator then suggest:

As a start I would be sure the software is running the latest version and if possible purge any non-used rows of data from the table cpg14x_jos_pictures

..........


This gallery is rather big, it's 5.12 GB, 13 folders with over 160,000 files. I am reasonably comfortable updating from the current version 1.4 .25 to 1.5.2 but wouldn't know how to purge any non-used rows of data from the table. Also, a couple of months ago I moved the site from a Joomla platform to WordPress, but didn't tidy up my SQL databases (didn't know how). So I was wondering whether dogwalks_joom cpg14x_jos_pictures should be running at all anyway?

Can anybody help me with this or I am quite happy to pay somebody to fix this gallery up and optimise it so it runs as smoothly as possible. Obviously I can't link to the website gallery, it's down. Hostgator have given me IP privileges to log in, but if any of you are happy to accept the job to fix it, I'll get the IP changed to yours.

Many thanks

Rob

[Αndré]

mysql> explain SELECT * FROM cpg14x_jos_pictures WHERE approved = 'YES' AND aid IN

What follows that is a very long list of numbers.

Please post the complete query (you can left out everything inside the brackets after AND aid IN), as it probably tells us what's going wrong. I guess it's the lastup meta album, but that's just an assumption without to know how your gallery looks like. Please post a link to your gallery, at least Google should have your gallery in the cache.

wouldn't know how to purge any non-used rows of data from the table [..] I was wondering whether dogwalks_joom cpg14x_jos_pictures should be running at all anyway?

That table is mandatory, there's nothing to purge.

[dogwalks]

Hi Andre

Thanks for getting back to me.

This is the link to the Gallery...

http://www.dogwalks.com.au/cpg/index.php

Google's cached pages link seems to have disappeared for me, hopefully you know where it is.

Here is the full query that hostgator sent me...

Coppermine Photo Gallery

The issue is what appears to be a common query in the software is examining 55k rows of data every time it's ran which is almost the entire table:

dogwalks_joom
cpg14x_jos_pictures 58536 <----- total rows in the table:

| 1 | SIMPLE | cpg14x_jos_pictures | range | aid_approved,pic_aid | aid_approved | 5 | NULL | 55310 <----- rows examined on the below query| Using where; Using filesort |

mysql> explain SELECT * FROM cpg14x_jos_pictures WHERE approved = 'YES' AND aid IN
-> (16,15,14,30,29,28,17,18,19,20,21,22,23,24,25,32,33,35,36,37,38,39,40,41,42,43,45,46,47,48,49,50,54,52,53,55,56,57,58,60,61,62,64,65,66,67,68,70,74,75,76,77,78,79,80,81,82,84,85,86,88,89,91,92,93,94,95,96,97,98,100,101,102,103,106,107,108,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,191,194,195,197,198,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,246,228,245,359,232,233,234,235,236,237,238,239,240,241,242,243,244,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,298,299,300,301,302,303,304,305,306,30! 8,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,355,356,357,358,360,361,362,364,365,366,367,368,369,371,372,373,374,375,376,377,378,380,381,382,383,384,385,386,387,388,389,390,395,392,393,397,398,399,400,401,402,403,404,405,406,407,408,443,411,412,413,414,415,416,417,419,420,421,422,423,424,425,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,444,445,446,447,448,449,450,453,452,456,457,458,460,461,462,463,464,465,466,467,469,470,471,472,473,474,475,476,477,478,480,481,482,483,484,485,486,487,488,490,491,492,493,494,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,
-> 535,536,537,538,539,540,541,542,543,544,546,547,549,550,551,552,553,555,556,557,558,559,562,563,565,566,567,568,574,569,570,571,572,575,576,577,580,581,582,583,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,602,603,604,605,607,608,609,610,611,612,616,617,618,619,620,621,622,623,629,625,627,630,631,632,633,634,635,636,637,638,639,640,641,643,644,645,646,647,648,650,651,652,653,654,655,657,658,659,671,661,662,665,666,667,668,672,673,674,675,676,677,679,680,681,682,698,684,685,686,687,688,690,691,693,695,696,697,699,700,702,703,704,705,706,707,708,709,710,712,713,715,716,717,718,719,722,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,748,749,750,751,752,753,754,755,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,779,780,781,782,784,785,786,800,788,789,790,791,792,793,795,796,797,798,799,802,803,804,805,806,807,808,809,810,811,812,813,814,818,819,820,821,822,823,824,827,8! 28,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,855,856,857,860,861,862,863,864,865,866,867,868,869,870,871,872,874,875,876,877,878,879,880,882,883,884,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,903,904,905,906,907,908,909,910,911,912,913,914,915,937,917,941,919,920,921,922,923,924,925,926,927,929,930,931,932,938,934,939,940,942,943,944,945,946,947,948,949,950,951,953,954,955,956,957,958,959,960,963,964,965,966,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,991,992,993,994,995,996,998,999,1000,1001,1004,1005,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1020,1023,1024,1025,1026,1027,1028,1030,1031,1032,1033,1034,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1076,1067,1068,1069,1070,1071,1072,1073,1074,1075,1077,1078,1079,1080,1081,1082,1083,1085,1087,1088,1089,! 1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,11! 05,1106, 1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1124,1125,1127,1128,1130,1131,1133,1134,1135,1136,1137,1138,1139,1140,1141,1143,1144,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1159,1160,1161,1162,1165,1166,1168,1170,1171,1172,1173,1174,1175,1176,1177,1178,1180,1181,1182,1184,1188,1189,1190,1191,1192,1193,1194,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1207,1208,1209,1210,1211,1212,1213,1214,1215,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1234,1235,1237,1238,1239,1240,1241) ORDER BY pid DESC LIMIT 35703
-> ;
+----+-------------+---------------------+-------+----------------------+--------------+---------+------+-------+-----------------------------+

Thanks

Rob

[Αndré]

I found your cached gallery here. On your index page I can see the random and the lastup meta album.

Unfortunately I wasn't able to find such a query in my gallery with the same gallery settings on the quick. Please enable debug mode and search for that query in the debug output (searching for ORDER BY pid should be enough). You'll find something like

Code Select Expand

[include\functions.inc.php:1908]
which will help us to find the source.

[dogwalks]

Not sure if I'm doing this right, but I enabled debug mode and then copied the information into notepad, but is search found nothing similar to ORDER BY pid, so I hope it's okay to post everything ...


USER:
------------------
Array
(
    [ID] => a780edcf2085e5b7908d301d17f0d485
    [am] => 1
    [lang] => english_gb
)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => karenkiernan
    [groups] => Array
        (
           

• => 1
          )
  
      [disk_max] => 0
      [disk_min] => 0
      [can_rate_pictures] => 1
      [can_send_ecards] => 1
      [ufc_max] => 3
      [ufc_min] => 3
      [custom_user_upload] => 0
      [num_file_upload] => 5
      [num_URI_upload] => 3
      [can_post_comments] => 1
      [can_upload_pictures] => 1
      [can_create_albums] => 1
      [has_admin_access] => 1
      [pub_upl_need_approval] => 0
      [priv_upl_need_approval] => 0
      [group_name] => Administrators
      [upload_form_config] => 3
      [group_quota] => 0
      [can_see_all_albums] => 1
      [group_id] => 1
  )
  
  ==========================
  Queries:
  ------------------
  Array
  (
     
• => SELECT extension, mime, content, player FROM cpg14x_jos_filetypes; (0s)
      [1] => select * from cpg14x_jos_plugins order by priority asc; (0s)
      [2] => delete from `dogwalks_joom`.cpg14x_jos_sessions where time<1338885984 and remember=0; (0.001s)
      [3] => delete from `dogwalks_joom`.cpg14x_jos_sessions where time<1337679984; (0s)
      [4] => select user_id from `dogwalks_joom`.cpg14x_jos_sessions where session_id = '3d3a50fba213f6e7ae23b2095ed501b0' (0s)
      [5] => select user_id as id, user_password as password from `dogwalks_joom`.cpg14x_jos_users where user_id=1 (0s)
      [6] => SELECT u.user_id AS id, u.user_name AS username, u.user_password AS password, u.user_group+100 AS group_id FROM `dogwalks_joom`.cpg14x_jos_users AS u INNER JOIN `dogwalks_joom`.cpg14x_jos_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' (0s)
      [7] => SELECT user_group_list FROM `dogwalks_joom`.cpg14x_jos_users AS u WHERE user_id='1' and user_group_list <> ''; (0s)
      [8] => 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 cpg14x_jos_usergroups WHERE group_id in (1) (0s)
      [9] => SELECT group_name FROM  cpg14x_jos_usergroups WHERE group_id= 1 (0s)
      [10] => update `dogwalks_joom`.cpg14x_jos_sessions set time='1338889584' where session_id = '3d3a50fba213f6e7ae23b2095ed501b0' (0s)
      [11] => SELECT user_favpics FROM cpg14x_jos_favpics WHERE user_id = 1 (0s)
      [12] => DELETE FROM cpg14x_jos_banned WHERE expiry < '2012-06-05 04:46:24' (0s)
      [13] => SELECT * FROM cpg14x_jos_banned WHERE (ip_addr='60.241.109.151' OR ip_addr='60.241.109.151' OR user_id=1) AND brute_force=0 (0s)
      [14] => SELECT COUNT(*) FROM cpg14x_jos_pictures WHERE approved = 'NO' (0s)
      [15] => SELECT value FROM cpg14x_jos_config WHERE name = 'theme' (0s)
  )
  
  ==========================
  GET :
  ------------------
  Array
  (
  )
  
  ==========================
  POST :
  ------------------
  Array
  (
  )
  
  ==========================
  VERSION INFO :
  ------------------
  PHP version: 5.2.17 - OK
  ------------------
  mySQL version: 5.5.23-55
  ------------------
  Coppermine version: 1.4.25(stable)
  ==========================
  Module: GD
  ------------------
  GD Version: bundled (2.0.34 compatible)
  FreeType Support: 1
  FreeType Linkage: with freetype
  T1Lib Support:
  GIF Read Support: 1
  GIF Create Support: 1
  JPG Support: 1
  PNG Support: 1
  WBMP Support: 1
  XPM Support: 1
  XBM Support: 1
  JIS-mapped Japanese Font Support:
  
  ==========================
  Module: mysql
  ------------------
  MySQL Supportenabled
  Active Persistent Links 0
  Active Links 1
  Client API version 5.5.23
  MYSQL_MODULE_TYPE external
  MYSQL_SOCKET /var/lib/mysql/mysql.sock
  MYSQL_INCLUDE -I/usr/include/mysql
  MYSQL_LIBS -L/usr/lib64 -lmysqlclient 
  ==========================
  Module: zlib
  ------------------
  ZLib Support enabled
  Stream Wrapper support compress.zlib://
  Stream Filter support zlib.inflate, zlib.deflate
  Compiled Version 1.2.3
  Linked Version 1.2.3
  ==========================
  Server restrictions (safe mode)?
  ------------------
  Directive | Local Value | Master Value
  safe_mode | Off | Off
  safe_mode_exec_dir | no value | no value
  safe_mode_gid | Off | Off
  safe_mode_include_dir | no value | no value
  safe_mode_exec_dir | no value | no value
  sql.safe_mode | Off | Off
  disable_functions | dl | dl
  file_uploads | On | On
  include_path | .:/usr/lib/php:/usr/local/lib/php | .:/usr/lib/php:/usr/local/lib/php
  open_basedir | no value | no value
  ==========================
  email
  ------------------
  Directive | Local Value | Master Value
  sendmail_from | no value | no value
  sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
  SMTP | localhost | localhost
  smtp_port | 25 | 25
  ==========================
  Size and Time
  ------------------
  Directive | Local Value | Master Value
  max_execution_time | 30 | 30
  max_input_time | 60 | 60
  upload_max_filesize | 64M | 64M
  post_max_size | 64M | 64M
  ==========================
  Page generated in 0.149 seconds - 16 queries in 0.001 seconds - Album set : ; Meta set: ;

[Αndré]

I assume that's not the debug output from the index page, right?

if any of you are happy to accept the job to fix it, I'll get the IP changed to yours.

That seems to be easier. If you want, I'll send you my current IP address via PM.

[dogwalks]

You're right, it wasn't. I've just redone it from the index page and this is what it says next to order by pid...

[139] => SELECT * FROM cpg14x_jos_pictures WHERE approved = 'YES'  ORDER BY pid DESC  LIMIT 0 ,8 (0s)

But I think you are right, probably best if you PM me your IP address and I'll get hostgator to switch over access to you.

Thanks Andre

Rob

[Αndré]

PM sent.

[dogwalks]

I have passed your IP over to hostgator, as soon as they grant you access I will email you.

Thanks!

[dogwalks]

That was quick, Hostgator have granted access. I have sent you an email.

Thanks!

[Αndré]

Okay it seems that cpg1.4.x behaves different than cpg1.5.x regarding the debug output. However, I changed your default gallery layout in the config ("The content of the main page") from

Code Select Expand

breadcrumb/catlist/alblist/random,2/lastup,2
to

Code Select Expand

breadcrumb/catlist/alblist

Now the query Hostgator gave you isn't executed on every page load, though the Coppermine debug output told me that this query wasn't the culprit. I think it's save to put your page back online for now. Regarding the limit issue, I suggest to update to cpg1.5.x as there's no support for cpg1.4.x anymore. Maybe that issue is already fixed there, I don't know.

[Αndré]

Erm, I just recognized that the query I just eliminated differs from that one Hostgator told you. My query had a correct limit value of

Code Select Expand

ORDER BY pid DESC  LIMIT 0 ,8
the other one has something like

Code Select Expand

ORDER BY pid DESC LIMIT 35703


I'll search for it now.

[Αndré]

I wasn't able to find a query which has such a high limit (selecting ~35k files). It has to be related with the lastup meta album, as the query looks very similar.

In include/functions.inc.php you'll find

Code Select Expand

$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $META_ALBUM_SET ORDER BY pid DESC $limit";

$limit is set some lines above within the same function:

Code Select Expand

        $limit = ($limit1 != -1) ? ' LIMIT '. $limit1 : '';
        $limit .= ($limit2 != -1) ? ' ,'. $limit2 : '';

$limit2 seems to be -1, that's the only way such a limit value is generated for queries. But, I searched through the code and it seems to be impossible to generate a big value for $limit1 and silmutaneously -1 for $limit2.

Maybe Hostgator can find out a corresponding URL for that query?

[dogwalks]

You've lost me a bit Andre, but I assume you haven't been able to repeat the problem experienced by hostgator where a 'query in the software is examining 55k rows of data every time it's ran'? Or repeat the error code 35703?

So I will go back to hostgator and invite them to take a look at what you have just said and see what happens from there.

I'll come back to you.

Thanks.

[dogwalks]

Here's Hostgator's reply...

Unfortuntely I wasn't able to locate a URL which could reproduce this result, however, it's very clear from our logs that this query is occurring very frequently on your site.

I did notice, however, that some of the code may be different between the two sites. You appear to be running an older version of Coppermine, 1.4.25. It's possible that this particular issue was fixed between then and the latest version.

------

Not sure what he meant by "between the two sites", unless he thinks you are trying to replicate the query on a more updated site? Do you have any idea, maybe the comment makes more sense to you?

[Αndré]

Doesn't makes sense to me, sorry. As I said I disabled 2 meta album blocks in your gallery. I'd like to know if this already fixed the issue. Of course (as I wasn't successful to reproduce the issue) Hostgator has to re-enable your gallery, at least temporary if it's not fixed yet.

[dogwalks]

Okay, I've asked them to test to see if the fault has gone, I'll get back to you when I get an answer. It might be a while though, it's 1:20 in the morning here, so it depends how long Hostgator take to run the test.

Thanks.

[dogwalks]

Okay, so Hostgator set the gallery to live again about 16 hours ago and I emailed them to see how it was going. Here's what they said:

In the time frame I was watching the server I did not observe any issues with the load of your script.

So, they are not exactly saying it's fixed, but it appears to be. Thanks Andre for all your help.

Going forward, I suspect I should upgrade to the latest version 1.5 .20. Is it wise to do that directly from my version which is 1.4 .25? I see quite a few problems in the forums from people who have attempted that in the past.

Thanks

[Αndré]

Most issues during upgrade occur because of people doesn't read & follow the upgrade docs carefully/completely and/or use themes that are not compatible with cpg1.5.x after the upgrade.

It's advisable to test the upgrade procedure on a (local) copy of your website.

[dogwalks]

Incredible! I've just discovered that actually, Hostgator didn't lift restrictions as they said they had, I've had three people contact me to say they can't access the page. The only people who have been accessing the gallery for the last 18 hours are me and you Andre. So it's not surprising they haven't seen any high resource usage, is it? They have now set it live, properly, so I'll need to wait until tomorrow to see if it is fixed.

My only concern with the upgrade is my theme, which is called siteground9. if it doesn't work, can I just select a new theme after upgrading or will the installation become unstable?