how to reduce loading time of index.php how to reduce loading time of index.php
 

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]

Main Menu

how to reduce loading time of index.php

Started by Bradster, July 12, 2013, 05:20:18 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Bradster

Index.php currently takes over 20 seconds to load, and I've gotten a few email (okay, account suspensions) from my host about the load my site is putting on the server. It looks like debug is saying the page is executing over 700 queries, and most of them look the same, but I don't really know what they mean.

I've read the documentation for the Album List View setting, and tried what was suggested in this thread, but it didn't help. No random pics are used, and my only (?) blocks are minicms/breadcrumb/lastalb,2/catlist/alblist/lastcom.


Website: http://anime-fanservice.org/coppermine/index.php (Not safe for work)
User account: Jets0n
Password: Spac3ly


Thanks in advance.

phill104

Do you have detailed statistics turned one? That is well known to create a little extra load so you could try disabling that.
It is a mistake to think you can solve any major problems just with potatoes.

Bradster

No detailed hit/vote statistics, sorry, though I do have file and album views turned on and turning them off does nothing. The vast majority of the queries are (and hopefully this doesn't technically count as posting debug without being asked, since that file is significantly longer):


=> SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = 'XXX' ) )  AND approved='YES' ORDER BY RAND() LIMIT 0,1 [index.php:1049] (1 ms)

SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE pid='XXXX' [index.php:1055] (0 ms)

SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid  FROM cpg15x_pictures  WHERE ((aid != '1213' ) AND (keywords like '%Bradster%' ))  AND approved='YES' [index.php:1018] (54 ms)


Turning lastalb,2 off doesn't fix the problem.

phill104

First thing you need to do is update to CPG1.5.24
It is a mistake to think you can solve any major problems just with potatoes.

Bradster

Overall, the update helped a bit in page load time. It's down to about 11 seconds- at least with most of the default settings left intact. I don't know if that speed increase couldn't also be attributed to the settings instead of the update.

Number of queries does seem to vary wildly based on the settings, and I've gotten it down to about 500- though I can't remember exactly which settings were changed to get this number.

I did save my config page to PDF, in case it would be useful for someone to look at it and find the "smoking gun"- though I secretly hope there aren't too many more things I could try, since I'm at what I'd consider to be the minimum of functionality at this point.

Joe Carver


Bradster

BBCode Control, FileMove, FileReplacer, MassImport, miniCMS, More Meta Albums, Move 2 Public, MP4 player, User Gallery Alphabetic Tabbing, phpMyAdmin

Joe Carver

Since I am not familiar enough with those plugins and since I can't see anything obvious
on the page or in the source, the best I have at the moment is:


  • Set debug ON for users, logout, copy the output, and attach it as a .zip file (no promises from me, but it might help)
  • Try uninstalling plugins you think that you live without

Bradster

Zip file attached. I'll start pruning plugins now- since I don't use them constantly maybe I'll just reinstall one when I need it. If it helps.

(I can't demand resolution since it's volunteer help for a free application, but your time is definitely appreciated)

Joe Carver

For some reason it looks like it is trying to pull a lot of thumbnails
for the albums in the categories on your home page which are not shown.
However, those thumbnails are shown in the Categories.

Usually that is a setting in Config>> Album list view>> Show first level album thumbnails in categories.

Is there anything in your theme or elsewhere that could affect that setting?
Is the query count the same or high in a category? (ex. anime-fanservice.org/coppermine/index.php?cat=9)

Bradster

Well, I'm not artistically-inclined enough to modify a default theme, and experimented with the "show first level" settings to make sure they were as low as possible without dropping the albums altogether. To my beginner's eye, the values in the config table appear to correlate with the settings I have on its page.

I haven't done an exhaustive line-by-line comparison, but it looks like all of the aid and pid calls are different. Most of these calls are made by lines 1078 and 1084 of index.php, and the text of both is:

$result = cpg_db_query($sql);

I assume it'd be bad form to copypasta the entire section that contains those lines, since they should appear in everybody's index.php and can be looked at independently.... unless mine has undergone a mysterious mutation which has somehow survived the update and overwriting of all files..

Bradster

Okay, after an impulse made me look at the SQL table again, I discover that quite a few albums are still using "random" thumbnails (-1) instead of an assigned one or "last uploaded" (0). This is most likely the source of my problem, though I don't understand CM enough to know why it's already calling for thumbnails on the main page when I only display the most recent albums.

This is more than a little irritating, because I know I spent quite a few hours well over a year ago changing all of the thumbnail settings away from random. The only way I can think that this was undone would be something stupid and believable like me manually importing table values from a backup that didn't have the correction in it.

Changing all of these will take some time, and I'll get back to this thread when I've been able to fix them, and any change in load times/queries.

ron4mac

If you're looking at the database and noticing the value of `thumb`, then entering a simple SQL command will reset all the albums

UPDATE `cpg_albums` SET thumb = 0
Substitute your table name if it is not `cpg_albums`.


Bradster

Thank you, I didn't know how to phrase that command.

Unfortunately it's still trying to query all of those thumbs. I haven't checked all of the pids, but quite a few of them belong to the thumbnail of the last updated albums in each category, but removing lastalb entirely (as a test) doesn't cut the number of queries down, though.

Joe Carver

#14
Quote from: Bradster on July 19, 2013, 04:47:32 PM
Thank you, I didn't know how to phrase that command.

Through phpMyAdmin (I use the version my host provides):

Open the db
Migrate to the cpg_albums table (or whatever the prefix is for your db)
Click SQL near the top of the page (returns window to run SQL queries)
Copy this into the field (remember the prefix)
UPDATE `cpg_albums` SET `thumb`= -1
Click Go

I am not certain that will solve your problem, although it is worth a try.

Things I do now know after trying to reproduce your problem without success include:

It will pull "basic" album thumbs on index, even when they are not called for in Config or shown.
The behavior / count did vary and seemed inconsistent when the value in the thumb column was changed,
but only by small amounts. However, my test gallery is nowhere near the scale of your gallery.

I would recommend making a backup of your db and performing a repair.
Creating a test gallery might help too, along with the other suggestions.
[edit]
Are you using album keywords?
[/]


Bradster

Unfortunately, replacing the thumb value with "-1" has no effect.

I have the "More Meta Albums" plugin installed, and have keywords for a few dozen albums, but not more than that because I've been too lazy about the project. However, something I noticed in the past is that CM itself assigns my username to all uploaded albums, even other peoples'. There are quite a few image keywords that other users have made for their stuff.

I've been making a test page from scratch, which is why I haven't replied earlier- the Mass Import plugin dies when trying to add the whole site (probably not surprising), so I've been going one letter at a time. I'm currently up to the letter S and can confirm the number of queries is almost back to "normal"- but the page response time is a fraction of what it was. I thought about bulking it up with old information to see if that would return a lot of the load time, but realized that since none of the images or albums have the same id numbers as before, it'd be a nightmarish mess.

http://anime-fanservice.org/coppertest/index.php

Bradster

Okay, so reducing the number of categories also reduces the number of queries, and increasing the number of albums per page inside the categories increases the queries significantly, regardless of what you choose to show on the main page. That definitely cripples a visitor's navigation of a page this size, since a quick load time on index.php will mean a much larger number of pages within each category. I guess they'll have to deal with it, because there's no way I'm doing another account upgrade to keep things quick, and am not inclined to use a second platform for older material.


Should I just mark this as "solved", since it basically boils down to the gallery size itself? Others might at least get some useful info from what was suggested here, but didn't work for me personally.

Thanks for the help. (Feel free to post whatever final comment strikes your fancy)

Joe Carver

Quote from: Bradster on July 22, 2013, 05:13:43 AM
Should I just mark this as "solved", since it basically boils down to the gallery size itself?
In my opinion, no. Although I can't be certain yet until a second opinion with better knowledge than mine is posted.

In the meantime try the mod. below. I am not too happy about it and have only performed limited testing,
(server & time issues) but the results are reduced queries when first level thumbs are turned off.

Backup index.php, then open / edit a copy.

Find:
        //unused code {SaWey}
        //$visibility = $album['visibility'];


Add directly below it:
        if ($CONFIG['first_level'] == 1)  {   

Find below:
            $alb_list[$aid]['thumb_pic'] = '<img src="' . $cpg_privatepic_data['thumb'] . '" ' . $cpg_privatepic_data['whole'] . ' class="image" border="0" alt="" />';
        }


Add directly below it:
        }

Save and upload.
Please reply if makes any difference.

Bradster

That makes quite a bit of difference! Queries on index.php are down to about 80, and page generation/load are both under 1s. It does take a little longer to load the category pages now (presumably since they're no longer called on the main page), but that is a much better trade-off since more people start on the main page than they do a category.

Joe Carver

Thanks for returning and marking the thread as solved (helpful for future searchers).

But I am curious...

Quote from: Bradster on July 27, 2013, 08:52:58 AM
It does take a little longer to load the category pages now

Can you post link to any particular page(s) with that behavior? And do you know if the
difference is due to increased query count? Or could you take a look at the count and time
differences? Again, my test setup is not as large by any means, so any large scale gallery effects
are impossible for me to test.