Jeżeli w twojej kancelarii program Komornik SQL chodzi wolno, często się zawiesza, lub wyskakują błędy połączeń SQL typu „time out” to jest to najprawdopodobniej problem z wydajnością bazy SQL. Do najczęstszych powodów słabej wydajności bazy należą:
1. ZBYT WOLNA PRZEPUSTOWOŚĆ POŁĄCZENIA KOMPUTERA Z SERWEREM BAZ DANYCH SQL
2. BRAK OKRESOWEGO ODBUDOWYWANIA INDEKSÓW
3. BRAK INDEKSÓW NA KLUCZOWYCH ATRYBUTACH NAJCZĘŚCIEJ UŻYWANYCH TABEL BAZY SQL
4. ZBYT WOLNE DYSKI TWARDE
Omówmy kolejno poszczególne zagadnienia. Kolejność nie jest przypadkowa. Odpowiada kolejności diagnozowania problemu którą powinniśmy przyjąć aby nie narazić się na zbyt duże koszty a zarazem skutecznie rozwiązać problem.
1. ZBYT WOLNA PRZEPUSTOWOŚĆ POŁĄCZENIA KOMPUTERA Z SERWEREM BAZ DANYCH SQL
Jeżeli korzystasz z programu Komornik SQL na komputerze podłączonym do sieci kancelaryjnej poprzez standard WIFI lub za pośrednictwem Internetu poprzez protokół VPN (szyfrowany standart wirtualnej sieci prywatnej) to na 99% jest to właśnie problem złej wydajności. Są to połączenia o zbyt małej przepustowości jak na ilość przesyłanych danych oraz ilość operacji przez programy bazodanowe. Jeżeli z jakichkolwiek powodów musisz pracować poprzez tego typu połączenia sugerujemy rozwiązanie następujące. Zamiast uruchamiać program Komornik SQL czy inny program bazodanowy lokalnie na komputerze połącz się przez Pulpit Zdalny (Remote Desktop) na inny komputer podpięty do sieci kablem LAN i pracuj na programie uruchomionym na tamtej właśnie maszynie zdalnej. Jeżeli większa ilość osób musi pracować w ten sposób, dobrą praktyką jest postawienie w kancelarii jednej tzw. Maszyny do prac zdalnych i zainstalowanie na niej programu TSPlus (przetestowaliśmy i polecamy tą aplikację) lub innej aplikacji terminalowej umożliwiającej polaczenia się do jednej maszyny wielu osobom na raz. To rozwiązanie w zupełności powinno rozwiązać problem słabej wydajności transmisji danych gdyż ilość informacji potrzeba do przesłania sesji zdalnej jest znacznie mniejsza niż do przetwarzania danych na bazie SQL. Jeżeli po zalogowaniu się zdalnie lub uruchomieniu programu Komornik SQL bezpośrednio na serwerze lub komputerze podłączonym do szybkiego, kablowego łącza LAN-owego nadal odczuwasz problemy opisane powyżej rozwiązania problemu trzeba doszukiwać się gdzie indziej.
2. BRAK OKRESOWEGO ODBUDOWYWANIA INDEKSÓW
Aby zrozumieć opisywany w tym punkcie problem trzeba najpierw zaciągnąć informacji, czym jest indeks w bazach danych. O tematyce indeksów powstało parę ciekawych książek, więc postaram się po krótce wytłumaczyć, o co chodzi. Indeks w bazach danych jest czymś w rodzaju książkowego indeksu słów zamieszczonego na końcu książki. Przyśpiesza wyszukiwanie interesujących nas danych bez konieczności czytania całej książki. W bazach danych tego typu indeks to indeks niezgrupowany (Nonclustered index). Zapisane są w nim informacje o położeniu danych wchodzących w skład indeksu (na których indeks został założony), lecz nie wpływa na fizyczne poukładanie danych w bazie. Tego typu indeksów możemy założyć na bazie wiele, każdy dla innej grupy informacji. Jest jeszcze drugi rodzaj indeksu, indeks zgrupowany (Clustered index). Przypomina on coś w rodzaju spisu treści zamieszczanego na początku książki. Również przechowuje on informacje o położeniu danych w bazie, ale dodatkowo fizycznie układa dane w bazie według danych wchodzących w skład tego indeksu (tak jak książka poukładana jest w kolejności według rozdziałów, czy książka telefoniczna według alfabetu). Logiczne jest, więc że dane fizycznie można poukładać w ramach jednej tabeli w jeden sposób (tak jak tabela Excela może na raz być wysortowana w jeden sposób bez względu na to po ilu kolumnach sortujemy na raz) tak, więc w tabeli może występować tylko jeden taki indeks zgrupowany. O zasadach zakładania indeksów postaram się napisać osobny artykuł tak, więc powrócę do opisu omawianego problemu. Pracując na bazie danych ciągle zapisujemy w niej nowe dane. Rejestrujemy nowe sprawy, generujemy nowe pisma itp. W związku z tym indeksy danych się dezaktualizują, co zmusza system właśnie do „czytania” całej bazy w poszukiwaniu danych zamiast posługiwania się indeksem, co jest operacją znacznie bardziej pracochłonną. Regularnie odbudowując indeksy aktualizujemy je do obecnego stanu danych w bazie, dzięki czemu baza SQL pracuje znacznie wydajniej. Jeżeli dysponujemy pełną wersją serwera SQL ( nie Express) okresowe odbudowanie indeksów możemy ustawić w harmonogramie Maintenance Plan. Odsyłam w tym punkcie do dokumentacji technicznych firmy Microsoft.
3. BRAK INDEKSÓW NA KLUCZOWYCH ATRYBUTACH NAJCZĘŚCIEJ UŻYWANYCH TABEL BAZY SQL
Jeżeli ustaliliśmy, że jesteśmy podłączeni do serwera baz danych SQL wystarczająco szybkim połączeniem sieciowym oraz regularnie odbudowujemy indeksy a nasze problemy wydajnościowe nadal występują to na 90% powinniśmy pomyśleć o założeniu nowych indeksów na danych nieobjętych dotychczasowymi indeksami. Niestety zagadnienie to wymaga sporego doświadczenia i wiedzy w zakresie operacji bazodanowych, jakie chcemy optymalizować, więc w tym zakresie sugerujemy zlecenie tej usługi wyspecjalizowanej firmie. Założenie nowych indeksów jest najtańszym (nie wymaga zakupu drogiego sprzętu a jedynie pracy specjalisty) i najwydajniejszym sposobem na zwiększenie wydajności bazy. Aby zrozumieć jak bardzo wydajny jest to sposób wyobraźmy sobie, że kupując nowy serwer zapewne zwiększymy wydajność naszej bazy jakieś 2-3 no może 10 krotnie jak wydamy sporo pieniędzy, zaś stawiając właściwe indeksy możemy przyśpieszyć pracę bazy danych 100 a nawet i 1000 razy.
4. ZBYT WOLNE DYSKI TWARDE
Jeżeli wyeliminowaliśmy powyższe problemy i założyliśmy na bazie właściwe indeksy, nadszedł czas na inwestycje w sprzęt. W pierwszej kolejności zalecamy postawienie na serwerze bazę danych SQL topografii dysków typu RAID 1 lub RAID 10. Pozwoli to w znaczący sposób przyspieszyć czas odczytu danych z dysków, co jest sprawą kluczową dla serwera baz danych SQL W drugiej kolejności sugerujemy inwestycję w dobre dyski twarde. Na rynku najpopularniejsze są 3 standardy dysków:
- SATA – są to typowe dyski występujące w przeciętnej klasy komputerach PC. Charakteryzują się niskim stosunkiem ceny do pojemności.
- SAS – Typowe dyski serwerowe. Są znacznie droższe od dysków SATA o porównywalnej wielkości, ale i ich wydajność i awaryjność są znacznie bardziej zadawalające.
- SSD – Dyski typu flasz. Charakteryzują się wielokrotnie szybszym czasem odczytu przy porównywalnym czasie zapisu do tradycyjnych dysków talerzowych. Ich cena jest uzależniona od technologii wykonania, ale bez względu na to należą do najdroższych dysków. Dyski te dzielimy na dyski MLC (Multi-Level Cell), które stanowią 99% rynku oraz na wielokrotnie droższe i wielokrotnie rzadsze dyski SLC (Single-Level Cell). Różnica pomiędzy nimi sprowadza się do „żywotności”. Tańsze dyski MLC mają znacznie niższą żywotność (maksymalną ilość operacji odczytu i zapisu) niż SLC. Wiem, że takie sprowadzenie tematy bardzo spłyca zagadnienie, więc pasjonatów tego zagadnienia odsyłam do „wujka google”. Cena dysków SLC jest tak duża, że ich wykorzystywanie mija się z celem.
Jak zorganizować strukturę dyskową w kancelarii? Najwydajniejszym rozwiązaniem jest stosowanie 3 macierzy dyskowych. Jedna macierz składająca się przynajmniej z 2 dysków (RAID 1) typu SAS zawierająca System operacyjny Windows Server, druga macierz składająca się przynajmniej z 2 dysków (RAID 1) typu SAS, na której ulokujemy pliki dziennika (LOG-u) wszystkich baz danych podpiętym na serwer SQL oraz trzecia macierz przynajmniej z 2 dysków (RAID 1) typu SSD, na której ulokujemy pliki danych baz. Jeżeli masz problem z wydajnością pracy programu Komornik SQL i chcesz go skutecznie rozwiązać a nie czujesz się na siłach, aby zastosować się do tych porad jesteśmy zawsze do twojej dyspozycji.
Pozdrawiam i zapraszam do kontaktu,
Przemysław Szymczuk