Bodybuilding.com ensures fast website performance, eliminates downtime and enables better business intelligence with real-time data replication from Dell Software
Country: United States
When reporting workloads began dragging down the performance of its website, Bodybuilding.com knew it needed to offload reporting to a separate database. But the IT team’s first solution — a daily static copy of the database — suffered from its own performance and downtime issues.
By maintaining a near real-time copy of its production database using SharePlex, Bodybuilding.com is now able to keep website performance strong while enabling advanced analytics and ad hoc reporting 24×7. Plus, SharePlex enables safer, seamless migrations that each save the company up to $280,000 by dramatically reducing downtime.
• Ensures high website performance by offloading reporting to a real-time replica
• Enables running an enterpriseclass environment on an Oracle Standard Edition budget
• Saves as much as $280,000 in lost revenue per migration by drastically reducing downtime
• Enables advanced analytics while slashing some query run times from 20–30 minutes to just 4–5 seconds Solutions at a glance
• Data replication
is not only the largest online retailer of nutritional supplements, it is also the world’s most visited health and fitness website. More than 1.7 million unique visitors a day turn to Bodybuilding.com for supplements, information, videos, motivation and more. Behind the scenes, just two database administrators (DBAs) are able to keep the website and its services humming with help from SharePlex from Dell Software.
“We have eliminated the two hours of downtime we used to have every night for our reporting database by switching
Reporting against the production database drags down performance for customers
To constantly improve its service to its customers and online community, Bodybuilding.com has developed a strong business intelligence (BI) strategy by mining website data to better understand customer behavior, stay abreast of trends and uncover new opportunities for growth. However, with up to 50 employees actively running reports against the production database, online transaction processing (OLTP) performance and the website experience of customers were both suffering. The company knew a new approach was needed — but it had to be mindful of the budget. “We knew we needed a way to separate our online transactional database from our ad hoc and BI reporting,” notes Sean Scott, Oracle DBA at Bodybuilding.com. “But we did not want to have to spend several hundred thousand dollars in licensing fees to move from Oracle® Standard Edition to Enterprise Edition.”
A static copy of the database offloads reporting, but at a high cost
The IT team came up with a strategy: Create a copy of the production database for reporting and update it each night. “We would collect logs for the standby database from our production database throughout the course of the day,” Scott explains. “At 10 p.m., we’d shut down the standby, update it with the new data and then open it again at midnight. At 12:01 a.m., we would have a snapshot of the prior day’s activities of the database as it existed moments after the day closed, which the BI team and others could use for reporting.”
Although this approach succeeded in keeping online transaction performance from being impacted by the reporting workload, it also had significant drawbacks. Obviously, because the standby database was not updated in real time, staff could not report on anything that happened during the course of a day until shortly after midnight, and the reporting database had to be shut down for two hours every night for the update. In addition, the process of refreshing the standby database each night erased the cache, causing significant performance issues for reporting each morning.
Moreover, since the standby database was opened in read-only mode, the DBAs could do little to improve performance or optimize reporting. “For example, we had no ability to create purposeful reporting indexes — we were just stuck with whatever the production database had,” Scott notes. “Therefore, we could not do all the analytics we wanted to do.”
A better approach: real-time replication
After getting by with this bespoke approach for about a year, Bodybuilding.com decided to explore a better alternative: real-time data replication. Having a secondary copy of the database kept constantly up to date, the company recognized, would enable reporting against current data, enable indexing optimized for reporting, and eliminate the nightly downtime and morning performance problems.
The two DBAs began evaluating the options on the market. Between them, they had experience with Oracle Data Guard and GoldenGate, Tungsten Replicator, Dbvisit Standby and Dbvisit Replicate, and Dell SharePlex. The clear winner, based on in-depth reviews of product documentation, personal experience and cost, was SharePlex.
“Since Data Guard requires Oracle Enterprise Edition, it was cost-prohibitive for us. GoldenGate is really expensive as well, and it always seemed awkward to me,” recalls Scott. “SharePlex, on the other hand, works with Oracle Standard Edition, and the licensing costs were extremely reasonable. Both of us had used it in the past and were sold on its features and performance.” In particular, Bodybuilding.com was pleased to see that SharePlex supports replication to SQL Server, which its BI team uses extensively, and Hadoop, which the team is currently exploring.
Vendor reputation was another key decision point for Bodybuilding.com. “Looking at the history of SharePlex, it’s obvious that Dell has a strong commitment to the product — with each new version, you see some really significant feature improvements and enhancements,” adds Scott. “And Dell was the hands down winner for quality of support.”
Better business intelligence right out of the box
With SharePlex, Bodybuilding.com is now replicating its Oracle Real Application Cluster (RAC) transaction database to a secondary RAC database in near real time, giving staff access to up-to-the-minute data around the clock. “We have eliminated the two hours of downtime we used to have every night for our reporting database by switching to SharePlex,” reports Scott. “And SharePlex replication is as close to real time as we need. Our latency averages around 4-6 seconds — and that’s straight out of the box; I don’t think we have changed a single parameter in the SharePlex environment.”
New indexes slash query run time, enabling BI and driving sales With a real-time replica dedicated to and optimized for reporting, Bodybuilding.com has been able to pursue the advanced analytics and BI it needs to retain its leadership position in the health and fitness industry. “With the SharePlex replica for our reporting, we can drop indexes that aren’t needed for analytics and add new ones that improve performance. For example, by building a handful of specific indexes around order dates in the SharePlex replica, we reduced query run times from 20–30 minutes to just 4–5 seconds,” explains Scott. “Plus, we can create new schemas that allow the analytics team to stage and transform data locally, rather than writing it off to a separate environment to be manipulated.”
Some of the reporting that SharePlex has enabled serves internal needs. For example, Scott notes that “thanks to SharePlex, everyone in the company is now extremely well-informed. We all get an email at 6 a.m. that lets us know
exactly what our numbers were for the previous day — gross sales, domestic sales, margin rates, site visits and so on.”
Other reporting is geared more directly to helping website visitors and driving sales. For instance, the company has been able to develop recommendation engines that offer product suggestions to customers who are shopping for a particular product or have identified a particular need, based on data about what others have purchased.
The company is also looking at ways to use the secondary SharePlex database to improve inventory management with initiatives such as predictive ordering.
Reducing the risk and anxiety of migrations
In addition to improving BI and reporting, Bodybuilding.com was eager to upgrade its aging production database hardware. But such a migration involves a lot of changes — not just changing the hardware, but also updating the operating system, moving to new storage, upgrading the database, applying patches, reorganizing storage layout and recovering free space. With a traditional migration, Bodybuilding.com recognized, all those changes would add up to a lot of risk — and it still wouldn’t get the results it wanted.
“With a traditional migration, we wouldn’t discover problems until we actually ran through the many steps of the migration, which would leave us open to significant risk,” Scott says.
“Moreover, since we’re using Oracle Standard Edition, many of the online features for rebuilding indexes are unavailable to us, so with a traditional migration, we would be unable to do the reorganization we need — the outage would simply be way too long.”
With SharePlex, Bodybuilding.com was able to virtually eliminate the risks associated with the migration while achieving its preferred outcome. “SharePlex takes a lot of the mystery and anxiety out of the migration process,” says Scott. “Because we’ll be running the systems in parallel prior to the upgrade, we’ll be able to do almost all of the preparation work ahead of time during regular business hours. We can put our application servers in the test pool and point them at the new database to make sure that everything runs. We’ll know what our performance is going to be because we can actually see it, and we can take care of any problems before we ever flip the switch. Plus, we can do the physical realignment we want because SharePlex supports the table movement and storage reorganization that’s required.”
Saving over a quarter of a million dollars in just one migration
The ease and security of a SharePlex migration stands in stark contrast to a traditional migration, which Scott says would involve multiple nights of downtime. “Without SharePlex, we wouldn’t be able to do all of the migration at once,” Scott notes. “Instead, we would need to move to the new hardware and operating system as part of one outage, move storage in another, and upgrade and patch in a third outage — for a total of at least six hours of downtime.”
Once upon a time, a few hours of downtime in the middle of the night might not have cost a company much. But today, for a busy international company like Bodybuilding.com that relies on 24×7 online sales, even an hour of downtime can be quite expensive, let alone three separate multi-hour outages to complete just one migration. Therefore, the savings from SharePlex were dramatic.
“With SharePlex, we can do 90 percent of the migration work during regular working hours, and all we have to really do during off-hours is the actual switch, which we’re electing to do as part of a 90-minute outage because it’s an opportunity to do a few other unrelated things at the same time,” says Scott. “By drastically reducing the downtime required by a traditional migration, SharePlex is saving us as much as $280,000 in lost revenue — and that’s for just one migration. We know SharePlex will pay for itself many times over in the coming years.”
About Dell Software
Dell Software helps customers unlock greater potential through the power of technology—delivering scalable, affordable and simple-to-use solutions that simplify IT and mitigate risk. This software, when combined with Dell hardware and services, drives unmatched efficiency and productivity to accelerate business results.