ugrás a tartalomhoz

5.5.65-MariaDB kérések várakoznak, nem teljesülnek

sandrosdj · Okt. 26. (H), 14.55
Sziasztok!

Adott a 5.5.65-MariaDB adatbázis szerver (CloudLinux repo-ból, CentOS7).
Több féle szerveren is fut, különböző beállításokkal, de a közös az, hogy gyakran leáll az egész és csak "killall -9 mysqld" használata után lehet újra elindítani, mert a systemd le sem tudja állítani.

Ilyenkor a process list tele van "Opening tables" és "Waiting for query cache lock" státusszal rendelkező sorral/kéréssel.

Egyszerűen nem találok rá megoldást sehol. Minap találtam egy lazy drop problémát, de nem vagyok bele előrébb, mert 5.5.30-tól az már javítva van.

Mit próbáljak meg, hogy megoldódjon ez a probléma?

Full alap CentOS konfigja van a szervernek, akkor is előjön ez az issue.

Egyébként ez a "custom" konfig, amivel ki van egészítve és így is előjön (pedig bíztam benne hogy az innodb_buffer_pool megoldja):

[mysqld]
join_buffer_size = 8M
table_open_cache = 1000
open_files_limit = 65536
max_connections = 800
wait_timeout = 600
interactive_timeout = 600
max-allowed-packet = 1G
max-connect-errors = 10000000
symbolic-links = 0
tmp_table_size = 8M
max_heap_table_size = 8M
skip-name-resolve
key_buffer_size = 8M
innodb_buffer_pool_instances = 2
query_cache_size=256M
query_cache_type=2
query_cache_limit=16M
innodb_buffer_pool_size=6G
 
1

deadlock?

mind1 valami név · Okt. 26. (H), 15.46
Nekem van egy olyan érzésem, hogy valami kellemes kis deadlock szituációba futsz bele rendszeresen.
Ha igazam van, akkor keress rá arra a Waiting... kezdetű üzenetre, mellétéve a deadlock szót!
Valahol a stackoverflow-n láttam erről valamit, ott azt hozták ki, hogy kisebb cache kellene.
Nem ismerem eléggé a mariadb-t, nem tudom, mekkora cache-t használ így ott azt hiszem, 1G volt a problémás, azt javasolták levinni párszáz MB-ra.
2

Köszönöm a tippet

sandrosdj · Okt. 26. (H), 19.47
Köszönöm a tippet, találtam is ezzel kapcsolatban hasonlót, ahol a Query cache méretét gondolják nagynak. Átállítottam pár szerveren, aztán kiderül megszűnik-e a gond (vagy legalább kevesebb lesz).
3

Kicsit hasonlít erre, de úgy

inf3rno · Okt. 27. (K), 02.01
Kicsit hasonlít erre, de úgy tűnik, hogy elég nagy a buffer pool. ref Hacsak nem az van, hogy nem tudja értelmezni, hogy G-t írsz be, nem rendes számot. Meg tudod nézni mennyire memóriát foglal le?
4

Lehet G-t írni

Pepita · Okt. 27. (K), 13.28
Itt azt írják:
8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, which is 2G.
7

Módosítottam a beállításokat

sandrosdj · Okt. 27. (K), 20.31
Módosítottam a beállításokat így:
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 8G

A query_cache beállításokat hagytam 256M ill. 1M-on.

A 21GB virt image, 6.8GB a memória így. Egyelőre nem volt gond vele, de csak pár perce él a beállítás. :D
5

max_connections

Pepita · Okt. 27. (K), 13.56
Egyidejűleg mennyi a tényleges connection-ök száma?
(Előrebocsátom, hogy MariaDB -t nemigen használok, csak MySql -t)
A megengedett 800 nekem igen soknak tűnik.
Itt is egy kis teljesítménygond:
To avoid potential performance issues, the number of chunks (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) should not exceed 1000.

Ebből az innodb_buffer_pool_chunk_size -t nem is látom a Te configodban, ha a default 128M, akkor OK, de mivel csak 2 instance van nálad, akkor lehetne nagyobb is.

Nekem gyanús / tippem:
- HA nagyon sok a connection, akkor kevés a bufferek száma.
- HA nagy (rekord- és oszlopszámú) táblákon bonyolult query-k futnak, akkor ki kéne próbálni a chunk_size emelését.
- Fontos lehet még, hogy egy - egy connection minél hamarabb "elengedje" a szervert, mert addig foglalja a buffert is, míg a connection-t.

Egyszer futottam lassulásos problémába, amikor php importált egész adatbázist (kvázi sql fájlokból), és a frissen létrehozott táblákat mind bent tartotta bufferben, "hátha kell még insert bele", elfogyott / megtelt a buffer -> innentől iszonyú lassan ment tovább.
Megoldást jelentett a táblánkénti connection close / open, de javult az innodb_buffer_pool_size időleges duplázásától is (ezt viszont nem értem, hogy önmagában miért segített).

Szóval szerintem a connection - buffer egyensúly lehet a gond, a többi (későbbi) kapcsolatok várnak az előzőekre, hogy felszabaduljon "egy kis RAM".
6

Én is inkább ilyesmire

inf3rno · Okt. 27. (K), 19.28
Én is inkább ilyesmire gondolok, nem a fent említett deadlock-ra. Szerintem az ellen kell, hogy legyen valami védelme a mai adatbázisoknak.
8

Közel nincs ennyi, azért

sandrosdj · Okt. 27. (K), 20.45
Közel nincs ennyi, azért állítottam 800-ra, mert a sok bent álló queryk miatt betelt gyakran a default limit. (Bár ennek nem sok értelme volt, mert amint előjött ez a probléma nem is engedett több kapcsolatot (conn fail, socket error, hasonló hibákkal).

A táblák mérete, indexek, lekérdezések nagyon vegyesek. Sok rendszer használ 1-1 mariadb szervert. (Kb. fele Wordpress)
A chunksize-t próbáltam módosítani, de nem ismeri a változót. Annyiban módosult a konfig, hogy 16 instancet állítottam be és 8GB a limit. Az elérhető RAM mennyiség 32GB, van ahol 64GB (ebből kb. a 70%-a rámehet mysql optimalizálásra).

ERROR 1193 (HY000): Unknown system variable 'innodb_buffer_pool_chunk_size'
9

MariaDB vs MySql

Pepita · Okt. 28. (Sze), 09.28
Érdekes, hogy "nem ismeri" ( = nem állítható) a chunk_size - ot, de szerintem az csak azt mutatja meg, hogy egy - egy instance mekkora "darabokban" kérhet további buffer területet.
Ha jelent javulást az instance 2 -> 16, akkor itt volt a gond. :)

Közel nincs ennyi, azért állítottam 800-ra, mert a sok bent álló queryk miatt betelt gyakran a default limit.
Hát nekem nagyon szúrta a szemem, hogy akár 800 "darab" kliens szeretne megkaparintani az összesen 2 bufferből egyet, ott nagy lehet a sorban állás...

ebből kb. a 70%-a rámehet mysql optimalizálásra
Itt mire gondolsz? Minek a 70%-a és miért éppen 70? Ha arra, hogy 32 GB * 70% = 22.4 GB-ot "megehet a vasról" a MariaDB szerver, akkor pool_size lehet 16 GB is, ennek van szüksége a legtöbb memóriára. (Többi configot hozzá kell nézni, hogy összesen ne menjen a kívánt "fogyasztás" fölé.)
10

Bár az eddigiek alapján nem

inf3rno · Okt. 28. (Sze), 17.12
Bár az eddigiek alapján nem túl nagy az esélye, de az is előfordulhat, hogy valaki hekkeli, és azért terhelődik túl.
11

Az elmúlt 2 napban nem volt

sandrosdj · Okt. 30. (P), 11.00
Az elmúlt 2 napban nem volt gond az instance és pool size átállítása után, remélem tényleg csak ennyi volt a probléma.

A "vas"ban lévő memória 70%-ára gondoltam, jól értetted. 16GB-ra állítottam 32GB-os szervereken, mert fut dockerben alternatív (újabb) MySQL szerver is, annak maradt 2-4GB a beállítása, de azokkal nem is volt gond.

Köszönöm a tippeket, segítséget!
12

+1

Pepita · Nov. 2. (H), 14.19
Akkor király, köszi a visszajelzést.