Implement ring buffer

Question

We have table registration data. It logs at a speed of 15K lines per second.

Question: How do we limit the size of the table to new 1 rows?

i.e. after reaching 1bn lines, it becomes a ring buffer, deleting the oldest line when adding the newest.

Triggers can load the system too much. Here is an example of a trigger on SO. We already use a bunch of settings to speed things up (for example, stored procedures, table parameters, etc.).

Answer 1

If there is something magical about 1 billion, I think you should consider other approaches.

The first thing that comes to mind is data sharing. Say put the data in one hour in each section. This will result in approximately 15,000 * 60 * 60 = 54 million entries per section. Every 20 hours you can delete a section.

One important benefit of splitting is that insert performance should work well, and you don't need to delete individual records. Additional overhead may occur depending on the load of the query, indexes, and other factors. But without additional indexes and the query load that is primarily inserted, it should solve your problem better than trying to delete 15,000 records every second along with the inserts.

Answer 2

Here is my suggestion:

• Pre-populate the table with thousands of 000,000 rows, including the row number as the primary key.
• Instead of insert to enter new lines, the log should support a counter variable, which is incremented each time, and update corresponding line according to the line number.

This is actually what you do with the ring buffer in other contexts. You will not continue to allocate memory and delete; you just overwrite the same array again and again.

Update: The update does not actually change the data in place , as I thought. Thus, it can be inefficient.

Answer 3

I do not have a complete answer, but I hope some ideas will help you get started.

I would add some kind of numerical column to the table. This value would increase by 1 until it reached the number of rows that you wanted to keep. At this point, the procedure will switch to update statements, replacing the previous line instead of inserting new ones. You obviously won’t be able to use this column to determine the order of the rows, so if you hadn’t done so already, I would also add a timestamp column so that you can later arrange them in chronological order.

To coordinate the counter value between transactions, you can use a sequence, then do modular division to get the counter value.

To handle any spaces in the table (for example, someone deleted some of the rows), you can use the merge operator. This should insert if the row is missing or update if it exists.

Hope this helps.

Answer 4

Just an idea difficult to write in a comment.

Create multiple log tables, e.g. 3, Log1, Log2, Log3

 CREATE TABLE Log1 ( Id int NOT NULL CHECK (Id BETWEEN 0 AND 9) ,Message varchar(10) NOT NULL ,CONSTRAINT [PK_Log1] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY] ) CREATE TABLE Log2 ( Id int NOT NULL CHECK (Id BETWEEN 10 AND 19) ,Message varchar(10) NOT NULL ,CONSTRAINT [PK_Log2] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY] ) CREATE TABLE Log3 ( Id int NOT NULL CHECK (Id BETWEEN 20 AND 29) ,Message varchar(10) NOT NULL ,CONSTRAINT [PK_Log3] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY] ) 

Then create a partitioned view

 CREATE VIEW LogView AS ( SELECT * FROM Log1 UNION ALL SELECT * FROM Log2 UNION ALL SELECT * FROM Log3 ) 

If you are using SQL2012, you can use the sequence

 CREATE SEQUENCE LogSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 29 CYCLE ; 

And then start pasting the values

 INSERT INTO LogView (Id, Message) SELECT NEXT VALUE FOR LogSequence ,'SomeMessage' 

Now you just need to trim the logtables on some chart

If you do not have sql2012, you need to create the sequence in a different way