mysql tuning

Linux-haladó

sziasztok!

Segítséget szeretnék kérni tőletek abban, hogyan tudnám még gyorsítani a mysql adatbázist.
Szerver: dell poweredge sc1430 (cpu: 1db intel xeon e5310 quad core, 6GB ram, 2*250 hdd)
os: ubuntu linux 8.04 LTS
Innodb és myisam táblák is vannak. De főként innodb.
jelenlegi my.cnf:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

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

[mysqld]

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
character-set-server = latin2
collation-server = latin2_hungarian_ci
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking

key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
table_cache = 512

query_cache_limit = 8M
query_cache_size = 256M

log_slow_queries = /var/log/mysql/mysql-slow.log

expire_logs_days = 10
max_binlog_size = 100M

skip-bdb

innodb_buffer_pool_size = 1G

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

  • 2384 megtekintés

( lavian v | 2010. 05. 18., k – 14:38 )

Ezekre nézz rá:
- mysqltuner
- tuning-primer

Érdemes a javaslataikon elgondolkodni, de minden változtatás után minimum pár órát várj (terheléstől függően), akár egy napot is, mielőtt újra tesztelteted.
Másrészt leírhatnád, hogy milyen tüneteid vannak jelenleg és mit szeretnél javítani rajta, valamint pár szóban a program környezetet is.

mysql: 5.0.51a
php: PHP Version 5.2.4-2ubuntu5.10

Van egy webáruház (de emellett még több kisebb-nagyobb weboldal is fut a szerveren, és ez a levelező szerver is). A webáruházban van egy termék kereső query, ami viszonylag sok ideig fut, ezen szeretnék gyorsítani, olyan 2-3 másodperc javulás már elfogadható lenne. Most 1-1 lap betöltése meg van max. 2 másodperc alatt,de ha termék keresővel keres valaki, akkor 10-12 másodperc, és ahogy bővül a kínálat úgy nő ez az idő is.

innodb_buffer_pool_size ennek adj mondjuk 3G méretet elsőre (elférjen benne a InnoDB rész), bár tesztprogramok adnak rá javaslatot
key_buffer méretét a tesztprogramok adatai alapján állítsd be akkorára, hogy elférjen benne
Indexelj minden olyan mezőt, ami alapján szűrsz, bár olyan esetben ellenjavallt, ha van olyan közte, ami gyakran változik (azt hagyd ki).

(Használj valamilyen phpcache mechanizmust és ha beilleszthető a kódba, sokat számíthat egy memcached szerver is, de ez úgy általában segít, nem a mysql oldalon.)

mysqltuner-t lefutattam. eredménye:

-------- Performance Metrics -------------------------------------------------
(ebből csak a ! jeleseket másolom be)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.8G (46% of installed RAM)
[!!] Joins performed without indexes: 4408

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)

Az elején nem tudsz változtatni, 32bit-es rendszeren nem szereti a nagy memóriafoglalást.

mysqloptimize -A -p -uroot
Egy kevésbé terhelt időszakban. Esetleg konkretizálhatod az adatbázist is, vagy a táblákat, ha gondolod. Ez mindent optimalizál.

join_buffer_size-t vedd nagyobbra! Úgy emlékszem, hogy kapcsolatonként értelmeződik, így túl is futhatsz a memóriafoglalással, ha minden összejön sok kapcsolat esetén és adott érték fölött, ill. ahogy írja: join esetében használj indexelt mezőket.

( petya | 2010. 05. 18., k – 14:46 )

Mekkora az adatbazis? 6 gb ram melle ha ez csak mysql szerver, akkor tobb is lehetne az innodb buffer pool merete. A lekerdezeseken tudsz optimalizalni? Mekkora az adatbazis innodb-s, illetve myisam-es resze? En ugy mennek neki, hogy a megnovelt buffer poolba belefer-e mar az adatbazis. Ha igen, a feladatot valoszinu megoldottad. Utana pedig megneznem, hogy melyik queryk tartanak a legtovabb, es mit lehet veluk kezdeni. Mi a tunet egyebkent?

Latom a query cache-ed nem 0. Gyakorlatban sokat nem szokott segiteni, es par misztikus belassulas okozoja.

Akkor lehetne azon meg mit optimalizalni:).

A ha a like elejen van a %, ez a query bamit csinalhatsz, mindig full table scan lesz, a join meg ront a helyzeten. A megoldasok:
- ha myisamed van, akkor hasznalod a myisam fulltext indexeit
- sphinxet hasznalsz (ezzel jarsz a legjobban)
- ha innodb ez a tabla, akkor idonkent csinalsz belole myisamet, amire csinalsz fulltext indexeket, de ettol jobb a sphinx

asszem szűrőn lehetek nálad, de leírom mégegyszer:

* fulltext, majd ha ez nem megy, akkor:
* saját fulltext kereső

A 2. nyilván sok munkával jár megcsinálni és karbantartani is, viszont max 1 heti melóból egy saját igényekre szabott keresőt tudsz csinálni.

Illetve van olyan nekifutas is, hogy létrehoznak egy myisam táblát/innodb tábla, az innodb táblán insert/update trigger a kereshető mezőket+PK beszúrja/frissíti a myisam táblába(n) is, majd FTS a myisam táblán, ami PK alapján hivatkozik az innodb-alapú táblára. így myisam-on tudsz match()-ni, majd egy joinnal megszerezni az adat. De mintha már ezt írta volna itt valaki.

--
"SzAM-7 -es, tudjátok amivel a Mirage-okat szokták lelőni" - Robi.

( zwei | 2010. 05. 18., k – 15:32 )

HA sok tmp tábla készül diszkre, és a /tmp nem tmpfs, akkor néha elég sokat tud dobni rajta egy:

tmpdir = /dev/shm

szerk: ezt akkor is érdemes meglépni, ha magas az IO wait a rendszerben.

( 3xtati | 2010. 05. 18., k – 16:40 )

subscribe
____________________
Ha igen akkor miért nem...
Linux 2.6.30-gentoo-r4 i686 Intel(R) Core(TM)2 Duo CPU E6550 @ 2.33GHz GenuineIntel GNU/Linux

( xclusiv v | 2010. 05. 25., k – 12:03 )

Szia,

Én úgy gondolom, hogy a HDD karcsú alatta, célszerű lenne beletenni még néhány (pl. 6) HDD-t és RAID 0+1-be tenni.

Nézd át a slow query log-ot, biztos találsz benne érdekességet.

InnoDB-t lehet raw partícióra tenni, ezzel az FS overhead-et csökkented.

Továbbá mindenféle cache-t próbál meg ésszerűen növelni, nyilván függően a többi szerver terhelésétől.