True.com is the leading internet dating web site, with over 1 million visitors a day. While there are hundreds of online dating sites, only True does background checks to protect its members from felons and those already married. This has differentiated True and has quickly made them the industry leader.
True uses Microsoft SQL Server 2000 databases to store their customer data. As their traffic has grown, custom stored procedures that worked fine on a few hundred thousand members became a problem when the number of members started to grow by tens of thousands per day. Even though True had the largest servers that Dell offers, queries were frequently timing out and users were getting web site errors.
After initial analysis, TitaniumSea determined that there were two main problems. First, there were a number of queries that could be greatly enhanced by adding indexes to reduce full table scans. Second, many of the stored procedures were sequentially stepping through million row tables using cursors and doing subqueries per row.
The bottleneck was quickly identified as the tempDB disk i/o, which was running at 130MB/sec almost continuously. In order to find the culprit stored procedures, TitaniumSea used the SQL Profiler tool and filtered on stored procedures that had at least 1000 pages of disk i/o per call. This subset of database calls was monitored over a month to measure progress. The table reflects data gathered over a 10 minute period each day:
|
7/19 |
7/23 |
7/26 |
7/27 |
8/16 |
| Total Queries |
38,000 |
37,872 |
8,266 |
8,946 |
6,334 |
| Avg Queries/sec |
63 |
63 |
13 |
14 |
10 |
| Avg Reads/sec |
243,000 |
163,014 |
121,677 |
109,911 |
83,097 |
| Total Reads (,000) |
146,000 |
97,156 |
72,519 |
65,507 |
49,858 |
| Total Writes (,000) |
26 |
17 |
11 |
10 |
6 |
| Total Duration (ms) |
1,420,000 |
941,315 |
762,988 |
582,262 |
516,535 |
| DB Busy seconds |
234 |
162 |
50 |
47 |
12 |
| New Members |
##,### |
##,### |
##,### |
##,### |
##,### |
| Total Site Visits |
###,### |
###,### |
###,### |
###,### |
###,### |
| Page Views |
#,###,### |
#,###,### |
#,###,### |
#,###,### |
#,###,### |
| Stickiness (Visit Duration) |
#:## |
#:## |
#:## |
#:## |
#:## |
| Capacity |
100% |
67% |
50% |
45% |
34% |
# = proprietary information
We also used the Profiler data to identify the most expensive stored procedures:
Hottest |
Sproc |
Calls |
Total Reads |
Avg Reads/call |
1 |
spGetMMItemSummary |
3 |
10724758 |
3574919 |
2 |
spGetSocioComparison |
74 |
7289201 |
98502 |
3 |
spGetProfileCompletion |
4358 |
4893344 |
1122 |
4 |
spGetUserProfileFull |
1865 |
4595888 |
2464 |
5 |
Chart_SubFact14Pie |
6 |
4178280 |
696380 |
6 |
Chart_SubFact12Pie |
6 |
4176084 |
696014 |
Over the course of 4 weeks, TitaniumSea was able to completely remove a critical issue for True, allowing them to focus on building their business instead of continuously upgrading hardware.
back to case studies
|
