2012 óta, mióta az SQL Server 2012 Enterprise Edition-ben megjelent az Availability Group, azóta folyamatosan megkapom a következő kérdéseket:
- Miért nem lehet több száz adatbázist egy szerveren Availability Group-ba tenni?
- Minek ennyi CPU?
A válasz nem triviális, ellenben számolható.
A Microsoft ajánlása, hogy maximum 100 adatbázis és összesen 10 AG legyen egy fizikai gépen, lásd itt. Akkor ezzel le is zártam, kész, ez az ajánlás :).
Persze, hogy nem csak ennyi, hanem nézzünk mögé kicsit. Kezdjük az alapoknál: minden SQL Server un. thread-eket (nem, nem fordítom le) használ a feladatok elvégzéséhez, ezek száma pedig függ a CPU magok számától! Kezd érdekes lenni, vajon mennyi az annyi?
Alapvetően 512 thread van, majd
- <=4 mag esetén marad az 512 thread
- >4 és <=64 esetén 512+(logikai magok -4)*16
- >64 esetén 512+(logikai magok -4)*32
Ezek 64 bites verzióra igazak, részletek itt.
Vegyünk egy 16 magos rendszert, ekkor a thread-ek száma: 512+(16-4)*16=704
Ebből mennyit használhat maximum csak az AG? A dokumentáció szerint a max worker száma -40, azaz 704-40=664. Igen ám, de akkor a lekérdezéseknek mi marad? Rövid válasz: szinte semmi és ez rossz!
Menjünk vissza az eredeti kérdésre, hogy miért nem lehet több száz adatbázist AG-ba tenni és miért mondja a Microsoft, hogy maximum 100 adatbázis és 10 AG.
Tételezzük fel a következőt: van 100 adatbázis, 1 AG, 2 secondary replica. Ebben az esetben mennyi lesz a minimum pool méret? Az alábbi képlet megadja:
(DC*(LCT+(LST*SRC)))+MHT
- DC: adatbázisok száma,
- LCT: log capture thread, minden primary replica adatbázis használ 1 darabot,
- LST: log send thread, minden másodlagos replica adatbázishoz tartozik 1 darab,
- SRC: secondary replica darabszám,
- MHT: message handler thread, 1 darab/ AG
Ezek alapján lássuk a számítást (100*(1+(1*2)))+1=301, azaz a 704 thread kb. 43%-a csak az Availability Group használatában lenne!!! Ez csak a primary oldal, a secondary esetén másként kell számolni, főleg az SQL Server 2016-os verziótól, ahol már van parallel redo is.
Lássuk egy élő példán is a különbséget: az alábbiakban 2 darab SQL Server, 1 primary és 1 secondary replica thread felhasználása látszik, 100 darab adatbázis és 120 egyidejű felhasználó esetén.
Ez egy 8 magos rendszer, ahol 576 thread van, majd 201 thread elmegy az Availability Group miatt.
Ugyan ez 20 adatbázis és 120 felhasználó esetén:
Ugye jól látszik a különbség: a töredéke ment el, azaz a lekérdezéseknek bőven maradt.
Még két másik metrikát is érdemes megnézni:
- HADR Task Count: az első esetben ez 150K, míg a másik esetben 15-25K között van
- CPU kihasználtság: természetesen itt is van különbség, az első esetben 20% körüli, míg a másik esetben ez átlag 8-12% volt.
Jól látható, hogy mennyire fontos a tervezés során ezt is figyelembe venni, nem feltétlen csak a kötségeket. Sajnos volt rá példa a munkáim során, amikor a költség felülírta a technológiát, a vége természetesen incidens lett.
Végezetül: mindig számoljuk ki, hogy mennyi CPU-ra van szükség Availability Group esetén és ehhez adjuk hozzá az alkalmazás követelményekben megadott CPU számot.