Optymalizacja bazy SQL Server

Odpowiedz Nowy wątek
2015-11-24 14:31
0

Witam
Mój zespół będzie przejmować projekt - system, który posiada dość dużą bazę ~szacunkowy rozmiar 200 GB. System korzystający z bazy danych operując na niej działa niezwykle wolno. Moim wstępnym pomysłem jest usprawnienie działania oprogramowania. Oprogramowanie wykorzystuje procedury do każdego zapytania. Czyli z poziomu oprogramowania wywoływane są procedury, które zawierają poszczególne zapytania do bazy - nie wiem na ile to jest dobre rozwiązanie, ale tak przyjęto x lat temu i tego się trzymano.

Z uwagi na to, że nie mam doświadczenia w pracy z takimi bazami szukam punktów zaczepienia, zależy mi na poprawie wydajności. System jest scentralizowany, myślałem wstępnie nad może rozproszeniem zastosowanie fragmentacji poziomej, pionowej. Jednak trzeba było to jakoś mądrze rozłożyć. Dwa moja wiedza na temat rozproszonych baz danych jest akademicka znam pojęcia, robiłem proste przykłady dla siebie, ale nie miałem przyjemności utrzymywać komercyjnego systemu tego typu.

Z drugiej strony baza będzie cały czas puchła.

Ogólnie fajnie jakby ktoś podzielił się doświadczeniami w tym zakresie. Bo na tym mi zależy.

Pozdrawiam

Pozostało 580 znaków

2015-11-24 14:55
MiL
1

Zależy do czego ta baza jest używana i ilu ma użytkowników.
Jeżeli chodzi o optymalizację to nie ma wyjścia jak wyszukać wąskie gardła i optymalizować zapytania. Jeżeli konstrukcja aplikacji jest zwalona to i tam wypadałoby poprawiać.
Plus taki że zapytania są w procedurach.

Pozostało 580 znaków

2015-11-24 16:41
2

Odpal aplikację z profilerem, porób coś, zobacz, co zajmuje najwięcej czasu. Jeśli to wywołania procedur, to zidentyfikuj te procedury, a potem wyznacz plany wykonania dla nich.


"HUMAN BEINGS MAKE LIFE SO INTERESTING. DO YOU KNOW, THAT IN A UNIVERSE SO FULL OF WONDERS, THEY HAVE MANAGED TO INVENT BOREDOM."

Pozostało 580 znaków

2015-11-27 09:12
0

System jest scentralizowany, myślałem wstępnie nad może rozproszeniem zastosowanie fragmentacji poziomej, pionowej

Zanim to zrobisz, najpierw trzeba poszukać tzw. "low hanging fruit". Parę pomysłów:

  • Somekind dobrze radzi - wrzuć to pod profiler, zobacz, które zapytania są powolne, może trzeba dodać jakieś indeksy.
  • Sprawdź, czy statystyki dotyczące danych są aktualne. Nie wiem jak to się robi w MS SQL, ale niemal każdyzaawansowany RDBMS ma jakieś polecenie do przeliczenia statystyk używanych w optymalizacji zapytań - niby baza powinna odświeżać je przyrostowo, ale algorytmy przyrostowe zwykle nie są zbyt dokładne i po jakimś czasie warto przeliczyć od zera i zweryfikować czy optymalizator bazuje na sesnownych założeniach.
  • Sprawdź plany powolnych zapytań i zobacz, czy optymalizator nie robi czegoś głupiego. Na pewno masz tam jakiś EXPLAIN czy coś takiego. Czasami nawet w najlepszych systemach baz danych optymalizator potrafi się mocno pomylić i trzeba go "naprowadzić" - np. upraszczając zapytanie.
  • Sprawdź, czy nie masz problemów z fragmentacją i nieusuniętymi danymi. Nie jestem od tego ekspertem w MSQL, ale tu jest fajny filmik, który wyjaśnia, co i jak, i dlaczego framgentacja ma kluczowe znaczenie dla zdrowia bazy, zwłaszcza fragmentacja wewnętrzna:
    . W innych bazach jest coś takiego jak REORGANIZE / VACUUM / COMPACT, które w niektórych patologicznych przypadkach potrafi podnieść wydajność o dwa rzędy wielkości (miałem taki przypadek w PostgreSQL, jeszcze zanim wprowadzili auto-vacuum).

W drugiej kolejności możesz próbować skalować pionowo, czyli wzmocnić maszynę, na której to stoi. Oczywiście po zdiagnozowaniu wąskiego gardła. Jeśli problemem są odczyty, może pomóc dorzucenie więcej RAMu, aby cache działał wydajniej. Może też pomóc zmiana HDD na SSD SLC (niestety będzie kosztowne, bo zwykłe konsumenkie tanie pamięci SSD MLC się do takich zastosowań nie nadają).

W trzeciej kolejności możesz próbować skalować poziomo, czyli popartycjonować i rozproszyć na wiele maszy.... Zaraz, o czym ja gadam. To jest MSSQL. To się niestety nie skaluje poziomo. :(

http://dba.stackexchange.com/[...]scale-sql-server-2008-or-2012

Najlepsza jest odpowiedź "Like gbn says, SQL doesn't really scale out like other RDBMs's do." Tak jakby inne RDMSy się skalowały... :D :D :D

edytowany 4x, ostatnio: Krolik, 2015-11-28 20:29
Zaraz, o czym Ty gadasz? MSSQL skaluje się "poziomo", tylko nie w każdej wersji silnika wszystkie zabawki są dostępne. Poza tym, Autoshrink to jest naprawdę niezłe bagno i nie powinno być włączone przy intensywnym systemie OLTP. Tu nie chodzi, o to że "stare dane spowalniają system", bo to trochę bzdura jest. Chodzi o pre-alokację storage pod przyszłe dane i uwolnienie nieużywanych alokacji (które powstały np. po usuniętych danych - ale nie tylko), przez co można zmniejszyć rozmiar bazy. Ale to na pewno nie jest dobry pomysł na optymalizacje sensu stricte. - wloochacz 2015-11-27 17:50
Nie, nie skaluje się. Żaden Rdbms się nie skaluje poziomo. Skalowanie poziome polega na tym, że dokładam drugie tyle serwerów w ciemno i bez modyfikacji bazy, i bez utraty gwarancji dotyczących spójności danych system ma automatycznie 2x większą wydajność zarówno odczytów jak i zapisów. MSSQL ma różne zabawki, które w szczególnych przypadkach pozwalają uzyskać jakąś namiastkę skalowania, ale jest z tym bardzo dużo zachodu, a wyniki są często mizerne. - Krolik 2015-11-27 20:40
Co do autoshrink to nie wiem. Może to nie to, co miałem na myśli. Chodziło mi o odpowiednik postgresowego Vacuum czy cassandrowego Compact. Każda baza musi mieć jakiś mechanizm do zmniejszania fragmentacji. W każdym razie logicznie usunięte, ale fizycznie pozostałe dane zajmują miejsce i spowalniają bazę. Czasami nawet bardzo mocno, jak na 1 żywy rekord musisz odfiltrowac 9 usuniętych. Zmniejszają też efektywność cache, bo system plików buforuje całymi stronami. No i bazy robią dostęp do dysku zwykle też całymi stronami. - Krolik 2015-11-27 20:44
Co do skalowania, to czytaliśmy chyba inne książki, bo z kolei ja jestem przekonany że nie istnieje na świecie system, który skaluje się tak prosto jak napisałeś. To fizycznie niemożliwe, przynajmniej dziś. Co do stron; w dzisiejszych RDBMS to nie jest takie proste jak opisałeś. Działa to zdecydowanie lepiej i jest daleko bardziej skomplikowane. W efekcie czego, baza zazwyczaj działa nieźle, a optymalizacja poprawnie zrealizowanej bazy danych nigdy nie jest zadaniem trywialnym. I niestety, nie ma jednego złotego środka na wydajność. - wloochacz 2015-11-28 18:18
Cassandra się tak skaluje. :P "Co do stron; w dzisiejszych RDBMS to nie jest takie proste jak opisałeś" - a gdzie ja napisałem, że jest proste? Nie znam dokładnie silnika MSSQL, ale każdy niezabawkowy RDBMS z jakim miałem do czynienia działa w oparciu o alokację całych stron. Jak sięgasz po jeden malutki rowek, cała strona wędruje z dysku do cache. Zapisujesz rowek, po pewnym czasie cała strona wędruje na dysk. Strony zapełnione danymi => dobra wydajność. Strony zapełnione śmieciami tj. usuniętymi danymi => kiepska wydajność, bo koszt liczy się w stronach, a nie rowkach. - Krolik 2015-11-28 20:04
Zrób sobie taki szybki eksperyment - wrzuć do tabelki milionów rowków, następnie skasuj wszystkie za pomocą DELETE FROM i do takiej pustej tabelki wykonaj jakikolwiek SELECT inny niż po kluczu głównym. Następnie powtórz to samo z TRUNCATE zamiast DELETE i poczuj różnicę. - Krolik 2015-11-28 20:04

Pozostało 580 znaków

2015-11-27 11:52
0

@mariano901229 ja od maja pracuję w projekcie, który oparty jest o MSSQL i tak jak u ciebie zapytania są w procedurach. Najpierw tak jak pisali poprzednicy sqlprofilerem zbadałem co dokładnie powoduje opóźnienia. W kolejnych krokach okazało się, że wyrzucenie niektórych zapytań z procedur do np widoku rozwiązywało problem. Niestety część rzeczy musiałem przeprojektować gdyż sam projekt zawierał błędy i nie było innej opcji jak tylko zaoranie mechanizmu i stworzenie go od początku. Istotnym problemem były też raporty. Gość, który się tym wcześniej zajmował nie znał chyba funkcji analitycznych i używał wbudowanych mechanizmów po stronie interfejsu użytkownika w efekcie był robiony select np z zakresu dat cały rok i aby wypluć podsumowanie rekordy były zliczane i sumowane w UI. Tego typu rzeczy też znacznie spowalniały całość procesu więc nie ma jednej odpowiedzi co zrobić. Trzeba siedzieć sprawdzać i optymalizować one by one.

Pokaż pozostałe 5 komentarzy
A ja jestem zdania, że baza danych jest od przechowywania i wyszukiwania danych, a nie robienia obliczeń, analiz czy logiki biznesowej. Do tego są osobne narzędzia. - Krolik 2015-11-27 14:29
Tyle, że wtedy wymagasz od end usera aby miał potężną maszynę kliencką, która wykona dla niego złożone analizy czy obliczenia. - woolfik 2015-11-27 14:40
Nie, dlaczego? Przecież użytkownik nie musi się łączyć bezpośrednio z bazą. Poza tym można mieć więcej niż jedną bazę, do różnych zastosowań. Są bazy / systemy przeznaczone do robienia analiz, np. Teradata, Vertical, Hadoop, Spark i są bazy do obsługi prostych transakcji np. Ms SQL, Oracle czy Cassandra. Na tych pierwszych transakcje raczej słabo działają, na tych drugich raczej słabo robi się operacje hurtowe. - Krolik 2015-11-27 15:14
@Krolik Ty chyba nie masz do czynienia na co dzień z normalnym polskim klientem (firmą), co? ;-) Bo to co piszesz to oczywiście prawda, ale rzeczywistość nie jest tak różowa. - wloochacz 2015-11-28 18:20
@Krolik, potwierdzam, na Oracle da się robić proste transakcje ;-) - vpiotr 2015-11-28 18:26

Pozostało 580 znaków

2015-11-28 18:46
2

Zacznij od zdefiniowania potrzeb.
Jakie są oczekiwania klienta?
Co jest za wolne? O ile ma przyspieszyć? Czy przyspieszenie o 3s wystarczy? A może o 30%? A może 10x?
Jak szybko zwalnia baza (czy stopniowo w ciągu 10 lat, czy dramat w ciągu miesiąca).
Jak szybko przyrasta baza.
Czy są "piki" - okresowe szczytowe obciążenia systemu - w ciągu doby lub miesiąca.
Ile jest aplikacji korzystających z bazy.
Jak wygląda sesja użytkownika - ta pozytywna i ta za wolna - pod względem dokładnej listy wykonywanych zapytań (lub SP).

Potem dodaj na stałe logowanie zapytań trwających >= t [ms], np. http://blog.brianhartsock.com[...]ty-sql-server-slow-query-log/

Potem masz kilka opcji:

  • najprostsza: poszukać zapytań które można przerobić bez zmiany API procedur oraz struktury bazy i poprawić te procedury
  • trudniejsza: poszukać zapytań, dla których warto zrobić zmiany struktury bazy nie pociągających za sobą zmiany API procedur
  • potem masz zmiany które pociągną za sobą zmiany API (dodatkowe parametry, wiele rekordów zamiast 1, statystyki w SQL zamiast UI...)
  • potem masz zmiany architektury bazy (sharding, część na NoSQL)

Jeśli to i tak będzie za mało:

  • ulepszasz sprzęt (SSD, więcej dysków, więcej RAM)
  • zatrudniasz DBA-ja (na stałe lub z określonym założonym celem)

Szacuje się, że w Polsce brakuje 50 tys. programistów
edytowany 1x, ostatnio: vpiotr, 2015-11-29 10:16

Pozostało 580 znaków

2017-11-24 13:34
Adams
0

odnośnie optymalizacji kodu SQL polecam: optymalizacjasql.blogspot.com

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0