07.04.2014

PDW: Index Organisation in der Praxis

Technical Value

Eine der großen Stärken der PDW ist der Clustered Columnstore Index (CCI). Allerdings beherbergt diese Funktion auch einige Tücken. Ein großer Performance Killer ist der Delta Store. Hier werden Datensätze abgelegt, die vorerst nicht in den Column Store überführt werden und somit nicht von den Vorteilen profitieren können (u.a. sehr gute Komprimierung, schnelle Abfrage-Performance). Dieses Problem zeigt noch größere Auswirkungen auf die Leistung, wenn neben dem CCI, Partitionen in die Tabellenstruktur einfließen. Der Delta Store wird pro Partition auf den Distributionen aufgebaut. Da erst ab einer Zeilenanzahl von ca. 100Tsd in den CCI komprimiert wird, können sich schnell große Datenmengen ergeben, die nicht von den Eigenschaften des CCI profitieren.

Ein einfaches Beispiel: 2 Compute Nodes => 16 Distributionen bei 2 Partitionen => 32 Deltastores macht bei 100Tsd Datensätzen pro Delta-Store insgesamt 3,2mio Datensätze. Das Problem verschärft sich mit zunehmender Anzahl an Partitionen. In unserem Fall sind Partitionen notwendig um einen schnellen Ladevorgang mittels Partition-Switching gewährleisten zu können.

Zur Optimierung ist es daher sinnvoll, den Delta Store regelmäßig aufzuräumen. Vorab möchte ich kurz auf den Delta Store und einige seiner Eigenschaften eingehen.

Einen guten Überblick über den Inhalt des Delta Stores liefert die System-View sys.pdw_nodes_column_store_row_groups . Diese beinhaltet neben sämtlichen CCI der Datenbank auch eine Übersicht über den Zustand der betroffenen Partitionen und die Anzahl an enthaltenen Datensätzen je Distribution. Die unterschiedlichen Zustände werden auf „Open“, „Closed“ oder „Compressed“ gesetzt. „Compressed“ wird genutzt um die Partitionen zu markieren die bereits in den CCI überführt werden konnten. Die Stellen markiert mit „Closed“ haben die maximale Anzahl an Zeilen erreicht und werden asynchron in den CCI verschoben (Hintergrundprozess „Tuple Mover“).

Für uns Interessant sind die mit „Open“ gekennzeichneten Sparten. Diese können mittels folgender Abfrage identifiziert werden.

  1.    create table #Reorganisation      
  2. with (distribution=replicate, location=user_db)      
  3. AS      
  4. SELECT      
  5.   LogicalTableName,      
  6.   partition_number,      
  7.   row_number()over(order by rows_per_partition desc,partition_number,LogicalTableName) as rownumber      
  8.  
  9. FROM      
  10.  
  11.     (SELECT      
  12.       object_name(IndexMap.object_id) AS LogicalTableName,      
  13.       CSRowGroups.partition_number,      
  14.       sum(CSRowGroups.total_rows) rows_per_partition      
  15.  
  16.     FROM sys.objects AS o      
  17.  
  18.     JOIN sys.indexes AS i      
  19.     ON o.object_id = i.object_id      
  20.  
  21.     JOIN sys.pdw_index_mappings AS IndexMap      
  22.     ON i.object_id = IndexMap.object_id      
  23.     AND i.index_id = IndexMap.index_id      
  24.  
  25.     JOIN sys.pdw_nodes_indexes AS NI      
  26.     ON IndexMap.physical_name = NI.name      
  27.     AND IndexMap.index_id = NI.index_id      
  28.  
  29.     JOIN sys.pdw_nodes_column_store_row_groups AS CSRowGroups      
  30.     ON CSRowGroups.object_id = NI.object_id      
  31.     AND CSRowGroups.pdw_node_id = NI.pdw_node_id      
  32.     AND CSRowGroups.index_id = NI.index_id      
  33.  
  34.     WHERE CSRowGroups.state_description ='OPEN'      
  35.     --And o.name = ''      
  36.  
  37.     Group By      
  38.       object_name(IndexMap.object_id),      
  39.       CSRowGroups.partition_number      
  40. ) ixinfo

Diese Zeilen entsprechen noch nicht im CCI abgelegten Datensätzen. Daher unsere Ansatz über ein Index Rebuild sämtliche mit „Open“ gekennzeichneten Delta Stores aufzulösen.

An dieser Stelle möchte ich auf den Blogeintrag meines Kollegen Hilmar Buchta verweisen (using-cursors-in-pdw ), da die Idee für den Cursor diesem Beitrag entstammt. Im Prinzip fragen wir den Tabellennamen und die mit “Open“ gekennzeichneten Partitionsnummer ab, die sich noch im Delta Store befinden. Die Anzahl Zeilen werden auf Partitionslevel summiert, da sich diese auf die Distributionen verteilen. Die Row_Number() im übergeordneten Select dient dem späteren Iterationsdurchlauf als Selektionskriterium. Die Partitionen werden anhand der Anzahl der enthaltenen Datensätze absteigend sortiert, so dass mit der größten Partition begonnen werden kann.

  1.    declare @maxrows int;      
  2. declare @i int;      
  3. declare @sql nvarchar(255);      
  4. set @maxrows=(Select count(*) from #Reorganisation);      
  5. set @i=1 while (@i<=@maxrows)      
  6. begin      
  7. Declare @TableName nvarchar(100);      
  8.   Set @TableName = (Select LogicalTableName from #Reorganisation      
  9.   where rownumber = @i);      
  10.  
  11. Declare @Partition int;      
  12.   Set @Partition = (Select partition_number from #Reorganisation where      
  13.   rownumber = @i);      
  14.  
  15.   set @sql ='Alter Index All On ' + @TableName +' Rebuild Partition =      
  16.   ' + convert(nvarchar,@Partition);      
  17. execute (@sql);      
  18. set @i=@i+1;      
  19.  
  20. end      
  21. drop table #Reorganisation;  

Es sollte berücksichtigt werden, dass die Rebuild Funktion einen Exklusiv Lock auf die Tabelle benötigt, andere Prozesse haben in der Zeit das Nachsehen. Möchte man eine bestimmte Tabelle identifizieren kann in der Where-Bedingung weiter eingeschränkt werden. Alternativ können die mit „Closed“ versehen Zeilen über ein Index Reorganize in den CCI verlagert werden. Dieser Prozess blockiert die betroffene Tabelle nicht.

In unserem Fall betraf dieses Problem eine vergleichsweise große Tabelle, die für die Historisierung des Datenbestandes vorgesehen war. Die Tabelle enthielt ca. 9 Mrd Zeilen mit insgesamt 124 Spalten, sowie 220 Partitionen. Von diesen waren 138 im Zustand „Open“ und 184 im Zustand „Closed“. Der aufmerksame Leser mag sich an dieser Stelle Fragen, wieso die Summe aus „Open“ und „Closed“ nicht glatt auf die 220 Partitionen aufgeht. Das liegt daran, dass Partitionen auf unterschiedlichen Distributionen keinen gemeinsamen Zustand innehaben müssen. Dies ist abhängig von der jeweiligen Größe des betroffenen Delta Stores.

Bevor wir den Prozess zur Restrukturierung angestoßen haben, hatten wir erhebliche Schwierigkeiten simple Abfrageergebnisse in angemessener Zeit zurück zu erhalten. Ein count_big(*) auf die gesamt Datenmenge dauerte mehrere Minuten. Nach dem Rebuild auf die „Open“ bzw.. „Closed“ Delta Stores, reduzierte sich die Abfragedauer auf unter eine Minute.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden