Witam,

Mam duży problem z wydajnością bazy danych Mysql InnoDB.

Mam ostatnio duży ruch. I co parę godzin (nawet co 2-3) działanie strony drastycznie zwalnia. Okazuje się że mam fragmentowane wszystkie tabele.
Gdy zauważę to zwolnienie robię OPTIMAZE na wszystkich tabelach i działanie znów jest bardzo szybkie, aż do kolejnego zwolnienia.

Co może być przyczyną fragmentacji?

Podejrzewam że naprawdę bardzo duża liczba UPDATEÓW I INSERTÓW.
Aplikacja często robi UPDATE na tych samym wierszach. Np. mam tabelę POST i tabelę COMMENTS oraz POST_POINTS. Liczbę komentarzy i punktów przechowuję w wierszu tabeli POST tak aby mieć do nich szybki dostęp bez konieczności JOIN i COUNT. Za każdym razem gdy ktoś doda komentarz lub oceni POST muszę robić UPDATE nowej liczby komentarzy i punktów. Czy to może być przyczyna?

Jak zapobiec tej sytuacji?

Robienie co 2-3 godziny OPTIMAZE to zapewne okropnie zły pomysł, bo z tego co czytałem dopisuje dane do ibdata1.
Czytałem to: http://gagor.pl/2011/12/mysql-proste-metody-optymalizacji/
Czy powinienem zrobić kompaktowanie plików, dodać do my.cnf zapis innodb_file_per_table i rozdzielić ibdata1? Czy może problem leży gdzie indziej?
Czytałem że innodb_log_file_size powinien wynosić 25% innodb_buffer_pool_size, ale maksymalnie mogę ustawić 2GB zamiast 10GB (przez 32 bit).
Baza danych stoi na dedykowanym serwerze, przeznaczonym tylko pod bazę danych z 64GB RAM. Mój my.cnf:

[SQL] pobierz, plaintext 
key_buffer              = 3G
key_buffer_size         = 3G
max_allowed_packet      = 1M
thread_stack            = 192K
thread_concurrency      = 32
thread_cache            = 8
thread_cache_size       = 500
join_buffer_size        = 64M
myisam-recover          = BACKUP
max_connections         = 150
table_open_cache        = 4096
table_cache             = 2000
query_cache_type        = 1
query_cache_limit       = 4M
query_cache_size        = 1G
sort_buffer_size        = 16M
read_buffer_size        = 16M
tmp_table_size          = 512M
max_heap_table_size     = 512M
open_files_limit        = 65536
innodb_buffer_pool_size = 40G
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size    = 2000M
innodb_log_buffer_size  = 8M
innodb_thread_concurrency = 32
innodb_flush_method     = O_DIRECT
[SQL] pobierz, plaintext 

fragmentacja niekoniecznie jest problemem.

sprawdź procesy(ich liczbę, jakie dlugo trwają), w chwili gdy zauważysz spowolnienie, nie od rzeczy by było włączyć/sprawdzić slow query loga, napisz jeszcze jaka wersja mysql ?

5.1.67

Tylko że po OPTIMAZE które defragmentuje bazę wszystko jest natychmiast idealnie przez kilka godzin, nie ma żadnych nawet chwilowych lagów. A gdy nastąpi już spowolnienie to jest cały czas źle, dopóki nie zrobię znów OPTIMAZE. Jeżeli nie zrobię OPTIMAZE nic się nie zmienia (raz tak było kilka godzin dopóki to zauważyłem).
Gdyby były długie i złe zapytania, obciążenie byłoby skokowe (tak myślę), raz lepiej raz gorzej, a tak nie jest.

przyczyn mogą być dziesiątki.
począwszy od QC do nawarstwienia się problemów poprzez ilość połączeń ( nie zapominajmy też że najzwyczajniej w świecie czasem hardware zdycha ).

powiadasz że optimize działa?
ja ci powiem że restart serwera też pewnie pomoże.. ale co z tego ?
nie jesteśmy lekarzami, nie leczymy objawowo, my musimy rozpoznać i naprawić przyczynę.

bez dostępu do logów, jakiegoś cacti, czy choćby tego o czym pisałem - jest to wróżenie z fusów.
jakie masz rozmiary tych plików?, ile masz tych rekordów? jeśli mniej niż kilka/kilkanaście milionów - to autorytarnie ( ) powiem że idziesz złym tokiem myślenia.

sprawdź:
logi dotyczące QC
ilość połączeń do bazy danych
listę procesów w czasie spowolnienia, zresztą sprawdź i przed.
sprawdź slow loga! tutaj mogą się kryć realne odpowiedzi..

J.

niezależnie od powyższego - pomyśl o przejściu na minimum 5.5 a najlepiej na 5.6
spora różnica.

Zajmujesz się optymalizacją i konfiguracją serwera baz danych zawodowo?

Potrzebuję pilnie to wszystko poukładać. Nie jestem specjalistą od baz danych. Przed chwilą znowu miałem reset serwera. Może chcesz to dla mnie zrobić, oczywiście odpłatnie.

-Nie wiem co to QC.
-Z PHPmyAdmin: maks. równoczesnych połączeń: 39
-Proces mysqld (komenda TOP) od 4% do 300% obciążenia CPU, przeważnie 20-30% (teraz, gdy jest dobrze i szybko po restarcie)
-Postaram się zaraz uruchomić slow loga
-baza to 2 mln wierszy w sumie, ale głównie przez jedną tabelę która ma 1,2 mln

Jak mam coś jeszcze podać to proszę napisz bardziej dokładnie gdzie tego szukać i jakie komendy wykonywać.

Witam.

Problem jest dość złożony.

- konfiguracja MySQL (w powiązaniu z architekturą serwera)
- nieoptymalne zapytania, struktura bazy ...

Spróbuj:

- analizy my.cnf --> https://github.com/rackerhacker/MySQLTuner-perl
- banalnej analizy tabeli --> http://dev.mysql.com/doc/refman/5.0/en/pro...re-analyse.html
- partycjonowania na tabelach z największym przyrostem danych --> http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
- slow_log dopiero odpalasz ale mówisz o dużej ilości INSERT UPDATE (locki na wierszach murowane) --> http://www.stevemeyers.net/2013/05/myisams...em-and-how.html

ha, trudno odpowiedzieć czy zajmuje się tym zawodowo.
i tak - i nie.

może najlepiej będzie jeśli powiem że zajmowałem się tym od czasu do czasu w zeszłej firmie, poza tym interesowałem się optymalizacją.
nie jestem adminem, choć trochę zostałem przeszkolony w pracy.

QC - query cache (cache zapytań)
jeśli jest zbyt wiele zapytań, i jest przekroczony rozmiar buforu - jest wykonywany zrzut pamięci... czyli operacja o jednym z najwyższych priorytetów (co oznacza że cała baza sobie czeka aż proces się zakończy)

ilość połączeń - nie jest źle wobec tego, sporo mniej niż max.

procesy - ok, czyli mysql ci zabija serwer ale mi chodziło o polecenie : http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html
wyświetli ci to listę poleceń jakie się właśnie wykonują, wraz z czasem ich wykonywania
możesz też doraźie je ubijać.

bez skrupułów zabijaj te które są powyżej 30 sec. http://dev.mysql.com/doc/refman/5.1/en/kill.html
przeglądarka i tak nie wyświetli już odpowiedzi
są narzędzia które to ułatwiają, nie pamiętam niestety(a nie mam tutaj) phpmyadmina... pewnie też coś takiego ma.


szukaj tych które trwają więcej niż kilka sec. jak masz ich dużo - to masz także poblokowane procesory (zakładam że masz min. 8 rdzeni)
zapisz je sobie i przeanalizuj te zapytania, trzeba koniecznie je przebudować. (explain itp)

podejrzewam ze wykonujesz cyklicznie jakaś kosztowna (czaso i zasobochlonna) operacje na tej dużej tabeli. musisz sprawdzić jakie operacje są na niej wykonywane, być może uda się je prosto zoptymalizować (np dodać jakiś index)


przeanalizuj, co się stało, co zostało zmienione, że serwer nie wyrabia.

zmiana kodu?
przyrost danych?
zwiększenie ruchu?

być może nie da się tego ominąć bez zmian sprzętowych (czasem ruch, ilość danych na tyle wzrasta, że nie ma opcji, sprzęt nie daje rady)

to tak na początek.


EDIT ważne
tak przyszło mi do głowy.
z ubijaniem bez skrupułów to tylko SELECT, nad insert/update to lepiej sie dwa razy zastanowić
slow loga włącz jedynie na jakiś czas. bo on także daje dodatkowe obciążenie

Odpaliłem mysqltuner.pl i oto wiersze oznaczone [!]:

[] Total fragmented tables: 42 -moja baza ma 28 tabel (czyli pewnie wszystkie są fragmentowane)
[] Maximum possible memory usage: 58.7G (93% of installed RAM) -serwer jest tylko pod MySQL wiec to chyba nie problem
[] Query cache efficiency: 19.0% (618K cached / 3M selects) -zapytania mam dla każdego zalogowanego użytkownika bardzo indywidualne, przez parametry, filtry, więc może to dla tego
[] Joins performed without indexes: 150
[] Table cache hit rate: 8% (189 open / 2K opened)

Dodatkowo odpaliłem doradcę w PHPMyAdmin, oto screen podpowiedzi: http://i.imgur.com/CHG1npc.png

Locki są na pewno (tak myślę), ale jak mówię przez 2-3 godziny wszystko jest super, a obciążenie ruchu jest ciągle takie samo. Wygląda jakby coś się zapychało aż osiągnie krytyczny stan i się zawiesza, ale to tylko moje przypuszczenie, dlatego Panowie proszę o pomoc, sprawa bardzo pilna.


EDIT:

Odświeżałem SHOW PROCESSLIST co pół sekundy oto co zauważyłem:

Wszystkie zapytania SQL pojawiają się i przy następnym odświeżeniu znikają, żadne nie czekają, tak jest nawet gdy strona zwolni bo już obserwowałem wcześniej. Zresztą swojego czasu optymalizowałem zapytania tak aby wykonywały się na indeksach i nie dłużej niż 0,1s.

Natomiast co każde odświeżenie pojawiaja się 1-3 procesów SLEEP, niektóre znikają od razu a inne nawet są przez 10 sekund.

qc sie nie przejmuj raczej.

tabele wszystkie jak rozumiem są na innodb?
sprawdz indexy w zapytaniach >> Joins performed without indexes: 150, to zgłasza problem

jak duży masz innodb_buffer_pool_size ?
ile ma rdzeni procesora? bo w ustawieniach masz ich sporo..

poszukaj, poczytaj o cacti i zainstaluj,
wtedy wszystkie logi są przedstawiane graficznie, co pomaga wyłapać problemy, pomaga. bardzo.

ja zmykam, jestem dopiero w poniedziałek.
powodzenia.