Massive performance problems after 4.6 -> 4.8c update

[dontom]

Hi,
i did the upgrade last monday night - after this the load on our database and our webserver tripled, so we had to shut down the albums totally because our whole site was getting very slow.

I just dropped 4 index-fields manually, that somehow were not dropped during the update - could this have caused this or do you have an other suggestion?

Our installation 26,899 users, 31,321 fotos and 27,851 comments - using vb3x user integration.
Our servers run using PHP Version 4.3.7 /w PHPA and MySQL 4.0.18
Tom

(attached the db load - our problem was the webservers load which was often > 30.00)

[Chuck S]

Dropping those 4 index's should help on larger installs--CORRECT. That is why that was added to the 4.8 update.

[dontom]

ok - i'll see if it is better tomorrow,
Tom

[WebDork]

Could you please post a bit more information about this ? I just upgraded and have around 190,000 users. I know what load is of course - but what is the function of the index files and should they be dropped ? Is the current code accessing them ?

[Chuck S]

If your index's did not drop when you ran the upgrade script you would have been notified big and bold.

You can safely go in and delete those in phpmyadmin.

Here are the queries so you see what was dropped

$query[] = "ALTER TABLE {$pp_db_prefix}categories DROP INDEX lastphoto";
$query[] = "ALTER TABLE {$pp_db_prefix}categories DROP INDEX thumbnail";
$query[] = "ALTER TABLE {$pp_db_prefix}iponline DROP INDEX userid";
$query[] = "ALTER TABLE {$pp_db_prefix}photos DROP INDEX approved";
$query[] = "ALTER TABLE {$pp_db_prefix}photos DROP INDEX date";

[WebDork]

Thanks - I did get errors that drops failed - but didnt pay attention as they were only drops. Will check it out.

[WebDork]

Uh oh. I hope I havent just done something dumb.

From your above post I understood that I could simply go in via phpmyadmin and delete those indexes from those tables ? I did that no problem, but now my site doesnt load ? Its like the index.php doesnt exist. Any ideas ? Help

[Chuck S]

Any Ideas?

YUP You did not drop the index's BUT SOMETHING ELSE. I would bet my life you dropped the actual table structure elements NOT THE INDEX

[WebDork]

Hmmm - I hope not - but probably did...

I thought to drop it was to click on this next to the above fields you mentioned:

Content visible to verified customers only.

But sounds like that drops the whole thing not just the index ?

If I did do the wrong thing - please tell me it can be fixed...

[Chuck S]

Yes you dropped the whole thing not the index

INDEX'S are listed underneath the structure in phpmyadmin. You going to kick yourself when you see that

Use these queries in phpmyadmin

ALTER TABLE iponline ADD userid VARCHAR(128) NOT NULL default ''

ALTER TABLE categories ADD lastphoto INT(11) UNSIGNED

ALTER TABLE categories ADD thumbnail INT(15) DEFAULT '0' NOT NULL

ALTER TABLE photos ADD date int(15) NOT NULL default '0'

ALTER TABLE photos ADD approved tinyint(2) NOT NULL default '0'

[WebDork]

Thanks heaps for that. I have run those queries. Do I now need to run something to populate the data - or will it fill itself in over time ?

[WebDork]

What I now seem to have is a site with no images but a MASSIVE approve queue. Is there a way I can set them to all approved ? No thumbs etc either in the approve queue.

[Chuck S]

UPDATE photos set approved='1' WHERE approved='0'

Now you try under scan database Check here to recalculate photos/posts numbers and check children.

I am assuming this is all you dropped.

[WebDork]

Thanks for all that - Im back up and running. Great relief.

Only last remaining problem seems to be all photo galleries have last photo uploaded set to "Thu Jan 1, 1970 12:00am by admin" and it doesnt seem to be updating.

[Chuck S]

Thats because the script never takes into account someone dropping key table structures. You should be fine when another new photo is uploaded

If you have a very recent database backup you could redo things otherwise the best thing to do since you dropped the photos date column would be to run this

UPDATE photos SET date='1087836012' WHERE date='0'

and then run another update counts

This will set all your photos and your last uploads to today since right now everything says the beginning the the epoch since the field is by default 0