Diversão com a compactação (columnstore) em uma tabela muito grande – parte 2

Diversão com a compactação (columnstore) em uma tabela muito grande – parte 2

Diversão com a compactação (columnstore) em uma tabela muito grande - parte 2
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Na parte 1, mostrei como a compactação de página e de columnstore poderia reduzir o tamanho de uma tabela de 1 TB em 80% ou mais. Enquanto eu estava impressionado por poder reduzir uma mesa de 1 TB a 50 GB, não fiquei muito feliz com a quantidade de tempo que demorou (de 2 a 14 horas). Com algumas dicas graciosamente emprestadas de pessoas como Joe Obbish, Lonny Niederstadt, Niko Neugebauer e outros, neste post, tentarei fazer algumas alterações na minha tentativa original de obter melhor desempenho de carga. Como o índice columnstore normal não compacta melhor que a compactação de página neste conjunto de dados, e levou 13 horas a mais para chegar lá, vou me concentrar apenas na solução mais avançada usando COLUMNSTORE_ARCHIVE compressão.

tarefa de compactação columnstore em segundo plano – foi um desperdício permitir que isso acontecesse durante a população, já que eu planejava reconstruir no final de qualquer maneira. Desta vez eu vou:

  • desativar esta tarefa usando o sinalizador de rastreamento global 634

Raspei a função e o esquema da partição inicial e construí uma nova com base em uma distribuição mais uniforme dos dados. Quero que 8 partições correspondam ao número de núcleos e ao número de arquivos de dados, para maximizar o “paralelismo do pobre homem” que pretendo usar.

Primeiro, precisamos criar um novo conjunto de grupos de arquivos, cada um com seu próprio arquivo:

ALTER DATABASE OCopy ADD FILEGROUP FG_CCI_Part1;
 
ALTER DATABASE OCopy ADD FILE (name = N'CCI_Part_1', size = 250000, 
  filename = 'K:Datao_cci_p_1.mdf') TO FILEGROUP FG_CCI_Part1;
 
-- ... 6 more ...
 
ALTER DATABASE OCopy ADD FILEGROUP FG_CCI_Part8;
 
ALTER DATABASE OCopy ADD FILE (name = N'CCI_Part_8', size = 250000, 
  filename = 'K:Datao_cci_p_8.mdf') TO FILEGROUP FG_CCI_Part8;

Em seguida, observei o número de linhas na tabela: 3.754.965.954. Para distribuir aqueles exatamente uniformemente em 8 partições, isso seria 469.370.744,25 linhas por partição. Para que funcione bem, vamos fazer com que os limites da partição acomodem os Próximo múltiplo de 1.048.576 linhas. Isto é 1,048,576 x 448 = 469,762,048 – qual seria o número de linhas nas quais filmamos nas 7 primeiras partições, deixando 466,631,618 linhas na última partição. Para ver o real OID valores que serviriam como limites para conter o número ideal de linhas em cada partição, executei essa consulta na tabela original (desde que demorou 25 minutos para executar, aprendi rapidamente a despejar esses resultados em uma tabela separada):

;WITH x AS 
(
  SELECT OID, rn = ROW_NUMBER() OVER (ORDER BY OID)
    FROM dbo.tblOriginal WITH (NOLOCK)
)
SELECT OID, PartitionID = 1+(rn/((1048576*448)+1))
  INTO dbo.stage
  FROM x
  WHERE rn % (1048576*112) = ;

Mais para descompactar aqui do que você poderia esperar. O CTE faz todo o trabalho pesado, pois precisa escanear toda a tabela de 1,14 TB e atribuir um número de linha para cada linha. Eu só quero voltar a cada (1048576*112)th linha, porém, como essas são minhas linhas de limite de lote, é isso que o WHERE cláusula sim. Lembre-se de que quero dividir o trabalho em lotes mais perto de 100 milhões de linhas por vez, mas também não quero processar 469 milhões de linhas de uma só vez. Portanto, além de dividir os dados em 8 partições, quero dividir cada uma dessas partições em quatro lotes de 117.440.512 (1,048,576*112) linhas. Cada conjunto adjacente de quatro lotes pertence a uma partição, portanto, o PartitionID Eu derivo apenas adiciona um ao resultado do número da linha atual inteiro dividido por (1,048,576*448), que garante que o limite esteja sempre no conjunto “esquerdo”. Em seguida, adicionamos um ao resultado, porque, caso contrário, estaríamos nos referindo a uma coleção de partições baseada em 0, e ninguém deseja isso.

Leia Também  Como recuperar exclusões acidentais no Armazenamento de Blob do Azure

Ok, foram muitas palavras. À direita está uma imagem mostrando o conteúdo (abreviado) do stage tabela (clique para mostrar o resultado completo, destacando os valores dos limites da partição).

Podemos então derivar outra consulta dessa tabela intermediária que mostra os valores mínimo e máximo para cada lote dentro de cada partição, bem como o lote extra não contabilizado (as linhas na tabela original com OID maior que o valor limite mais alto):

;WITH x AS 
(
  SELECT OID, PartitionID FROM dbo.stage
),
y AS 
(
  SELECT PartitionID, 
    MinID = COALESCE(LAG(OID,1) OVER (ORDER BY OID),-1)+1,
    MaxID = OID 
  FROM x
  UNION ALL 
  SELECT PartitionID = 8, 
    MinID = MAX(OID)+1, 
    MaxID = 4000000000 -- easier than remembering the real max 
  FROM x
)
SELECT PartitionID, 
  BatchID = ROW_NUMBER() OVER (PARTITION BY PartitionID ORDER BY MinID),
  MinID, 
  MaxID,
  RowsInRange = CONVERT(int, NULL)
INTO dbo.BatchQueue
FROM y;
 
-- let's not leave this as a heap:
CREATE UNIQUE CLUSTERED INDEX PK_bq ON dbo.BatchQueue(PartitionID, BatchID);

Esses valores são assim:

Diversão com a compactação (columnstore) em uma tabela muito grande - parte 2 1
Para testar nosso trabalho, podemos derivar daí um conjunto de consultas que serão atualizadas BatchQueue com contagens de linhas reais da tabela.

DECLARE @sql nvarchar(max) = N'';
 
SELECT @sql += 'UPDATE dbo.BatchQueue SET RowsInRange = (
  SELECT COUNT(*)
    FROM dbo.tblOriginal WITH (NOLOCK)
    WHERE CostID BETWEEN ' + RTRIM(MinID) + ' AND ' + RTRIM(MaxID) + '
) WHERE MinID = ' + RTRIM(MinID) + ' AND MaxID = ' + RTRIM(MaxID) + ';'
FROM dbo.BatchQueue;
 
EXEC sys.sp_executesql @sql;

Isso levou cerca de 6 minutos no meu sistema. Em seguida, você pode executar a seguinte consulta para mostrar que todos os lotes, exceto o último, são capazes de preencher totalmente os grupos de linhas e não deixar resto para o uso potencial do armazenamento em delta:

Leia Também  Aonde assistir Ignite - SQL TECH BLOG
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
ALTER TABLE dbo.BatchQueue ADD DeltaStore AS (RowsInRange % 1048576);

Agora a tabela fica assim:

Diversão com a compactação (columnstore) em uma tabela muito grande - parte 2 2

Certamente, todo lote tem 117.440.512 milhões de linhas calculadas, exceto a última que, pelo menos idealmente, conterá nosso único armazenamento delta não compactado. Provavelmente também podemos evitar isso, alterando ligeiramente o tamanho do lote para esta partição para que todos os quatro lotes sejam executados com o mesmo tamanho ou alterando o número de lotes para acomodar algum outro múltiplo de 102.400 ou 1.048.576. Uma vez que isso exigiria novas OID valores da tabela base, adicionando mais 25 minutos ao nosso esforço de migração, deixarei esta partição imperfeita deslizar – especialmente porque não estamos obtendo o benefício completo da compactação de arquivo.

o BatchQueue A tabela está começando a mostrar sinais de que é útil para processar nossos lotes para migrar dados para nossa nova tabela columnstore clusterizada e particionada. Que precisamos criar, agora que conhecemos os limites. Existem apenas sete limites, então você certamente poderia fazer isso manualmente, mas eu gosto de fazer com que o SQL dinâmico faça meu trabalho por mim:

DECLARE @sql nvarchar(max) = N'';
 
SELECT @sql = N'CREATE PARTITION FUNCTION PF_OID([bigint])
AS RANGE LEFT FOR VALUES 
(
  ' + STRING_AGG(MaxID, ',
  ') + '
);' FROM dbo.BatchQueue
   WHERE PartitionID < 8 
     AND BatchID = 4;
 
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Resultados:

CREATE PARTITION FUNCTION PF_OID([bigint])
AS RANGE LEFT FOR VALUES 
(
   489542824,
   965961973,
  1436859196,
  1909084364,
  2380757248,
  2851969364,
  3322791542
);

Uma vez criado, podemos criar nosso esquema de partições e atribuir cada partição sucessiva ao seu arquivo dedicado:

CREATE PARTITION SCHEME PS_OID AS PARTITION PF_OID TO 
(
  CCI_Part1, CCI_Part2, CCI_Part3, CCI_Part4, 
  CCI_Part5, CCI_Part6, CCI_Part7, CCI_Part8
);

Agora podemos criar a tabela e prepará-la para a migração:

CREATE TABLE dbo.tblPartitionedCCI
(
	OID bigint          NOT NULL,
	IN1 int             NOT NULL,
	IN2 int             NOT NULL,
	VC1 varchar(3)      NULL,
	BI1 bigint          NULL,
	IN3 int             NULL,
	VC2 varchar(128)    NOT NULL,
	VC3 varchar(128)    NOT NULL,
	VC4 varchar(128)    NULL,
	NM1 numeric(24,12)  NULL,
	NM2 numeric(24,12)  NULL,
	NM3 numeric(24,12)  NULL,
	BI2 bigint          NULL,
	IN4 int             NULL,
	BI3 bigint          NULL,
	NM4 numeric(24,12)  NULL,
	IN5 int             NULL,
	NM5 numeric(24,12)  NULL,
	DT1 date            NULL,
	VC5 varchar(128)    NULL,
	BI4 bigint          NULL,
	BI5 bigint          NULL,
	BI6 bigint          NULL,
	BT1 bit             NOT NULL,
	NV1 nvarchar(512)   NULL,
	VB1 varbinary(8000) NULL,
	IN6 int             NULL,
	IN7 int             NULL,
	IN8 int             NULL,
 	-- need to create a PK constraint on the partition scheme...
 	CONSTRAINT PK_CCI_Part PRIMARY KEY CLUSTERED (OID) ON PS_OID(OID)
);
 
-- ... only to drop it immediately...
ALTER TABLE dbo.tblPartitionedCCI DROP CONSTRAINT PK_CCI_Part;
GO
 
-- ... so we can replace it with the CCI:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Part
  ON dbo.tblPartitionedCCI 
  ON PS_OID(OID);
GO
 
-- now rebuild with the compression we want:
ALTER TABLE dbo.tblPartitionedCCI  
REBUILD PARTITION = ALL WITH 
(  
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1 TO 7), 
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (8)
);

No próximo post, configurarei ainda mais o BatchQueue tabela, crie um procedimento para processos para enviar os dados para a nova estrutura e analise os resultados.

Leia Também  What is Oracle ASM to Azure IaaS?