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 

[tomerro]

Odpaliłem mysqltuner.pl i oto wiersze oznaczone [(IMG:style_emoticons/default/exclamation.gif) !]:

[(IMG:style_emoticons/default/exclamation.gif) ] Total fragmented tables: 42 -moja baza ma 28 tabel (czyli pewnie wszystkie są fragmentowane)
[(IMG:style_emoticons/default/exclamation.gif) ] Maximum possible memory usage: 58.7G (93% of installed RAM) -serwer jest tylko pod MySQL wiec to chyba nie problem
[(IMG:style_emoticons/default/exclamation.gif) ] 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
[(IMG:style_emoticons/default/exclamation.gif) ] Joins performed without indexes: 150
[(IMG:style_emoticons/default/exclamation.gif) ] 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.

Ten post edytował tomerro 24.05.2013, 14:08:26