Mysql - sok tabla

Adatbázis: SQL, XML DB

Sziasztok,

Adott egy mysql szerver, verzioja 5.1.37. A szerveren van kb. 1300 adatbázis, az összes adatbázisban pedig 1.400.000 tábla.
A szerver hirtelen lelassult, így egy tuningot szeretnék csinálni rajta, de sajnos nem tudom futnak le a mysqltuner és a tuning-primer.sh tool-ok sem, egyszerűen kifagynak. A szerveren vannak MyIsam es InnoDB táblák is. A szerver konfigurációja 4CPU, 4GB RAM. Van valakinek ötlete, hogy mit csináljak? Köszönöm előre is.

Itt a config amit használok:
[mysqld]
#GENERAL
user = mysql
default-storage-engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock

#DATA STORAGE
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

#MyISAM
key_buffer = 500M
myisam-recover = FORCE,BACKUP

#SAFETY
max_allowed_packet = 16M
max-connect-errors = 1000000

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 320M
max-heap-table-size = 320M
query-cache-type = 0
query-cache-size = 0
max-connections = 150
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
#innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /var/log/mysql_slow
long-query-time =2

  • 8380 megtekintés

( tompos v | 2014. 10. 11., szo – 08:06 )

Csak nehany otlet, annyira nem ertek hozza:)

AMugy nem irtad meg, mit jelent a lelassult (pl. iras v. olvasas..)?

innodb_open_files
table_open_cache

sort_buffer_size
read_buffer_size

A 1.400.0000 tabla valojaban eliras es egymillio-negyszazezer v. rosszul tagoltal es tizennegymillio?

Nem irtad azt sem, mekkora az adatmennyiseg, de a te esetedben lehet, h az innodb_file_per_table megszivat.

"Nem iras/olvas probléma, mert alig van lekérdezés"

Fentebb az egyik kolléga jelezte, hogy a motornak annyi fizikai memóriát füllentettél pertizánprovisioning alapon, hogy ha annak a tizedét elhiszi, már akkor is swapra szalad. Ez pedig az adatbázis teljesítményének a halála. Nem csak a ingyenes MySQL-é: a piac legdrágább, legprofibb szoftjainak kisokosai is vastag pirossal keretezik be, hogy ne nyújtózkodj túl a takarón.

( uid_6201 v | 2014. 10. 11., szo – 09:32 )

Kedvelem az ilyen komoly témákat a fórumon. Elgondolkoztatóak és segítenek megelőzni nekem is a hasonló szívásokat.

Egy hasznos tool: http://mtop.sourceforge.net/ - megmutatja, melyik folyamat terheli leginkább. Ezzel térképeztem már fel hibásan indexelt sokmillió rekordos db-t.

Továbbá ennyi tábla (= fájl) esetén olyan problémád is lehet, hogy a kernel MAX OPEN FILEDESCRIPTOR paraméterét elérve a lezárásokra vár az SQL szerver fájlkezelő része.

Lásd: ulimit -n

Erre egy megoldás: http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-…

Kíváncsi vagyok hogy végül mivel kerülöd el.

Ha nincs a swap felé erős forgalom és van még cache és buffer (lásd: top parancs), akor miért ne.
És egy jó kérdés ide is: mivel lehet monitorozni, hogy mennyire intenzív a swap felé a forgalom (tehát nem azt, mennyi adat van a swap-en, hanem hogy mennyire intenzív a csereforgalom a mem<-->swap között?)

( gedez | 2014. 10. 11., szo – 10:11 )

Ha csak 4GB memória van benne, akkor gondolom más szolgáltatás nem fut a szerveren, így a query-k hálózatról jönnek, feltételezem adott alhálózaton belülről.
Ami hasznos információ szokott lenni az ilyen "lassú" dolgok megválaszolásához, hogyha az alábbiakra megkeresed a válaszokat:
-lassuláskor a CPU terhelés milyen?
-lassuláskor IO wait jelentkezik?
-ha jelentkezik IO wait, akkor iotop mit mutat?
-milyen tároló van alatta? HDD/SSD?
-slow query log?
-a tmp-ben látsz mysql temp fájlokat létrejönni?
-mysql processlist milyen query futásokat mutat? előfordul-e olyan, hogy egymásra várnak a process-ek, esetleg deadlock alakul ki

Köszönöm a válaszokat.

- Lassuláskor a CPU wa elég nagy. Cpu(s): 1.3%us, 1.6%sy, 0.1%ni, 75.1%id, 21.5%wa, 0.2%hi, 0.3%si, 0.0%st
- Az iotop-nal, az IO oszlop nem mutat semmit, viszont 10-20 M/s ír olvas a mysql a lemezre.
- A taroló sima HDD (SATA).
slow query nem igazan van, az az érdekes hogy ha nincs egyáltalán trafik (csak én próbálgatom) akkor is web applikáció nagyon lassan töltődik be.
- A tmp-ben van egy #sql_ee0_0.MYD állomány amely újraindítás után jön létre, egy idő után nagyon nagy lesz (50GB), majd egy idő után eltűnik, ilyenkor a processlist-ben fut egy "checking permissions" query ami nagyon sokat tart.
- a processlist- sokszor üres, akkor jelenik meg benne valami amikor én tesztelem a web applikációt.
Szóval csináltam a live szerverről egy clone-t és azt próbálgatom.

Érdemes a show full processlistet nyomogatni a mysql konzolban, illetve megnézni, hogy mennyi a slow_query_time, mert ha 5-10mp, abba egyáltalán nem biztos, hogy bekerül valami. A /tmp -es 50GB-os file egy félbemaradt temp táblás lekérdezésre utalhat (azaz valami remekbeszabott join) és egy ilyen lekérdezést tetszőleges szervert lefog.

Hogyan tudom ezt megnövelni?
A mostani érték:

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

( Batman | 2014. 10. 12., v – 00:15 )

Szia,

Ami rögtön szembetűnő, hogy a jelenlegi beállításokkal a MySQL több memórát tudna felhasználni mint ami rendelkezésére áll, ekkor pedig elkezd swappolni ami persze lassu majd a végén le is állhat.
Előszőr is az innodb-buffer-pool-size elhasznál 2G, aztán ott van a key_buffer is , és persze a MySQL minden egyes connectionkor alapból allokál memoriát a kapcsolatnak ez pedig a következőkből épül fel: read_buffer_size, sort_buffer_size, read_rnd_buffer_size and tmp_table_size
Neked a tmp_table_size 350MB , 150 kapcsolatod van engedélyezve tehát 150 * 350MB az 45G, szóval jócskán túllépted a fizikai memoriádat.

A query cache ki van kapcsolva jelenleg , pedig az is segíthet bár az ugyan csak memoriát használ.

Nem lehetetlen, hogy a szervert be lehet állítani hogy optimálisan működjön a jelenlegi adatbázisokkal és lekérdezésekkel.
De ehez ismerni kellene a lekérdezéseket mert ahoz kell szabni a beállításokat.

De amit javasolni tudok és egyiksem fog tetszeni, hogy ha lehetséges válts 5.6-os MySQL-re,gyors sok újítás van benne ami neked is hasznos lehet, válts Innodb-re minden táblán, jobban tolerálja a hibákat, gyors, és egyszerűbb az adatbázis szervert is behangolni egy féle engine kiszolgálására. Utoljára de talan a legfontosabb, rakj a szerverbe annyi memoriát amennyit csak bírsz. A MySQL imádja a memoryát :)

ui: attól hogy MySQL-ben be van állítva az open-files-limit még magában a rendszerben is fel kell emelni.
"ulimit -n" kiiírja a jelenlegi beállításokat.
"losf | wc -l" megmondja jelenleg hány fájl nyitott.

A következőket tapasztaltam a hétvége folyamán:

- nincs slow query, pedig a long-query-time 1 sec-re van állítva, de valamiért még mindig lassú.
- a "use database_name" is nagyon lassú (kb. 5-10 sec), az innodb típusú adatbázisoknál, viszont ezek nem íródnak be a long query logba. Ey után a query-k már gyorsan futnak.
- én azt gondolom, hogy valahol megközelítem vagy átlépem a mysql 5.1 határait ezért megy lassan. Mivel nincsenek slow query-k fogalmam nincs hogy mi lehet a hiba.

Ezért kérdeztem én is, hogy mekkora a ibdata1.

De nem mérvadó mert "innodb-file-per-table = 1" van beállítva így nem egy közös fájlban tárolja az adatokat. Persze így is lehet oka a lassúságnak.
Viszont ez nem biztos hogy jó választás ebből a szempontból "default-storage-engine = InnoDB"

Én azt javasolnám, hogy amit lehet konvertáljon MyIsam-ra majd ürítse az ibdata fájlokat.

Vagy több erőforrásra (memória, diszk alrendszer) van szükség.

Úgy néz ki, hogy valamit találtam:
A probléma nem az InnoDB adatbázisoknál van, hanem azoknál amelyek MyISAM engine-t használnak.
Azt vettem észre, hogy a "checking permission" típusú műveletek sokat tartanak.
Pld. ha leakarok törülni egy myisam adatbázist eltart vagy 4-8 percet.

( igor | 2014. 10. 13., h – 16:05 )

Ha vannak MyISAM tábláid is, akkor a query-cache-size miért 0?

thread-cache-size = cpu szám x 2 (ez jelenleg nálad 50!)
thread_concurrency = cpu szám x 2
query_cache_limit = 1048756
query_cache_size = 512M
query_cache_type = 1

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 8M
innodb_lock_wait_timeout = 50
innodb_file_per_table
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = cpu szám x 2
innodb_flush_method = O_DIRECT

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 8M
join_buffer_size = 8M

Valamint 1300 adatbázishoz szerintem kevés a max-connections=150

A szerver amelyet próbálok megjavítani, nem egy produkciós szerver, hanem egy másolat a produkciós szerverről.
Szóval nincs terhelés alatt, csak én dolgozok rajta.
Tehát az 1300 adatbázisból csak 4 van használva.
Az information_schema-bol a lekérdezések annyira lassúak, hogy gyakorlatilag nem futnak le.
Bármilyen paramétert változtatok, nem tapasztalok változást, ugyanolyan lassan megy.
Slow query gyakorlatilag nincs (1 sec-ra van beállitva).
Mit kellene változtassak, hogy a metaadatokat gyorsabban olvassa?
Most ezek a beállítások vannak:
[mysqld]

table_open_cache=20000
table_definition_cache=20000

key_buffer=4000M

max_heap_table_size=2000M
tmp_table_size=2000M

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 8M
join_buffer_size = 8M

innodb_buffer_pool_size=400M
innodb_lock_wait_timeout=900

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /var/log/mysql_slow
long-query-time = 1

https://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_i…

The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 1MB for the built-in InnoDB, 8MB for InnoDB Plugin.

https://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_i…

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Beraktam a innodb_stats_on_metadata=OFF es a innodb_additional_mem_pool_size=20M valtozokat is de semmi javulás.
Nem lehet valami operációs rendszer vagy filerendszer probléma?
A szerver amin futtatom a mysql-t eleg regi: Ubuntu 9.10
A fájlrendszer pedig ext3.
Én úgy gondolom, ha mysql probléma lenne akkor kellene legyen slow query.
Tudom, hogy sok az adatbázis, de abszolút nincs terhelés alatt a szerver (1 felhasználó kérdezgeti az adatbázisokat)

Rossz felé indultál, ha ez nem egy production szerver akkor minek a sok cache? Nem lesznek queryk vagy ha igen akkor nem érdekes hogy 2sec vagy 10sec alatt fut le.
Úgy látszik azt se olvastad amit írtam mart megemelted a tmp_table_size méretet, így már két connectionnél elfogy az összes memoriád...

Szerintem inkádbb indulj el a másik irányba és vedd lejebb az értékeket , hogy ne fuss ki a memoryából...

Már hogy fogyna el, a szerverbe tettem meg 4GB Ram-ot, most 8GB van. A http://www.mysqlcalculator.com/ szerkesztettem egy új config-ot. Az alábbi konfiguráció szerint a Mysql 6482.6 MB memóriát használ.

[mysqld]

table_open_cache=20000
table_definition_cache=20000

key_buffer=2000M

max_heap_table_size=1000M
tmp_table_size=1000M

sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
join_buffer_size = 4M

innodb_buffer_pool_size=1000M
#innodb_lock_wait_timeout=900
innodb_stats_on_metadata=OFF
innodb_additional_mem_pool_size=20M

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /var/log/mysql_slow
long-query-time = 1

Miért tart 54,4 secundumot egy "use database" parancs? Csak ezt az egy query-t futtatom. Ilyenkor használja az information_schema-t is? Mert abból aztán igazán lassan olvas.

# Time: 141014 10:42:11
# User@Host: root[root] @ localhost []
# Query_time: 54.425763 Lock_time: 0.000076 Rows_sent: 3604 Rows_examined: 3604
use cdf7bbd17552_test;
SET timestamp=1413276131;
show databases;

Kipróbáltam a profiling-et.

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> use cdf7bbd17552_test;
Database changed

mysql> SHOW PROFILES;
+----------+------------+-------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------+
| 1 | 0.00010800 | SELECT DATABASE() |
| 2 | 0.02734200 | show databases |
| 3 | 0.00757300 | show tables |
+----------+------------+-------------------+
3 rows in set (0.00 sec)

Ilyen rövid időket ír ki, de a valóságban a eltart jó pár másodpercig.

Vigyazz, mert a kliensben is eltoltheti az idot - use database eseten a metaadatok osszegyujtesevel. mysql -A opcio segitsegevel ezt a kliens adatgyujtest kikapcsolhatod.

generalj egy scriptet, ami semmi mast nem csinal, csak adatbazisok kozott valt, es nezd meg a klienst/servert IS ltrace/strace/gdb/perf eszkozokkel

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)

Az error logban pedig semi hiba:

141014 09:17:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141014 9:17:05 [Note] Plugin 'FEDERATED' is disabled.
141014 9:17:06 InnoDB: Started; log sequence number 0 1215876151
141014 9:17:06 [Note] Event Scheduler: Loaded 0 events
141014 9:17:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.37-1ubuntu5.5-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

thread-cache-size = cpu szám x 2 (ez jelenleg nálad 50!)

Ez az aktív kapcsolati szálakat cache-eli, és a Threads_created/Connections értékének függvénye. Ha ez több mint 0.01, akkor kell növelni, de a Max_used_connections-nál mindig legyen nagyobb. A thread_concurrency az ami a CPU*2.