Graffiti Beta 1 - Review (part 2): Performance

I finally ran some simple performance tests on Graffiti Beta 1. By simple I mean that only a stopwatch and web browser were used. The purpose was to get a general "feel" for Graffiti's performance with a large numbers posts in the database.

I ran the test on a Dell Inspiron Laptop (2GHz Core Duo, 2GB Ram, 7200 RPM HDD) running Windows Vista 32bit, IIS 7, and SQL Express 2005.

Since I was using my own development laptop, and it has limited free HDD space, I changed the DB recovery mode to "Simple". No other database tuning was performed.

To generate the data I just created a SQL Schema project in Visual Studio 2008 Team System then created a simple data generation plan. The generated data included 5 categories, 100,000 posts, and 200,000 comments. The posts were split evenly among the 5 categories. Comments were also split evenly among the posts (2 comments to a post). 

Results:

100k posts and 200k comments:

• Graffiti was able to display the user facing pages with only a minor performance impact.
   
• The initial load of the site's home page took nearly 5 seconds.
    
• Refresh of the home page took less than 1 second.
   
• The initial load of a category page took about 2 seconds, with refreshes being nearly instant.
   
• Clicking the "older posts" button in any content list took about 4 seconds. Once loaded though, revisiting those pages was nearly instant.
   
• Loading a content item's view took about 1 second on first load, and was nearly instant thereafter.
   
• Loading the control panel resulted in a SQL Timeout error every time.
  

  Looking at the queries in SQL Profiler: It performs a select count against the posts table, probably for use by the summary chart on the dashboard.
    
  Testing the query manually: about 1 minute and 30 seconds to count through the table using the same where and group by clauses. Counting the posts without any where or group clauses still takes around 30 seconds. HDD throughput was at 100% during the entire duration of the query's execution.
   
  This is an area where a full version of SQL server on a dedicated piece of server hardware would make a huge difference. This kind of query bottlenecks on the low throughput of the laptop's hard drive... Low HDD performance is a typical problem on any laptop though.

 50k posts and 100k comments:

• User facing pages loaded with no noticeable performance problems. Speeds were a little snappier than with 100k posts especially on initial page loads and when using the pager in content lists.
   
• The admin dashboard page loaded in 17 seconds on the first load, and was nearly instant on subsequent loads. The "posts" tab of the admin section was the slowest, taking about 28 seconds on the initial load, and about 9 seconds on subsequent loads. The "comments" tab of the admin tools was surprisingly snappy, taking only 3 seconds to load initially.  

Conclusion:

Graffiti beta 1, without special optimization, should be able to serve the needs of most realistic sites in it's target market.

Even on a modestly equipped laptop, performance did not significantly degrade until the number of posts exceeds the 50k mark. On real server hardware it would probably be able to handle well over 100,000 posts with little problem for the end users. The admin tools will degrade more significantly than user-facing pages.

Oddly, the number of comments seemed to have little impact on overall performance.

Looking at the queries that had the most significant performance problems, it will be rather trivial for Telligent to either optimize the queries for larger numbers of posts, or provide alternate configurations that trade convenience features (such as daily report generation rather than real-time reporting) for better performance.

Reality Checks:

• If you actually have anywhere near 100k posts, you probably are not going to be using any off-the-shelf product without significant customization and DB tuning anyway. Keep in mind that it takes 273 posts a day to get to 100k in a single year. Only a large news outlet is likely to generate that quantity of content and most of them would still take a few years to break the 100k mark.
   
  However, 100k comments isn't too terribly unrealistic for a very popular site.
   
• There is one major difference between this test and a production environment of Graffiti. In a production situation, Graffiti would have created one folder on the file system for each post that was created via the GUI tools.  Within each generated folder would be a generated "default.aspx" file. The main purpose of these files is to give IIS a default document to use since the graffiti URLs don't specify actual page names. Graffiti also stores a little bit of metadata in these files that can help it render the posts faster (it doesn't seem to have trouble rendering the post if these files are absent though).
  
  I was unable to quickly find a convenient way to mimic how Graffiti generates these folders and files without resorting to writing a custom app of my own... but this is a simple test, so I deemed it not to be worth the effort.
  
  I personally would not want to deploy Graffiti on a large scale without a way to disable the creation of these files and folders. Strictly speaking, this feature is only necessary on IIS 6, or IIS 7 when using the classic asp.net pipeline. It would not be unreasonable to expect that Graffiti will support the IIS7 integrated pipeline in the future and allow the per-post folders and files feature to be disabled (please!).