Dlaczego MySQL mówi, że brakuje mi pamięci?

Próbowałem wykonać dość duży INSERT...SELECTw MySQL z JDBC i otrzymałem następujący wyjątek:

Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

Ponieważ tak naprawdę nie zwracam obiektu ResultSet, pomyślałem, że przestrzeń sterty Java nie powinna stanowić problemu. Jednak i tak próbowałem go podnieść i nic nie dało. Następnie próbowałem wykonać instrukcję w MySQL Workbench i uzyskałem zasadniczo to samo:

Error Code 5: Out of memory (Needed 1073741816 bytes)

Powinienem mieć dużo pamięci RAM, aby wykonać te operacje (wystarczające, aby zmieścić cały stół, z którego wybieram), ale domyślam się, że istnieją różne ustawienia, które muszę dostosować, aby wykorzystać całą moją pamięć. Korzystam z podwójnie bardzo dużej instancji Amazon EC2 High Memory Double z systemem Windows Server 2008 AMI. Próbowałem majstrować przy pliku my.ini, aby korzystać z lepszych ustawień, ale o ile wiem, mogłem pogorszyć sytuację. Oto zrzut tego pliku:

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=1024M
table_cache=256
tmp_table_size=25G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_repair_threads = 2
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=13M
innodb_buffer_pool_size=23G
innodb_log_file_size=622M
innodb_thread_concurrency=18
innodb_file_per_table=TRUE
join_buffer_size=4G
max_heap_table_size = 10G

Czy to tylko kwestia zmiany powyższych ustawień, aby działały lepiej w moim środowisku? Jeśli tak, jakich ustawień powinienem użyć? Jestem jedynym, który kiedykolwiek korzysta z tego wystąpienia; Używam go do mojego osobistego projektu hobby, który obejmuje analizę statystyczną dużych zbiorów danych. W związku z tym mogę swobodnie zużywać wszystkie dostępne zasoby do własnych zapytań.

Jeśli to nie jest kwestia zmiany tych ustawień, na czym polega problem? Dziękujemy za wszelką pomoc, która może pomóc w lepszym skonfigurowaniu wszystkiego.

Biorąc pod uwagę, że jest to instalacja systemu Windows, @DTest nadal podał właściwy początkowy kierunek.

Zastosuj następującą formułę:

Większość ludzi korzysta z tego:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections

Wolę to:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Zmienne te należy dostosować, dopóki formuła nie zapewni 80% zainstalowanej pamięci RAM lub mniej.

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections

Spróbowałbym zmniejszyć twoje rozmiary buforów. Zwiększenie ich rozmiarów, tak jak je masz, spowoduje problemy. Ile masz pamięci do uruchomienia tych wartości:

query_cache_size=1024M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_buffer_pool_size=23G

Niektóre rozmiary buforów są przydzielane dla wątków, na przykład myisam_sort_buffer_size 10G przydziela 10G dla każdego wątku.

Najpierw znacznie zmniejszyłbym te wartości, a następnie zbadałem, jakie wartości naprawdę potrzebujesz, aby mieć przydzieloną tak dużo pamięci RAM (jeśli w ogóle).

Szybki sposób na określenie ilości pamięci, którą MySQL myśli, że mogłaby przydzielić, jest następujący:

wget mysqltuner.pl

perl mysqltuner.pl

Po uruchomieniu tego skryptu dowiesz się, jaki procent zainstalowanej pamięci RAM MySQL myśli, że można ją bezpiecznie przydzielić. Jeśli podana odpowiedź przekracza 100%, zdecydowanie musisz zmniejszyć rozmiar bufora. Najważniejsze z nich to:

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections key_buffer_size
(niezbyt skuteczne po 4G)

@DTest już określił dla ciebie kierunek w swojej odpowiedzi, więc +1 dla jego odpowiedzi. Skrypt perla powie ci, co się stanie, jeśli go nie ustawisz lub zmienisz dowolną wartość. Oto przykład:

Mój klient ma read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size = 2M
join_buffer_size = 128K
max_connections = 1050

Oto wyniki z mysqltuner.pl:

MySQLTuner 1.2.0 -
Raporty o głównych błędach Haydena , prośby o funkcje i pliki do pobrania na stronie http://mysqltuner.com/
Uruchom z „--help”, aby uzyskać dodatkowe opcje i filtrowanie wyników
Wprowadź swój login administracyjny MySQL: lwdba
Podaj swój administrator MySQL hasło:

-------- Statystyki ogólne ---------------------------------------- ----------
[-] Pominięto sprawdzanie wersji skryptu MySQLTuner
[OK] Aktualnie działająca obsługiwana wersja MySQL 5.0.51a-community-log
[!!] Przełącz na 64-bitowy system operacyjny - MySQL nie może obecnie używać cała twoja pamięć RAM

-------- Statystyki silnika pamięci masowej --------------------------------------- ----
[-] Status: + Archiwum -BDB + Federated + InnoDB -ISAM -NDBCluster
[-] Dane w tabelach MyISAM: 319M (tabele: 108)
[-] Dane w tabelach InnoDB: 2M (tabele: 5)
[!!] Suma podzielonych tabel: 22

-------- Wskaźniki wydajności ---------------------------------------- ---------
[-] Do: 52d 23h 15m 57s (72M q [15,875 qps], 241K conn, TX: 2B, RX: 1B)
[-] Odczyt / zapis: 59% / 41%
[-] Całkowita liczba buforów: 34,0 mln globalnych + 2,7 mln na wątek (maks. 1050 wątków)
[!!] Przydział> 2 GB pamięci RAM w systemach 32-bitowych może spowodować niestabilność systemu
[!!] Maksymalne możliwe użycie pamięci: 2,8G (72% zainstalowanej pamięci RAM)
[OK] Wolne zapytania: 0% (54 / 72M)
[OK] Najwyższe wykorzystanie dostępnych połączeń: 6% (65/1050)
[OK] Rozmiar bufora klucza / całkowite indeksy MyISAM: 8,0M / 82,1 mln
[OK] Współczynnik trafień w buforze kluczy: 100,0% (4b buforowane / 1M odczytów)
[!!] Pamięć podręczna zapytań jest wyłączona
[OK] Sortowanie wymagające tabel tymczasowych: 0% (sortowanie tymczasowe / sortowanie 948K)
[OK] Tabele tymczasowe utworzone na dysku: 3% (11 KB na dysku / 380 KB ogółem)
[!!] Pamięć podręczna wątków jest wyłączona
[!!] Współczynnik trafień w pamięci podręcznej tabeli: 0% (64 otwarte / 32 K otwarte)
[OK] Otwórz plik zastosowany limit: 2% (125 / 5K)
[OK] Natychmiastowe uzyskanie blokady tabeli: 99% (30 mln natychmiastowych / 30 mln blokad)
[OK] Rozmiar danych / pula buforów InnoDB: 2,7 M / 8,0 M

-------- Zalecenia ----------------------------------------- ------------
Ogólne zalecenia:
Uruchom OPTYMALIZACJĘ TABELI, aby defragmentować tabele w celu uzyskania lepszej wydajności
Włącz dziennik wolnych zapytań, aby rozwiązywać problemy z
błędnymi zapytaniami Ustaw rozmiar wątku_pamięci podręcznej na 4 jako wartość początkową
Zwiększaj stopniowo bufor_tabeli, aby uniknąć limitów deskryptorów plików
Zmienne dopasować:
rozmiar_pamięci_podstawowej (> = 8
mln ) rozmiar_pamięci_podstawowej (początek o 4)
pamięć_podstawowa (> 64)

Proszę zwrócić uwagę na wskaźniki wydajności

[-] Całkowita liczba buforów: 34,0 mln globalnych + 2,7 mln na wątek (maks. 1050 wątków)

że MySQL może przydzielić do 72% zainstalowanej pamięci RAM na podstawie ustawień w /etc/my.cnf.

34M jest oparty na kombinacjach innodb_buffer_pool_size i key_buffer_size

2,7 mln na wątek oparto na read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size.

Wielokrotność 2,7M jest oparta na max_connections.

Dlatego musisz dostosować te parametry, dopóki raport Metryki wydajności nie wskaże, że masz mniej niż 100% (najlepiej poniżej 80%) zainstalowanej pamięci RAM.

Nie powiedziałeś, ile masz pamięci RAM? Zakładam, że ma co najmniej 32 GB.

innodb_buffer_pool_size - 23G

Dobry na tyle pamięci RAM.

query_cache_size = 1G

O wiele za duży. Jest nieefektywny, gdy jest duży. Polecam nie więcej niż 50 mln.

key-buffer_size = 5G

Może być twardy limit 4G (nadal) w systemie Windows, miał twardy limit 4G. Twój 5G mógł zmienić się w 1G. W każdym razie, jeśli wszystkie twoje tabele to InnoDB, po co marnować pamięci RAM. Ustaw na 50M.

Ponieważ komunikat o błędzie miał dokładnie 1G, pachnie jak sort_buffer_size. 32M może być rozsądne.