Table Pattern: Rotating Log / Ring Buffer



原文  http://kejser.org/table-pattern-rotating-log-ring-buffer/ 添加到推刊

 

Most database systems need some form of log table to keep track of events, for example for auditing purposes. To avoid the log growing forever, it is often a good idea to regularly rotate old log entries out of this table. For small log tables, running a DELETE statement works well for this purpose. However, as the log throughput grows, it is often preferable to use partition switching instead. In this blog, I will show you an implementation of a rotating log table.

The Table Structure

A rotating log is essentially a ring buffer where the writes happen at one part of the buffer and the old records are removed at the other end. For the implementation example, we will assume you wish to rotate the log daily, removing and aggregating old entries and making sure that the new entries are added to a place that is easy to rotate in the future.

It is possible to traditional partition “sliding window” techniques and add new partitions daily to achieve this. However, I find the splitting, merging and switching a bit clunky for this purpose. Instead, I prefer to implement a rotating log like this:

Transact-SQL

CREATE PARTITION FUNCTION PF_RingBuffer (TINYINT)
AS RANGE RIGHT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12)

CREATE PARTITION SCHEME PS_RingBuffer 
AS PARTITION PF_RingBuffer ALL TO ([DATA])

CREATE TABLE RingBuffer
(
  EventTime DATETIME NOT NULL DEFAULT GETUTCDATE()
  , Attribute VARCHAR(50) NOT NULL 
  , Value BIGINT NOT NULL
  , Offset AS CAST(DATEDIFF(day, 0, EventTime) % 14 AS TINYINT) PERSISTED
) ON PS_RingBuffer (Offset)

CREATE PARTITION FUNCTION PF_RingBuffer ( TINYINT ) AS RANGE RIGHT FOR VALUES ( 0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 ) CREATE PARTITION SCHEME PS_RingBuffer AS PARTITION PF_RingBuffer ALL TO ( [ DATA ] ) CREATE TABLE RingBuffer (    EventTime DATETIME NOT NULL DEFAULT GETUTCDATE ( )    , Attribute VARCHAR ( 50 ) NOT NULL    , Value BIGINT NOT NULL    , Offset AS CAST ( DATEDIFF ( day , 0 , EventTime ) % 14 AS TINYINT ) PERSISTED ) ON PS_RingBuffer ( Offset )

With this design, I never have to alter the partition function or scheme, I can simply switch out the old parts of the ring buffer.

For the purposes of this implementation, let me add a few more tables:

Transact-SQL

CREATE TABLE RingBuffer_SwitchTarget
(
  EventTime DATETIME NOT NULL DEFAULT GETUTCDATE()
  , Attribute VARCHAR(50) NOT NULL 
  , Value BIGINT NOT NULL
  , Offset AS CAST(DATEDIFF(day, 0, EventTime) % 14 AS TINYINT) PERSISTED
) ON PS_RingBuffer (Offset)

CREATE TABLE RingBuffer_Aggregate
(
  EventTime DATE NOT NULL
  , Attribute VARCHAR(50) NOT NULL
  , Value BIGINT NOT NULL
) ON [DATA]
ALTER TABLE RingBuffer_Aggregate ADD CONSTRAINT PK_RingBuffer_Aggregate
PRIMARY KEY (EventTime, Attribute, Value)

CREATE TABLE RingBuffer_SwitchTarget (    EventTime DATETIME NOT NULL DEFAULT GETUTCDATE ( )    , Attribute VARCHAR ( 50 ) NOT NULL    , Value BIGINT NOT NULL    , Offset AS CAST ( DATEDIFF ( day , 0 , EventTime ) % 14 AS TINYINT ) PERSISTED ) ON PS_RingBuffer ( Offset ) CREATE TABLE RingBuffer_Aggregate (    EventTime DATE NOT NULL    , Attribute VARCHAR ( 50 ) NOT NULL    , Value BIGINT NOT NULL ) ON [ DATA ] ALTER TABLE RingBuffer_Aggregate ADD CONSTRAINT PK_RingBuffer_Aggregate PRIMARY KEY ( EventTime , Attribute , Value )

The  RingBuffer_SwitchTarget  will be used to switch out the old entries in RingBuffer . The RingBuffer_Aggregate is used to keep an aggregate of old data, which of course will be much smaller.

To illustrate, here is the flow we are implementing:

Rotating the Log

With the table structures in place, we can now write the procedure that rotates the log. Using a few simple math tricks, we can rotate and the log with a simple piece of code:

Transact-SQL

CREATE PROCEDURE RingbufferRotate
  @Now DATETIME = NULL
  , @PartitionsToKeep INT = 7
AS
SET NOCOUNT ON
BEGIN
  IF @Now IS NULL SET @Now = GETUTCDATE()

  DECLARE @BufferSize INT
  SELECT @BufferSize = COUNT(*)
  FROM sys.partitions P 
  JOIN sys.tables T
    ON P.object_id = T.object_id 
  WHERE T.name = 'RingBuffer' 

  IF @PartitionsToKeep > @BufferSize BEGIN
    RAISERROR ('Can''t keep more partitions than the current buffer size of: %i', 16, 1, @BufferSize)
    RETURN
  END

  DECLARE @CurrentPartition INT
  SET @CurrentPartition = DATEDIFF(day, 0, @Now) % @BufferSize

  DECLARE @OldestPartitionToKeep INT
  SET @OldestPartitionToKeep 
    = (@CurrentPartition + @BufferSize - @PartitionsToKeep) % @BufferSize

  /* Start from the next partition up from current and move forward */
  DECLARE @P INT = (@CurrentPartition + 1) % @BufferSize
  DECLARE @I INT = 0
  WHILE @I < @BufferSize - @PartitionsToKeep BEGIN 
    DECLARE @Sql NVARCHAR(4000)
      = 'ALTER TABLE RingBuffer SWITCH PARTITION <p> TO RingBuffer_SwitchTarget PARTITION <p>'
    SET @Sql = REPLACE(@Sql, '<p>', CAST(@P + 1 AS NVARCHAR)) /* Partition are 1 offset */

    EXEC sp_executesql @Sql

    SET @I = @I + 1;
    SET @P = (@P + 1) % @BufferSize
  END
    
  MERGE INTO RingBuffer_Aggregate AS T
  USING (SELECT CAST(EventTime AS DATE) AS EventTime
           , Attribute
           , SUM(Value) AS Value
         FROM RingBuffer_SwitchTarget
         GROUP BY CAST(EventTime AS DATE), Attribute) AS A
    ON A.EventTime = T.EventTime
    AND A.Attribute = T.Attribute
    WHEN MATCHED THEN
      UPDATE SET T.Value = T.Value + A.Value
    WHEN NOT MATCHED THEN
      INSERT (EventTime, Attribute, Value)
      VALUES (A.EventTime, A.Attribute, A.Value);

    TRUNCATE TABLE RingBuffer_SwitchTarget;
END

CREATE PROCEDURE RingbufferRotate    @ Now DATETIME = NULL    , @ PartitionsToKeep INT = 7 AS SET NOCOUNT ON BEGIN    IF @ Now IS NULL SET @ Now = GETUTCDATE ( )    DECLARE @ BufferSize INT    SELECT @ BufferSize = COUNT ( * )    FROM sys . partitions P    JOIN sys . tables T      ON P . object_id = T . object_id    WHERE T . name = 'RingBuffer'    IF @ PartitionsToKeep > @ BufferSize BEGIN      RAISERROR ( 'Can' 't keep more partitions than the current buffer size of: %i' , 16 , 1 , @ BufferSize )      RETURN    END    DECLARE @ CurrentPartition INT    SET @ CurrentPartition = DATEDIFF ( day , 0 , @ Now ) % @ BufferSize    DECLARE @ OldestPartitionToKeep INT    SET @ OldestPartitionToKeep      = ( @ CurrentPartition + @ BufferSize - @ PartitionsToKeep ) % @ BufferSize    /* Start from the next partition up from current and move forward */    DECLARE @ P INT = ( @ CurrentPartition + 1 ) % @ BufferSize    DECLARE @ I INT = 0    WHILE @ I < @ BufferSize - @ PartitionsToKeep BEGIN      DECLARE @ Sql NVARCHAR ( 4000 )        = 'ALTER TABLE RingBuffer SWITCH PARTITION <p> TO RingBuffer_SwitchTarget PARTITION <p>'      SET @ Sql = REPLACE ( @ Sql , '<p>' , CAST ( @ P + 1 AS NVARCHAR ) ) /* Partition are 1 offset */      EXEC sp_executesql @ Sql      SET @ I = @ I + 1 ;      SET @ P = ( @ P + 1 ) % @ BufferSize    END         MERGE INTO RingBuffer_Aggregate AS T    USING ( SELECT CAST ( EventTime AS DATE ) AS EventTime            , Attribute            , SUM ( Value ) AS Value          FROM RingBuffer_SwitchTarget          GROUP BY CAST ( EventTime AS DATE ) , Attribute ) AS A      ON A . EventTime = T . EventTime      AND A . Attribute = T . Attribute      WHEN MATCHED THEN        UPDATE SET T . Value = T . Value + A . Value      WHEN NOT MATCHED THEN        INSERT ( EventTime , Attribute , Value )        VALUES ( A . EventTime , A . Attribute , A . Value ) ;      TRUNCATE TABLE RingBuffer_SwitchTarget ; END

And that’s really all there is to it.

Summary

In this blog, I have shown you how to implement a rotating log without the need for a lot of partition function management. Using a ring buffer of dates, a full day of data can be rotated around with a single SWITCH statement.

The ring buffer mechanism can also be used for heaps or tables with only a cluster index that are so large that online index rebuilds don’t fit in memory and tempdb . Instead of rebuilding the entire table, each entry in the ring buffer can be rebuilt, one at a time, in a rotating manner. It is trivial to modify the above stored procedure to implement this functionality.