Dotaz: SQL Error : 5 Out of memory

[Fero Taraba]

Mam nasledujuci problem na webovej aplikacii, ide vlastne o diskusne forum s databazou, ktora ma 1,2GB. Server bezi na C2D E6400 s 3GB RAM, ako databaza je pouzity MySQL vo verzii 5.0.85 (v blizkej dobe updatnem na 5.0.87).

Problem je ten, ze niekedy vyskoci hlaska SQL Error : 5 Out of memory (Needed 1046556 bytes) alebo nieco na ten sposob... Myslim, ze by to mohlo ist napravit zmenou mojho configu, avsak neviem co by som potreboval zmenit. Pozeral som si aj configy a manual, no neviem co presne. Vedel by mi niekto pomoct? Tu je moj config:

[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# The MySQL server
[mysqld]
bind-address    = 127.0.0.1
port		= 3306
socket		= /tmp/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 64M
expire_logs_days = 3
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

max_connections=1000

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 3M
write_buffer = 3M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout 

Odpovědi

[Heron]

Neptal jste se už na root.cz? V konfigu máte extrémně vysoký počet spojení, takto to bude chtít kolem 10GB RAM. Jak píšete, máte 3GB. Používáte 64b MySQL? Zkuste si projít tuto diskusi a dejte sem výsledek z programu mysqltuner.pl.

[Fero Taraba]

Nepytal, teda aspon si na to nespominam. Mozno ak tak niekedy pred rokom ci dvomi, ale tusim prave tu na abclinuxu.cz som riesil par otazok. Skusim ten perl script pustit v noci, ked bude server bez vytazenia a uvidim. Myslite, ze je to sposobene poctom pripojeni a ze by trebalo az tolko giga ramky? Pretoze ked sa divam na vytazenie, grafy a podobne, tak momentalne je vyuzite ramky nasledovne: active 551MB, inactive 1.75GB, wired 235MB, buffers 111MB, cache 145MB, free je nieco okolo 250MB. Vytazenie procesoru je tak okolo 70-80 percent, load average sa pohybuje okolo 0.7 az 1.0, niekedy v spicke vybehne na aj 1.2. Nie som expert na tieto cisla, ale myslim si, ze server nie je pretazeny, teda aspon z tohto mi to nevyplyva.

[Fero Taraba]

Tak som to nakoniec pustil, tu je vysledok:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.85
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 808M (Tables: 529)
[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 39

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13m 5s (50K q [64.190 qps], 1K conn, TX: 102M, RX: 12M)
[--] Reads / Writes: 54% / 46%
[--] Total buffers: 602.0M global + 24.3M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 24.3G (817% of installed RAM)
[OK] Slow queries: 0% (0/50K)
[OK] Highest usage of available connections: 1% (11/1000)
[OK] Key buffer size / total MyISAM indexes: 512.0M/677.0M
[OK] Key buffer hit rate: 99.8% (43M cached / 70K reads)
[OK] Query cache efficiency: 58.7% (20K cached / 34K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 96
[OK] Temporary tables created on disk: 8% (116 on disk / 1K total)
[OK] Thread cache hit rate: 99% (11 created / 1K connections)
[OK] Table cache hit rate: 97% (186 open / 191 opened)
[OK] Open file limit used: 3% (339/11K)
[OK] Table locks acquired immediately: 99% (34K immediate / 35K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Add skip-bdb to MySQL configuration to disable BDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 128.0K, or always use indexes with joins)

[Heron]

Switch to 64-bit OS - MySQL cannot currently use all of your RAM

Maximum possible memory usage: 24.3G (817% of installed RAM)

Pokud to nechcete přeinstalovávat na 64b systém, je nutné to nastavit tak, aby Maximum possible memory usage: bylo méně než 2GB (a méně než 100% RAM ). Bez toho se dál nedostanete.

No, jak z toho ven. Snižte max_connections na rozumný počet. Rozumný počet je Highest usage of available connections:. Pokud to vydrží běžet 24h, tak můžete použít daný počet. A pak snížit key_buffer. Jo a vypnout engine, které nepoužíváte (innodb, bdb).

[Fero Taraba]

Dakujem za rady, niektore som spravil uz pocas vikenda, napriklad znizil key_buffer, max_connections som dal na 170, proste dostal som to vyuzitie ramky na dakych 97 percent. Bezalo to den, ale dival som si potom opat MySQLtunner a videl som, ze tam bola zase rada, aby som zvysil key_buffer, tak som ho z 256 vratil na 512. InnoDB, Barkley a proste to, co mi pisalo, tak to som vypol. Avsak potom mi zase dalo tuto hlasku: join_buffer_size (> 1.0M, or always use indexes with joins)

Co s tym? Pridal som takuto hodnotu a nastavil ju na 1M, no nepomohlo to. Najskor to pisalo 128, potom to po par hodinach behu skocilo az na mega.

[Heron]

Ten mysqltuner berte jako doporučení. On se prostě snaží, aby to bylo co nejrychlejší, na to je napsán. Nejrychlejší nastavení často znamená nejvíce paměti (je to logické). Vyvážení těchto parametrů je velmi složité a závisí to na zkušenostech administrátora (takto lze získat pouze pár procent výkonu navíc, největší problém je špatně napsaná aplikace, optimalizací SQL dotazů lze dosáhnout mnohem větší rychlosti). Jen se snažte držet celkovou paměť pod 2GB.

[Fero Taraba]

Aplikacia je phpBB, takze az tak zle by to optimalizovane nemalo byt. Chapem, ze to asi zrejme nie je stavane na tak velke fora, pripadne ze by to chcelo dobre upravy a vyladenia, no hlavne by to chcelo najskor poriadne nastavit MySQL 5. V noci som sa s tym pohral a znizil max_connections, nastavil som ich na 90 s tym, ze max vyuzitie pamate sqlkom je momentalne 2.0GB, pricom ked tam bolo 89, tak to bolo 1.9GB. Cize som to hodil na 90 spojeni. Problem je, ze ked sa teraz vecer divam, tak niekedy vecer okolo desiatej databaza aj tak padla. Ako to bolo pocas dna, tak to zmapovane nemam.

[Kroko]

Aplikacia je phpBB, takze az tak zle by to optimalizovane nemalo byt.

Tak tohle me fakt rozesmalo ...hlavne ta druha cast vety za ,

[Kroko]

Jsou situace kdy se sql dotazy optimalizovat nedaji, napr. u hostingu kde hostuje par radoby programatoru se sprasenym kodem :(

[tomk]

Mozna by bylo dobre tu chybu opsat presne a napsat, kde se objevuje. Takhle by mi to pripadalo na limit pameti pro proces v php, kde narazi a chybu pise nejaky DB framework. Zkusil bych proto zvednout mnozstvi vyuzitelne pameti pomoci memory_limit v php.ini.

Tomas

[Fero Taraba]

Objavi sa to hocikde na fore, prevazne ked chcem otvorit temu, precitat prispevky alebo poslat novy prispevok. V PHP je nastaveny memory_limit na 32M.

[Bedňa]

Nepoznám na slovensku server, možno okrem jedného, čo by zjedol 3GB, mám podozrenie že to fórum môže mať dva problémy, buď nevykopáva z databázy sedenia (prípadne ich drží neúmerne dlho), alebo nieje dostatočne zabezpečené proti botom a tí sa tam naraz 100x prihlásia a zhodia ho, prípadne kombinácia oboch.

[Fero Taraba]

100 botov predsa nemoze zhodit taky server. V robots.txt mam bloknute iste adresare a subory, takze ostavaju len tie hlavne casti fora pre botov.

[cronin]

V robots.txt mam bloknute iste adresare a subory, takze ...

... takze si skaredym robotom povedal, ze tie adresare a subory existuju, co by mozno inak ani nezistili. robots.txt je v dnesnej dobe dost pochybny nastroj a je diskutabilne, ci sposobuje viac osohu alebo skody. Tymto suborom sa riadia iba slusne roboty; lenze slusne roboty su slusne, takze aj bez obmedzeni v robots.txt sa spravaju tak, aby server nezahltili.

[Bedňa]

Tak ako bolo už spomínané na fóre je hlavným žrútom max_connection­s=1000, podľa mňa 1000 spojení fakt dosť, ako píše Tomáš Crhonek on má nastavené max_connections = 32 a výpočet:

Jsou to jednak společné buffery (u vás 1GB + možná 1GB na query) a pak buffery přidělované každému konektu (u vás odhaduji 7MB x 1000 = 7GB). Což znamená, že mysqld potřebuje 9GB RAM. Což neprojde především proto, že jako 32b aplikace dostane maximálně 2GB. Proto to padá.

[Fero Taraba]

Pocet spojeni je z 1000 znizeny na 90, to je posledne cislo co som dal, MuSQLtuner pise vyuzitie pamate na rovne 2GB. Ked som dal o jedno spojenie menej (89), tak vyuzitie ramky bolo 1.9GB. Aj tak to padlo pocas dna.

[Bedňa]

Koľko návštev máš denne? Lebo to nieje možné aby MySQL za bežných podmienok s 2GB padalo. Tie 2GB máš vyťažené keď tam nikto nieje? To je potom zle treba ubrať ešte spojení a sledovať koľko to zožere keď tam príde jeden človek, či ešte netreba ubrať v iných nastaveniach. Posledná možnosť je že Ti niekto robí DoS útoky, s tým že nemáš dobre zabezpečené vstupy, vtedy aj niekoľlko botov, ktorý dajú vylistovať celú databázu vrátane všetkých obrázkov, môžu zožrať pamäť.

[Heron]

Pozor, ona tam ta DB zřejmně není sama. Pokud něco vyžere zbytek paměti (zblázněný apache, php, cokoliv), může to dělat totéž.

To tazatel: prošel jste systémové logy? Nejsou tam záznamy o OOM killeru? Máte nasazený nějaký monitoring, grafy? Používáte swap?

[Fero Taraba]

Logy mam vypnute, zapnute su len pre Apache z dovodu AWstats. Bezi mi tam Munin na statistiky, v grafe za poslednych 24 hodin nevidim ziadne vyuzitie RAMky cez 1GB, takze tomu moc nechapem. Dnes to opat vecer padlo, pocas dna som to vobec nesledoval.

[Bedňa]

ziadne vyuzitie RAMky cez 1GB

MySQL, alebo celého systému? Pokiaľ sa pamäť využíva len do 2GB a MySQL hlási že nemá dosť, tak potom chyba v MySQL, alebo systému, kua lebo to inak nieje možné.
Hoď sem phpinfo()

[Fero Taraba]

Vyuzitie celeho servera berie RAMku len do jedneho giga, takze to pada zrejme na niecom inom, aj ked napise. Ak sa pozriem na graf, tak tam vidiet vyrazne skoky na nom, takze mi to pripada, ze prave v tom momente to padlo a nastal restart MySQL. Tu je graf ako pamate, tak a loadu: http://bit.ly/B9nBY

Tu je phpinfo na yousendit.com: http://bit.ly/2M0wYE

[Bedňa]

Nespúšta sa na servery cron? narážam na tie skoky.
Tiež mám skúsenosť že divné (a náhodné) vyťaženie systému spôsobovala doska, vyzeralo to ako by žiadny proces nevyťažoval mašinu a pritom bol systém vyťažený naplno, neviem či sa hádal procák s doskou a hdd dokopy.
Pár nastavení s phpinfo() sa mi nepozdáva, keď prídem domov ešte sa na to pozriem.

[Fero Taraba]

Ano, pusta sa tam cron, konkretne AWstats sa pusta, potom sa pusta komprimovanie logov pre Apache a tiez nejake dalsie veci.

Ktore nastavenia PHP sa ti prosim ta nezdaju a radil by si mi ich zmenit? Dakujem.

[Bedňa]

Prepáč že som už neodpísal ale mám toho teraz dosť, vyriešil si to? Keď áno podeľ sa s nami o výsledky, pokiaľ nie, ja by som postupoval spôbom, pozreť využitie pamäte, potom sa pripájať na stránku niekoľkonásobne a sledovať ako rastie využitie pamäte, hoď sem zistenia.

[Fero Taraba]

Pridam este posledny export z mysqltunera:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.85
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 805M (Tables: 528)
[!!] Total fragmented tables: 19

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 24m 1s (1M q [47.316 qps], 49K conn, TX: 2B, RX: 221M)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 827.0M global + 13.2M per thread (89 max threads)
[OK] Maximum possible memory usage: 2.0G (65% of installed RAM)
[OK] Slow queries: 0% (6/1M)
[OK] Highest usage of available connections: 31% (28/89)
[OK] Key buffer size / total MyISAM indexes: 777.0M/687.5M
[OK] Key buffer hit rate: 100.0% (882M cached / 366K reads)
[OK] Query cache efficiency: 57.1% (492K cached / 863K selects)
[!!] Query cache prunes per day: 85848
[OK] Sorts requiring temporary tables: 0% (68 temp sorts / 79K sorts)
[!!] Joins performed without indexes: 1401
[OK] Temporary tables created on disk: 5% (1K on disk / 32K total)
[OK] Thread cache hit rate: 99% (252 created / 49K connections)
[OK] Table cache hit rate: 52% (256 open / 488 opened)
[OK] Open file limit used: 4% (445/11K)
[OK] Table locks acquired immediately: 99% (881K immediate / 886K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 24M)
    join_buffer_size (> 1.0M, or always use indexes with joins)

[melkors]

IMHO je klicove hlasani "[!!] Joins performed without indexes: 1401"

Uz v tom predeslem vypisu bylo neco o neindexovanych/spatne indexovanych tabulkach ... Osobne bych se vydal touhle cestou. Opravdu to muze silne pomoci.

[Heron]

Začínám být v koncích

Tabulky jsou v pořádku? Když se na ně mrknete třeba v phpmyadminovy a dáte zkontrolovat, tak jsou ok?

[Bedňa]

Ešte raz píšem hoď sem výpis phpinfo(), a to výužitie pamäte do 1GB čo si písal, to si myslel len MySQL alebo celý systém, lebo ďalej sa nepohneme.

[Fero Taraba]

Ano, tabulky su vsetky OK. Dal som kontrolu, opravu, optimalizaciu, nie je ziadny problem, vsetko OK. Pri optimalizacii to par kilo ci mega usetrilo, ale poskodena tabulka nebola ziadna.