4images Forum & Community
4images Issues / Ausgaben => Discussion & Troubleshooting => Topic started by: up23 on March 31, 2006, 01:44:17 PM
-
I've been kind of stress testing 4images, and I've noticed that (with the cache turned on) the mysql query that takes the most time (>70%) is the lines:
$sql = "SELECT cat_id, COUNT(*) AS num_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1
GROUP BY cat_id";
$result = $site_db->query($sql);
from global.php. Is there any way to put the result of this in the cache to speed things up?
I believe the time increases linearly with the number of files. I'm running the mysql under xampps with windows xp and 70,000 files in the database. Category pages show up in 0.7 seconds with cache; this mysql query takes 0.55 seconds.
As a side note, I sped up the details.php page by commenting out the next/prev file section, and use include of global2.php where global2.php has the code above commented out. No the most finesse way, I know. Also, the "show new" feature seems to take a lot of resources, but then all 70,000 files in my test database were new.
So if anyone know how to cache the above code, please post it. Thanks.
-
The same problem here...i search for this issue to resolve it but sometime the server load is so high and in the proceses this apear there.
-
Try this, in global.php replace
$sql = "SELECT cat_id, COUNT(*) AS num_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1
GROUP BY cat_id";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$cat_cache[$row['cat_id']]['num_images'] = $row['num_images'];
}
$site_db->free_result();
With: $cat_cache_file = ROOT_PATH."data/.htcatcache"; //name like that should protect from accessing from web
$cat_cache_data = "";
$cached = 0;
if (file_exists($cat_cache_file) && filemtime($cat_cache_file) > (time() - 10) && $cat_cache_data = file($cat_cache_file))
{
foreach($cat_cache_data as $data)
{
$data = explode(" ", $data);
if (!isset($data[1]) || !isset($cat_cache[$data[0]]))
{
$cached = 0;
break;
}
$cat_cache[$data[0]]['num_images'] = intval($data[1]);
$cached = 1;
}
}
if (!$cached)
{
$sql = "SELECT cat_id, COUNT(*) AS num_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1
GROUP BY cat_id";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$cat_cache[$row['cat_id']]['num_images'] = $row['num_images'];
$cat_cache_data .= $row['cat_id']." ".$row['num_images']."\n";
}
$site_db->free_result();
file_put_contents($cat_cache_file, trim($cat_cache_data));
}
But I doubt it will improve performance...
P.S. PHP v5 or above required.
-
I will check it and i will tell you if is better. Do you think the original script without any mood installed must be ok? I have around 30.000 wallpapers and the site is going well but when i have more visitors... everything is different and i get that server load warnings. It was the time when 4000 visitors every day but almost every day something is goin on. I like the script and for me is a complete one and i develop my template in more than 6 month every day working something. I really want to make it to work right. I can give you my admin detail if helps more to figure what the problem is. I'm really lost in so much coding sometime.
www.pulsarmedia.eu
-
I put your code and also i changed in funtions.php getimagesize with filesize (for who use // MOD multi download // getimagesize must be unchanged in that section) and now everything is really ok :) Load is normal and google crawl rate is set to full :) A few minutes ago i had 190 guests including boots and load was 2.8 :) Verry much thanks !!
-
I'm glad (and must say little surprised) it helped...
one thing I forgot to mention is that it caches only for 10 seconds, which controls by this line:
if (file_exists($cat_cache_file) && filemtime($cat_cache_file) > (time() - 10) && $cat_cache_data = file($cat_cache_file))
-
I made just this two modifications and is better like before...What value you think i must enter? Also i want to ask you if you know anything else what i can do in this direction. Now i disabled the cache for categories and for detail page for comment and for sort images to work but i want to prepare for much visitors in future. Thank you!
-
I notify something...If guest users is browsing the page eveithing is fine but sometime when i have on page 4-5 registered users the server load is high... i cant explained and i don't know where to look to see what is causing this load. I still looking for any solutions to improve this load.
-
maybe it's because registered users have access to more images/categories?
-
I restricted just download option and no more categories will show after registration. Is any way to chech what can do sometime this load? On server or in any place?
-
Could you explain what is cached by this hack? The category page (if so, what the difference from $cache_page_categories=1)?
-
this hack affects pretty much all pages. it probably only works on 4images with a lot of categories and slow mysql server, it has nothing to do with bult-in caching system