I have a question about what might be running on my server after an upgrade.
Background: Previously helped client run the upgrade, had many issues. I restored her database and original file structure, then upgraded from V1 to V2, then did the next upgrade from V2 to V3. The upgrade went flawlessly that time - no issues whatsoever.
Now that the upgrade is completed, I still have users locked out so noone can get to the eFiction database. However, I notice a ton of processes going on. I'm wondering where these are coming from. Example from mysql "show full processlist"
2539 peja localhost wwomb Query 714 Sending data SELECT COUNT(DISTINCT author.uid) FROM fanfiction_authors as author LEFT JOIN fanfiction_stories as stories ON stories.validated > 0 AND (FIND_IN_SET(author.uid, stories.coauthors) > 0 OR stories.uid = author.uid) LEFT JOIN fanfiction_authorprefs as ap ON author.uid = ap.uid
2645 peja localhost wwomb Query 680 Sending data SELECT COUNT(DISTINCT author.uid) FROM fanfiction_authors as author LEFT JOIN fanfiction_stories as stories ON stories.validated > 0 AND (FIND_IN_SET(author.uid, stories.coauthors) > 0 OR stories.uid = author.uid) LEFT JOIN fanfiction_authorprefs as ap ON author.uid = ap.uid
2700 peja localhost wwomb Query 668 Locked UPDATE fanfiction_stories SET count = count + 1 WHERE sid = '32960' LIMIT 1
2703 peja localhost wwomb Query 668 Locked SELECT stories.*, author.penname as penname, UNIX_TIMESTAMP(stories.date) as date, UNIX_TIMESTAMP(stories.updated) as updated FROM (fanfiction_authors as author, fanfiction_stories as stories) WHERE author.uid = stories.uid AND stories.validated > 0 ORDER BY rand( ) DESC LIMIT 1
2707 peja localhost wwomb Query 668 Locked SELECT author.penname as penname, author.uid as uid, story.*, UNIX_TIMESTAMP(story.date) as date, UNIX_TIMESTAMP(story.updated) as updated, story.validated as valid FROM fanfiction_stories as story, fanfiction_authors as author WHERE story.sid = '4624' AND story.uid = author.uid
These will run with tables locked, etc, for a while. I'll notice the tables being updated on the backend in the /var/lib/mysql/$DATABASE directory so I know they're doing something. Where can I find more information about this?
Walter
Those look like normal page views but taking an abnormally long time rather than post-upgrade processes. Try switching off the random story block - I notice amongst the queries is:
SELECT stories.*, author.penname as penname, UNIX_TIMESTAMP(stories.date) as date, UNIX_TIMESTAMP(stories.updated) as updated FROM (fanfiction_authors as author, fanfiction_stories as stories) WHERE author.uid = stories.uid AND stories.validated > 0 ORDER BY rand( ) DESC LIMIT 1
This is from the random story block and it has very poor performance. It could be causing your disk to thrash from sorting temporary tables all the time slowing down the rest of the site.
Odd - I limited access to the site in httpd.conf saying it was available only from my IP addy. Nobody is hitting the eFiction database, according to my apache logs. That's why I was thinking it was post-upgrade processes. Given that there are 2000 authors, 300+ categories, and 15,500 stories, I figured any post-processing would take a while.
