Sql-Server

什麼是大型 TTL 數據集合的可擴展儲存機制

  • October 16, 2013

我們目前有一個舊的 web 服務，它將每個 xml 請求/響應儲存在 Sql Server 中。數據只需要保留 3 天即可視為過期。Sql Server 不擅長刪除行，因為每個刪除都是事務日誌的一部分。數據庫目前以每天 6-10GB 的速度增長，而且還會增加。只有大約 1% 的儲存響應被呼叫，因此這是一個非常繁重的應用程序。每個請求/響應 xml 文件的大小最大為 14k。

對於每天高達 50/100gb 的數據，您會選擇哪種儲存機制？

我知道該解決方案是不可持續的，我真的在尋找戰術解決方案，因為我們無法輕易更改所有客戶查詢和重新查詢數據的方式。我們可以研究一個對 TTL（Riak、Postgres）等具有本機支持的數據庫，或者文件/blob s3/azure 儲存解決方案更適合？如果我們必須掃描多個儲存桶（因為儲存桶有容量限制），尤其是與目前的 sql server 單表查找相比，雲 blob 儲存解決方案的問題可能是查找性能。

對想法和建議持開放態度？

我創建了一個非常簡單的展示，說明分區切換如何為您工作：

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1
IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date
IF EXISTS ( SELECT * FROM sys.partition_functions WHERE name = 'pf_date' ) DROP PARTITION FUNCTION pf_date
GO

CREATE PARTITION FUNCTION pf_date (DATE) AS RANGE RIGHT FOR VALUES ( '1 Jan 2013', '1 Feb 2013', '1 Mar 2013', '1 Apr 2013', '1 May 2013', '1 Jun 2013', '1 Jul 2013', '1 Aug 2013', '1 Sep 2013', '1 Oct 2013', '1 Nov 2013', '1 Dec 2013' );
GO

-- !!TODO don't use ALL TO PRIMARY, instead create individual files and filegroups
CREATE PARTITION SCHEME ps_date AS PARTITION pf_date ALL TO ( [PRIMARY] )
GO

IF OBJECT_ID('dbo.largeTable') IS NULL
CREATE TABLE dbo.largeTable 
   ( 
   rowId INT IDENTITY, 
   someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
   dateAdded DATE DEFAULT GETDATE(), 
   addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
   ts ROWVERSION,

   CONSTRAINT pk PRIMARY KEY(dateAdded, rowId) 
   ) ON [ps_date](dateAdded)
GO


CREATE TABLE dbo.largeTable1
   ( 
   rowId INT IDENTITY, 
   someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
   dateAdded DATE DEFAULT GETDATE(), 
   addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
   ts ROWVERSION,

   CONSTRAINT pk2 PRIMARY KEY(dateAdded, rowId) 
   ) ON [PRIMARY]
GO


-- Create some dummy data
INSERT INTO dbo.largeTable DEFAULT VALUES
GO 5

-- Multiply the data a bit
INSERT INTO dbo.largeTable ( someData, dateAdded, addedBy ) 
SELECT someData, DATEADD( month, -2, dateAdded ), addedBy
FROM dbo.largeTable
UNION ALL
SELECT someData, DATEADD( month, -1, dateAdded ), addedBy
FROM dbo.largeTable 
UNION ALL
SELECT someData, DATEADD( month, 1, dateAdded ), addedBy
FROM dbo.largeTable
GO


-- Have a look at the data
SELECT 'before' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Switch out oldest partition with data and truncate it
ALTER TABLE dbo.largeTable SWITCH PARTITION 9 TO dbo.largeTable1
GO

TRUNCATE TABLE dbo.largeTable1
GO

SELECT 'after' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Merge the range as no longer required
ALTER PARTITION FUNCTION pf_date() MERGE RANGE ( '1 Sep 2013' );
GO

TRUNCATE TABLE 在某些條件下可以是最少記錄的操作。請查閱數據載入性能指南以獲取有關該主題的更全面的處理。還有一個關於“從分區或表中刪除所有行”的部分。

祝你好運！

我看到您正在尋找非常快速的周轉，只要您擁有 SQL SERVER 的企業版，分區就可以很好地工作。只是想展示另一種“定制的水平分區方法”。正如您所說，沒有太多時間對每個客戶進行更大的更改；在我看來，一旦在內部進行了測試，這種唯一的數據庫更改工作最快。

假設這是目前系統。我們正在嘗試設計一種允許輕鬆刪除過期數據而不會影響現有系統性能的設計。

   if object_id('mytesttable','u') is not null
       drop table mytesttable
   go
   create table mytesttable
   (
       id      int identity
       ,value  nvarchar(max)
       ,lastdt date default getdate()
   )
   go

   if object_id('pr_insert_mytesttable','p') is not null
       drop procedure pr_insert_mytesttable
   go
   create procedure pr_insert_mytesttable
   (
       @value nvarchar(max)
   )
   as
   begin
   begin try
   begin tran

       insert into mytesttable (value)
       select @value

   commit tran
   end try
   begin catch
       rollback tran
   end catch
   end
   go


   insert mytesttable (value,lastdt)
               select '<my data on 10-01-2013>','10-01-2013'
   union all   select '<my data on 10-02-2013>','10-02-2013'
   union all   select '<my data on 10-03-2013>','10-03-2013'
   union all   select '<my data on 10-04-2013>','10-04-2013'
   union all   select '<my data on 10-05-2013>','10-05-2013'
   union all   select '<my data on 10-06-2013>','10-06-2013'
   union all   select '<my data on 10-07-2013>','10-07-2013'
   union all   select '<my data on 10-08-2013>','10-08-2013'
   union all   select '<my data on 10-09-2013>','10-09-2013'
   union all   select '<my data on 10-010-2013>','10-10-2013'
   union all   select '<my data on 10-011-2013>','10-11-2013'
   union all   select '<my data on 10-012-2013>','10-12-2013'
   union all   select '<my data on 10-013-2013>','10-13-2013'
   union all   select '<my data on 10-014-2013>','10-14-2013'
   union all   select '<my data on 10-015-2013>','10-15-2013'

這是您目前的表格

目前表結構

正如您所提到的，讓我們將到期門檻值設為 3 天。想法是創建具有最多 3 天價值數據的單個表。並創建 API 層，該層將在不更改客戶端程式碼的情況下確定將傳入數據放在何處。所以最終結果將是 5 個不同的表，最多儲存 3 天的數據。API 將根據需要創建新表。

您將需要一次現有的數據遷移腳本；如下所示。如果您執行以下腳本，它將創建新表並從原始表中複製適當的數據，如下圖所示。

   Declare @mindt date, @maxdt date, @sql nvarchar(max)='', @tblname nvarchar(200)='', @paradef nvarchar(1000)=''
   select @mindt = MIN(lastdt),@maxdt=MAX(lastdt) from mytesttable
   while @mindt <= @maxdt
   begin

       set @mindt = DATEADD(day,3,@mindt)
       set @tblname = 'mytesttable'+'_'+Replace(cast(@mindt as nvarchar(10)),'-','')

       set @sql = N'
                       if object_id('''+@tblname+N''',''u'') is not null
                           drop table '+@tblname+N'
                   '
       execute sp_executesql @sql  

       set @sql=   N'          
                       create table '+@tblname+N'
                       (
                           id      int identity
                           ,value  nvarchar(max)
                           ,lastdt date default getdate()
                       )
                   '
       execute sp_executesql @sql

       set @sql = N'
                       Delete from '+ @tblname+N'

                       ;set identity_insert '+ @tblname+N' ON
                       insert into '+@tblname+N' (id,value,lastdt)
                       select id,value,lastdt
                       from mytesttable
                       where lastdt < @mindt and lastdt >= DATEADD(day,-3,@mindt) 
                       ;set identity_insert '+ @tblname+N' OFF

                       --select * from '+ @tblname+N'
                   '
       set @paradef=N'@mindt date' 
       execute sp_executesql @sql,@paradef,@mindt
   end

新系統

現在插入 API 會是什麼樣子？這是一個修改過的插入 API，它將查看表是否存在於該數據適合的位置或需要創建新表的位置。

   if object_id('pr_insert_mytesttable_modified','p') is not null
       drop procedure pr_insert_mytesttable_modified
   go
   create procedure pr_insert_mytesttable_modified
   (
       @value nvarchar(max)
   )
   as
   begin
   begin try
   begin tran
       declare  @sql nvarchar(MAX)=''
               ,@chktblname nvarchar(200)=''
               ,@inserttblname nvarchar(200)=''
               ,@today date=getdate()
               ,@paradef nvarchar(1000)=''

       set @inserttblname = 'mytesttable'+'_'+Replace(cast(@today as nvarchar(10)),'-','')
       set @chktblname = 'mytesttable'+'_'+Replace(cast(DATEADD(day,-3,@today) as nvarchar(10)),'-','')

       IF EXISTS (select top 1 1 from sys.sysobjects where name = @chktblname)
       BEGIN
           SET @inserttblname = @chktblname
       END
       ELSE
       BEGIN
           set @sql = N'
                           if object_id('''+@inserttblname+N''',''u'') is not null
                               drop table '+@inserttblname+N'
                       '
           execute sp_executesql @sql  
           set @sql=   N'          
                           create table '+@inserttblname+N'
                           (
                               id      int identity
                               ,value  nvarchar(max)
                               ,lastdt date default getdate()
                           )
                       '
           execute sp_executesql @sql
       END


       set @sql = N'
                       insert into '+@inserttblname+N' (value)
                       select @value
                   '
       set @paradef=N'@value NVARCHAR(MAX)'    
       execute sp_executesql @sql,@paradef,@value

   commit tran
   end try
   begin catch
       rollback tran
   end catch
   end
   go

那麼為什麼要檢查這個動態程式碼生成呢？

這不需要企業版。

程式碼生成並不像看起來那麼複雜。它只有一次創建。

一個簡單的 DROP TABLE API 將完成您需要的最快刪除操作。

在任何給定時間，在您的情況下，選擇都不會查看超過 3 天的價值數據。

當您刪除整個表時，刪除/截斷後沒有問題需要處理，例如碎片。

引用自：https://dba.stackexchange.com/questions/51531

相關問答

Sql-Server

不同的磁碟驅動器相同的後端儲存

  • March 11, 2022
檢視問答
Sql-Server

嘗試回收未使用空間導致 SQL Server 中已用空間顯著增加

  • March 7, 2022
檢視問答
Sql-Server

具有混合儲存的 Windows 伺服器虛擬機上的 Microsoft SQL 伺服器儲存最佳實踐

  • February 27, 2022
檢視問答
Sql-Server

IBM FlashSystem V9000 儲存 - 是否應將 os、mdf 和 ldf 以及 tempdb 分離到不同的捲上？

  • December 26, 2021
檢視問答
Sql-Server

從半滿的巨大 FG 中回收空間，其中包含分區和 LOB 數據

  • December 16, 2021
檢視問答
Sql-Server

SAN 上臨時 db 文件的性能

  • November 1, 2021
檢視問答