sposób, aby zapobiec oczekiwaniu zapytań na blokadę poziomu tabeli

Napotkaliśmy problem po przeniesieniu bazy danych naszego klienta na dodatkowy serwer. Powinno to mieć pozytywny wpływ na wydajność witryny, ale występuje problem z blokowaniem tabel w MyISAM. (Słyszałem o używaniu InnoDB zamiast MyISAM, ale nie możemy zmienić silnika w najbliższej przyszłości).
Możemy wykryć to w zapytaniu o aktualizację, które jest wykonywane, gdy moderator aktywuje komentarz na stronie artykułów. Oto proces:

• zapytanie o aktualizację jest przetwarzane SET status = 1 WHERE id = 5(indeks jest ustawiony)
• buforowane pliki strony są usuwane

W tym momencie cała strona staje się wolna. Sama baza danych jest zajęta przez kilka minut. Pobierałem listę procesów kilka razy i zobaczyłem około 60 wpisów z różnych zapytań select, które były w stanie oczekiwania na blokadę poziomu tabeli .

1. Nie rozumiem, dlaczego ta aktualizacja w tabeli article_commentsmoże wpływać na instrukcje select, aby tabela articleczekała na blokadę na poziomie tabeli. Na liście procesów prawie wszystkie oczekujące zapytania pochodziły z tej tabeli. Czytałem o tym, że aktualizacje / wstawki są lepsze niż selekcje i że może to powodować takie problemy, ale sama tabela artykułów nie jest aktualizowana po aktywowaniu komentarzy, więc selekcje nie powinny czekać. Czy źle to zrozumiałem?
2. Czy jest coś oprócz zmiany na InnoDB, aby zapobiec temu zachowaniu lub przynajmniej uzyskać lepszą równowagę? Jestem bardzo zirytowany faktem, że ten problem nie pojawił się przed przeniesieniem bazy danych na nowy serwer. Wydaje mi się, że jest trochę błędna konfiguracja, ale nie wiem, jak to zidentyfikować.

Silnik pamięci masowej MyISAM jest wściekle znany z wykonywania blokad pełnych tabel dla dowolnego DML (INSERT, UPDATEs, DELETE). InnoDB zdecydowanie rozwiąże ten problem w dłuższej perspektywie.

Pisałem o zaletach i wadach korzystania z MyISAM vs InnoDB

• InnoDB vs MyISAM z wieloma indeksami (05 lipca 2012)
• Który jest szybszy, InnoDB lub MyISAM? (3 maja 2012 r.)
• Masz jakieś problemy z konwersją z MyISAM do InnoDB? (09 stycznia 2012 r.)
• Czy powinienem używać silnika pamięci masowej innego niż MyISAM do optymalizacji tych tabel, czy powinienem uzyskać lepsze dyski? (07 października 2011 r.)
• Korzyści z blokowania na poziomie stołu (14 września 2011 r.)
• Jakie są główne różnice między InnoDB i MyISAM? (14 kwietnia 2011 r.)

Jeśli chodzi o twoje aktualne pytanie, oto możliwy scenariusz:

• articlei article_commentssą to tabele MyISAM
• article_commentsma jeden lub więcej indeksów z statusjako kolumną
• Aktualizacje stron indeksu article_commentssą buforowane w buforze kluczy MyISAM (rozmiar według key_buffer_size ), co powoduje, że stare strony indeksów nie są przechowywane w buforze kluczy MyISAM
• Masz WYBIERZ zapytania, które wykonują JOIN między articleiarticle_comments

W moim sugerowanym scenariuszu SELECTs względem articletabeli można powstrzymać od zezwolenia na zapisy z powodu konieczności oczekiwania na article_commentsuwolnienie się od dowolnego DML (w tym przypadku an UPDATE)

W tym momencie cała strona staje się wolna. Sama baza danych jest zajęta przez kilka minut.

Pachnie jakbyś miał dużą pamięć podręczną?

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 | -- Not over 50M
| query_cache_type             | DEMAND   | -- Only if using SQL_CACHE
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

W przypadku systemów produkcyjnych z dużą ilością zapisów, równie dobrze możesz WYŁĄCZYĆ cache_pytania.

Wszystkie wpisy w query_cache dla danej tabeli są usuwane, gdy każdy zapis występuje do tej tabeli. Im większa QC, tym wolniejsze jest to zadanie.

MyISAM używa blokad „na poziomie tabeli”. Odczyty i zapisy nie mogą wystąpić w tym samym czasie (na tej samej tabeli). Surowy, ale skuteczny.