Tag Archive: s3

My Weekly Bookmarks for September 4th

Written on September 5, 2009 by in SQL Server

Here’s my bookmarked links for September 1st through September 4th:

Tech Links

The Junk Drawer

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

3 Comments - Leave a comment!

Upgraded My Web Host

Written on May 6, 2009 by in SQL Server

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.)

Up Up and Away!

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

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

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

12 Comments - Leave a comment!

The Cost of Log Shipping SQL Server to Amazon S3

Written on April 21, 2009 by in SQL Server

Want to know how much it’ll cost to back up your database transaction logs to Amazon S3?  I’ll show you with a snippet of T-SQL.

Note the parameters at the top for retention periods.  The defaults assume one full backup weekly.  DBAs would normally want to do full backups more often, but when you’re paying by the upload, you may want to consider only uploading one full backup per week and then uploading transaction log backups the rest of the time.  You can still take full and differential backups locally for faster local recovery times as long as you set your backup parameters correctly so that you don’t break your log chain.

The @months_to_retain_full_backups parameter says we’re storing two months of full backups in S3.  This gives you some offsite flexibility while minimizing your upload costs.

The upload cost is set at $.10 per gig because that’s Amazon’s long-term price.  There’s a temporary sale going on for $.03 per gig for uploads, but you can’t set your budget by that.

With no further ado, here’s the query.  If it doesn’t work on your system, let me know – this is a really rough draft that I’ve only used on a dozen or so systems, and it wouldn’t surprise me if it’s got bugs.

DECLARE  @upload_cost_per_gb              DECIMAL(6,2),
         @storage_cost_per_gb             DECIMAL(6,2),
         @days_to_retain_log_backups      INT,
         @full_backups_uploaded_per_month INT,
         @months_to_retain_full_backups   INT
 
SET @upload_cost_per_gb = .10
SET @storage_cost_per_gb = .15
SET @days_to_retain_log_backups = 14
SET @full_backups_uploaded_per_month = 4
SET @months_to_retain_full_backups = 2
 
SELECT   bs.database_name,
         COUNT(* ) AS backups,
         SUM(bs.backup_size / 1000000000) AS transaction_backup_size_gb,
         SUM(bs.backup_size / 1000000000 * @upload_cost_per_gb) AS transaction_backup_upload_cost,
         SUM(bs.backup_size / 1000000000 * @storage_cost_per_gb / 30 * @days_to_retain_log_backups)
                   AS transaction_backup_storage_cost,
         (SELECT   TOP 1 (fbs.backup_size / 1000000000)
          FROM     msdb.dbo.backupset fbs
          WHERE    bs.database_name = fbs.database_name
                   AND fbs.type = 'D'
          ORDER BY backup_start_date DESC) AS last_full_backup_size_gb,
         (SELECT   TOP 1 (fbs.backup_size / 1000000000 * @upload_cost_per_gb
                          * @full_backups_uploaded_per_month)
          FROM     msdb.dbo.backupset fbs
          WHERE    bs.database_name = fbs.database_name
                   AND fbs.type = 'D'
          ORDER BY backup_start_date DESC) AS full_backup_upload_cost,
         (SELECT   TOP 1 (fbs.backup_size / 1000000000 * @storage_cost_per_gb
                          * @full_backups_uploaded_per_month * @months_to_retain_full_backups)
          FROM     msdb.dbo.backupset fbs
          WHERE    bs.database_name = fbs.database_name
                   AND fbs.type = 'D'
          ORDER BY backup_start_date DESC) AS full_backup_storage_cost
FROM     msdb.dbo.backupset bs
WHERE    bs.type = 'L' /* log backups only, not diffs or fulls */
         AND bs.backup_start_date &gt;= DATEADD(dd,-30,GETDATE())
GROUP BY bs.database_name
ORDER BY bs.database_name

This query revolves around transaction log backups, but if you only do incrementals, you could change that bs.type = “L” filter in the query to be “I” instead, and you’ll get the cost on incrementals.  I don’t have a query for databases that only do full backups, but frankly, if you’re only doing fulls, your data probably isn’t important enough to get internet-based online backups available.

This query is good for more than just gauging costs, too: keep in mind that your internet connection will need to be able to handle uploading your backups out to Amazon S3.

This is only a ballpark estimate. There are other costs that this query does not cover.  For example, Amazon charges you for some types of requests, like checking directory contents.  These costs can vary, but they’re generally very small relative to the bandwidth and storage costs.  This query also doesn’t check for backup compression, but if you’re paying by the byte for bandwidth and storage, you should seriously consider using backup compression.  The ROI is extremely high: software like Quest LiteSpeed pays for itself here in no time.

If this topic interests you, I’ll be discussing it in much more detail during my “Log Shipping To The Cloud” session at the SSWUG Virtual Conference on Thursday.  (Use VIP code SPVBOZSP09 for $10 off the $125 entry price.)  If you register for the V-Conference, you can watch the sessions whenever it’s convenient for you – not just Thursday.  However, if you happen to watch it during the first showing on Thursday, I’ll be online in the chat room for the session and you can ask me questions live.

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

2 Comments - Leave a comment!

Spring SSWUG Virtual Conference coming up

Written on March 3, 2009 by in Cloud Computing, SQL Server

Last fall, I spoke at the SSWUG Virtual Conference and had a really good time with it, so I signed up again to do the Spring SSWUG Virtual Conference too.

sswug-virtual-conferenceHere’s how it works: I fly out to Tucson to record my sessions in SSWUG’s sssswanky TV studio ahead of time, and then you can watch all of the sessions in your web browser later.  On the release days, when my videos are scheduled to go out for the first time, I sit in on the sessions in the chat room.  I answer questions you’ve got about the material, clarify things, and the other attendees can also share tips and tricks in the chat.  If you can’t clear your schedule on the release days (April 22/23/24), you can go back and watch the videos later – the only drawback being that I won’t be there to hold your hand while you watch it.  Of course, you can always email me with questions too, or catch me on Twitter.

I’m really excited about my sessions:

Log Shipping to the Cloud

In an ideal world, we’d have a standby SQL Server in a disaster recovery datacenter, but we can’t always afford that luxury. In this session, Brent Ozar will explain how to log ship your databases to Amazon S3, a cloud-based file service, and bring up a standby SQL Server in the event of an emergency. It’s like having your own disaster recovery datacenter, but without the cost – until emergency strikes.

Reaching Compliance with SQL Server 2008

To become a production DBA at a public company, hospital, bank or even just a security-minded firm, you need to understand the basics of regulatory compliance. In this session, you’ll learn how to talk the language and learn what tools are available in each version of SQL Server to make your job easier.

SQL Server Encryption

Regulatory compliance needs are a big pain point for DBAs. How do we make sure our data stays safe? Brent will explain the options available for SQL Server 2005 and 2008, do a Transparent Data Encryption implementation with SQL 2008, and demonstrate its strengths and weaknesses. Attendees will get scripts and sample Policy-Based Management policies.

Plus Great Sessions by Much Smarter Guys

Get a load of these:

  • Using Database Mirroring for High Availability by Eric Johnson
  • Become Your Own Picasso: Writing Better T-SQL by Jason Strate
  • Disk IO Tuning for SQL Server 2005 by Kevin Kline
  • 7 SQL Server Development Practices More Evil Than Cursors by Paul Nielsen
  • Panic! Disaster Strikes by Sarah Barela
  • Visual Studio Team System Database Edition – Managing Database Deployment by Ted Malone
  • Optimizing Backup and Restore Performance by Tom LaRock
  • Auditing Your Users by TJ Belt

And I’m only scratching the surface – check out the full list of SQL Server abstracts.

Register for the SSWUG V-Conference now and use VIP code SPVBOZSP09 for $10 off.  That coupon code can be combined with other discounts, too, like the early bird registration or the alumni registration.

This is a heck of a lot of quality training for less than $100.  I’ve heard from a lot of DBAs whose training budgets have been sliced away, and I gotta tell you that this is probably the best way to spend $100 on training.  The video style is really easy to follow, the speakers are just top notch, and the subject matter is really appropriate for what DBAs have to do in their jobs.  I hope you join me in my sessions!

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

0 Comments - Leave a comment!