how to reduce loading time of index.php

[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.

[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

[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]

Are there any plugins installed?

[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

Code Select Expand

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]

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)

Code Select Expand

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]

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:

Code Select Expand

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

Add directly below it:

Code Select Expand

        if ($CONFIG['first_level'] == 1)  {   

Find below:

Code Select Expand

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

Add directly below it:

Code Select Expand

        }

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...

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.