Bottleneck in client notification mechanism
-
Hello All,
I am looking for suggestions on the design and implementation
of a mechanism that notifies clients of specific changes in
our database. Our current implementation, while resolves the
problem, creates an explicit bottleneck, which has caused
contention problems under stress conditions.
In our current implementation:
* Our clients use ADO and connect to a SQL Server 2000 database.
Lets call it the "asset" database.
* Each client can "open" an asset from the database, in which case,
all information about that asset is copied into an object in
the client side.
* As assets are modified, created, and deleted, we add a row to
a Notification table, which is used as a circular buffer. Each
row in that table contains information regarding the nature of
the change.
* We also have another table, NoticeIndex, which contains one row
and one column and stores the number of the row in the Notification
table where the last notice was inserted.
* Every 10 seconds, the clients poll the Notification table to get
all the changes inserted since the "last time" they checked.
* Then, each client processes the new changes selecting the ones
that it cares about, e.g. updating the objects associated with
open assets, updating lists in the interface, etc. (this will
depend on the front-end exposed through the client).
As you can see, as a result of this design, we must lock the
Notification and NoticeIndex tables every time we need to write
or read notices, in order to avoid inconsistencies. We first must
read the value of the current row number from the NoticeIndex
table and use that value to read/write to the Notification table.
If we are adding a notification, we also must update the
NoticeIndex table.
This has proven to be a bottleneck under stress conditions. For
example, in a typical system with 8 clients, we might have
100 changes/second (i.e. 160 new columns added to the NOtification
table per second). In addition to that, every 10 seconds, the
clients poll for changes, basically reading 1000 rows from the
Notification table.
Every other solution that I could think would also have similar
restrictions with respect to locking, which would result in the
same latencies.
Is this possible to resolve this problem without creating such
intrinsic contention? Any suggestion would be greatly appreciated.
- CD
-
I once implemented such a notification system using a three-tier mechanism. Instead of connecting directly to the database, the client would connect to an intermediary component. The componant would dispatch database request to the DBMS and notify every other client of failure/success of those requests.
I used a DCOM componant at that time but now I think a socket connection would be more effecient.
In my case the number of requests and their complexity were not too high, otherwise it can become tedious to set up.
Patrick
Patrick Duflot
-
I assume you mean new rows in the Notifications table, not columns.
Off the top of my head, I don't see a better solution. Since this should mostly occur in memory, disk changes won't help. I suppose faster CPU/Memory could help, but that's a limited gain and not a great solution.
When you read and update the data, do you do anything else? Or is it
begin tran
select @i = x from y
update y set x = @i + z
commit tran
Or do you do anything else? Limiting this read/update is the best you can do there. What about using a Q for notifications, or writing the data with a trigger to another table (inserts only) and then reading and processing that table on the client?
Also, 100/sec doesn't sound like a lot. I've had read queries go up over 11,000/sec with some writes in there, but more like 1/sec.
What size server we talking here?
-
Hello Steve,
You are correct. I meant rows, not columns. Also, I do not do much when I insert or read a notice from the Notification table. I try to keep the processing as small as possible to minimize the locking latencies. I wish there was some way to avoid the locks altogether or to simplify even further what is done when notices are inserter or read.
I do not understand what you meant when you asked what was the "size" of the server. Are you talking storage, speed, etc. In terms of hardware, the server is a dual Xenon server (2GHz) with 2MB of RAM and 100GB of disk (RAID 1). Our OS is Win 2003 server. Our database in particular is very small in comparison to the typical applications. It is between 400MB-600MB. The schema is also quite simple. Most of our issues, however, come from the fact that we use the database as the backend for a high-performance video system (for storage, transmission, and editing). We do not store the actual video in the database. Only the metadata associated with it.
The Notification table is used as a circular buffer. The maximun number of rows is set to a number large enough to support the rate that changes are produced versus the rate the clients poll for changes. This is to ensure that clients do not loose any updates. Since it is a circular buffer, I do not need to delete the previous notices as I wrapp wround. They are
simply overwritten with new notices.
The basic steps of our InsertNotice stored procedure are included in the end of the email. I am not sure how I could avoid locking while still ensuring correct behavior. For instance, if a client attempted to read changes while another client was in the middle of execiting the InsertNotice stored procedure, the results would be wrong.
Each client reads the notices independently by first obtaining the current notice number from the NoticeIndex table and then reading the range of notices from the Notification table. It created a recordset based on the query:
Select * from Notification where sequenceNumber > @lastNoticeNumber and sequenceNumber < = @currentNoticeNumber
Could youpossibly ellaborate a little more on your last comments?
"... Or do you do anything else? Limiting this read/update is the best you can do there. What about using a Q for notifications, or writing the data with a trigger to another table (inserts only) and then reading and processing that table on the client? "
Thank you
- CD
1. Declare and open a cursor for the NoticeIndex table. This blocks any other
access.
declare insertNotice_cursor cursor scroll_locks for
select lastNumber from NoticeIndex
for update
-- Open the cursor
open insertNotice_cursor
2. Fetch the lastNumber from the cursor
fetch next from insertNotice_cursor
into @lastNumber
3. Increment to get the next sequence number. Wrapp aroud if
we have the maxNumber of changes. (@maxNumber is defined elsewhere)
set @lastNumber = @lastNumber + 1
if @lastNumber = @maxNumber
begin
set @lastNumber = 0
end
4.Insert/Update the notice to the table
update Notification set
...
where
sequenceNumber = @lastNumber
5. Update NoticeIndex record
update NoticeIndex
set lastnumber = @lastNumber
where current of insertNotice_cursor
6. Close and deallocate cursor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply