GoldenLine
Zaloguj się
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Witam;

Od dłuższego czasu pracuję nad optymalizacją bazy danych Myslq. Mam kilka pytań z tym związanych:
1) Jakie są skuteczne sposoby podziełu tablę aby zmniejszyć Table_locks_waited.
2) Czy użycie widoków w celu podzielenia tabeli jest dobrym rozwiązaniem. Czy zapytanie na widoku jest szybsze od zapytania na tabeli pierwotnej?
3) Czy zapytanie na perspektywie blokuje tabele pierwotną?
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: Optymalizacja baz danych w MySQL

ad 1)
- Spróbuj partycjonowania
- Postaraj się zoptymalizować strukturę tabeli
- Postaraj się zoptymalizować zapytania korzystając z polecenia EXPLAIN - tutaj ważny jest odpowiedni dobór indeksów
- Jeżeli jest przewaga czytania nad insert/update postaraj się stworzyć warstwę pośrednią/cache np. w memcached, szczególnie jeżeli często powtarzają się te same zapytania - jest też cache samego MySQL ale w memcached jest lepsza kontrola nad całym procesem

Widoków i perspektyw nie używam w MySQL, więc tutaj nie pomogę :)

Polecam też: http://www.mysqlperformanceblog.com/ i dotyczące tego tematu: http://www.mysqlperformancetuning.com/how-to-reduce-ta...Tomasz Zadora edytował(a) ten post dnia 06.05.12 o godzinie 19:49
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Dziękuje Tomasz za bardzo treściwe linki.

- liczba update-insert-select to 7%-4,5%-23% wszystkich zapytań. Rozbieżność zapytań jest dość spora gdyż userzy pracują na indywidualnych filtrach.
- optymalizacja tabeli zrobiona, indeksy pozakładane
- nad partycjonowaniem się zastanawiam i jestem przekonany że to pomoże

Jednak mam już aplikacje która dzieli główną, największą tabele na widoki. Tzn aplikacja działa na tabeli pierwotnej bądź widokach które bazują na tej samej tabeli, ale zawężają liczbę rekordów (dzielą tabelę na mniejsze).
Zastanawiam się czy to rozwiązanie - wykorzystanie widoków do zawężenia liczby przeszukiwanych rekordów jest fatalne i nieoptymalne?Marcin Borek edytował(a) ten post dnia 06.05.12 o godzinie 22:16
Link do wypowiedziLink
Piotr B.

Piotr B. Handlarz też
człowiek

Temat: Optymalizacja baz danych w MySQL

Skorzystaj z narzędzi Questa: Toad for mySQL i Spotlight on MySQL.

Te narzędzia są darmowe.
Link do wypowiedziLink
Krzysztof Eugeniusz Kotkowicz

Krzysztof Eugeniusz Kotkowicz Freelancer,
Administrator
systemów
teleinformatycznych

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
2) Czy użycie widoków w celu podzielenia tabeli jest dobrym rozwiązaniem. Czy zapytanie na widoku jest szybsze od zapytania na tabeli pierwotnej?

Zdecydowanie nie jest. Wydajność widoków w MySQL kuleje straszliwie.
Link do wypowiedziLink

konto usunięte

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
Witam;

Od dłuższego czasu pracuję nad optymalizacją bazy danych Myslq. Mam kilka pytań z tym związanych:
1) Jakie są skuteczne sposoby podziełu tablę aby zmniejszyć Table_locks_waited.

Popracować nad zapytaniami i indeksami tak, by wszystko pracowało wydajnie - wówczas problemy z lockami pojawić się mogą zwykle dopiero przy naprawdę sporym ruchu i wielu równoległych zapytaniach róznego typu. A jeśli ruch już jest spory, można także pomyśleć o migracji tabel do InnoDB.
2) Czy użycie widoków w celu podzielenia tabeli jest dobrym rozwiązaniem. Czy zapytanie na widoku jest szybsze od zapytania na tabeli pierwotnej?

Nie i nie. To nie jest właściwy kierunek.

Maciek
Link do wypowiedziLink

konto usunięte

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
3) Czy zapytanie na perspektywie blokuje tabele pierwotną?

denormalizacja i dane nadmiarowe w tabeli

to bardzo przyspiesza wykonywanie selectów, dane w takiej tabeli można ładować podczas normalnych operacji insert/update jako kolejne kroki w wykonywanym zadaniu

i śmiało mogę się założyć że takie rozwiązanie (łącznie z insertami) będzie szybsze od zapytań na joinach
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Przemysław R.:
Marcin Borek:
3) Czy zapytanie na perspektywie blokuje tabele pierwotną?

denormalizacja i dane nadmiarowe w tabeli

to bardzo przyspiesza wykonywanie selectów, dane w takiej tabeli można ładować podczas normalnych operacji insert/update jako kolejne kroki w wykonywanym zadaniu

i śmiało mogę się założyć że takie rozwiązanie (łącznie z insertami) będzie szybsze od zapytań na joinach


Jeśli operujemy na widoku utworzonego z połączenia dwóch tabel, czy każdy insert/update do jednej z tych dwóch tabel powoduje dodatkowo wykonanie select join w celu odświeżenia widoku?
Link do wypowiedziLink
Michał Gruchała

Michał Gruchała Skalowalność,
wydajność,
niezawodność

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
Od dłuższego czasu pracuję nad optymalizacją bazy danych Myslq.

Skoro optymalizujesz, to znaczy, że coś "źle/wolno" działa - co?
Mam kilka pytań z tym związanych:
1) Jakie są skuteczne sposoby podziełu tablę aby zmniejszyć Table_locks_waited.

Zależy jak wygląda tabela, jak jest używana, czy jest duża.
Możesz albo zrobić to "ręcznie" (czyli X tabel) albo użyć partycjonowania - ale musisz wiedzieć jakie zapytania idą do tej tabeli.
Myślę, że zanim do tego dojdziesz, to można jeszcze X rzeczy sprawdzić...

Używasz InnoDB?
Jeśli nie masz włączonego slow loga, to włącz i spójrz na zapytania, które się tam pojawiają.
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Michał Gruchała:
Marcin Borek:
Od dłuższego czasu pracuję nad optymalizacją bazy danych Myslq.

Skoro optymalizujesz, to znaczy, że coś "źle/wolno" działa - co?


Działa ok, ale chcę żeby działało super ;)
Żeby doprowadzić do stanu - Zero problemów w STATUSach ;)

Mam kilka pytań z tym związanych:
1) Jakie są skuteczne sposoby podziełu tablę aby zmniejszyć Table_locks_waited.

Zależy jak wygląda tabela, jak jest używana, czy jest duża.

Jedna tabela spośród wszystkich jest dość spora ok. 0.5 - 1GB. Przechowuje informacje z kilku lat na których operuje każdy użytkownik. Niemalże otwarcie każdej strony jest selectem do tej tabeli. Z tym że prawie każdy użytkownik operuje w 99% an danych z ostatnich 6 miesięcy.
Możesz albo zrobić to "ręcznie" (czyli X tabel) albo użyć partycjonowania - ale musisz wiedzieć jakie zapytania idą do tej tabeli.
Myślę, że zanim do tego dojdziesz, to można jeszcze X rzeczy sprawdzić...

Używasz InnoDB?

MyISAM
Jeśli nie masz włączonego slow loga, to włącz i spójrz na zapytania, które się tam pojawiają.

OczywiścieMarcin Borek edytował(a) ten post dnia 07.05.12 o godzinie 10:35
Link do wypowiedziLink

konto usunięte

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
Michał Gruchała:
Marcin Borek:
Od dłuższego czasu pracuję nad optymalizacją bazy danych Myslq.

Skoro optymalizujesz, to znaczy, że coś "źle/wolno" działa - co?


Działa ok, ale chcę żeby działało super ;)
Żeby doprowadzić do stanu - Zero problemów w STATUSach ;)

Mam kilka pytań z tym związanych:
1) Jakie są skuteczne sposoby podziełu tablę aby zmniejszyć Table_locks_waited.

Zależy jak wygląda tabela, jak jest używana, czy jest duża.

Jedna tabela spośród wszystkich jest dość spora ok. 0.5 - 1GB. Przechowuje informacje z kilku lat na których operuje każdy użytkownik. Niemalże otwarcie każdej strony jest selectem do tej tabeli. Z tym że prawie każdy użytkownik operuje w 99% an danych z ostatnich 6 miesięcy.

Kilka uwag:

1) to nie jest duża tabela
2) jeśli każde otwarcie strony robi select, to już na wejściu masz problem
3) no to partycjonuj tak żeby w jednej partycji mieć dane z ostatnich 6 miesięcy, wtedy dostęp do danych z tej partycji powinien być szybszy, ale oczywiście to zależy ile jest tych danych z ostatnich 6 miesięcy i co to znaczy.
Możesz albo zrobić to "ręcznie" (czyli X tabel) albo użyć partycjonowania - ale musisz wiedzieć jakie zapytania idą do tej tabeli.
Myślę, że zanim do tego dojdziesz, to można jeszcze X rzeczy sprawdzić...

Używasz InnoDB?

MyISAM

To jeszcze ktoś trzyma ważne dane w MyISAM? Nie boisz się tak bez transakcji? Zresztą, może masz taki flow, że to nie boli... ale założę się, że jednak nie.
Link do wypowiedziLink
Michał Gruchała

Michał Gruchała Skalowalność,
wydajność,
niezawodność

Temat: Optymalizacja baz danych w MySQL

Jedna tabela spośród wszystkich jest dość spora ok. 0.5 - 1GB. Przechowuje informacje z kilku lat na których operuje każdy użytkownik. Niemalże otwarcie każdej strony jest selectem do tej tabeli. Z tym że prawie każdy użytkownik operuje w 99% an danych z ostatnich 6 miesięcy.

Jak niżej zostało napisane - to nie jest dużo.
Taka tabela w całości mieści się w pamięci - zakładam, że masz ze 2GB RAMu na maszynie ;0
MyISAM

Zmigruj do InnoDB i problemy z lockami zginą - pod warunkiem, że nie używasz myisamowego full text searcha....

MyISAM lockuje całą tabelę, InnoDB konkretny wiersz...
Jeśli nie masz włączonego slow loga, to włącz i spójrz na zapytania, które się tam pojawiają.

Oczywiście

Oczywiście masz, czy oczywiście włączysz? :)

Nie proponowałbym jeszcze partycjonowania - danych jest tak mało, że partycjonowanie to dodatkowa robota i może nic nie dać.
Dużo za wcześnie (moim zdaniem) na takie akcje....
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Michał Gruchała:
Jedna tabela spośród wszystkich jest dość spora ok. 0.5 - 1GB. Przechowuje informacje z kilku lat na których operuje każdy użytkownik. Niemalże otwarcie każdej strony jest selectem do tej tabeli. Z tym że prawie każdy użytkownik operuje w 99% an danych z ostatnich 6 miesięcy.

Jak niżej zostało napisane - to nie jest dużo.
Taka tabela w całości mieści się w pamięci - zakładam, że masz ze 2GB RAMu na maszynie ;0


RAMu u MNIE 2GB ;)

MyISAM

Zmigruj do InnoDB i problemy z lockami zginą - pod warunkiem, że nie używasz myisamowego full text searcha....

Tutaj właśnie jest problem, że userzy też wyszukują po opisie i właściwym rozwiązaniem jest nałożenie tam FULL TEXT SEARCHA. CHOĆ wyszukiwanie po tekście w tej kolumnie to jakieś 0.1% zapytań.

Czy MyISAM nie jest szybszy od InnoDB, ale za to bez transakcji? Bezpieczeństwo mogę sobie odpuścić.


MyISAM lockuje całą tabelę, InnoDB konkretny wiersz...
Jeśli nie masz włączonego slow loga, to włącz i spójrz na zapytania, które się tam pojawiają.

Oczywiście

Oczywiście masz, czy oczywiście włączysz? :)


Włączony


Nie proponowałbym jeszcze partycjonowania - danych jest tak mało, że partycjonowanie to dodatkowa robota i może nic nie dać.
Dużo za wcześnie (moim zdaniem) na takie akcje....


Partycjonowanie włącze z podziałem na lata, gdyż rekordy sprzed 1,5 roku wogóle nie są używanie prawie. Bardziej można je traktować jako archiwum.
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Szymon G.:
Marcin Borek:
Michał Gruchała:
Marcin Borek:
Od dłuższego czasu pracuję nad optymalizacją bazy danych Myslq.

Skoro optymalizujesz, to znaczy, że coś "źle/wolno" działa - co?


Działa ok, ale chcę żeby działało super ;)
Żeby doprowadzić do stanu - Zero problemów w STATUSach ;)

Mam kilka pytań z tym związanych:
1) Jakie są skuteczne sposoby podziełu tablę aby zmniejszyć Table_locks_waited.

Zależy jak wygląda tabela, jak jest używana, czy jest duża.

Jedna tabela spośród wszystkich jest dość spora ok. 0.5 - 1GB. Przechowuje informacje z kilku lat na których operuje każdy użytkownik. Niemalże otwarcie każdej strony jest selectem do tej tabeli. Z tym że prawie każdy użytkownik operuje w 99% an danych z ostatnich 6 miesięcy.

Kilka uwag:

1) to nie jest duża tabela


Czy ograniczenie co do tabeli MyISAM nie jest 2GB danych?

2) jeśli każde otwarcie strony robi select, to już na wejściu masz problem

Tzn. Robię cache po stronie aplikacji. Jednak chodziło mi bardziej o to że bez cache każda strona musi zrobić selecta do tej samej jednej tabeli. Jest ona najcześciej przeglądana, insertowana, updatowana.

3) no to partycjonuj tak żeby w jednej partycji mieć dane z ostatnich 6 miesięcy, wtedy dostęp do danych z tej partycji powinien być szybszy, ale oczywiście to zależy ile jest tych danych z ostatnich 6 miesięcy i co to znaczy.

Liczba danych w czasie jest proporcjonalna.

Możesz albo zrobić to "ręcznie" (czyli X tabel) albo użyć partycjonowania - ale musisz wiedzieć jakie zapytania idą do tej tabeli.
Myślę, że zanim do tego dojdziesz, to można jeszcze X rzeczy sprawdzić...

Używasz InnoDB?

MyISAM

To jeszcze ktoś trzyma ważne dane w MyISAM? Nie boisz się tak bez transakcji? Zresztą, może masz taki flow, że to nie boli... ale założę się, że jednak nie.
Link do wypowiedziLink
Paweł Grzegorz Kwiatkowski

Paweł Grzegorz Kwiatkowski Architekt
oprogramowania,
Ericsson

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
MyISAM

Zmigruj do InnoDB i problemy z lockami zginą - pod warunkiem, że nie używasz myisamowego full text searcha....

Tutaj właśnie jest problem, że userzy też wyszukują po opisie i właściwym rozwiązaniem jest nałożenie tam FULL TEXT SEARCHA. CHOĆ wyszukiwanie po tekście w tej kolumnie to jakieś 0.1% zapytań.

Czy właściwe rozwiązanie to sprawa dyskusyjna. Zapewne najprostsze w realizacji ;-)

Jeśli zależy Ci na wydajności to pozostaje InnoDB (pozbędziesz się table locków) + inna realizacja wyszukiwania pełnotekstowego (zdaje się, że jest to jedyny argument za MyISAM).
Link do wypowiedziLink

konto usunięte

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
Czy ograniczenie co do tabeli MyISAM nie jest 2GB danych?

Nie jest.

Jeszcze raz powtórzę. Sprawdź swoje zapytania. Jeśli jakikolwiek SELECT może wykonywać się dłużej niż ułamek sekundy, to nigdy nie pozbędziesz się długich lock waitów, bo taki odczyt będzie on blokował UPDATE, DELETE czy nawet INSERT (tutaj już w zależności od sytuacji). A pierwszy zablokowany zapis blokuje także następne nadchodzące odczyty.

Jeśli nie ma już dalszej możliwości optymalizacji SELECTów, to następnym krokiem jest tylko migracja do InnoDB. Tworzenie "partycji" tylko i wyłącznie w celu pozbycia się locków w tej sytuacji nie było by dobrym pomysłem, bo nie gwarantuje jakichkolwiek rezultatów jeśli chodzi o unikanie blokowania zapytań. Z dużym prawdopodobieństwem można nawet przyjąć, że nie zmieni się nic.

Pytaniem podstawowym jest też na ile rzeczywiście te lock waity są problemem. Czyszczenie statystyk dla samego czyszczenia nie ma sensu, bo jest to praca wykonana bez celu. Jeśli 0.001% wykonanych zapytań cierpi z tego tytułu, oraz z punktu widzenia użytkownika nie jest to nijak odczuwalne, czy skupianie się na tym jest w ogóle potrzebne?
Link do wypowiedziLink
Krzysztof K.

Krzysztof K. scala / java / php
developer with
server administrator
po...

Temat: Optymalizacja baz danych w MySQL

podsumowując,

migracja do innoDb pomoże,
jak full text search jest potrzebny to zastanów się na Solr'em np.

no i polecam książkę High Performance MySQL ( a także blog mysqlperformanceblog )
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Maciej Dobrzanski:
Marcin Borek:
Czy ograniczenie co do tabeli MyISAM nie jest 2GB danych?

Nie jest.

Jeszcze raz powtórzę. Sprawdź swoje zapytania. Jeśli jakikolwiek SELECT może wykonywać się dłużej niż ułamek sekundy, to nigdy nie pozbędziesz się długich lock waitów, bo taki odczyt będzie on blokował UPDATE, DELETE czy nawet INSERT (tutaj już w zależności od sytuacji). A pierwszy zablokowany zapis blokuje także następne nadchodzące odczyty.

Jeśli nie ma już dalszej możliwości optymalizacji SELECTów, to następnym krokiem jest tylko migracja do InnoDB. Tworzenie "partycji" tylko i wyłącznie w celu pozbycia się locków w tej sytuacji nie było by dobrym pomysłem, bo nie gwarantuje jakichkolwiek rezultatów jeśli chodzi o unikanie blokowania zapytań. Z dużym prawdopodobieństwem można nawet przyjąć, że nie zmieni się nic.

Pytaniem podstawowym jest też na ile rzeczywiście te lock waity są problemem. Czyszczenie statystyk dla samego czyszczenia nie ma sensu, bo jest to praca wykonana bez celu. Jeśli 0.001% wykonanych zapytań cierpi z tego tytułu, oraz z punktu widzenia użytkownika nie jest to nijak odczuwalne, czy skupianie się na tym jest w ogóle potrzebne?


Chciałbym mieć bazę super działającą gdyż za 2 lata być może jej wielkość i liczba userów w systemie może wzrosnąć kilkukrotnie.
Chcę poznać jak najlepsze techniki optymalizacji bazy i poeksperymentować trochę ;)
Moja baza nie graniczy na skraju działania, ale chciałbym pozbyć się negatywnych statystyk.
Link do wypowiedziLink
Marcin Borek

Marcin Borek Java Developer,
Comarch

Temat: Optymalizacja baz danych w MySQL

Czy ktoś poleci mi treściwe materiały na temat skutecznego zakładania indeksów?
Link do wypowiedziLink
Michał Gruchała

Michał Gruchała Skalowalność,
wydajność,
niezawodność

Temat: Optymalizacja baz danych w MySQL

Marcin Borek:
Czy ktoś poleci mi treściwe materiały na temat skutecznego zakładania indeksów?

książka "High Performance MySQL"
oraz
http://jpipes.com/presentations/index_coding_optimizat...
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Optymalizacja bazy danych m...




Wyślij zaproszenie do
Anuluj