MySQL szerver magas cpu használat

Linux-kezdő

Sziasztok,

Egy újabb teljesítmény problémára keresném a magyarázatot, ami alapvetően számomra érthetetlen.

Adott egy weboldal kb 1000-1500 egyedi látogatóval / nap, ami csúcsidőben 2-6 embert jelent egyszerre. Látogatónként 2-4 oldalt néznek meg és a lapon kb 30-35mp-t töltenek. Tehát alapvetően elmondható, hogy a weboldal nem szolgál ki semmiféle extra terhelést és látogató bázist.

Mindezek ellenére a napokban megugrott a MySQL cpu használata. Amivel nem is lenne gond, mert hát dolgozzon, de nem ritka az, hogy kitömi mind a két magot, ami a szerver alatt van, Google Cloud szolgáltatásban. Így vannak esetek, hogy 10-15mp, míg betölt egy-egy oldal.

Megnéztem már mindent, amit lehet. Próbáltam túrni a logokat, hogy slow_query van-e, mert az simán kinyírhatja, de nincs olyan query, ami elérné az 1mp futási időt. Nézegettem, hogy milyen lekérdezések futnak és abból mennyi. Van pár olyan, ami sokszor lefut, azon még lehetne okosítani, de az a query önmagában nem okozhat ekkora terhelést.
A szerveren nem frissítettem és nem állítottam semmit. Csináltam pár fejlesztést az oldalon ez tény és való, de adatbázist nem igazán érintett, illetve azokat már szétszedtem, kiszedtem, átvariáltam... de semmi nem oldotta meg.

Szóval itt valami más lesz a probléma, csak sajnos nem tudom, hogy mi. Mivel lehetne kideríteni, hogy mi a gondja a MySQL szervernek? Nem tudtok ajánlani valami monitorozót, vagy bármilyen megoldást arra, hogy fény derüljön a terhelés okára? Biztos vagyok benne, hogy valami ok nélküli terheli, csak nem találom, hogy micsoda. Ha leállítom a szerveren az apache-ot, akkor pillanatok alatt visszaáll a szerver load és beáll a világbéke. Tehát az 100%, hogy valami az oldalon keresztül terheli, csak nem jövök rá, hogy micsoda és nem tudom, hogy a slow_query-n kívül még mit nézhetnék meg, hogy rájöjjek a terhelés forrására.

A segítséget előre is köszönöm.

  • 3224 megtekintés

( blackluck v | 2018. 08. 21., k – 15:43 )

Show processlist; mit mond ilyenkor a nagy terheles alatt?
Nem fogyott el memoria/kezdett el swap-elni?
Logokban hiabauzenet, pl max connection-t elerte vagy hasonlo?

Show processlist; nem mutat semmit. Nem megy ott semmilyen query ilyenkor. Vagy ha van is ott valami, akkor pillanatok alatt eltűnik. 4 nap adatai alapján 40 olyan query volt, ami 1mp-nél tovább futott, abból 38 1 és 1,3 mp között volt. A másik 2 az ugyan elszállt, de azokról tudok és 4 nap alatt 2x futott és azok is éjjel.

Memória még van 2GB nincs kihasználva, SWAP-hez még nem nyúlt semmi. Tök üres.

Max connection elérte egyszer a mysql, ami most jelenleg 50-re van állítva. Ezt sem igazán értem, hogy hogyan lehetett, mivel ennyi ember egyszerre még soha nem volt az oldalon. :)

Más egyéb hiba üzenet nincs a logban.

Akkor kezdjuk az elejen, mibol latod hogy mysql terheli a procit?
Ilyenkor terheles tenylegesen cpu terheles vagy inkabb valami io wait vagy egyeb?
Mennyi ideig tart az ilyen terheles altalaban, milyen gyakran jelentkezik, van-e valami rendszer az elofordulas idejeben?
Milyen mysql szerver (mysql, percona vagy mariadb) es milyen verzio?
Hogy nez ki mysql szerver konfigja, foleg kulonfele memoria es connection limitek, illetve mekkora adattal dolgozik hozzavetolgesen (adatbazisok merete, tablak mennyisege) es milyen tablakon engine-el (innodb, myisam, netan egyeb)?
Vannak-e grafikonok rendszerrol/mysql szerverrol?
Ha mysql logokban nem is latszik semmi, kliens oldali (apache, php) vagy rendszer szintu(syslog, messages, dmesg stb.) van-e valami szokatlan akkortajt?
Ha mysql processlist-ben nem latszik semmi az azert van mert olyan gyorsan vegez vagy csak mar elmult addigra a terheles? Esetleg logolast feljebb allitani es nem csak 1 masodpercnel lassabb query-ket logolni?

A top és a htop kombót használom, ahogy egyértelműen a CPU oszlopba jelentik meg a terhelés. Be van kapcsolva IO és egyéb kijelzések is, de azoknál nem látok olyat, ami terhelést indokolna.

IOWait-et nem tudom, hogy hol lehetne érdembe nézni. Jó tanács?

A terhelés néhány másodperctől, a néhány percig is eltart. Teljesen véletlenszerűen jön és mindenféle előjel nélkül, illetve eltűnni is így tűnik el.

MySQL telepítés 5.7.23-as verzióval (Ubuntu 16.07 default repojában lévő MySQL, mindenféle repo módosítás nélkül)

InnoDB enginet használ minden adatbázis. Két adatbázist használ, az egyik 450MB-s, abból egy tábla (statisztikát gyűjt, ritkán írja, belekérdezni még ritkábban kérdez, az 220MB), táblából 48 db van benne. A másik adatbázis nincs egész 1MB 4 táblával.

Jelenleg nincs semmilyen grafikonos megjelenés. Munint lehet rá dobni, azt ismerem, sokat használtam. De pont a MySQL-ről nem szokott túl sok érdemi infót adni.

Áttúrtam a webszerver access logját... találtam valami cseh indexelő botot... kitiltottam az IP-jét. Valami bohóc oldalnak gyűjtött adatokat 1 IP-ről, másodpercenként többször jött. Ezen kívül semmi rendellenes bejegyzés és access log alapján sem látom, hogy DoS lenne, mert ránézésre valósnak tűnnek a kérések és nincs is olyan ördöngösen sok. Apache error log semmit nem tartalmaz, elvétve egy két olyan bejegyzést, hogy nincs beállítva egy változó, vagy ilyesmi (php warning), de ezeket javítgatom, ahogy időm engedi. De semmi olyan, ami a terhelést indokolná. MySQL log ugyan ez. Megnéztem error semmi, slow_query alapján napi 5-10 olyan query, ami mondjuk 1 és 1,3 mp között fut le.

processlist-ben látottak nem indokolnak terhelést, mert pillanatok alatt kikerül onnan minden query. Nem láttam még ott beragadni semmit.
Ha gyorsan végez egy query, akkor terelése se lehet nagy. Nem?? Tehát ha 0.32mp alatt végez egy query, annak a cost-ja sem akkora, ami ilyen mértékű kilengést okozhatna. Vagy rosszul gondolom? slow_query-t elkezdem 0-as kvótával logolni és majd valami scriptet gyártok, ami használható adatot ad belőle. De félek, nem fogok találni benne semmit.

Alább a MySQL config... A Fine Tuning rész az egy aktuális állapot, az már volt minden. mysqltunnel éppen aktuális ajánlása alapján és a google féle perf. tuningok ajánlásai alapján variálgatom, de áttörést még egyetlen beállítás sem hozott.


#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve

connect_timeout = 5 # alap beallitasa: 10
interactive_timeout = 2 # alap beallitasa: 100
wait_timeout = 2 # alap beallitasa: 100

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size = 8M
join_buffer_size = 8M
max_allowed_packet = 4M
table_open_cache = 4M
sort_buffer_size = 4M
net_buffer_length = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
thread_stack = 192K
thread_cache_size = 0
tmp_table_size = 4M
max_heap_table_size = 4M

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
max_connections = 50
#table_cache = 64

#
# * Query Cache Configuration
#
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 8M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log

#
# Here you can see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysqld_slow_queries.log
long_query_time = 1
log-queries-not-using-indexes

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 512M
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 128M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table

innodb_fast_shutdown=0
innodb_flush_method=O_DIRECT

# Uncomment this to get FEDERATED engine support
#plugin-load=federated=ha_federated.so
loose-federated

#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

top-ban 'wa' resz lenne a az io wait, annyi hogy erdemes magonkent is nezni, hatha valami 1 magot hajt ki es arra var valami, de megnezheted iotop-al vagy dstat is diszk muveleteket.
Bar ha fel giga az innodb buffer akkor kb elfer benne az egesz adatbazisod, igy csak iras muveletek eseten lesz erdemben sql-nek diszk hasznalata meg file temp tablak gyartasanal, talan kevesbe errol van szo.

A cpu oszlopban jelenik meg a terhelest gondolom mysqld-re erted, hogy annal latod a terhelest, vagy lehet valojaban valami mas terheli kozben a gepet?

A buffer es cache ertekek ranezesre nem tunnek kevesnek a 2G memoriahoz, meg hogy 0.5G innodb buffer es 50 max connection, de nem szamoltam utana es mysqltuner megmondja mennyi lehet a max memoriahasznalat ezzel a konfiggal.

Mentesekhez van hasznalva binlog vagy nem lehet azt kikapcsolni? Mondjuk ha keves az irasmuvelet akkor nem veszes, de ha jelentosebb es kozben nincs hasznalva akkor plusz terhelest tud adni.

Grafikonok hasznosak tudnak lenni ha gond van vagy tervezni kell a jovore hogy mekkora terheles szokott lenni, mik a jellemzo ertekek es a problemas idoszakban hogy valtozik, sokszor olyanokbol konnyebben ki lehet szurni milyen iranyban erdemes nezelodni. Mysql eseten is van jopar ami hasznos lehet pl: memoria hasznalat, tranzakciok szama (tipusonkent), kliensek szama, query cache hasznalata (bar ugy latom az ki van kapcsolva), temp tablak hasznalata stb. Muninhoz is van jopar ezekhez mysql_ es tarsai neven, de lehet zabbixhoz is talalni pl percona monitoring toolkitben: https://www.percona.com/software/database-tools/percona-monitoring-plug…

Egy query terhelese jo esellyel nem lehet nagy ha gyorsan kikerul, de ha tobb ilyen fut egyszerre akkor mar kevesbe mind1 hogy mennyire gyorsan. Ha pl 0.3sec alatt fut le 1 query akkor onmagaban nem sok, de ha ebbol jon egyszerre 10 egy weboldal betolteshez akkor mar jo esellyel nem 0.3sec alatt adja vissza az osszest, de talan nem is 3 sec lesz azert.
Erdemes ezert slow query logot elemezni, vagy ha el tudod kapni ilyen terheles idoszakban akkor aktualis halozati forgalmon (127.0.0.1:3306) nezni mysql muveleteket, percona toolkitben van query digest azzal slow query logot is tudod elemeztetni, meg tcpdump-al csinalt capture-t is elemezni: https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html (jo esellyel ubuntu repoban is benne vannak ezek alapbol).

Elso korben mindenkepp azt kene behatarolni hogy amikor jelentkezik a nagy terheles akkor azt mysql okozza-e es ha igen akkor abban milyen muveletek tortennek, hogy lehessen vizsgalni az miert ekkora gond, aztan lehet az lesz belole, hogy sok egyforma query fut le egyszerre es query cache vagy valami egyeb cache megoldas kellene mysql es webszerver koze, de siman lehet olyan is hogy valami olyat kerdeznek le ami nincs (jol) indexelve, vagy adatmennyiseg elerte azt a limitet valamelyik tablaban amivel ugyanaz a query ami evek ota fut mar nem fer bele sort bufferbe es temp tablakat general.

( AiRLAC v | 2018. 08. 21., k – 16:27 )

Megtörték és bitcoint számol? :)

--

"After successfully ignoring Google, FAQ's, the board search and leaving a undecipherable post in the wrong sub-forum don't expect an intelligent reply."

egyébként már erre is gondoltam, de a szerver load akkor megy fel, amikor a MySQL process cpu használata felugrik, így csak a mysql-el lehet összefüggésben

illetve, amikor leállítom az apache-ot, akkor nullára redukálódik a szerver terhelése, tehát tuti, hogy web irányából jön a terhelés és nem a szerver irányából

Akkor inkább az apache logokban kellene szétnézni nem ? első körben milyen vhostok futnak ezen a cuccon, mik a beállítások, stb. Esetleg elképzelhető, hogy a rajta lévő valamelyik hostot / weboldalt megtörték / innnnnnyektáltak bele valami "extra" kódot, vagy csak egyszerűen DDOS.

Értem, hogy SQL "viszi" el a loadot, de ennek simán lehet egy megtört weblap / szarul megírt weblap is az oka és azt lehet előbb "fogod" meg apache logokban mint mysql logokban. De csak egy tipp.

apache stop. mysql stop. logok rotálása -> ezáltal kapsz 0-ás logokat "frissen". -> sql / apache start, utána pedig figyelni hogy az apache logban (error logban is) mik történnek. Én erre indulnék el így hirtelen, mindentől függetlenül.

Valamint ha random az eset, akkor is "friss logokat" erőltetni + amikor nagy a "szar" akkor apache stop -> logok átment és mehet az elemzés.

( emotox | 2018. 08. 22., sze – 08:48 )

Erdemes lenne megnezni, hogy ebbol mennyi az iowait, elofordulhat, hogy az applikacio nem hasznal perzisztens kapcsolatokat, vagy nagy a halozati kesleltetes, esetleg hosszan futo tranzakciokra var a mysql amik applikacio oldalon viszik el az idot.

( Proci85 v | 2018. 08. 22., sze – 09:12 )

Telepítettél mostanában Munin-t? Nekem az innodb_ pluginja ette meg a MySQL szervert :)

( NoMan | 2018. 08. 22., sze – 14:43 )

Feltöltöttem egy képet az imgBB-re, ahol látható az, amit én látok. Ilyen kiugrások vannak teljesen véletlenszerű időpontban. Jól látszik, hogy a loadot is felnyomja ilyenkor.

https://ibb.co/cDjs0z

iotop segítségével tudod a processzre bontva is megnézni hogy mi fut és mi használ erőforrást, picit máshogy mint a top paranccsal:
iotop -o -d2

vagy top -H és nyomj 1-est, ekkor mutatja a thread-eket is, ha egy app több threaden fut pld mysql is, hogy melyik eszik sokat.

iostat hasonló a glances-hez, fapadosabb:
10 report 2 másodpercenként:

iostat -xh 2 10

atop-ot szoktak még felrakni, beállítható hogy állandóan fusson és mérjen. 24 órás reportokat készít amit atop-pal utólag lehet brózolni.

Nézd meg hogy nem érint-e téged az "SQL leap second bug". -> Írtad hogy ezt-azt csináltál mostanában a szerveren, nem raktál fel véletlenül ntpd szolgáltatást amivel pontos időt szinkronizálsz a netről?

Filerendszer nem korrupt? Van elég szabad inode? -> df -hi

ntpd-t nem tettem fel, google cloud féle ubuntu 16.07 telepítés, tökéletesen pontos az ideje, nem kellett betennem semmi sync-et, valamit google nagyon tud

szabad hely van inode is és rendes is... nem korrupt a fájlrendszer

végig teszteltem minden top-os parancsot és nem látok semmiféle rendeselleneset, iowait problémát kizárnám

( NoMan | 2018. 08. 23., cs – 14:28 )

Az mi???

https://ibb.co/e6Qx3K

Ez a szemem előtt zajlott le. Pont néztem, ahogy csökken a load, már 0.41-nél járt, amikor a táblázatba semmi nem látszott, csak ami a screen-en van, míg a load 0.41-ről megugrott 1.61-re. Még a végén kiderül, hogy nem is a MySQL?

Mi okozhat ilyen kiugrás úgy, hogy lent semmi nem látszik? Az, hogy VPS nem generálhat ilyet. Bár még nem hallottam olyat hogy a hoston lévő terhelést a guest gépen számszerű módon ki lehetett mutatni. Olyat már igen, hogy a guest lassú volt, de a top-jába semmi nem látszott. Költözés megoldotta, de a guest nem szokta kijelezni, ha a hostot valami leterheli. Vagy tévednél, előfordulhat, hogy a node-on változott valami és nem is nálam?

Az hogy megugrott a load az egy atlagerteket jelol, jelen esetben 1 perces atlagban 1.61-et, az pedig hogy eppen milyen process fut es mennyi eroforrast hasznal egy aktualis allapot amit top default-ban talan 3 masodpercenkent kerdez le.
Szoval lehet az hogy valami hirtelen megdobta a load-ot, valahol elfogyott a kapacitas vagy egyeb okbol, de mikor top ujra lekerdezte mar megszunt az oka csak 1 perces load atlagban latszik a nyoma. Tehat valojaban ott volt valami, akar lathattad is volna csak olyan gyorsan lezajlott hogy akkor 2 mintavetel kozt tortent.

Amugy ha host nem tud cpu eroforrast adni akkor top-ban 'st' terheleskent latszodhat, de ha diszk rendszer valik elerhetetlenne/lassul be vagy memoria valojaban overcommited erosen es swap-bol kapja a vm baromi lassan akkor azokat top-ban kevesbe latod.

dmesg-ben vagy rendszer szintu logokban arra az idoszakra van barmi bejegyzes?
Teszem azt elfogyott a memoria es oom killer kilott valamit vagy elerhetetlenne valt a diszk, netan cpu hang time vagy barmi hibara utalo jel?

ilyenekkel van tele a dmesg


[11151570.618411] audit: type=1400 audit(1535028280.580:118): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=24280 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=0
[11151570.618555] audit: type=1400 audit(1535028280.580:119): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/24280/status" pid=24280 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114
[11151755.124572] audit: type=1400 audit(1535028465.102:120): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/24805/status" pid=24805 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114
[11151755.128297] audit: type=1400 audit(1535028465.106:121): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=24805 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=0
[11151755.128437] audit: type=1400 audit(1535028465.106:122): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/24805/status" pid=24805 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114
[11156462.747527] audit: type=1400 audit(1535033173.133:123): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/4831/status" pid=4831 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114
[11156462.747543] audit: type=1400 audit(1535033173.133:124): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=4831 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=0
[11156462.747602] audit: type=1400 audit(1535033173.133:125): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/4831/status" pid=4831 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114

Nekem az látszik, hogy a mysql írogat rendesen. Amíg a mysql ír, addig a rá várakozó apache (php) szálak növelik a loadot szépen. iowait-ed nincsen, az rendben van.
Az a tippem, hogy vagy valami szép nagy sort vagy temp tábla van a háttérben, megfelelő konfigokat kell nagyobbra venni.
Vagy valamiért sokat logol.

Amúgy egy szép table-level-lock is meg tud szívatni úgy, hogy csak időnként van, egyenként minden query tök jó, de ha ketten összejönnek akkor megáll az élet egy darabig.

--
Gábriel Ákos

Cache fut több szinten is, mert a mysql is kezel query_cache-t, illetve a site is cacheli a statikus listákat és azokat nem adatbázisból szolgálja ki, hanem egy 5 perces lejárattal bíró cache-ből.

Logolás valóban van, de nem olyan számottevő, mert csak a slow_query és az index nélkül futó lekérdezéseket logolja a rendszer. Napi szintem 1-2M a log. Nem érzem soknak. Apache is okosítva van, mert a statikus tartalmak (js, css, képek) kiszolgálását nem logolja. (vissza kapcsoltam ezt az elemzéskor, nehogy az legyen, hogy ebből van sok, de nem volt kiugró, teljesen reális volt a statikus elérés is)

Sort van sok az oldalon, ez tény, mert szinte mindent rendezve kell megjelenítenem, de ezek a sortok elég ritka esetekben térnek el a kronológiától, vagyis az inkrementális mező sorrendjétől. Tehát bár van order by minden query-n, de az ellenőrzésen kívül nem sok mindent csinál, mert eleve sorrendben adja a DB.

Temp tábla... érdekesebb kérdés. Sajnos ennyire nem ismerem a mysql belső működését és nem tudom, hogy mikor és milyen esetben készít temp táblát és azt sem tudom, hogy hogyan lehet ezt ellenőrizni. Utána olvasok. De persze a konfig se mindig így nézett ki, volt nagyobb is a memória méret, csak akkor meg átestem a ló túl oldalára, mert akkor meg az allokáció miatt volt terhelés, mert azt olvastam a beállított értékeket minden connection elején allokálja (persze nem minden beállítható konfig értékét).

Magam semmilyen tábla lock-ot nem használok, nem volt indokolt. Tranzakció sincs használva csak egy éjszakai scriptben.

( h3m | 2018. 08. 23., cs – 19:25 )

Először is a mysqltuner-t érdemes futtatni.
Aztán megnézni, hogy rendben vannak-e az indexek.
Rendelkezésre áll-e elegendő memória a mysql-nek?
Hol történnek a rendezések (sort), memóriában vagy lemezen?

--
Professional IT Services - Informatikai tanácsadás és outsourcing
www.professional-it-services.hu

A mysqltuner-nek az a beállítás nem tetszik, amit éppen futtatok. Egyszer sok, utána kevés. Szóval mindent középre állítottam.

Az indexeket folyamatosan ellenőrzöm. Van egy beállítási lehetőség, ahol a slow_query logba beteszi azokat is, amik nem használ indexet. Ezek alapján javítom amit tudok.

A gépbe van még 3GB memória, amit nem használ semmi. A mysql által allokálható memória méretét pedig folyamatosan változtattam, de nem hozott eredményt egyik beállítás sem.

Ezt hol tudom ellenőrizni? Hol látom az, hogy hol rendez a MySQL?

Ha tudod hogy milyen query futott le akkor explain-el meg tudod nezni hogy csinalja, a query-ket meg vagy logold le vagy pt-query-digest-el elemezd a mysql forgalmat, vagy jobb ha elotte lemented a mysql forgalmat (ip alapu kapcsolatnal egyszeru tcpdump-al, socket-nel mar macerasabb de socat-al atmasolhatod localhost:port-ra a socket forgalmat es arra tcpdump-al lemented, majd utana erre pt-query-digest-el elemzed milyen problemas query-k futottak le.

Amugy show global status like '%tmp%' -al meg tudod nezni mennyi temp tabla keletkezett, illetve mennyi diszken tarolt abbol, ha az erosen novekszik akkor nem fer bele memoriaba.
Ugyanugy show global status like 'sort_merge_passes' -al lathatod a sort-ok szamat amik sort_buffer_size-ba nem fertek bele, szinten ha nagyon novekszik akkor lehet erdemes novelni (ertelmes meretig globalisan is, felette session szinten).

( dorsy v | 2018. 08. 26., v – 09:41 )

ok, ok, de fogalmunk sincs hogy nez ki a DB, milyen tablak, milyen indexekkel, mennyi adattal, azon pedig milyen query-k futnak.
vaktaban lovoldozunk.

nemi hint: https://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

nem vagy db architect, leghatekonyabban ugy tudsz optimalizalni, ha nem futtatsz "bonyolult" es/vagy sok sort atnyalazo query-ket, mindig csak egy reszet kered el a szukseges infonak majd szoftver oldalon oldod meg az osszerendelest. Foleg ugy, hogy php eseten tudsz "vegtelen" feldolgozot inditani (akar tobb gepen), a DBA meg sokszor 1 szalon kell fusson.

Uhh, ez konkrétan hülyeség, hogy kérjünk le adatokat a dbms-től aztán php-ban meg bindzsizzünk vele. Lehet még jobb lenne leküldeni egy db dump-ot a webbrowser-nek aztán ott bindzsizni vele js-ben. :)
Vegyük észre: a dbms pont arra van kitalálva, hogy "sok sort atnyalazo query-ket" futtasson és a végén visszaadja pont azt, amire szükséged van.

Igen is meg nem is. Szo nem volt arrol, h az egesz db-t huzd be. Egyszeru, nem 100csillio tablabol joinolt lekerdezest kell irni. Tipikusan a mysql egy ilyet kitol neked a diszkre temp tablaba. Es maris kurvalassu lesz.
Ha mar js, nezd meg a nagyok mit csinalnak, raw data megy a browsernek, dolgozzon a user gepe a tuloldalon. Abbol sok van.
A dbms nagyon jo dolog, addig amig jol tudsz ra optimalizalni.
Ugyanakkor a scaling nagyon nem trivialis, szoftver oldalon meg annyi feldolgozot tudsz spawnolni amennyi kell.

Az egész dump nyilván csak poén volt, attól meg eléggé hányok amit a nagyok csinálnak, de ez most itt off.

És ugyan nem 100 csillió táblából, de néha 5-10 táblából, néha milliós vagy nagyobb rekordszámokkal is sikerül normális teljesítményt kihoznom az rdbms-ből, csak oda kell figyelni pár dologra: az adatbázist jól kell megtervezni, a query-t jól kell megírni (barátod az explain), és a dbms paramétereit is jól kell belőni.
Real world példa: egy 5-6 join-os, group by 1,2,3,4,5,6-tal végződő query, ami egyebek mellett egy kb. 2 milliós és egy kb. 10 milliós táblából dolgozik <5 sec alatt kiköpi a szükséges aggregált kb. 80-100 adatsort - és ez egy kifejezetten gyenge gépen van. Azt azért megnézném, hogy ezt akár php-ben, akár kliens oldalon (browser) js-ben ettől hatékonyabban hozod ki...

Itt nincsenek ekkora táblák. Van pár log tábla, ami felduzzadt, ezért 400MB az adatbázis, de egyébként 20-30ezer soros a tábla. Van olyan query, ami 4-5-6 joinnal dolgozik, de a futás ideje nem több ezeknek se, mint 0,2-0,4mp. Többször kérve ugyan azt, még ennyi sem.

Egyszerűen ez a gondom, hogy semmi nem indokolja ezt a terhelést és mégis ott van és látom a két szememmel.

Ma már oda jutottam, hogy frissítettem a PHP-t, az apache-ot, majd telepítettem a gépre egy nginx-et áttereltem oda a kéréseket, de semmi. Ugyan az volt a helyzet, pedig azt gondoltam, hogy a frissítés hoz javítást, de erre nem adott megoldást.

Már tényleg nem tudom, hogy merre indulhatnék.

Nezd meg a logokat!

Logold a szoftverbol inditott query-ket, elotte-utana timestamppel!

Az amit a "szemeddel latsz" micsoda?
Lassu valami valahol?
Mit jelent a "load"?
Kinek faj az ha dolgozik a CPU? (az a dolga)
Hany konkurrens php szal dolgoztatja a DB-det ilyenkor?

Nezz utana es hasznald a linuxos toolokat:
vmstat, dstat(rengeteg kapcsoloja van es tud csv-t!), sar, stb.!

Valamint: google:netdata! Hidd el, ertekelni fogod!

A CPU nagyon fáj például a juzernek, amikor dedikált szerverre irányítják minimum havi 30e+áfáért, az éves 5e+áfa-s tárhelyét kiváltandó, hiszen akkora erőforrás igénye van, hogy három AWS és négy Azure is kevés hozzá. Rossz esetben már eleve dedikált szerveren vagy, de a 3GHz-es dualxeonok is vért izzadnak, aztán lehet duplázni triplázni a 100e nettó havidíjat, illetve fizetni az extra vellanyt.

Az sem utolasó szempont, hogy érdemes lenne a skálázódásra is figyelni, mert azt bármilyen minimális látogatószám emelkedéstől eldobja magát az egész.

A logot már széttúrtam, de semmi olyat nem találtam benne, ami itt érdekes lenne. Elvileg nem történik semmi kiugró. Scriptekkel is elemeztem.

Azt látom a szememmel, hogy időről-időre úgy megugrik a load, hogy 10-15mp-re nő a weboldal válaszideje, ami alapvetően elfogadhatatlan.

A linux féle szerver load-ot értem a load alatt.

A CPU dolga valóban ez és nem is érdekelne... sőt, dolgozzon is, de a fentebb írt oldal lassulás már igen csak zavaró.

Hát ez az, hogy elég ritkán látni olyat, hogy egyszerre több ember is nézi az oldalt, így még csak konkurens szálakról sem lehet beszélni. Mint írtam, elég kicsit az oldal látogatottsága (sajnos).

Ok és okozat.

Ha 10-15mp-re nő egy oldal letöltése akkor én ott valami lock-ot (akár implicit) gyanítanék.
Ha egy query miatt 15mp-ig nincs (más) kiszolgálás akkor a load azért nő meg, mert a további bejövő kérések várni kényszerülnek.

Tehát nem a load miatt lassul le egy oldal kiszolgálása, hanem egy oldal lassú kiszolgálása miatt nő meg a load.

Ha reprodukálni akarod akkor csinálj teszteket, pl. jmeter-rel.

--
Gábriel Ákos

Ertem en es teljsen jo. Addig amig ezek a query-k nem jonnek 5 secen belul. Vagy mondjuk 10-esevel. (Nem mindig lehet 5 secet varni egy lekerdezesre. Tehat a "normalis teljesitmeny" igencsak relativ.)

En is megnezem, h ilyenkor mit csinalsz :)

Mert akkor bizony vagy nem engeded, h az egesz tablara induljanak ilyen query-k, vagy darabolhatod a tobbszazK-s tablaidat - felteve hogy gondoltal erre mikor tervezted a DB-t - (eljen a postgres10!) es indithatod a lekerdezest szimultan (nincs mar olyan, h 1 magos CPU) es szoftverben szepen osszerendezgetheted. Nem biztos (sot!), hogy kevesebb CPU cycle, de parhuzamosithato (az rdbms meg vagy tobb szalon csinalja vagy nem. aka: eljen az automatic query optimization...nem).

Sose agyj légyszives ilyen tanácsot, hogy select * from aztán majd lesz valami a PHP vagy más script oldalon, ez már 20 éve is elég ciki volt ugyanis. Azért van az SQL és az RDBMS, hogy megoldja a lekérdezést és azért vagy fejlesztő, hogy meg tudj írni, hogy egy épkézláb lekérdezést, amitől nem áll fejre a szerver. A DBA az Administrator amúgy, nem query optimalizátor és nem is SQL séma fejlesztő, bár valszin érdemes vele konzultálni.

Egy fenti tippel tökéletesen meg lehet fektetni bármilyen webszervert és utána jön a csodálkozás, hogy állaweboldal, szaraszerver és társai. Az ilyen "úgysem jön 5mp-enként a lekérdezés c. sztorikat is villám gyorsan lehet felejteni, mert jönnek a botok website-ot indexelni, hiszen a webmastah szíjjel szórta a magokat az univerzumban. A szerver bármilyen adminjához pedig hiába szaladsz, ha egyszerűen minden CPU-t, RAM-ot és diszk IO-t elfogyasztasz.

Továbbá az sem működik, hogy a tetszőleges CMS vagy egyedi PHP kódot felhányjuk a szerverre és majd lesz valami. Kell vele foglalkozni, több fronton is. Én elsőre a http://tools.pingdom.com -ot szoktam javasolni, hogy azon lehet a kliens oldali "élményt" nézegetni.

Ez a query egy napi/heti/havi aggregált statot készít főleg log jellegű táblákból, szóval 1x fut le naponta, hetente, havonta. Nyilván ez teljesen off a topic témájában, csak azért írtam hogy egy gyenge konfigon is simán le lehet kezelni ilyen rekordszámokat sql-ben értelmezhető válaszidővel, mert az SQL erre van kitalálva.
És szerintem még lehetne javítani is a futásidőn, de ez már az a kategória ami napi 1 (max 3) futásra elég jó, egyszerűen nem éri meg foglalkozni vele, főleg hogy akkor fut, amikor nem nagyon van írás a táblákba...