Fragmentacja danych - problem z wydajnością Opcje

[tomerro]

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 

[alegorn]

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



Ten post edytował alegorn 24.05.2013, 14:00:08