FAQ: Planet Lotus upgrade / move.

[Yancy Lent]

Please use this thread to post any issues; concerns, etc regarding this well overdue move.

Thanks,
Yancy

[Yancy Lent]

Update.... 9:36 AM 3/18/2008

The VSD is setup.
Figured out (with help) how to use VSFTP; much better then ftp.
Files have been copied.
Working on a dry run of the database move.
Still have to work out how the a record move will happen. I want to coordinate that at night so it has time over night to cook.

[Yancy Lent]

Update: 9:34 PM 3/18/2008

New server up and running. Bug testing for the next few hours.
The A Record took a couple minutes to switch; was NOT expecting that.
Forum Just moved over. This is the first post; and a test. So far so good. I highly recommend SMF over phpBB ive used both and SMF is like mysql just what you need.

Next will work on the feeds.

[Yancy Lent]

Update: 11:42 PM 3/18/2008

The main stuff has been moved over.

To do:
Create the cron jobs to update the site. For now i have the old fashion way of running scheduled bat files.
Test dogears.
Figure why keywords isn't working; then back-fix all of them.
Work on the site performance. MySQL tweaking, php tweaking, apache, select call tuning, indexes. In time it will speed up.
Create cleanup routine; i have a list somewhere. Basic data scrubbing.

_bed

[eknori]

The "Notes Relation" indicator does not seem to work correctly since 18-Mar2008 10:00pm

[Yancy Lent]

The "Notes Relation" indicator does not seem to work correctly since 18-Mar2008 10:00pm

Yeah, this what i termed:

Figure why keywords isn't working; then back-fix all of them.

in my post above. Thanks for catching it. It is amazing how different it looks with out the different shapes. <>

[hoss.macian]

Hey man,
Noticed this morning that on both my Palm TX, BlackBerry and even via Firefox, when trying to access http://mobile.planetlotus.org, the request returns "hi sharepoint".  Just an FYI bud.

[Yancy Lent]

Im on it!

How did i miss this!

Thanks!!!

[hoss.macian]

Im on it!

How did i miss this!

Thanks!!!

I'll give you a good talking to about that when I'm in Boston at Admin2008 

[clinfoot]

Some post move issues:

No Lotus relevant keywords in this post - all posts, even Lotus relevant ones.
Speed - I know you have tuning to do but it is rather slow.
mobile.planetlotus.org no longer resolves at all in DNS here - could be a propagation or TTL issue, if you have made changes.

[clinfoot]

And BTW - if feedburner is now working for you, could you please revert to polling my real feed? Thanks.

[Yancy Lent]

Update... 5:12 PM 3/19/2008

mobile.planetlotus.org is up and running!

Next some updates, testing feeds and the keyword indicators.

[Yancy Lent]

3 VERY inefficient Select statements seem to be the cause of the slow down. It was so easy to blame the hardware and configuration 

If anyone would like to take a crack at it...

This is what finds what blogs are hot....

SELECT count(*), hitsTABLE.rssid, rssTable.name, rssTable.blog, rssTable.type
FROM `hitsTABLE`, rssTable
WHERE hitsTABLE.rssid = rssTable.id and
(hitsTABLE.created > (DATE_SUB( CURDATE( ) , INTERVAL 5 DAY )))
GROUP BY hitsTABLE.rssid
ORDER BY 1 DESC
Limit 6

The EXPLAIN says... Using temporary; Using filesort (the crux of the problem, due to the area in bold above.)

The MySQL version is 5.0.45
hitsTABLE.rssid = rssTable.id are both indexed.

[DarkRedSpiral]

Yancy

My first instinct would be to subquery the Hitstable file without a join to get the 6 records
and then do the join after that. This would allow the Count to be done in isolation on a single
table forcing the join onto the result table which will have only 6 entries rather than the 1000's
you currently have.

Try this SQL

SELECT * FROM (
                         SELECT count(*) as HitCount, hitsTABLE.rssid
                         FROM `hitsTABLE`
                         WHERE   (hitsTABLE.created > (DATE_SUB( CURDATE( ) , INTERVAL 5 DAY ))
                         GROUP BY hitsTABLE.rssid
                         ORDER BY 1 DESC
                         Limit 6
                      ) AS TZero, 'rssTable' WHERE TZero.rssid = rssTable.id

Steve

[Yancy Lent]

Holly Cow!

It's still reporting the "Using temporary; Using filesort" in the EXPLAIN...

But, its gone from 6.5ish seconds to 0.25ish seconds!

Thanks Steve!

I am still pondering running a batch ever 10 minutes to populate a temp table so i get it down to the .002 range. But this is a great temp fix!!