Year: 2005

Categories
Technology Weblogging

The Survival Guide to LAMP: MySQL and Saving the Pig

In the last two weeks, two WordPress weblog sites have had their sites suspended or moved to interim servers because of performance issues. In both cases, the ISPs who hosted the sites (different companies) sent snapshots of the MySQL processes that caused the problems with the emails.

I worked with one of the sites offline, but the owner of the second site, Ampersand, posted the copy of the email he received at the WordPress support site. I grabbed a sample of it, as follows:

| 2073 | theenn2_amptoons | localhost | theenn2_MT | Query |
4 | Copying to tmp table | SELECT alas_posts.*,
MAX(comment_date) AS max_comment_date FROM alas_comments,
alas_posts WHERE alas |
| 2078 | theenn2_amptoons | localhost | theenn2_MT | Query |
4 | Copying to tmp table | SELECT alas_posts.*,
MAX(comment_date) AS max_comment_date FROM alas_comments,
alas_posts WHERE alas |

Plain as dirt what the problem is, eh?

Both of the weblogs are WordPress, but the SQL that generated the performance hit differed. With one, it was the latest comment plug-in; with the other, it was the SQL to support a category listing. In addition, this isn’t specific to WordPress–it could occur with the PHP-based version of Movable Type, ExpressionEngine, or any other MySQL based tools that have dynamic access.

Ostensibly, there is something wrong with these two sites. However, they’re only representative of what we’ll most likely see more of in the future. Our weblogging tools are becoming increasingly sophisticated, the data richer and more complex, the functionality modular and extensible by every person with a text editor and a yen–all packaged up in one reusable, standard, one-size-fits-all package. To make it all even more interesting, these applications are being installed on systems where you can get all you can eat for $5.88 a month, which means that a lot of sites need to be hosted on each server in order for the company to break even.

Things are bound to start breaking. But hey, it’s all fun, until you get that email that says your site is a pig, and it’s just been sent to the butcher.

Please! Won’t someone save the pig!

Now that we’ve established that the world is out to get our weblogs, let’s focus back on these problems, and in particular, the information that the ISPs sent.

In both cases, the messages contained a phrase “Copying to tmp table” and then what looks like a standard SQL query. If you look for this phrase in any of the WordPress code, you won’t find it–it’s a MySQL process that only shows up when you run SHOW PROCESSLIST within MySQL, or access the same from PHPMyAdmin.

Now, depending on your ISP’s tech person and how proficient they are with database optimization, you may be told that “Copying to tmp” is a ‘bad’ thing and shouldn’t happen, and therefore something is wrong and the code is crappy. Well, this isn’t true.

MySQL optimizes queries before they’re executed, to get the maximum amount of data in finished format with the minimum amount of processing and time. Part of the optimization can be to build a temporary table to hold an intermediate set of data before finishing the query. In addition, if the order the data (the sorting sequence) is on one column, but it’s grouped on another, or on a column in a different table, MySQL uses a temporary table.

There is a function, EXPLAIN, that provides information about how MySQL will execute a query. Developers use this in order to fine-tune the SQL so that the use of ‘expensive’ operations are avoided. If you have access to PHPMyAdmin, and run a query, the option to run EXPLAIN is provided with the results. Still, you can only tweak a query so much, and sometimes even the optimal SQL results in MySQL creating a temporary table.

When the use of a temporary table is always bad is when MySQL doesn’t have enough memory to hold all of the contents of the temporary table; the tool then needs to copy the contents to disk. Anytime MySQL has to go to the disk rather than memory, performance takes a hit. This shows up in the processes as:

“Copying to tmp table on disk”

However, what showed up with both of the weblogs that had problems is:

“Copying to tmp table”

An open question at MySQL asks whether this is the tmp table or memory, but from the impact to the servers, we can probably assume it’s to disk.

If it is, then the problem could be that tmp space allocated isn’t enough, and MySQL is having to write to the disk frequently. Or it could be, depending on your type of MySQL, that the maximum space allocated for memory is less than the maximum size allocated for the tmp space. Or other variations of settings at the database level.

Or it could be a badly written plugin, or too many plugins, or a cheap host that doesn’t allocate enough space for the sites hosted, or less than optimum SQL query, or even a trackback attack

Get used to sleeping with the pig

Ampersand’s site, Alas, a Blog gets between 1000 and 2000 visitors a day. It’s a popular site and gets lots of comments, and spam, of course, so several plug-ins were installed to help contain it. Evidentally, it was one of these plug-ins that started to have problems, because the query is not part of the WordPress code. In particular, if you look for max_comment_date in WordPress, you don’t find it.

However, with the second weblog, the query is within list_cats, a built-in WordPress function, and looks like the following:

SELECT cat_ID, cat_name, category_nicename, category_description, COUNT( wp_post2cat.post_id ) AS cat_count
FROM wp_categories
INNER JOIN wp_post2cat ON ( cat_ID = category_id )
INNER JOIN wp_posts ON ( ID = post_id )
WHERE post_status = ‘publish’
AND post_date_gmt < ‘2005-03-18 20:48:04’
AND cat_ID <>1
GROUP BY category_id
LIMIT 0 , 30

While Ampersand’s weblog problem was being discussed in the WordPress forum, a third person also had the same problem — most likely in a completely different area.

Bacon

With the growing number of ‘cheap’ hosting sites, and an increasing use of sophisticated PHP applications and MySQL queries, not to mention the extensibility of the tools, we’ll see more of this problem–especially as we demand more and more from our tools. Think about it: how many plugins are you using with your WordPress installation?

So what can you do? As a starter, you might want to look at that ‘good’ deal you get from your host. Not all inexpensive hosts cut costs and have less than optimal installations, but you’re less likely to have a host that will patiently work through problems with you if you’re only there for the 5.88 special.

You could also trim the fat by dropping plugins that you really don’t need, and make sure that whatever tools or applications or plugins you use are fully baked, i.e. have been through a healthy bug fix period.

If a problem does occur, make sure to file a report with the developers of whatever tool you’re using, providing all the information the host provides. The SQL used in the tool may not be optimum, and being informed of problems provides necessary feedback.

Hopefully this hasn’t been more info than you want or need (”too much sharing!”). At the least, if this situation comes up, you’re not going to be as intimidated when your host sends you an email that tells you …your site is a pig, fix it, or we’re kicking you off.

Categories
Burningbird Photography Places

Ads are gone

Here are some of the photos from today’s Alley Spring Mill trip. I need to return in about 3 weeks when the trees have started to green. And I also need to go about mid-morning, when the light on the Mill will be better.

It is a wonderous place, though. As was all the countryside on the trip down, even in winter with barren trees.

You might notice, if you access this post individually, that the ads are gone.

After reading several negative posts about AdSense this week from people who read my weblog–two new ones just today–I have decided to remove my ads. Since I don’t provide full content in my syndication feeds, I don’t want readers to have to install special software to remove the ads just to visit my site.

The money from the ads would have been enough to pay for my web site, my internet connectivity costs, and maybe even enough left over for a Ted Drewes Frozen Custard. However, I also didn’t like seeing the drilling ads come up with my ANWR writing.

Which is too bad, because I really like Ted Drewes.

Speaking of ads and making money, AKMA wrote on this today:

But y’all didn’t start blogging just for my entertainment. If blogging is putting bread on a few tables, buying toys for a few kids, putting together the down payment for a newlywed’s house, then I’m the last one in line to bemoan times past. It’s all changed, but do you know what? It was going to change anyway. It was going to change anyway, and while it’s changing, there are no people I would rather have those changes benefit than the wonderful friends I met back when none of us was making a cent off blogging.

Odd thing about all this is, of all the changes I’ve seen over the years–in the character of our writing, our interests, and who we interact with and how–making money or running ads was never a cause.

Categories
Weblogging

Character

I spent much of the day in the heart of the Ozarks, at Alley Spring Mill. The previous photos I’ve seen of it didn’t do it justice, and neither do the ones I took today. It is an extraordinary place — a mill on a crystal clear river with bright green and pure aqua waters; cut into a limestone cliff with odd rock formations dotting trails surrounding the area. I’ll have photos later and more about the trip, but I’m tired tonight — it is about an 8 hour round trip. I love the Ozarks, though. Each time I go I try a different route, and see a whole new aspect of the hills, and I’ll never tire of it.

However, today was not all daisies and daffodils (though there were a lot dotting the hills on the way — daffodils, not daisies). My enjoyment was shadowed by the fact that I wasn’t too happy with myself today.

I do have a temper, and will not hesitate to blast the hide off of idiotcy, but I’m not a ‘mean’ person, or at least, I try to avoid mean behavior. (Some will probably argue that I fail.) However, there’s a difference between being critical of a person’s action or behavior, and making statements about the person’s character; especially statements that can cause hurt or embarrassment. The former is being diligent, the latter can be ‘mean’.

I crossed the line between the two recently in regards to Jeremy Wright, when I moved to discredit the person in order to discredit than the event, and the reaction to the event. Sometimes we need to do this — the person is the event and the event the person, but I don’t think this was one of those times; the issue wasn’t about what Jeremy wrote, as much as it was about how other webloggers reacted to what he wrote. That, is what I should have focused on.

But, in a moment of frustration in the discussion thread, I cast doubts on Jeremy’s character in order to cast doubts on what he wrote–purely to strengthen my argument. I regretted doing so almost immediately, but had no way of deleting the comment; later, when Jeremy entered the discussion, I could have, and should have, apologized for bringing up the issue of his character, but I dug in. Got mulish.

There was no need for this: I am more than capable of forming a solid and well reasoned argument without doing so. A better argument, really, because the point I was trying to make got lost because of ‘character’.

Jeremy, my apologies for casting doubts on your character, when I should have focused on the event, and the reactions of other webloggers to the event.

Categories
Political

I have a bridge

Recovered from the Wayback Machine.

Here’s the End of the story.

Here’s links from:

BoingBoing
Neville Hobson
Kottke (quick link may scroll off page)
Politech
Media Culpa
Scoble
CBS Market Watch
Committee to Protect Blogging
A Blog Herald Entry

In fact, here’s the Technorati Cosmos for the story.

And, just for grins and giggles, this.

Now check out Joi Ito’s post and the comments. Especially the comments.

I have to go fix a bug I introduced into Wordform (yes, I can create bugs with the best of them), go on a nice walk, and then work on a long story about the environment that involves, ANWR, baby seals, and manatees, and features artwork by an up and coming young artist.

First though, some photos of Chain of Rocks Bridge (click on the first two for a larger version). Pretty, isn’t it? A bit rusty here and there, but still sound. Want to learn more about it?

Inquire within.

update

Here’s my final take on this issue, which I pulled from a response to Jeremy that I made in a comment in Joi’s weblog post (some editing, links added):

Here’s a scenario:

You showed up at the airport to fly to the job but you didn’t have the proper paperwork according to NAFTA professional regulations. You didn’t have the formal signed offer from the company, guaranteeing the job, and when you were asked for information about the company for verification, you didn’t have that either. I also imagine that when you were asked, you were probably ‘astonished’ that the guards would even think that you were offered a consulting job in the states without once talking to someone on the phone.

When you were asked which NAFTA professional classification you would be working on, you probably proudly proclaimed “Weblogging!” This isn’t on the list. I couldn’t go up to Canada to work as a ‘blogger’, either. But writing was on the list, as is IT and development.

Then they asked you for the verification information about your residency and the position — again, as per clearly visible, easily accessible NAFTA requirements.

All the while, what were you doing, and how were you acting? Were you challenging, nervous, uptight you didn’t have any of the stuff you needed, maybe even angry? Were you still suffering the effects of your move, and confused and agitated?

So rather than a person challenged ‘just because they’re a blogger’, is there a possibility you were challenged because you didn’t have the paperwork you needed, were worried about getting the job, frustrated and pissed at the ‘paperwork’ and ‘regulations’, and reacted accordingly?

But what the world is seeing is that you were abused at the border, seemingly without cause, and primarily because you said you were a weblogger.

So what’s the harm in all of this? FUD.

One place people shouldn’t be nervous at is crossing the border into another country. Now, there’s a whole lot of Canadians reading this, who are going to be a lot more nervous.

I read in comments in one of the weblogs linking to you a person who said that they had crossed the border many times on business the last few years, and not only had no problems, were actually welcomed:

It’s the luck of the draw, always has been. I lived 5 years in the US, traveled often and never had problems coming back: in fact, was always made to feel welcome. But one always reads about these things and I’m always nervous.

But one always reads about these things… People are discounting their own experiences, because of what you, and others, write.

That’s the harm. This blind belief that what a weblogger writes is the absolute truth. Never taking into account our own personal biases. Never challenging the events as they’re told, because they only reflect one person’s viewpoint. Never even attempting to see if there’s more to the story.

No, just link and tell everyone, well, we webloggers, we’re persecuted all over the world. (That’s the new weblogger thing now: the persecuted citizen media.) Now a Canadian weblogger can’t even enter the US without being hassled! Why? Because it’s _such_ a great story. Not to mention that we just love to demonize the DHS and border guards.

If I’m ‘mad’, since you want to reduce this to an emotional reaction, it’s at the webloggers who linked to you and who didn’t once look beneath the surface of the story; not at you.

I’ll take this at face value: I am sorry you lost the gig, and that you were hassled, and that you were turned away from the border. I know what it’s like to be worried about money, and I don’t have a family like you do.

But I don’t think Canadian webloggers have to run from the borders, screaming in terror. Nor should they expect to be hassled when coming down to this country to work, if they’re prepared according to NAFTA regulations.

Categories
Media

Firefly and Battlestar

I rather liked tonight’s episode of Battlestar Galactica. It was twisty and turned a lot and I even liked the subplot with the Adam and mechanical Eve back on Caprica. Loved the last scene and the jazz and the whole crew dancing together.

The development of the female characters in the show continues to be terrific. They are wonderfully strong, independent, and, most importantly, consistent. I do think there are too many characters and subplots to be managed effectively, and I’m surprised more people haven’t noticed that Baltar talks to himself a lot. Still, it’s rich, and I’m glad to see SciFi hasn’t ‘mainstreamed’ it.

If you’ve not seen Battlestar and don’t have the SciFi channel, you can at least see the first episode, as SciFi has released a complete recording of the first episode including outtakes. (Thanks to SFCrowsNest for link.)

However, as good as Battlestar is, it doesn’t compare with Firefly, and I wanted to extend a thank you to those of you who recommended it. I rented the DVDs, watched them, gave them to my roommate, he watched them and then immediately went out and bought himself a copy. Now I’ve watched the series again — especially the Train job, which is one of the better of the episodes, though Out of Gas and Trash are, also, exceptional.

Can’t wait for the movie. Perhaps Firefly fans who are also bloggers should get together in one city and go see it at the same time. Anyone want to see the release of Serenity in St. Louis, September 30th?