« Warning: Me Possibly Acting Stupidly About to Commence | Main | Technical Post Chaser »

May 25, 2007

Technical Notes and Solicitations

For all of you wondering how I might possibly have blown up the site yesterday, here's what's going on.

This blog, like most blogs, has a SQL database, in which all the data pertaining to the blog resides. The permitted size of that database is 100 MB, and currently the database is well above that size. The technical folks at 1&1, my host provider, say that it's not like I'm going to get in trouble for this, but that the database itself has an increasingly large chance of becoming corrupted as time goes by. I'm not geek enough to know anything about MySQL, so I can't judge the accuracy of this statement; be that as it may I'd rather not have the Whatever go kerplooey.

At the same time the Movable Type infrastructure of Whatever is becoming increasingly unstable, partly due to age and partly due to the fact I am apparently not competent enough to update the software, and every time I try I break something with the way it works and I am not savvy enough to fix it when I do. Basically the place is coming down around me, albeit in slow motion, so none of you on the outside would notice.

This presents both problems and opportunities. The opportunity lies in that this would probably be a fine time to do a significant revamp of the Whatever and either do a complete, clean installation of MT, or look at some other option that is robust enough technically to handle 25,000 visitors a day. The problem lies in dealing with previous entries (and attendant media and their URL paths and etc). The last time I did a major revamp of the site, back in 2003, my solution to this issue was simply to leave out everything I had written prior to the switchover. I don't think that's an optimal solution now.

Underpinning all of this is the database issue; I think it would be lovely to leave the present database as is, as an archive, and plug in some sort of blogging software to access the entries/comments therein while the "new" Whatever runs off a new database. The problem with this is that I don't know how to do it. I tried doing it with a WordPress install I did on my scalzi.info domain (very easy to do, incidentally -- so much easier than MT), but was confronted with two problems: Just making the WP install write to the current database does not make it display the information therein, and using the "import" function is not useful, in no small part because I can't get my MT install to export a significant percentage of the blog posts using its export function. I've either screwed up the software in some fundamental way or it's simply that the Whatever is too damn big (the third option is that I'm incompetent in using the software, which is, as noted earlier, a very big consideration).

So, in sum: This is what I'm thinking about going forward --

1. Looking at blogging/community software to maintain and build out Whatever/Scalzi.com and possibly some of the other domains I own (which is to say, possibly adding the option for a place for folks to post their own entries, etc) -- something that can point to more than one database for additional iterations of the software would be optimal;

2. Creating a static archive of Whatever posts to this point (i.e., no additional comments or entries into that database) that is easily accessible -- and if at all possible still maintaining the previous URLs, so everything isn't broken;

3. Finding someone who is more technologically competent than me to help me build this out and install it for me. No, I wouldn't expect this work to be done for free.

I'm taking suggestions on how to accomplish all three, so if you have ideas for software, how to build out an archive (or even if it's necessary), or if you feel you're just the sort of geek who can help me with this sort of project, go ahead and comment in the thread or drop me an e-mail.

(CAVEAT: Please don't comment in this thread just to comment, or to offer "advice" that's not actually on point, i.e., don't be "helpy" when I actually need helpful here, because that's just going to irritate me. There will be other comment threads to be silly or tangential. Please make this comment thread on point. Thank you.)

Posted by john at May 25, 2007 10:21 AM

Trackback Pings

TrackBack URL for this entry:


Kate Nepveu | May 25, 2007 10:31 AM

For #2, all you have to do is (1) switch to static publishing and republish everything if you aren't using it already (meaning that HTML files for everything get created on your server); (2) turn off commenting in the MT interface; (3) walk away.

You didn't ask about this, but you can also write a new template in MT to export things a bit at a time: http://www.sixapart.com/movabletype/kb/imports/customized_expo.html

Kate Nepveu | May 25, 2007 10:39 AM

PS: if I were you, and if I decided to stick w/MT and go for a clean upgrade, I'd leave the site as it is; export piecemeal as above; clean install MT into a different directory and database; import everything; and then when it all worked, backup the prior version and then republish into the "correct" directory.

It's what I did when I did my switchover from Blogger to MT, and it's a safety-net all the way.

I'm not *expert* at MT, so I doubt I could do this as fast as other people; but I have mucked around some and could certainly do the steps above if no-one else steps up.

David Huff | May 25, 2007 10:41 AM

Well, if you're up for moving to a different provider, I could heartily suggest birdhouse hosting: http://hosting.birdhouse.org/

The owner, Scot Hacker (yeah, that's his real name :) is a very bright & helpful guy and I've done business with him myself. So maybe the answer is, "Find a hosting service that'll do this jazz for you, instead of becoming an expert at it yourself."

Just a thought. Good luck!

John Scalzi | May 25, 2007 10:44 AM

David Huff:

Actually, I'm not at all interested in moving host providers. 1&1 gives me something like 200GB of Web space and 3TB of bandwidth a month (i.e., far more than I am ever going to use) and have additionally given me exemplary service otherwise.

It's not a hosting issue, in other words.

I'm not interested in becoming an expert, hence the call to geek to do it for me. However, moving everything to a new host provider add just another layer of complexity, and I'm not up for that.

Paul Guinnessy | May 25, 2007 10:49 AM

One of the reasons our housing association uses BlueHost.com is that it automatically upgrades your wordpress software at a click of a button. It makes maintence easier for the technically challenged.

Alternately you may want to consider a more robust product such as Joomla http://www.joomla.org/ that provides a more "content management type" experience and should be pretty scalable without requiring too much programming experience. Plus its free.

Kate Nepveu | May 25, 2007 11:05 AM

PS^2: can someone with MySQL experience confirm the 100MB limit? I strongly suspect that's a host limit not one intrinsic to MySQL; Making Light, for instance, *must* be over that size.

John Scalzi | May 25, 2007 11:10 AM


I suspect the same, actually -- the tech support dude said size wasn't really the issue, stability was.

Incidentally, I'm generating static archive pages even as I type this. Go me!

Patrick Nielsen Hayden | May 25, 2007 11:10 AM

Indeed, I was just in the process of looking that up when I saw Kate's post. Quite right: Making Light's MySQL database is 191 megabytes, and we've been told by MySQL gurus of our acquaintance that it's in good shape.

We are in kind of the same position as John--Movable Type has become too complicated for our level of technical competence, it was never designed for the kind of thousand-message comment threads we often get, and there are a bunch of features we'd love to implement if we could figure out how. But on the specific matter of MySQL, we've actually given knowledgable MySQL people permission to poke around in our database on more than one occasion, and they say it's perfectly healthy.

Dennis Deery | May 25, 2007 11:11 AM

I would say the 100M limit is probably the host's preferred limit. MySQL should have no problem handling a database in excess of 1GB size.

Jp | May 25, 2007 11:28 AM

MySQL can have problems with large individual tables, but can handle much larger databases than that without any problems at all (production high-traffic databases of 50Gb plus aren't an issue for it....)

The other alternative is to port the data out of the MT database into the Wordpress schema at the DB level rather than trying to use the inbuilt tools, which shouldn't take more than a day or two for someone with SQL and Perl/Python/language of choice skills.

Caveat: I know nothing of the particular MT/Wordpress DB schemas (but can't see how either would do or store something so esoteric that it would invalidate the general approach).

The advantage of that approach is that when it's done, you basically have a completely standard install of the new software which won't complicate future maintenance.

Kate Nepveu | May 25, 2007 11:34 AM

Incidentally, I'm generating static archive pages even as I type this. Go me!

Go you!

I think MT recommends dynamic archive pages only for disk space reasons, which is certainly not applicable in your case. Static individual archives take a while to publish, but since you tend to do design changes in a linked CSS file, there's no reason not to use them: they'll still be there if your database goes kablooey, they don't hit the database to be generated when someone looks at them, and you won't need to republish them often, if at all.

(After my old web host had horrible database problems while I was using dynamic publishing, well, I just don't feel comfortable using dynamic publishing anymore. And I was able to figure out enough PHP to get along--not to mention that more plugins work for static publishing.)

Stephen Granade | May 25, 2007 11:44 AM

One possibility for creating a static map of your site would be to use wget. With some fiddling (or, more likely, a wget-knowing geek to fiddle for you), wget can dump a copy of your website to static pages exactly as it looks to the outside world. In other words, any mod_rewrite tricks that are going on would be preserved, the permalinks would all be the same, etc.

It would be more initial setup but vastly less in-process work.

To expand on what Jp said, you might get someone to write you a script to pull data from your MT databases in the format WordPress expects for importing. That way you could then continue the MT-to-WordPress porting instructions as given.

Stephanie | May 25, 2007 11:53 AM

The WordPress Codex page on MT migrations includes a link to this helpful guide on migrating large sites. You're right; the site is too big for MT's export function. The trick is to copy everything to a local environment so you're not hampered by the host's limits on CGI and PHP scripts.

I could help with this, but I have some prior commitments that will keep me tied up for the next three or four weeks.

Post a comment.

Comments are moderated to stop spam; if your comment goes into moderation, it may take a couple of hours to be released. Please read this for my comment moderation policies.
Preview will not show paragraph breaks. Trust me, they're there.
The proprietor generally responds to commenters in kind. If you're polite, he'll be polite. If you're a jackass, he'll be a jackass. If you are ignorant, he may correct you.
When in doubt, read the comment thread rules.

Remember Me?

(you may use HTML tags for style)