Prowadzimy witrynę (Moodle), którą użytkownicy obecnie uważają za wolną. Myślę, że udało mi się wyśledzić problem, tworząc MySQL, tworząc tymczasowe tabele na dysku. Obserwuję zmienną created_tmp_disk_tablesw administracji serwera Mysql Workbench i liczba ta rośnie z około 50 tabelami / s. Po kilku dniach użytkowania created_tmp_disk_tableswynosi> 100 tys. Wydaje się również, że pamięć nie została zwolniona. Użycie stale rośnie, aż system stanie się praktycznie bezużyteczny i musimy ponownie uruchomić MySQL. Muszę go ponownie uruchamiać prawie codziennie, a zaczyna się to od użycia około 30-35% dostępnej pamięci i kończenie dnia 80%.
Nie mam żadnych obiektów blob w bazie danych ani kontroli nad zapytaniami, więc nie mogę ich zoptymalizować. Użyłem również Kreatora konfirmacji Percona, aby wygenerować plik konfiguracyjny, ale mój.ini też nie rozwiązał mojego problemu.
pytania
Co powinienem zmienić, aby powstrzymać MySQL od tworzenia tabel tymczasowych na dysku? Czy muszę zmienić ustawienia? Czy mam wrzucić więcej pamięci?
Jak mogę zatrzymać MySQL przed zjedzeniem mojej pamięci?
Edytować
Włączyłem slow_queriesdziennik i odkryłem, że zapytanie SELECT GET_LOCK()zostało zarejestrowane jako wolne. Szybkie wyszukiwanie ujawniło, że zezwoliłem na trwałe połączenia w konfiguracji PHP ( mysqli.allow_persistent = ON). Wyłączyłem to. Zmniejszyło to szybkość, z jaką MySQL zużywa pamięć, ale nadal tworzy tabele tymczasowe.
Sprawdziłem również, czy key_buffer sizejest wystarczająco duży. Spojrzałem na zmienną key_writes. To powinno być zero. Jeśli nie, zwiększ wartość. Mam key_buffer_sizezero key_readsi zero, key_writeswięc zakładam, że key_buffer_sizejest wystarczająco duży.
Zwiększyłem tmp_table_sizei max-heap-table-sizedo 1024M, ponieważ wzrost liczby utworzonych tabel_tmp_dysk może wskazywać, że tabele nie mieszczą się w pamięci. To nie rozwiązało problemu.
Edytuj 2
Jeśli widzisz wiele sort_merge_passesna sekundę w wynikach POKAŻ GLOBALNY STAN, możesz rozważyć zwiększenie tej sort_buffer_sizewartości. Miałem 2 sort_merge_passesna godzinę, więc uważam, że sort_buffer_sizejest wystarczająco duża.
Patrz: Podręcznik Mysql włączony sort_buffer_size
Edytuj 3
Zmodyfikowałem sortowanie i dołączam do buforów, jak sugeruje @RolandoMySQLDBA. Wynik jest wyświetlany w poniższej tabeli, ale myślę, że created_tmp_tables_on_disknadal jest wysoki. Zrestartowałem serwer mysql po zmianie wartości i sprawdziłem created_tmp_tables_on_diskpo dniu (8 godzin) i obliczyłem średnią. Jakieś inne sugestie? Wydaje mi się, że jest coś, co nie mieści się w jakimś pojemniku, ale nie mogę zrozumieć, co to jest.
+---------------------+-------------+-------------+--------------------+
| Tmp_table_size, | Sort_buffer | Join_buffer | No of created |
| max_heap_table_size | | | tmp_tables on disk |
+---------------------+-------------+-------------+--------------------+
| 125M | 256K | 256K | 100k/h |
+---------------------+-------------+-------------+--------------------+
| 125M | 512K | 512K | 100k/h |
+---------------------+-------------+-------------+--------------------+
| 125M | 1M | 1M | 100k/h |
+---------------------+-------------+-------------+--------------------+
| 125M | 4M | 4M | 100k/h |
+---------------------+-------------+-------------+--------------------+
Oto moja konfiguracja:
+-----------------------+-----------------------+
|DATABASE SERVER |WEB SERVER |
+-----------------------+-----------------------+
|Windows Server 2008 R2 |Windows Server 2008 R2 |
+-----------------------+-----------------------+
|MySQL 5.1.48 |IIS 7.5 |
+-----------------------+-----------------------+
|4 Core CPU |4 Core CPU |
+-----------------------+-----------------------+
|4GB RAM |8GB RAM |
+-----------------------+-----------------------+
Dodatkowe informacje
+--------------------+---------+
|PARAM |VALUE |
+--------------------+---------+
|Num of tables in Db |361 |
+--------------------+---------+
|Size of database |2.5G |
+--------------------+---------+
|Database engine |InnoDB |
+--------------------+---------+
|Read/write ratio |3.5 |
|(Innodb_data_read/ | |
|innodb_data_written)| |
+--------------------+---------+
|Avg table size |15k rows |
+--------------------+---------+
|Max table size |744k rows|
+--------------------+---------+
Ta konfiguracja została mi dana, więc mam nad nią ograniczoną kontrolę. Serwer WWW zużywa bardzo mało procesora i pamięci RAM, więc wykluczyłem tę maszynę jako wąskie gardło. Większość ustawień MySQL pochodzi z narzędzia do automatycznego generowania konfiguracji.
Monitorowałem system za pomocą PerfMon przez kilka reprezentatywnych dni. Na tej podstawie dochodzę do wniosku, że to nie system operacyjny zamienia się na dysk.
My.ini
[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="D:/DBs/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=125
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
table-definition-cache= 1024
max-heap-table-size= 32M
thread_cache_size=38
MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=1024K
read_rnd_buffer_size=256K
sort_buffer_size=1024K
join_buffer_size=1024K
INNODB Specific options
innodb_data_home_dir="D:/DBs/"
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=8
