Soci (Soczó Zsolt) szakmai blogja

2014.04.22.

SQL Server 2014 újdonságok – 2. In-Memory OLTP – hash indexek

Filed under: Adatbázisok,SQL Server,SQL Server 2014,Szakmai élet — Soczó Zsolt @ 08:50

A memóriatáblákhoz kötelező minimum 1 indexet létrehozni, és ez az index egy hash index kell legyen. Mivel kötelező rajtuk egy primary key is, ezért az első indexen nem kell sokat gondolkodni, az a pk mögötti index lesz.
A hash index hasonló mint a .NET Dictionary vagy bármely más rendszer hash táblája. Az index értékekből hasht képeznek, az azonos hash-ű sorokat összeláncolják pointerek mentén. Az adatok NEM lapokon vannak elhelyezve, mivel a lapok a diszk adatok miatt voltak kialakítva a normál táblák esetén. Mivel nincsenek lapok, nem veszekednek a szálak a lapok latch-elésén se (mint a .NET lock kulcsszó), így sokkal nagyobb párhuzamosságot érnek el, mint a korábbi struktúra esetén. Diszk tábláknál ha egy lapon sokan matatnak egyszerre, be kell várják egymást. Itt minden sor szinten van tárolva, azaz sokkal finomabb felbontású régiókat kell védeni, ráadásul a módosítás sose helyben történik, de erről majd a konkurenciáról szóló részben írok.

Nincsenek GAM, SGAM, PFS, IAM és más egyéb szokásos adatösszetartó lapok, csakis az indexek tartják egyben egy tábla sorait. Ezért kell minimum 1 index a táblára.
További indexeket is létre lehet hozni, összesen max. 8-at. Azért ennyit, mert az ütköző hash-ek miatt össze kell láncolni a sorokat, így minden sorba be van építve fixen hash index számú pointer a láncoláshoz, de be kellett határolni a számukat, ne fogjanak el túl sok helyet. Így néz ki egy sor, a végén vannak a láncolt listákhoz a pointerek :

InMemoryTableRow

Az első mezőkről majd a párhuzamosság kezeléséről (concurrency) szóló részben írok.

A hash indexnél fontos a BUCKET_COUNT, ez szabályozza mennyire szórjon a hash függvény. Ha túl kicsire vesszük, hosszú láncolt listák alakulnak ki egy bucketen belül. Ha túl nagy, feleslegesen eszi a memóriát, és lassítja a scaneket, mivel minden bucketet végig kell néznie scan során. Mivel 64 biten dolgozunk, egy pointer 8 byte, így hash index mérete BUCKET_COUNT * 8 byte, csak mindig a legközelebbi, 2 hatványra kerekítik belülről. Azaz 1000-et megadva 1024 vödör keletkeze, ami 8192 byte (ez persze a gyakorlatban nagyon pici lenne, nem lenne hatékony index). Érdemes akkorára venni, mint ahány különböző értéke van a kulcsnak * 1-2. Érhető, hisz így nem nagyon lesz sokelemű a láncolt lista egy bucketen belül, kevés lesz az ütközés.
Primary keynél könnyű a helyzet, mivel egyedi értékeket tartalmaz. Ha a tábla például 3 millió sort tartalmaz, és várhatóan a közeljövőben se nő mondjuk 5 millió fölé, akkor egy hasonló nagyságrendű BUCKET_COUNT jó lehet. Később, ha sokkal több sor lesz, majd újra létrehozzuk a táblát más BUCKET_COUNT-tal. Ebből látszik, hogy a normál táblákkal szemben itt nem annyira önhangoló minden, mint megszoktuk.
Normál oszlopoknál egy select count(distinct oszlop) lekérdezéssel könnyű megnézni, mennyi egyedi érték van a táblában. Ha mondjuk 100 különböző érték van egy 5000 soros táblában, akkor egy nagy BUCKET_COUNT esetén is csak 100 bucketben lesz érték, azaz a bucketekben átlagban 50 sor lesz, amiben már csak lineárisan lehet keresni. Magyarul, erre nem jó a hash index, ide sima fa alapú indexre lesz szükség, mivel azt is létre lehet hozni a memória táblákon.

Nézzük meg a gyakorlatban!

Hozzunk létre egy memória táblát az AdventureWorks Person táblája adataival:

CREATE TABLE [Person].[Person_InMem]
(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),
	[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),
	[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),

CONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH 
(
	[BusinessEntityID]
)WITH ( BUCKET_COUNT = 1000)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

INSERT INTO [Person].[Person_InMem]
           ([BusinessEntityID]
           ,[PersonType]
           ,[NameStyle]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[EmailPromotion]
           ,[rowguid]
           ,[ModifiedDate])
SELECT [BusinessEntityID]
      ,[PersonType]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailPromotion]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[Person]

Az eredeti táblából kihagytam az xml oszlopokat, azok nem támogatottak memória táblákban. Hash bucket számosságnak 1000-et adtam meg. A táblában 19972 sor van. Mivel primary key-ről van szó, ugyanennyi különböző érték van benne. De csak 1024 bucketünk van, azért átlagosan kb. 20 sor kerül egy bucketbe. Nézzük meg, igaz-e az elmélet, az új dm_db_xtp_hash_index_stats nézettel:

SELECT 
   object_name(hs.object_id) AS 'object name', 
   i.name as 'index name', 
   hs.total_bucket_count,
   hs.empty_bucket_count,
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
   hs.avg_chain_length, 
   hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs 
   JOIN sys.indexes AS i 
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id
where object_name(hs.object_id) = 'Person_InMem'
object name    index name                        total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length
-------------- --------------------------------- -------------------- -------------------- ---------------------- -------------------- --------------------
Person_InMem   PK_Person_InMem_BusinessEntityID  1024                 0                    0                      19                   38

Látható, hogy az átlagos lánc hossza egy bucketben 19, de van legalább egy 38 hosszú is, mivel a hash függvény nem teljesen egyenletes.
Jó ez nekünk? Nagyon nem. Ideális esetben 1-2 tétel van csak egy bucketben, de 10 fölött már erősen érezhető lesz a teljesítményveszteség. Látható, hogy az üres bucketek (áttérek a vödör szóra, de elég hülyén hangzik) száma 0. Ez is probléma, “nincs hely” az új sorok részére, azaz még nagyobb torlódás lesz később. Az útmutató szerint az üres vödrök száma legalább 33% kell legyen, hogy legyen hely a táblának növekedni.

Próbáljuk meg magasabbra venni a vödrök számát, mondjuk 10000-re. Mivel memória tábláknál nincs alter table, újra létre kell hozni, más paraméterekkel, majd újratölteni. Ez után a hash statisztikák:

object name    index name                        total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length
-------------- --------------------------------- -------------------- -------------------  ---------------------- -------------------- --------------------
Person_InMem   PK_Person_InMem_BusinessEntityID  16384                4933                 30                      1                   8

Na, ez már sokkal jobban néz ki. 16384 vödör van (10000 után ez volt a következő 2 hatvány), 30%-a a vödröknek üres, átlagosan 1 elem van minden vödörben (nyilván kicsit több, mivel 19000 sor van 16000 vödörre).

De nem mindig lehet csak a vödrök számával ilyen szép helyzetet előállítani, ha túl sok ismétlődés van az adatokban. Hozzunk létre egy második indexet is, ezúttal a FirstName oszlopra:

SELECT count(distinct FirstName) FROM [AdventureWorks2012].[Person].[Person]; --1018

CREATE TABLE [Person].[Person_InMem]
(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),
	[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),
	[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),

CONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH 
(
	[BusinessEntityID]
)WITH ( BUCKET_COUNT = 10000)
,
INDEX [ix_FirstName] NONCLUSTERED HASH
(
	[FirstName]
) WITH ( BUCKET_COUNT = 1000)
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

De nem jön létre az index, mert:
Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

Ez van, a hasht gyorsan kell tudni előállítani, ebben a verzióban legalábbis nem mentek bele, hogy a nyelvek nemzeti sajátosságait kihasználva is képezzenek hasht. Ezekre lehet majd rendes fa alapú indexet létrehozni, de most koncentráljunk még a hash indexekre.

Lássuk a dátum oszlopon:

SELECT count(distinct [ModifiedDate]) FROM [AdventureWorks2012].[Person].[Person] --1290

CREATE TABLE [Person].[Person_InMem]
(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),
	[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),
	[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),

CONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH 
(
	[BusinessEntityID]
)WITH ( BUCKET_COUNT = 10000)
,
INDEX [ix_ModifiedDate] NONCLUSTERED HASH
(
	[ModifiedDate]
) WITH ( BUCKET_COUNT = 1000)
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Dátum oszlopra már megy a hash index.

A hash statisztikák:

object name    index name       total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length
-------------- --------------- -------------------- -------------------  ---------------------- -------------------- --------------------
Person_InMem   ix_ModifiedDate                 1024                 262                      25                   26                  143

Csapnivalóak az eredmények. Bár van 25% szabadon, azaz a vödrök számát nem lőttük alá, mégis átlagosan 26 sor van egy vödörben. Miért? Mert minden dátum kb. 20-szor szerepel a kulcsban, nem elég egyedi az oszlop. Az ilyenekre nem igazán hatékony a hash index.
A következő részben megnézzük, hogyan tárolja és hogyan használja a hash indexeket az SQL Server.

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress