Sql Server Numa Ayarları

Sunucunuzun performasını direkt olarak etkileyecek, yanlış kullanılması halinde ciddi performans kayıplarına sebep olabilecek bir yapılandırmadan bahsediyoruz. Kesinlikle uzmanlık gerektiren bir konudur. Bu konuda detaylı bilgi aramak, samanlıkla iğne aramaya benziyor. Elimden geldiğince bu konuda bildiklerimi aktaracağım çayınızı kahvenizi alınız ve sabırla okuyunuz.

Numa Nedir?

Öncelikle NUMA ne demektir ondan kısaca bahsedelim. Numa “Non-Uniform Memory Access” yani düzensiz bellek erişimi veya düzensiz bellek mimarisi olarak adlandırılan tasarımdır.  İşlemci ve bellek arasındaki erişimi optimize etmek için kullanılır. Standart mimari nasıldır?

Standart mimaride işlemci veri yolu ile belleklere bağlıdır ve kendisine verilen görevleri yerine getirmek için geçici depolama ortamı olarak bu bellekleri kullanır. Ancak bizler genelle büyük sistemlerin yöneticiliğini yapmaktayız ve çalıştığımız sunucularda çok sayıda işlemci bulunmaktadır. Peki bu yapı birden fazla işlemci olan bir ortamda nasıl olurdu?

Birden fazla işlemci olan ortamlarda üstte belirttiğim resimde bulunan şekilde bir mimari olursa ne olur? Tüm işlemciler tüm belleğe erişebilirler. Ancak veri yolu ve ramlere erişmeyi sağlayan kontrolcü tek olduğu için işlemciler birbirlerini beklemek zorunda kalırdı. ( UMA yani Uniform Memory Access – Düzenli Bellek Erişimi )

Üstelik günümüzde üretilen tüm işlemciler ramlerden daha hızlıdır. Yani burada ramlere erişirken kesinlikle bir darboğaz yaşamamız olasıdır. Peki nasıl olması gerekiyor ki herhangi bir darboğaz yaşamayalım ve tüm işlemcileri aynı anda kullanabilelim?

Üstte gördüğünüz yapıyı inceleyelim. Görüldüğü üzere her bir işlemci kendine ait bir ram bloğuna direkt olarak çok hızlı bir şekilde erişebiliyor. Ayrıca ihtiyaç duyduğu anda ise birbirleri üzerinden diğer ram bloklarına da erişebiliyor. İşte Numa dediğimiz tasarım budur. İsmi de buradan gelmektedir. Düzensiz bellek erişimi, kendine ait ram bloğuna hızlı diğer bloklara daha yavaş. Yukarıda gördüğünüz resimde her işlemci ve ram bloğu bir numa düğümüdür.

Sunucunuzda kaç adet numa düğümünüz var? Bu sorunun cevabını görev yöneticinizi açarak basitçe görebilirsiniz. Performans kısmına gelin ve CPU tıkladıktan sonra boş bir yere sağ tıklayarak numa düğümlerine tıklayınız.

Görüldüğü üzere benim makinamda 2 Numa Node bulunmaktadır.

SQL SERVER NUMA ilişkisi

Şimdi yavaş yavaş asıl konumuza geçebiliriz. SQL server 2016 ve üzeri sürümlerde otomatik Soft-NUMA özelliği bulunur. Ancak bunları kendinize göre optimize etmeniz gereken durumlarda olur.

Örnek :

Sql Server Enterprice Edition kullanıyorsanız size maksimum 20 Fiziksel + 20 Mantıksal Core  kullanabilme limiti verecektir. Sunucunuzun 2 numa düğümünde 2şer soket ve 12 core olduğunu varsayarsak her numa için kullanabileceğiniz 12 fiziksel 12 mantıksal core bulunmaktadır. Enterprice editinde limitli kullanma hakkı olduğu için 48 core kullanamayacak 40 core kullanabilecektir. Bu 40 core’ı 24-16 şeklinde dağıtmış olabilir. Bu çoğunlukla sorunlara sebep olmaz ancak daha efektif bir kullanım için 20 – 20 kullanmasını istiyor olabilirsiniz. Veya tam tersi core sayısını 20 – 20 şeklinde vermiştir ancak sizin paralel işlem çalıştıracağınız büyük sorgularınız vardır maxdop = 20 yapmak yerine maxdop = 24 yapmak isteyebilirsiniz. Bu durumda da 24 – 16 şeklinde olmasını isteyebilirsiniz. Ekran görüntüleri ile devam edelim şimdi daha iyi anlayacaksınız.

Her performans tunning işlemine ortamı tanıyarak başlarım ve mutlaka öncelikle sql server versiyonuna bakarım.

SELECT @@VERSION

Bu komut size sql server sürümünü gösterecektir. Benim örnek için oluşturduğum instance sürümü :  Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19045: )

Sonra üzerinde çalıştığım makinanın özelliklerine bakarım ki bunu da yukarda belirtmiştim. 2 numa node ve 48 mantıksal işlemci, 64 GB ram.

EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

Komutu ile işlemci loglarına baktığımda şöyle bir uyarı olduğunu görüyorum.

SQL Server detected 2 sockets with 12 cores per socket and 24 logical processors per socket, 48 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

48 mantıksal işlemcinin yalnızca 40 adetini kullanabildiğimi ve bunun sql server lisansı ile limitlendiğini anlıyorum.

SELECT osn.node_id, osn.node_state_desc, osn.memory_node_id, osn.processor_group, osn.cpu_count, osn.online_scheduler_count,

       osn.idle_scheduler_count, osn.active_worker_count,

          osmn.pages_kb/1024 AS [Committed Memory (MB)],

          osmn.locked_page_allocations_kb/1024 AS [Locked Physical (MB)],

          CONVERT(DECIMAL(18,2), osmn.foreign_committed_kb/1024.0) AS [Foreign Commited (MB)],

          osmn.target_kb/1024 AS [Target Memory Goal (MB)],

          osn.avg_load_balance, osn.resource_monitor_state

FROM sys.dm_os_nodes AS osn

INNER JOIN sys.dm_os_memory_nodes AS osmn

ON osn.memory_node_id = osmn.memory_node_id

WHERE osn.node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

Yukarıda yazdığım script ise bana hangi numa nodunda kaç mantıksal işlemci ile hizmet verildiğini belirtiyor.

Sunucuma ne kadar yük geleceğini, ne amaçla kullanılacağını araştırıyorum. Araştırmalarım sonucunda görüyorum ki çok fazla paralel işleme ihtiyacım yok. Burada her bir numa düğümüne atan mantıksal işlemci miktarını eşitlemeye karar veriyorum.

ALTER SERVER CONFIGURATION

SET PROCESS AFFINITY CPU = 0 TO 9, 10 TO 19, 28 TO 37, 38 TO 47;

Yukarıda yer alan komut ile 4 soketimin hangi mantıksal işlemcileri kullanacağını belirtiyorum. İlk numa nodeumda 0-23 arasındaki mantıksal işlemciler, ikinci numa nodeumda ise 24-47 arasındaki mantıksal işlemcileri kullanabilirim. Bu sayede aşağıda yer alan görüntüyü elde ediyorum.

Görüldüğü üzere her sokete 10 mantıksal işlemci atadık ve bu işlemciler kendi numa node’u içerisinde yer alan belleğe daha hızlı erişebilecekleri için transaction yoğunluğu fazla olan ortamlarda muhtemelen daha iyi çalışacaklardır. Muhtemelen diyorum çünkü bu bahsedilen ayarlar ortamdan ortama çok değişkenlik gösterebilir.

SQL Server NUMA farkındalığına sahip olduğundan, sorgunuz her iki NUMA düğümü içinde paralel olarak çalışabilir. Sorgunun tek bir NUMA düğümünde mi yoksa birden fazla NUMA düğümünde mi çalışacağı, çeşitli faktörlere bağlı olacaktır.

Bu faktörler arasında sorgunun karmaşıklığı, yürütme planı, kaynak kullanımı ve mevcut yük durumu gibi etkenler yer alır. SQL Server, paralel sorguları mümkün olduğunca NUMA düğümleri arasında dengeli bir şekilde dağıtmaya çalışır.

Sonuç olarak, 48 mantıksal işlemciye sahip 2 NUMA düğümü olan bir SQL Server sunucusunda, maxdop ayarı 20 olan bir sorgu, her iki NUMA düğümü içinde paralel olarak çalışabilir. Ancak, sorgunun tam olarak hangi NUMA düğümü üzerinde çalışacağı, yukarıda belirtilen faktörlere bağlı olacaktır.

Maximum Degree of Parallelism

SQL Server’da “Maximum Degree of Parallelism” (Maksimum Paralellik Derecesi) ayarı, SQL Server’in sorguları paralel olarak çalıştırma yeteneğini kontrol eder. Bu ayar, sorguların kaç iş parçacığında eşzamanlı olarak çalışabileceğini belirler. Maksimum Paralellik Derecesi ayarını doğru bir şekilde yapmak, sistem performansını optimize etmek için önemlidir. Ayarın neye göre yapılması gerektiği ise çeşitli faktörlere bağlıdır. İşte dikkate almanız gereken bazı faktörler:

1. Donanım Özellikleri: Sunucunun donanım özellikleri, Maksimum Paralellik Derecesi ayarını etkileyen önemli bir faktördür. İşlemci sayısı, çekirdek sayısı, bellek kapasitesi ve disk hızı gibi donanım faktörleri, ayarın yapılmasında dikkate alınmalıdır. Daha güçlü bir donanıma sahip sistemlerde daha yüksek bir paralellik derecesi ayarı kullanılabilir.
2. Yük Tipi: Sisteminizde çalışan sorguların tipi ve yükü de ayarın belirlenmesinde etkilidir. Örneğin, büyük miktarda veri işleyen karmaşık sorgular yüksek paralellik derecesi gerektirebilirken, küçük sorgular için daha düşük bir paralellik derecesi daha verimli olabilir. Yük profili ve kullanıcı talepleri göz önünde bulundurulmalıdır.
3. İş Yükü Performans Testleri: Farklı Maksimum Paralellik Derecesi ayarlarıyla yapılan performans testleri, sisteminiz için en uygun ayarı belirlemek açısından faydalı olabilir. Gerçekçi bir üretim ortamına benzer yüklerle testler yaparak, farklı paralellik derecelerinin sistem performansı üzerindeki etkisini değerlendirebilirsiniz.
4. NUMA Yapılandırması: Sunucunuzda Non-Uniform Memory Access (NUMA) yapılandırması varsa, Maksimum Paralellik Derecesi ayarı NUMA düğümleri arasındaki dengeyi sağlamak için dikkate alınmalıdır.

Cost Threshold for Parallelism

Cost Threshold for Parallelism (Paralellik İçin Maliyet Eşiği), SQL Server’da paralel sorguların çalıştırılması için gereken minimum sorgu maliyetini belirleyen bir ayar olarak kullanılır. Bir sorgunun maliyeti, sorgunun işlemci, bellek ve disk gibi kaynakları kullanma yoğunluğunu temsil eder. Cost Threshold for Parallelism ayarı, bu maliyetin belirli bir eşiği aştığında sorgunun paralel olarak çalıştırılmasına izin verir.

SQL Server, sorguları paralel olarak çalıştırarak işlemci kaynaklarını daha etkin bir şekilde kullanabilir ve sorgu performansını artırabilir. Ancak, her sorgunun paralel olarak çalıştırılması gerekmeyebilir. Bazı basit veya hafif sorgular için paralel işleme ek maliyet getirebilir ve performansı düşürebilir. Bu nedenle, Cost Threshold for Parallelism ayarı, sorgunun işlemci kaynaklarını paralel olarak kullanması için belirli bir maliyetin geçilmesi gerektiğini belirler.

Varsayılan değeri 5’tir, yani bir sorgunun maliyeti 5 birimden büyük olduğunda paralel olarak çalıştırılabilir. Ancak, bu değer sistem gereksinimlerine, donanım özelliklerine ve iş yüküne bağlı olarak değiştirilebilir.

Cost Threshold for Parallelism ayarını doğru bir şekilde yapmak, sistem performansını optimize etmek için önemlidir. Düşük bir değer seçilirse, daha basit sorgular da paralel olarak çalıştırılabilir ve küçük iş yüklerinde ek maliyet getirebilir. Yüksek bir değer seçilirse, yalnızca daha karmaşık ve maliyetli sorgular paralel olarak çalıştırılır ve düşük maliyetli sorgular seri olarak işlenir. Bu ayarı yaparken, sistem gereksinimlerini, donanım özelliklerini, yük profillerini ve performans testlerini dikkate almak önemlidir.

Optimal bir kullanımı yoktur, tamamı ile sisteminize, tecrübenize ve hatta deneme yanılma yöntemi ile kendiniz için optimal bir değer bulmanız gerekir.

Özellikle bu yazımda bahsettiğim konular aslında bir best practice’i olmayan konulardır. Ortam, sistem, ihtiyaç ve beklentilere göre düzenlenmesi gereken bir ayarlar bütünüdür. Yorum kısmından veya iletişim sayfamızdan bizlere ulaşabilirsiniz. Bilgi ve tecrübemizi paylaşmakta, hata ve eksiklerimizi düzeltme konusunda çok istekliyiz.