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 

[rocktech.pl]

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

Ten post edytował rocktech.pl 24.05.2013, 13:43:34