Michael Bell’s Blog

With my in-memory log-hit table I talked about in my last entry, I started getting these errors a few times a day, and it was enough to bomb the whole app:
Column ‘blahblah’ is constrained to be unique.  Value ‘-4862′ is already present.
Column ‘blahblah’ is constrained to be unique.  Value ‘-4847′ is already present.
Column ‘blahblah’ is constrained to be unique.  Value ‘-4768′ is already present.

Let me show you how the ‘blahblah’ column is built in the table construction:
DataColumn dc;

dc = new DataColumn(“HitID”, typeof(Int32));
dc.Unique = true;
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
_MyTable.Columns.Add(dc);

So how in the world is this happening? Let me show the piece of code that was throwing the exception:
DataRow row = MemoryTables.HitList.NewRow();
row["FileID"] = Convert.ToInt32(context.Request.QueryString["fi"]);
row["FileName"] = fi.Name;
row["Username"] = _Username;
row["MemberID"] = _MemberID;
row["Success"] = true;
row["Bytes"] = fi.Length;
MemoryTables.HitList.Rows.Add(row);

Fairly straight forward.. right? With each hit to an image, this thing fires and inserts a new row into the in-memory table. About 20 rows get inserted a second. After much trial and error, I completely eliminated this problem by re-arranging a few lines of code. The new code reads:
DataRow row = MemoryTables.HitList.NewRow();
MemoryTables.HitList.Rows.Add(row);
row["FileID"] = Convert.ToInt32(context.Request.QueryString["fi"]);
row["FileName"] = fi.Name;
row["Username"] = _Username;
row["MemberID"] = _MemberID;
row["Success"] = true;
row["Bytes"] = fi.Length;

By creating a new row and adding it to the table before I populate it with any values, I eliminate my problem. I’d love to hear an explanation if anyone has one…

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();

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