Here’s my bookmarked links for August 23rd through August 28th.  I’m using an automatic plugin to build this list, and I can see that this probably isn’t going to work – I just found way too many things interesting in one week, and it doesn’t break stuff out into categories.  Blogger fail.  Here it is anyway as an example of What Not To Do during my Better Blog Week:

• Dual-screen gScreen laptop gets pictured, hopefully launching this year – The screens slide out sideways. This oughta be a whole lot of fun when used in the middle seat of airplanes.
• The Query Optimizer and Parameter Sniffing – A classic SQL Server performance issue illustrated with clear, easy-to-reproduce scripts in AdventureWorks.
• I Love Getting Stuff In The Mail… Thanks @BrentO and Quest Crew!! – Colin got his new SQLServerPedia t-shirt. I love these, and I’m jealous of everybody who got theirs, because I don’t have one yet, hahaha.
• SQL Server State of the Union – Before your annual review, you need to start thinking about how you can justify your cost and your job. Chris’s list of high-level environment status checks is a good place to start.
• eBay Find of the Day: Gorgeous 1964 Lincoln Continental has a 723-hp secret – This, ladies and gentlemen (well, mostly gentlemen), is the reason why the Buy It Now button was invented. And also, the American Express card.
• Yelp for iPhone’s hidden augmented reality feature – Wow – you can “see” restaurants by looking through your iPhone 3GS. This app is already great even without the augmented reality part.
• People of Walmart – Hint: these aren’t the employees. Pictures of random shoppers. Very, very random.
• YouTube – HIGH SPEED NINJA ACTION – It’s fair to say that when the ninjas are wearing roller skates and the lead actor can’t outdrive them, it’s a low-budget flick.
• Top 5 reasons to run for the PASS Board of Directors – By Joe Webb.
• Iomega’s ix4-200d: A Killer Desktop Storage Array – VMware approved, Windows iSCSI approved, and under $1,000 with four drives – this is going to be a barnstormer. I’m buying one as soon as we get to Chicago. Great storage appliance capabilities.
• Come on 64bit so we can leave the mem…. – Microsoft PSS explains the MemToLeave issue on x64 servers, saying there’s no such thing.
• Adding geo-redundancy to failover clustering – Paul Randal discusses the options to add a disaster recovery datacenter option to your SQL Server failover cluster.
• Why isn’t my SharePoint Environment Social??? – Joel Oleson gives 10 ways that you can tell your environment isn’t social enough, and how to fix it.
• PASS Board of Directors Nominations – Tom LaRock (a current PASS Board of Directors member) sends out his call for volunteers and a list of people he’d like to see run, including me, and it ain’t happenin’ this year.
• Clearing the Windows page file and its effect on server performance – If you set up Windows to clear the page file on every boot, stop to think about how big the page file is and how fast your drives are. It could be the slowest thing in your boot process.
• PASS Call for Nominations – Andy Warren (a current PASS Board of Directors member) puts out the call for volunteers.
• SQL Bacon Nugget of Excellence Award @SQLGal – For the Enterprise Policy Framework, an excellent extension to SQL Server 2008′s Policy-Based Management.
• Web Page Analyzer – free website optimization tool website speed test check website performance report from web site optimization – Checks your web site to see where you need to improve performance. Used when I installed W3 Total Cache on my site. The minify and compression options dropped my page size by around 80%.
• The Dilbert Blog: Champagne Moments – Scott Adams talks about why he never cracked open the champagne – he was always worried about the next step.
• Twitter’s Golden Ratio (That No One Likes To Talk About) – You can’t really follow everybody who follows you. As your social network grows, there’s going to be people who follow you that just don’t interest you. This isn’t a bad thing.
• Tac Bac – Tactical Canned Bacon – The only way we’re going to be able to survive the coming zombie onslaught while holed up in our bunkers: Tactical Bacon.
• Solution to Encyclopedia Brown and the Case of the Data Corruption – I’m not favoriting this because it’s got Quest Toad, but because it’s a really, really, REALLY well-written blog post. If you wanna be creative with a SQL Server blog post series, David Stein shows how it’s done.
• SQL Server SAN Migration – Things to think about when you change your server’s storage.
• Open Windows Firewall Ports for SQL Server the Easy Way – Use scripts to open the Windows Firewall ports without pointing and clicking.
• Treating The Results of a Stored Procedure as a Table Expression – Awesome trick! Take the output of a stored proc into a table without knowing in advance what the output will look like. Genius.
• VMware virtualization – Where do I start? – Good getting-started article on picking your first VMware platform.
• Getting to PASS on the Cheap | Jeremiah Peschka, SQL Server Developer – Want to go to the PASS Summit in Seattle? It’s the best SQL Server learning event I’ve ever attended. Jeremiah shows you how to convince your boss to pay for it.
• Wikipedia To Require Editing Approval – We’re ahead of the curve at SQLServerPedia – we’ve been using the same “flagged revisions” code to have our editors approve changes before they go live. I’d like to think they took a cue from us.
• Made2Manage Lacks Referential Integrity – David Stein talks about an ERP application that doesn’t enforce integrity inside the database. “Trust, but verify.”
• The Rambling DBA: Jonathan Kehayias : Troubleshooting the SQL Server Memory Leak (or Understanding SQL Server Memory Usage) – More about MemToLeave.
• Overview of Gemini features – Screenshots of SQL Server’s new Excel-based client tools for business intelligence.
• PASS Board of Directors – Call for Nominations – Want to help the SQL Server community in a big way? The Professional Association for SQL Server has a Board of Directors that puts in a ton of work to advance community interests. In this post, Steve Jones lays out some suggestions for folks thinking about running. I think it’s a great list.
• Kickstarter – Like micropatronage: sponsor artists to produce projects (music, visual arts, performance, etc) and you get something back from the artist.
• The Great DBA Schism – Rod Colledge lists the different kind of DBA job titles. He’s 100% right. The cloud will change this over time, but not in the very near future.
• Backup Times – Script to show how long a backup will take to finish. (Disclaimer – I haven’t tested this.)
• Let me Show You Inside a Secret Blogging Alliance – Several bloggers banded together to promote each other. The article explains what they did for each other, and how they gained.
• Think Differential Backups Aren’t Worth The Hassle? Think Again Using LiteSpeed for SQL Server! – Whitepaper I wrote about how painful differential backups are, and how you can make them easier with Quest LiteSpeed v5.1′s new SmartDiff. There’s a video of me talking, and I get a chuckle out of that.
• Pain of the Week: Top 10 DMVs for Easier SQL Server Monitoring – Free hour-long webcast by me and Tim Ford about how to use SQL Server’s Dynamic Management Views to monitor your server.
• ALL, ANY, and SOME: The Three Stooges – Brad Schulz explains the rarely used SQL operators ALL, ANY and SOME. (No, I’d never used them either.)
• PASS Update #13 – Andy Warren discusses changes to the PASS Board of Directors election process. This time, the Board members will be elected prior to the PASS Summit.
• xkcd – A Webcomic – Tech Support Cheat Sheet – Show your friends, family, and neighbors how to be their own computer expert and stop calling you.
• Wibiya – Really slick-looking toolbar that sits at the bottom of your blog and shows info from comments, Twitter, Facebook and more. They’re in private beta, but I signed up for it after I saw it on another blogger’s site.

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

The good news: I’ve been getting a lot of web visitors lately.

The bad news: I’ve been getting a lot of web visitors lately.  The last dot is April (the down-slope is May, Google’s graph labels are goofy.)

Visitors - Up Up and Away!

I don’t write my stuff in order to get hits.  I’ve always taken a simple approach to what I write: if I’ve got a problem, and I can’t find the solution quickly online, then I document my solution on my site.  Sometimes it’s about SQL Server, sometimes it’s about Twitter, and soon I’ll have a big post on how to build a Hackintosh.  The thing is, when you write something useful, other people do come visit, and it really starts to build up steam.

How I Started: A Shared Web Host

For years, I’ve relied on a shared web host plan from HostGator and been very happy with it.  For about $25/mo, you get 24gb of drive space and 250mb of bandwidth.  In April, I used around 3gb of disk space and 25gb of bandwidth to serve about 50k visitors.

However, as a SQL Server DBA, I can tell you that storage space and bandwidth have nothing to do with performance.

You can serve static HTML files all day long from a cheap shared web host, but when you introduce applications like WordPress, MySQL and 3rd party plugin applications, suddenly you’re talking about CPU and memory capacity issues.

The first workaround is to use caching: if your web site isn’t changing that fast (like if people aren’t leaving comments like crazy) then it makes sense to cache as much as possible.  Serve pages out of memory instead of rebuilding them every time, and you’ll cut down on the number of requests sent back to the database.  For WordPress bloggers, that’s as simple as using the plugin WP-Super-Cache: just install and enable it, and it automatically serves cached pages to your visitors.

Caching Isn’t That Easy

I lied: caching isn’t as easy as installing a plugin.  Oh, sure, that’s what the sysadmins will tell you, but it only works if all of your other plugins work correctly with WP-Super-Cache.  How can you tell?  Well, uh, you can’t.  The first problem I ran into was that the MobilePress plugin (which makes your site look better on iPhones and Blackberries) doesn’t always work correctly if WP-Super-Cache is enabled.  I decided to abandon mobile support in favor of caching, because I didn’t want my site to fall over when I got linked by someone famous.

Later, I played around with plugins to display ads on my blog if the visitor was coming from a search engine.  Statistics show that visitors from Google stumble in looking for a particular solution, and they’re more likely than other users to click ads.  Bloggers should avoid showing ads to their regular readers, because those folks aren’t tempted to click ads.  I dabbled with running ads on my old posts, and I ended up making around $100/month.  However, when I ran ads, the caching didn’t work correctly: they would cache the ad status based on the first visitor to the page.  If that visitor was eligible to get ads, then everybody who saw the cached page also saw ads.  I could disable caching, and then everybody got the right content, but then I was running a risk of putting too much load on my web site again.  That time, I decided to abandon ads in favor of caching.

This past Sunday I redesigned BrentOzar.com, and in the process, I temporarily disabled caching so that I could test my changes in several browsers & OS’s in rapid fire.  Sundays are usually low-traffic days, so I wasn’t too worried about load.  Erika and I drove over to my parents’ house to say goodbye before they embarked on a road trip, and when I came back home, my web site was down.

When Sysadmins Attack

Sherri Shepherd, co-host of The View, had Tweeted a link to my Twitter tips.  (Side note – after chatting with her for a while, I can see why she’s a host – she’s one of those people you just naturally want to open up to.)  She had about 68,000 followers at the time, and enough of them clicked on the link to make my web host notice.  HostGator’s sysadmins shut my site down for using too much CPU/memory, but they didn’t call me to tell me that.  Instead, they simply changed permissions on one of the PHP pages on my site so that WordPress wouldn’t work.  I had no idea my account had been shut down, so I started troubleshooting the site.

I found the permissions problem, contacted HostGator, and things went rapidly downhill from there.  I’m not going to go into details here because it’s not going to fix the problem, but I’ll give one particular lowlight: I was told by an engineer that I would need to call back later after the security team was done with their current stuff.  I wasn’t allowed to hold – I had to call back later when it might be a better time for them.  Riiight.

Douglas Hanna, the Customer Service Manager for HostGator, followed up with me the next day:

“I really can’t apologize enough for what seems like you serving as a example of Murphy’s Law at HostGator. Issues like this are difficult to deal with in the first place and I completely understand your frustration when we don’t deal with the issues properly on our end.”

Douglas and I had a long and constructive email thread about the problems I encountered, and he’s already started taking actions to make sure they don’t happen again.  At that point, though, I’d already moved my main site onto another hosting provider (MediaTemple) temporarily while I research the best dedicated hosting provider for my needs.

When You Should Upgrade Your Hosting Plan

In the process of researching hosting plans, I ran across a good summary of WordPress loads by David Seah:

• 500 pageviews a day will be your first sign of strain if you’re using one of the el-cheapo $5/mo “unlimited” hosting plans.  You’ll need to look at caching or reducing your WordPress plugin use.
• 1,500-2,000 pageviews a day will be your next wall with reseller web hosting plans.  You have to start paying attention to image sizes, plugins that load a lot of external files, comment spam, reliance on other web sites, and so on.
• 3,000-5,000 pageviews a day starts to run into problems on shared server plans like my $25/mo plan at HostGator.  At this point you have to be really careful with your configuration, or consider moving to a private virtual server.

I’ve been doing around 3-4k pageviews on weekdays, but on peaks when I get links like Sherri’s, I hit 8-10k pageviews per day.  Even though I could probably hunker down, do some serious tweaking, and make it work on HostGator, it was time to kick it up a notch.  A dedicated virtual server would give me the power I need to run ads for Google visitors again, and that would pay for itself.

Even with moving to my own server, though, I still couldn’t ensure that the site wouldn’t fall over if Sherri tells Barbara Walters about it, so I’m taking another step too.

Caching Images on Amazon S3

Images take a lot of time to download relative to the text of a web page.  Even my fairly minimal web site had a lot of small images after my redesign.  To see how images impact page load time, get the free Firefox extension Firebug.  It shows a complete breakdown of all the factors involved with a page load.  The most obvious example is the large picture across the top of the site, which shows several of my portraits by TheFuzzball.  That image is roughly 70kb, and when hosted on my web server, it took over a second to download.

Instead of hosting that image on my web server, I copied it to Amazon S3, a cloud-based file storage service that can also act as a web server for static files.  I’ve talked about S3 here before, like when I started selling Twitter t-shirts with a site hosted on Amazon S3.  (Latest tally on that project: in the black by $50.)  The domain name i.brentozar.com points to an Amazon S3 storage bucket.  I changed the web template to pull the header image from S3, and now the download takes around 300ms instead of 1200ms.  Still not blazing fast, but better than 1 second, and furthermore, it takes more load off my web server in the event that I get a ton of sudden traffic.

This same technique can be used for images embedded in posts, too.  The TanTan Amazon S3 plugin for WordPress seamlessly swaps out the WordPress upload code behind the scenes so that when you upload pictures for your posts, they’re automatically stored in Amazon S3 instead.  Unfortunately, there’s not a quick way to migrate all of the pre-existing post images over to S3, but on the plus side, they still work fine on the local web server.  They just don’t take advantage of the Amazon S3 speed boost.  This weekend I’ll go through my high-volume posts (like the Twitter ones) and moving the images over to S3 manually, but I’ll leave the rest of the older ones on the local server for now.

Every Image Counts – and Costs

Social Homes Plugin

Every little image counts, though.  I recently added the WordPress plugin Social Homes, which shows little icons for all of your social networks.  Visitors can click on those links to jump to your profile on other web sites.  I loved the way it looked, but it had a horrendous impact on my page load times.  Other sites aren’t using the fastest web servers around, and they took quite a while to deliver the favicon files involved.

Here’s a screenshot of FireBug when loading my site, and notice all the favicon.ico files loaded from all over the web.

Loading Favicons From Everywhere

This didn’t make my web server perform poorly – after all, the images were already hosted on the other web sites – but it made my page load slowly.  Even worse, if one of the social networking sites was down (which happens more than you’d think) then the “Loading” stage would last forever on my web site.  To the visitors, it seemed like the page was loading slow, and that sucks.

The first thing I did was copy all of those favicon.ico files to Amazon S3 so I could cache them all myself.

Next, I copied the HTML produced by Social Homes, and used a text editor to replace the image names so that they pointed to my own site:

Old: <img src=”http://www.facebook.com/favicon.ico”…

New: <img src=”http://cached.brentozar.com/favicon-facebook.ico” …

Then I removed the Social Homes widget and replaced it with a plain text widget with my copy/pasted HTML.  Presto, faster page load times.

There’s a cost to this: I pay for bandwidth used at S3 as well as $.01 per 10,000 “get” requests.  If you sit here hitting refresh on my web page, you can run me out of pocket change.  On the plus side, though, my web site stays fast and stays up even if Ashton Kutcher name-drops me.

I’m ready.  Coming soon: how to build your own Hackintosh Pro.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts