Too many albums, cannot edit properties

[Oasis]

OK, up to now there have been hardly any load problems with the system.
However the users are increasing day by day, and there are now nearly 1500 registered users and over 1000 albums. Recently I have begun to experience some extreme slowdowns when editing album properties using the admin account. It has reached the point that I am constantly receiving system timeouts. The problem apparently only affects the admin account and not regular users, since they see only their albums. What is causing this and is there a way I can fix this? I need to fix this because I regularly have to make albums private because they contain inappropriate images.

By the way, the site is at http://photo.enctu.org
I know some of you have had problems connecting to it in the past weeks. It was apparently due to a network block that the Ministry of Education had placed on the IP we were using due to an open proxy last year on that IP. The block has been removed so there shouldn't be any problems connecting now. If you still have problems please let me know.

[Tarique Sani]

There is a possible cause for slow response in the album manager script with huge number of albums BUT editing properties of a single album should not give you any problems....

[Oasis]

hmm, the slow response I am experiencing is in modifyalb.php

When I click on "PROPERTIES" next to an album while logged in as admin, it takes forever to load the properties page (e.g. http://photo.enctu.org/modifyalb.php?album=1511)

If it DOES load before the timeout, then I can edit the properties fine...
However, recently, more often than not, it gives me a fatal error max execution time exceeded (I set it at 60 seconds), and it is getting worse.
In a regular user's admin mode, however, it loads in the blink of an eye.

[hyperion]

Oasis,

Are you familiar enough with MySQL to play with the tables?  If so, then try indexing the user_id field in the users table, and index the category field in the albums table.  Can anyone think of a reason why this would not help?

You can also run an optimize command to "defrag" a table is it has been modified a great deal, but that will necessitate an interruption of service.  The optimize command locks the table while it does its work.

-Hyperion

[Oasis]

Thanks tarique and hyperion!

I have found out the actual cause of the slowdown. It was apparently due to the script having to fetch the information of every single album in the gallery to create the listbox on the top right corner.

I have commented out the contents of function alb_list_box in modifyalb.php (since that is not actually very useful anymore when you have thousands of albums) and the speed has returned imediately. Now I'll just have to find a way so that it shows itself only to regular users and not to the admin.

[Oasis]

OK I figured it out...
Wasn't as hard as I had expected  :lol:

function alb_list_box()
{
        global $CONFIG, $album, $PHP_SELF;

//        if (GALLERY_ADMIN_MODE) {
//                $sql = "SELECT aid, IF(user_name IS NOT NULL, CONCAT('(', user_name, ') ', title), CONCAT(' - ', title)) AS title ".
//                           "FROM {$CONFIG['TABLE_ALBUMS']} AS a ".
//                           "LEFT JOIN {$CONFIG['TABLE_USERS']} AS u ON category = (".FIRST_USER_CAT." + user_id) ".
//                           "ORDER BY title";
//                $result = db_query($sql);
//        } else {
                $result = db_query("SELECT aid, title FROM {$CONFIG['TABLE_ALBUMS']} WHERE category = '".(FIRST_USER_CAT + USER_ID)."' ORDER BY title");
//        }

        if (mysql_num_rows($result) > 0 ){
                $lb = "<select name=\"album_listbox\" class=\"listbox\" onChange=\"if(this.options[this.selectedIndex].value) window.location.href='$PHP_SELF?album='+this.options[this.selectedIndex].value;\">\n";
                while ($row = mysql_fetch_array($result)) {
                        $selected = ($row['aid'] == $album) ? "SELECTED" : "";
                        $lb .= "        <option value=\"" . $row['aid'] . "\" $selected>" . $row['title'] . "</option>\n";
                }
                $lb.= "</select>\n";
                return $lb;
        }
}

[Tarique Sani]

Oasis - good work  I had removed that box in my local setup hence missed it

Well this should be documented and looked into - I guess for large setups it is best removed as having 1000s of albums will be a problem

Also the album manager script will have to be optimised for larger number of categories and albums

[Joachim Müller]

@oasis: please start a tracker on this and assign it to yourself. Thanks.

GauGau