Cum pot face ca o bază de date MySQL să ruleze complet în memorie? (Programare, Mysql, Bază De Date)

John Hoffman a intrebat.

Am observat că serverul meu de baze de date suportă motorul de baze de date Memory. Vreau să fac o bază de date pe care am făcut-o deja să ruleze InnoDB complet în memorie pentru performanță.

Cum pot să fac acest lucru? Am explorat PHPMyAdmin și nu găsesc o funcționalitate „change engine”.

Comentarii

  • Ar trebui să înțelegeți mai întâi ce faceți acolo. Folosirea motorului de memorie are anumite implicații. –  > Por usr.
  • rularea în memorie înseamnă că datele sunt șterse de îndată ce reporniți daimonul MySQL (sau se blochează), ar fi mai bine să vă jucați cu query_cache pentru MySQL sau memcached pentru salvarea rezultatelor –  > Por Mikey.
  • Nu sunt sigur că asta este ceea ce doriți. Care este mai exact problema ta de performanță? –  > Por Mario.
  • Baza mea de date este foarte lentă în acest moment. Interogările mele au o mulțime de instrucțiuni JOIN, iar unele dintre seturile de date interogate sunt uriașe. M-am gândit că dacă aș pune toate aceste date în memoria RAM, lucrurile ar fi mai rapide, nu? –  > Por John Hoffman.
  • Foarte puțin probabil. Adăugați/ajustați indexurile. Consolidați-vă interogările pentru eficiență. Lucrurile de acest gen sunt, în general, mai eficiente și ar trebui să fie abordarea dvs. inițială. –  > Por PinnyM.
7 răspunsuri
PinnyM

Presupunând că înțelegeți consecințele utilizării motorului MEMORY, așa cum se menționează în comentarii, și aici, , precum și alte câteva pe care le vei găsi căutând despre (fără siguranța tranzacțiilor, probleme de blocare, etc) – poți proceda în felul următor:

Tabelele MEMORY sunt stocate diferit de InnoDB, așa că va trebui să folosiți o strategie de export/import. Mai întâi, descărcați fiecare tabel separat într-un fișier folosind SELECT * FROM tablename INTO OUTFILE 'table_filename'. Creați baza de date MEMORY și recreați tabelele pe care le veți folosi cu această sintaxă: CREATE TABLE tablename (...) ENGINE = MEMORY;. Apoi puteți importa datele folosind LOAD DATA INFILE 'table_filename' INTO TABLE tablename pentru fiecare tabel.

Comentarii

    49

  • Nicăieri nu se spune cum se creează o bază de date sau un tabel în memorie. –  > Por still_dreaming_1.
  • În ceea ce privește observația făcută de @INTPnerd, atunci când recreați tabelele, utilizați sintaxa: CREATE TABLE someTable (...) ENGINE = MEMORY; –  > Por PinnyM.
  • Există vreun motiv pentru care nu sugerați mysqldump? Tocmai l-am încercat și nu am văzut nicio problemă (totuși, doar un test foarte rapid). –  > Por Bet Lamed.
  • @BetLamed restabilirea folosind ieșirea din mysqldump va funcționa, dar mult mai lent decât un import în masă, deoarece fiecare INSERT va trebui să fie executat separat (cu cheltuielile generale suportate de o tranzacție INSERT). Importul în bloc elimină acest aspect și este de așteptat să fie mult mai rapid pentru cantități mari de date. –  > Por PinnyM.
Jotschi

De asemenea, este posibil să plasați directorul de date MySQL într-un director tmpfs în, accelerând astfel apelurile de scriere și citire a bazei de date. S-ar putea să nu fie cel mai eficient mod de a face acest lucru, dar uneori nu poți schimba pur și simplu motorul de stocare.

Iată intrarea mea în fstab pentru directorul de date MySQL

none            /opt/mysql/server-5.6/data  tmpfs   defaults,size=1000M,uid=999,gid=1000,mode=0700          0       0

De asemenea, poate doriți să aruncați o privire asupra fișierului innodb_flush_log_at_trx_commit=2 . Poate că acest lucru vă va accelera suficient de mult MySQL.

innodb_flush_log_at_trx_commit modifică comportamentul de curățare a discului mysql. Când este setat la 2, va curăța bufferul doar la fiecare secundă. În mod implicit, fiecare inserție va provoca o curățare și, prin urmare, va cauza o încărcare IO mai mare.

Comentarii

  • De asemenea, poate doriți să aruncați o privire la libeatmydata. Aceasta previne apelurile fsync și cele mai vechi de sincronizare și, astfel, accelerează aplicația. flamingspork.com/projects/libeatmydata (apt-get install eatmydata ; eatmydata /etc/init.d/mysql restart) – –  > Por Jotschi.
  • Mă gândesc să am tot mysql-ul în memorie și o altă instanță mysql pentru a replica și persista datele. Desigur, presupunând că pot face față riscului de pierdere a datelor din cauza întârzierii replicării. –  > Por Fabio Montefuscolo.
  • @Jotschi Linkul respectiv are 404 pentru mine –  > Por Dan.
Joseph Lust

Memory Engine nu este soluția pe care o căutați. Pierzi tot ceea ce ai mers la o bază de date în primul rând (adică ACID).

Iată câteva alternative mai bune:

  1. Nu folosiți îmbinări – foarte puține aplicații mari fac acest lucru (de exemplu, Google, Flickr, NetFlix), deoarece este nașpa pentru seturi mari de îmbinări.

Un LEFT [OUTER] JOIN poate fi mai rapid decât o subîntrebare echivalentă, deoarece serverul ar putea fi capabil să o optimizeze mai bine – un fapt care nu este specific doar MySQL Server.

Manualul MySQL

  1. Asigurați-vă că coloanele pe care le interogați au indici. Folosiți EXPLAIN pentru a confirma că aceștia sunt utilizați.
  2. Folosiți și măriți spațiul Query_Cache și spațiul de memorie pentru indexurile dvs. pentru a le avea în memorie și a stoca căutările frecvente.
  3. Denormalizați-vă schema, în special pentru îmbinările simple (de exemplu, obțineți fooId din barMap).

Ultimul punct este esențial. Obișnuiam să iubesc îmbinările, dar apoi a trebuit să execut îmbinări pe câteva tabele cu peste 100 de milioane de rânduri. Nu e bine. Mai bine inserați datele pe care le alăturați în tabelul țintă (dacă nu sunt prea multe) și interogați pe coloane indexate și veți obține interogarea în câteva ms.

Sper că acestea vă ajută.

Comentarii

  • Acest ultim punct poate fi foarte bun pentru performanță, dar poate fi un factor mortal pentru consistența și întreținerea datelor, dacă nu este utilizat în mod corespunzător. Actualizările de declanșare sau strategiile similare de replicare a memoriei cache pot fi de ajutor în acest caz, dar modul de stocare în memoria cache a datelor denormalizate nu trebuie să fie luat ca atare… –  > Por PinnyM.
  • @PinnyM bună observație. Aici trebuie să se decidă ce este cel mai important. RDBMS sunt excelente pentru atomicitate și consistență, dar nu și pentru scalare și performanță, mai ales în cazul îmbinărilor. 6NF (Star Schema) este foarte normală, dar nu se bucură de conexiuni. Puteți păstra în continuare datele din unele tabele în mod normalizat și puteți utiliza declanșatori și sarcini programate pentru a actualiza tabelele denormalizate (doar pentru citire) din tabelele normalizate (citire/scriere). –  > Por Joseph Lust.
  • 1 și 4 pentru sunt cele mai proaste sfaturi din toate timpurile. –  > Por Darwin.
  • @Darwin mulțumesc pentru downvotes. Alege consistența sau scara. Cu miliarde de rânduri, consistența adesea nu merită costurile suplimentare, în funcție de cazul de utilizare (de exemplu, cum ar fi toate magazinele Col pe care le folosesc la locul de muncă). –  > Por Joseph Lust.
  • @JosephLust aveți dreptate, „Alegeți consistența sau scara”. Și aproximativ 99 la sută din bazele de date este mai bine să aibă consistență. Majoritatea dezvoltatorilor nu ajung niciodată să lucreze cu baze de date mari. Prin mare mă refer la faptul că nu o pot avea în memorie pentru un preț rezonabil. Cred că astăzi se pot obține servere cu memorie de 0,5 până la 1 TB la un preț rezonabil. –  > Por Darwin.
Kevin Bedell

Dacă baza de date este suficient de mică (sau dacă adăugați suficientă memorie), baza de date va funcționa efectiv în memorie, deoarece datele dvs. vor fi stocate în memoria cache după prima solicitare.

Modificarea definițiilor tabelelor bazei de date pentru a utiliza motorul de memorie este probabil mai complicată decât aveți nevoie.

Dacă aveți suficientă memorie pentru a încărca tabelele în memorie cu ajutorul funcției MEMORY motor, aveți suficientă pentru a regla setările innodb pentru a pune totul în cache oricum.

Seaux

„Cum pot face asta? Am explorat PHPMyAdmin și nu găsesc o funcționalitate „change engine”.”

Ca răspuns direct la această parte a întrebării tale, poți emite o comandă ALTER TABLE tbl engine=InnoDB; și se va recrea tabelul în motorul corespunzător.

Jice

În locul motorului de stocare Memory, se poate lua în considerare MySQL Cluster. Se spune că acesta oferă performanțe similare, dar că suportă operațiunea susținută de disc pentru durabilitate. Nu l-am încercat, dar pare promițător (și este în dezvoltare de câțiva ani).

Puteți găsi documentația oficială a MySQL Cluster aici.

Mike Q

Gânduri suplimentare :

Ramdisk – setarea unității temporare pe care MySQL o folosește ca disc RAM, foarte ușor de configurat.

memcache – serverul memcache este ușor de configurat, folosiți-l pentru a stoca rezultatele interogărilor pentru o perioadă X de timp.