Efficient Hit Logging with ASP.NET 2.0 & Microsoft SQL Server
I’ve been running image hosting sites for a couple of years. Through my trials I discovered a few things. Running the web server AND the sql server off the same box when you are getting a couple of million image hits a day can be trying on your system. The first version of my image hosting site wrote logs as they came in. 1 image hit actually equalled 3 queries to the DB. That was due to a lack of experience in both architecture and ASP.NET.
In July I decided to rewrite my ASP.NET 1.1 in 2.0. I have plenty of ram in my box, and i knew I wanted to do less queries to my database. What I wound up doing was an in-memory datatable that gets a record for each hit. I used the TableNewRow datatable event to check the record count with each insert. Probaboy not the best method… but I went with it. A more preferable way to do it would be to use a Timer object and firing on the Tick event. In any case, once I reach 10k records, I do a “dump” to the database of the in-memory stuff, and then clean my in-memory table.
As for the “dump”… well… the redesign could not have come at a better time. I was able to utilize the new .NET 2.0 SqlBulkCopy class. What this amounted to was a 4 second query every 10 minutes. Compare that to the alternative of doing several inserts a second… and you can start to see the advantage of in the in-memory/SqlBulkCopy dump technique.
After I released my new version of my image hosting site, I found out REAL fast that there were some problems related to sharing in-memory datatables that are accessed (read, write) constantly (writes 15-20 times a second).
It took me quite a bit of trial and error to figure out how to get it stablized. The server I was working on had dual xeon processors with hyper-threading enabled. What I found was that when this combination was being used, and I was logging hits to an in-memory datatable 15-20 times a second, different threads were trying to log to this table at the same time, causing all hell to break loose.
The fix – briefly lock the table during writes:
lock (MemoryTables.HitList)
{
MemoryTables.HitList.Rows.Add(row);
}
The dump/rebuild procedure also requires a lock, but it’s very brief:
using (SqlConnection sourceConnection = new SqlConnection(_ConnectionString))
{
sourceConnection.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(sourceConnection))
{
#region Dump and clear hit list
copy.DestinationTableName = “FileHits”;
lock (_HitList) // Place a lock on the table so incoming hits won’t interupt our dump or our clear
{
copy.WriteToServer(_HitList);
try
{
if (!(EventLog.SourceExists(SourceName)))
{
EventLog.CreateEventSource(SourceName, LogName);
}
// Insert into Event Log so we know logging is taking place as expected;
EventLog MyLog = new EventLog();
MyLog.Source = SourceName;
MyLog.WriteEntry(“Hit data has been dumped. HitList Records: ” + _HitList.Rows.Count, EventLogEntryType.Information);
}
catch (Exception ex)
{
throw;
}
finally
{
_HitList.Clear();
}
}
#endregion
}
}
I half-expected the locking to cause a delay problem in my logging abilities, but I’ve noticed no bottlenecks at all, and this method has been operating trouble free for about a month now.
Recent Comments