Jak monitorować serwery MSSQL?
Monitoring serwerów Microsoft SQL Server z poziomu linuxa nie jest zadaniem trywialnym. Możemy do niego podejść dwojako, wykonując przez NRPE (Nagios Remote Plugin Extension) jakiś skrypt odpytujący naszą bazę danych, albo sposób który stosujemy – czyli bezpośrednie łączenie się z SQL Serverem przez protokół TDS (Tabular Data Stream).
Do tego celu wykorzystamy bibliotekę FreeTDS. Nawiasem mówiąc FreeTDS, poza współprac ą C, działa z językami skryptowymi takimi jak Perl czy PHP. FreeTDS jest dostarczany na licencji GNU LGPL i pozwala łączyć się również z bazami Sybase.
FreeTDS dostarcza aplikację tsql
Prostym sposobem podłączenia się do serwera w takim przypadku jest
Niestety takie rozwiązanie zadziała jedynie w przypadku użytkownika SQL’owego, jeśli chcemy skorzystać z uwierzytelniania Windows. W tym momencie zwykle pojawia się pierwszy problem, na szczęście do rozwiązania. Chodzi o używaną wersje protokołu. Zwykle niższa wersja TDS pozwala na logowanie, jednak dopiero wersja 7.0 pozwala na uwierzytelnianie Windows, a co za tym idzie np. logowania domenowego.
Często domyślna wersja to 4.2 albo 5.0, co skutkuje komunikatem:
Szybkie rozwiązanie jest dość proste
Jeśli jednak planujemy monitoring dużej ilości baz i chcemy utrzymać porządek proponuję skorzystać z /etc/freetds.conf gdzie możemy zdefiniować naszą instancję – także o dynamicznym porcie TCP
Wybór odpowiedniej wersji powinien wyglądać następująco:
Schemat naszego pliku może wyglądać następująco
albo w przypadku dynamicznego zamiast port
Najczęściej używaną metodą do monitorowania SQL Servera jest check_mssql_health. I wyczerpuje ona dużą część potrzebnych sprawdzeń. Metoda dostępna na Nagios Exchange, jak się okazuje dość uniwersalna jeśli chodzi o wersje MSSQL. W pomocy podręcznej wtyczki znajdziemy następujące informacje, które możemy podzielić na grupy: Activity, Memory, Data i T-SQL. Plusem zostały oznaczone te, które udało nam się uruchomić na SQL Server 2000, wszystkie natomiast zostały przez nas uruchomione na nowszych wersjach MSSQL Server.
Ważnym elementem jest jeszcze kwestia uprawnień do Monitoringu. W trakcie wdrożeń systemu op5 uznaliśmy że sugerowane uprawnienia sysadm są zdecydowanie zbyt duże.
To co udało się ustalić to, że użytkownik monitoringu potrzebuje uprawnień PUBLIC na wszystkich monitorowanych bazach. Roli na bazach MASTER i MSDB oraz przypisania do:
MASTER – Grant view database state to op5, Grant selekt on sys.sysperfinfo to op5, Grant selekt on sys.traces to op5, grant execute on sys.sp_monitor to op5
MSDB – Grant view database state to op5, grant select on dbo.sysjobhistory to op5, grant select on dbo.sysjobs to op5, grant select on dbo.sysjobschedules to op5
Jeśli wydaje nam się że któryś z checków nie działa pomimo takich uprawnień powinno pomóc w ustaleniu sprawcy utworzenie pliki /tmp/check_mssql_health.trace z uprawnieniami zapisu dla użytkownika monitoringu – znajdziemy tam kompletną listę problemów.
Postanowiłem rozszerzyć swoją wczorajszą wypowiedź, ponieważ nie przedstawia ona wszystkich parametrów które czasami chcielibyśmy i możemy zmonitorować.
W przypadku sprawdzania wydajności MSSQL przychodzi nam z pomocą tabela sys.dm_os_performance_counters.
Tabela ta zawiera kolumny object_name, counter_name, instancje_name, cntr_value, cntr_type. O ile nie trudno domyślić się znaczenia czterech pierwszych kolumn to cntr_type słusznie powinno wzbudzić niepokój.
Jak rozumieć dziwną wartość którą tam widzimy? Możemy te wartości podzielić na 3 groupy:
1. Wartość bezwzględna – wartości 65792 i 65536
2. Na sekundę – wartość 272696576
3. Countery wymagające podzielenia przez country bazowe (np. Cache Hit Ratio i Cache Hit Ratio Base) – wartości 1073874176 I 537003264. Baza – 1073939712.
Gdzie szukać informacji na temat optymalnych wartości tych liczników. Z pomocą przychodzi firma Quest, która opublikowała pewien plakat -www.quest.com/backstage/images/promotion...rfmonance-Poster.pdf
Przyjrzyjmy się najważniejszym pozycjom, niekoniecznie dostarczanym przez check_mssql_health.
Podejście jest nieco inne nawet już w przypadku ilości rekompilacji na sekundę. Okazuje się bowiem, że pożądana wartość SQL Re-Compilations/sec nie powinna być bezwzględna, a stanowić mniej niż 10% wartości SQL Compilations/sec (obydwa w SQL Statistics).
Podobnie ma się sprawa ilości kompilacji która powinna stanowić mniej niż 10% wartości Batch Requests/sec.
Liczniki z grupy /sec wymagają jednak dwóch odczytów w jakimś interwale czasowym. Można to tłumaczyć tak, że owszem ich sens jest na sekundę jednak wartość jest bezwzględna od pewnego momentu (niestety nie jestem w stanie stwierdzić czy to np. restart serwera, restart instancji, czy może instalacja instancji) .
Kolejnymi licznikami wartymi uwagi są Processes blocked I Logins/sec z obiektu General Statistics. Jednocześnie krotki o object_name Buffer Manager możemy uznać ze najciekawszą z perspektywy monitoringu wydajności część tej tabeli.
Liczniki Page writes/sec i Page reads/sec. Znajdziemy tu również licznik Readahead pages/sec, którego wartość nie powinna przekraczać 20% Page Reads/sec.
Bezwzględny licznik Page life expectancy który nie powinien być mniejszy niż 300. O czym należy pamiętać przy ustawianiu odpowiednich progów. Podobnie Free pages – nie mniejsze niż 640.
Po krótkim spojrzeniu w kod check_mssql_health zauważamy że on również korzysta z tej tabeli i counterów takich jak Lazy writes/sec, czy przykładu trzeciej grupy counterów – Buffer cache hit ratio/Buffer cache hit ratio base.
Druga część artykułu opiera się o sprawdzanie zużycia pamięci operacyjnej. Jak już wspomniałem we wcześniejszym artykule counter Total Server Memory który również znajdziemy w tabeli opisywanej wyżej nie jest do końca miarodajny. Nowoczesny system bazodanowy zwykle będzie chciał wykorzystać pamięć jak najoptymalniej, przez co Total i Target Server Memory w większości przypadków będą sobie równe. Więc co możemy tak naprawdę zmierzyć. Odpowiedź znajduje się w poniższych T-SQL. Kolejność odpowiada poziomowi miarodajności otrzymanej wartości. Niestety w zależności od wersji MSSQL prawdopodobnie tylko jeden z 3 transact-sql będzie skutecznie pokazywać poprawną wartość. Co ważne wartość względną, co w dużych wdrożeniach pozwala na ustalenie sensownych progów.
Tak przygotowany monitoring w połączeniu z check_mssql_health powinien dać nam komplet informacji, które mogą być wartościowe przy monitorowaniu Microsoft SQL Server.
Monitoring serwerów Microsoft SQL Server z poziomu linuxa nie jest zadaniem trywialnym. Możemy do niego podejść dwojako, wykonując przez NRPE (Nagios Remote Plugin Extension) jakiś skrypt odpytujący naszą bazę danych, albo sposób który stosujemy – czyli bezpośrednie łączenie się z SQL Serverem przez protokół TDS (Tabular Data Stream).
Do tego celu wykorzystamy bibliotekę FreeTDS. Nawiasem mówiąc FreeTDS, poza współprac ą C, działa z językami skryptowymi takimi jak Perl czy PHP. FreeTDS jest dostarczany na licencji GNU LGPL i pozwala łączyć się również z bazami Sybase.
FreeTDS dostarcza aplikację tsql
KOD: ZAZNACZ CAŁY
Code:
tsql { -S servername [-I interface] | -H hostname -p port }
-U username [-P password] [-o options]Prostym sposobem podłączenia się do serwera w takim przypadku jest
KOD: ZAZNACZ CAŁY
Code:
$ tsql –H host –p 1433 -U login –P hasloNiestety takie rozwiązanie zadziała jedynie w przypadku użytkownika SQL’owego, jeśli chcemy skorzystać z uwierzytelniania Windows. W tym momencie zwykle pojawia się pierwszy problem, na szczęście do rozwiązania. Chodzi o używaną wersje protokołu. Zwykle niższa wersja TDS pozwala na logowanie, jednak dopiero wersja 7.0 pozwala na uwierzytelnianie Windows, a co za tym idzie np. logowania domenowego.
Często domyślna wersja to 4.2 albo 5.0, co skutkuje komunikatem:
KOD: ZAZNACZ CAŁY
Code:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the ServerSzybkie rozwiązanie jest dość proste
KOD: ZAZNACZ CAŁY
Code:
$TDSVER=7.0 tsql –H host –p 1433 –U ‘DOMENA\login’ –P hasłoJeśli jednak planujemy monitoring dużej ilości baz i chcemy utrzymać porządek proponuję skorzystać z /etc/freetds.conf gdzie możemy zdefiniować naszą instancję – także o dynamicznym porcie TCP
Wybór odpowiedniej wersji powinien wyglądać następująco:
Quote:Product TDS Version Comment
Sybase before System 10, Microsoft SQL Server 6.x 4.2 Still works with all products, subject to its limitations.
Sybase System 10 and above 5.0 Still the most current protocol used by Sybase.
Sybase System SQL Anywhere 5.0 only Originally Watcom SQL Server, a completely separate codebase. Our best information is that SQL Anywhere first supported TDS in version 5.5.03 using the OpenServer Gateway (OSG), and native TDS 5.0 support arrived with version 6.0.
Microsoft SQL Server 7.0 7.0 Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters), and support for Unicode.
Microsoft SQL Server 2000 7.1 Include support for bigint (64 bit integers), variant and collation on all fields. Collation is not widely used.
Microsoft SQL Server 2005 7.2 Includes support for varchar(max), varbinary(max), xml datatypes and MARS[a].
Microsoft SQL Server 2008 7.2 (unchanged)
Schemat naszego pliku może wyglądać następująco
KOD: ZAZNACZ CAŁY
Code:
[global]
tds version = 7.0
[NASZA_NAZWA_DLA_INSTANCJI]
host = 10.1.1.1
port = 1433
tds version = 7.2albo w przypadku dynamicznego zamiast port
KOD: ZAZNACZ CAŁY
Code:
instancje = nazwa_instancjiNajczęściej używaną metodą do monitorowania SQL Servera jest check_mssql_health. I wyczerpuje ona dużą część potrzebnych sprawdzeń. Metoda dostępna na Nagios Exchange, jak się okazuje dość uniwersalna jeśli chodzi o wersje MSSQL. W pomocy podręcznej wtyczki znajdziemy następujące informacje, które możemy podzielić na grupy: Activity, Memory, Data i T-SQL. Plusem zostały oznaczone te, które udało nam się uruchomić na SQL Server 2000, wszystkie natomiast zostały przez nas uruchomione na nowszych wersjach MSSQL Server.
KOD: ZAZNACZ CAŁY
Code:
Activity:
+ connection-time - czas połączeni
+ cpu-busy - obciążenie procesora
+ io-busy – ilość pełnych skanowań tablicy na sekundę
+ connected-users - liczba połączonych sesji
+ transactions - ilość transakcji (na każdą bazę)
+ batch-requests - ilość batch requests na sekundę
+ latches-waits - ilość oczekujących latchy
+ latches-wait-time - średni czas oczekiwania – naszym zdaniem są lepsze sposoby mierzenia wydajności
+ locks-waits – ilość locków na sekundę
locks-timeouts - ilość timeoutów na sekundę
+ locks-deadlocks - ilość deadlocków na sekundę
lazy-writes - ilość lazy write na sekundę
+ failed-jobs – liczba niepowodzeń jobów w określonym czasie
+ checkpoint-pages - check pointy na sekundę (dirty pages)
Memory:
+ total-server-memory - ilość pamięci przypisanej do instancji – słaby licznik, zwykle równy ustawionemu przez administratora target-server-memory
+ mem-pool-data-buffer-hit-ratio - Data Buffer Cache Hit Ratio
page-life-expectancy - ilość sekund przez które strona jest trzymana w pamięci przez wyczyszczeniem
+ free-list-stalls
Data:
database-online - sprawdzenie czy bazy danych są online
database-free -sprawdzenie wolnego miejsca w bazach danych
database-backup-age - wiek backup w godzinach
database-logbackup-age - wiek log backup w godzinach
database-file-auto-growths/shrinks – liczba eventów File Auto Grow/Shrink w określonym czasie
database-logfile-auto-growths/shrinks - Log File Auto Grow/Shrink
database-datafile-auto-growths/shrinks - Data File Auto Grow/Shrink
database-file-dbcc-shrinks - DBCC File Shrink
T-SQL
+ sql -dowolny sql
+ sql-runtime – czas wykonania dowolnego SQL
+ sql-recompilations - ilość rekompilacji na sekundę
+ sql-initcompilations - ilość kompilacji na sekundę
Informacyjne
list-databases
list-datafiles
list-locksWażnym elementem jest jeszcze kwestia uprawnień do Monitoringu. W trakcie wdrożeń systemu op5 uznaliśmy że sugerowane uprawnienia sysadm są zdecydowanie zbyt duże.
To co udało się ustalić to, że użytkownik monitoringu potrzebuje uprawnień PUBLIC na wszystkich monitorowanych bazach. Roli na bazach MASTER i MSDB oraz przypisania do:
MASTER – Grant view database state to op5, Grant selekt on sys.sysperfinfo to op5, Grant selekt on sys.traces to op5, grant execute on sys.sp_monitor to op5
MSDB – Grant view database state to op5, grant select on dbo.sysjobhistory to op5, grant select on dbo.sysjobs to op5, grant select on dbo.sysjobschedules to op5
Jeśli wydaje nam się że któryś z checków nie działa pomimo takich uprawnień powinno pomóc w ustaleniu sprawcy utworzenie pliki /tmp/check_mssql_health.trace z uprawnieniami zapisu dla użytkownika monitoringu – znajdziemy tam kompletną listę problemów.
Postanowiłem rozszerzyć swoją wczorajszą wypowiedź, ponieważ nie przedstawia ona wszystkich parametrów które czasami chcielibyśmy i możemy zmonitorować.
W przypadku sprawdzania wydajności MSSQL przychodzi nam z pomocą tabela sys.dm_os_performance_counters.
Tabela ta zawiera kolumny object_name, counter_name, instancje_name, cntr_value, cntr_type. O ile nie trudno domyślić się znaczenia czterech pierwszych kolumn to cntr_type słusznie powinno wzbudzić niepokój.
Jak rozumieć dziwną wartość którą tam widzimy? Możemy te wartości podzielić na 3 groupy:
1. Wartość bezwzględna – wartości 65792 i 65536
2. Na sekundę – wartość 272696576
3. Countery wymagające podzielenia przez country bazowe (np. Cache Hit Ratio i Cache Hit Ratio Base) – wartości 1073874176 I 537003264. Baza – 1073939712.
Gdzie szukać informacji na temat optymalnych wartości tych liczników. Z pomocą przychodzi firma Quest, która opublikowała pewien plakat -www.quest.com/backstage/images/promotion...rfmonance-Poster.pdf
Przyjrzyjmy się najważniejszym pozycjom, niekoniecznie dostarczanym przez check_mssql_health.
Podejście jest nieco inne nawet już w przypadku ilości rekompilacji na sekundę. Okazuje się bowiem, że pożądana wartość SQL Re-Compilations/sec nie powinna być bezwzględna, a stanowić mniej niż 10% wartości SQL Compilations/sec (obydwa w SQL Statistics).
Podobnie ma się sprawa ilości kompilacji która powinna stanowić mniej niż 10% wartości Batch Requests/sec.
Liczniki z grupy /sec wymagają jednak dwóch odczytów w jakimś interwale czasowym. Można to tłumaczyć tak, że owszem ich sens jest na sekundę jednak wartość jest bezwzględna od pewnego momentu (niestety nie jestem w stanie stwierdzić czy to np. restart serwera, restart instancji, czy może instalacja instancji) .
Kolejnymi licznikami wartymi uwagi są Processes blocked I Logins/sec z obiektu General Statistics. Jednocześnie krotki o object_name Buffer Manager możemy uznać ze najciekawszą z perspektywy monitoringu wydajności część tej tabeli.
Liczniki Page writes/sec i Page reads/sec. Znajdziemy tu również licznik Readahead pages/sec, którego wartość nie powinna przekraczać 20% Page Reads/sec.
Bezwzględny licznik Page life expectancy który nie powinien być mniejszy niż 300. O czym należy pamiętać przy ustawianiu odpowiednich progów. Podobnie Free pages – nie mniejsze niż 640.
Po krótkim spojrzeniu w kod check_mssql_health zauważamy że on również korzysta z tej tabeli i counterów takich jak Lazy writes/sec, czy przykładu trzeciej grupy counterów – Buffer cache hit ratio/Buffer cache hit ratio base.
Druga część artykułu opiera się o sprawdzanie zużycia pamięci operacyjnej. Jak już wspomniałem we wcześniejszym artykule counter Total Server Memory który również znajdziemy w tabeli opisywanej wyżej nie jest do końca miarodajny. Nowoczesny system bazodanowy zwykle będzie chciał wykorzystać pamięć jak najoptymalniej, przez co Total i Target Server Memory w większości przypadków będą sobie równe. Więc co możemy tak naprawdę zmierzyć. Odpowiedź znajduje się w poniższych T-SQL. Kolejność odpowiada poziomowi miarodajności otrzymanej wartości. Niestety w zależności od wersji MSSQL prawdopodobnie tylko jeden z 3 transact-sql będzie skutecznie pokazywać poprawną wartość. Co ważne wartość względną, co w dużych wdrożeniach pozwala na ustalenie sensownych progów.
KOD: ZAZNACZ CAŁY
Code:
select 100*cast((SUM(virtual_address_space_committed_kb)+
sum(locked_page_allocations_kb)+
sum(multi_pages_kb)) as real)/
(select physical_memory_in_bytes/1024 from sys.dm_os_sys_info) as usage
from sys.dm_os_memory_nodesKOD: ZAZNACZ CAŁY
Code:
select 100*cast((sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)) as real)/
(select physical_memory_in_bytes/1024 from sys.dm_os_sys_info) as usage
from sys.dm_os_memory_clerks
where type='memoryclerk_sqlbufferpool'KOD: ZAZNACZ CAŁY
Code:
select 100*cast((sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)) as real)/
(select physical_memory_kb from sys.dm_os_sys_info) as usage
from sys.dm_os_memory_clerks"Tak przygotowany monitoring w połączeniu z check_mssql_health powinien dać nam komplet informacji, które mogą być wartościowe przy monitorowaniu Microsoft SQL Server.
