Installasjon og innledende konfigurasjon av MySQL på linux

Legg igjen en kommentar 6,950

Denne artikkelen vil beskrive ulike MySQL-innstillinger, hovedsakelig de som påvirker ytelsen. For enkelhets skyld er alle variabler delt inn i seksjoner (grunnleggende innstillinger, restriksjoner, trådinnstillinger, forespørselsbuffer, timings, buffere, InnoDB). Først, la oss avklare navnene på noen av variablene som endret seg i versjon 4 av MySQL, og både gamle og nye varianter av navn fortsetter å bli funnet på nettverket, noe som reiser spørsmål.

Så i versjon 4 har en rekke variabler endelsen _size. Dette gjelder thread_cache_size-variabelen og variabler fra seksjonen Buffere... Før versjon 4 ble read_buffer_size-variabelen kalt record_buffer. Se også skip_external_locking-variabelen fra seksjonen Grunnleggende innstillinger før versjon 4 ble det kalt skip_locking.
Variabler faller inn i to hovedkategorier: verdivariabler og flaggvariabler. Variabler med verdier skrives i konfigurasjonsfilen som variabel = verdi, og flaggvariabler er ganske enkelt spesifisert. Du har kanskje også lagt merke til at i noen tilfeller brukes "-" i variabelnavn, og i noen tilfeller brukes "_". Variabler med bindestrek er startalternativene til serveren og kan ikke endres mens serveren kjører (ved hjelp av SET); understrekede variabler er serveralternativer og kan endres umiddelbart. Hvis vi snakker om en "tilstandsvariabel" eller det anbefales å observere verdien til en variabel hvis navn er skrevet i formen Variable_Name, bør du utføre SHOW STATUS LIKE "Variable_Name"-spørringen for å få verdien til denne variabelen, eller se på statusfanen i phpMyAdmin, hvor det vil være flere kommentarer etter verdien av denne variabelen.
La oss nå begynne å beskrive variablene og deres mulige verdier.

Grunnleggende innstillinger

lavprioriterte oppdateringer- Dette alternativet senker prioriteten til INSERT / UPDATE-operasjoner over SELECT. Relevant hvis dataene er viktige å lese raskere enn å skrive raskere.
skip-ekstern-låsing- alternativet er installert som standard, fra og med versjon 4. Instruerer MySQL-serveren til ikke å bruke eksterne låser når du arbeider med databasen. Eksterne låser er nødvendig i situasjoner der flere servere har tilgang til de samme datafilene, dvs. har samme datadir, som ikke brukes i praksis.
hoppe over-navn-løs- ikke definer domenenavn for IP-adresser til tilkoblede klienter. I dette tilfellet må brukertillatelser ikke konfigureres for verter, men for IP-adresser (med unntak av localhost). Hvis du bare kobler til serveren fra den lokale maskinen, så spiller det ingen rolle. For eksterne tilkoblinger vil det øke hastigheten på tilkoblingsoppsettet.
hoppe over nettverk- ikke bruk nettverket, dvs. håndterer ikke TCP/IP-tilkoblinger i det hele tatt. I dette tilfellet vil kommunikasjon med serveren skje utelukkende gjennom kontakten. Anbefales hvis du ikke har programvare som kun bruker TCP/IP for å kommunisere med serveren.

Begrensninger

binde-adresse- grensesnittet som serveren vil lytte til. Av sikkerhetsgrunner anbefales det å sette 127.0.0.1 her hvis du ikke bruker eksterne tilkoblinger til serveren.
max_allowed_packet- den maksimale størrelsen på data som kan overføres i én forespørsel. Øk hvis du får feilen "Pakken for stor".
max_connections- maksimalt antall parallelle tilkoblinger til serveren. Øk den hvis du står overfor problemet med For mange tilkoblinger.
max_join_size- forbyr SELECT-setninger som forventes å analysere mer enn det angitte antallet rader eller mer enn det angitte antallet disksøk. Brukes for å beskytte mot skjeve søk som prøver å telle millioner av rader. Standardinnstillingen er over 4 milliarder, så du vil mest sannsynlig redusere den betraktelig.
max_sort_length- indikerer hvor mange byte fra begynnelsen av BLOB- eller TEXT-feltene som skal brukes ved sortering. Standardverdien er 1024, hvis du er bekymret for feil utformede tabeller eller spørringer, bør du redusere den.

Strøminnstillinger

thread_cache_size- indikerer antall bufrede tråder. Etter å ha behandlet forespørselen vil ikke serveren avslutte tråden, men plassere den i hurtigbufferen hvis antall tråder i hurtigbufferen er mindre enn den angitte verdien. Standardverdien er 0, øk den til 8 eller umiddelbart til 16. Hvis du observerer en økning i verdien av tilstandsvariabelen Threads_Created, bør du øke thread_cache_size enda mer.
thread_concurrency- kun relevant for Solaris / SunOS, i motsetning til det som er skrevet på nettverket. "Spør" systemet hvor mange tråder som skal starte samtidig ved å utføre et funksjonskall thr_setconcurrency... Den anbefalte verdien er dobbelt eller tredobbelt antall prosessorkjerner.

Spørringsbufring

query_cache_limit- maksimal størrelse på en bufret forespørsel.
query_cache_min_res_unit- minimumsstørrelsen på blokken som er lagret i cachen.
query_cache_size- størrelsen på cachen. 0 deaktiverer cache-bruk. For å velge den optimale verdien, er det nødvendig å overvåke tilstandsvariabelen Qcache_lowmem_prunes og sikre at verdien øker litt. Du må også huske at en unødvendig stor cache vil skape unødvendig belastning.
query_cache_type- (AV, DEMAND, PÅ). AV deaktiverer caching, KREVE- caching vil kun utføres hvis direktivet er til stede SQL_CACHE i forespørselen, PÅ slår på caching.
query_cache_wlock_invalidate- bestemmer om dataene skal hentes fra cachen hvis tabellen de refererer til er låst for lesing.

Tenk på en spørringsbuffer som en hash-array med spørringer som nøkler og spørringsresultater som verdier. I tillegg til resultatene, lagrer MySQL en liste over tabeller i hurtigbufferen, hvor utvalget er bufret. Hvis noen av tabellene, utvalget som er i hurtigbufferen, endres, fjerner MySQL slike valg fra hurtigbufferen. MySQL cacher heller ikke spørringer, hvis resultater kan endres.
Når MySQL starter opp, tildeler en blokk med minne i størrelsen query_cache_size. Når du utfører en spørring, så snart de første radene i resultatet er mottatt, begynner serveren å bufre dem: den tildeler en minneblokk i hurtigbufferen lik query_cache_min_res_unit, skriver resultatet av valget til den. Hvis ikke hele utvalget passer inn i blokken, tildeler serveren neste blokk, og så videre. På tidspunktet for starten av opptaket vet ikke MySQL om størrelsen på det resulterende utvalget, derfor, hvis størrelsen på utvalget som er registrert i hurtigbufferen er større enn query_cache_limit, stopper opptaket og den okkuperte plassen frigjøres, derfor , hvis du vet på forhånd at resultatet av utvalget blir stort, bør du utføre det med direktivet SQL_NO_CACHE.

Tidspunkter

interactive_timeout- tid i sekunder der serveren venter på aktivitet fra siden av den interaktive tilkoblingen (ved hjelp av flagget CLIENT_INTERACTIVE) før du lukker den.
log_slow_queries- instruerer serveren til å logge lange ("langsomme") spørringer (som varer lenger enn long_query_time). Det fullstendige filnavnet sendes som en verdi (for eksempel / var / log / slow_queries).
long_query_time- hvis forespørselen tar lengre tid enn den angitte tiden (i sekunder), vil den bli ansett som "sakte".
net_read_timeout
net_write_timeout- tid i sekunder, hvor serveren vil vente på å motta data før tilkoblingen avbrytes. Hvis serveren ikke betjener klienter med veldig trege eller ustabile kanaler, vil 15 sekunder være nok her.
wait_timeout- tid, i sekunder, hvor serveren venter på at en tilkobling skal være aktiv før den avsluttes. Generelt vil 30 sekunder være tilstrekkelig.

Buffere

Alle buffere har én ting til felles - hvis, ved å sette en stor bufferstørrelse, data går til personsøkingsfilen, vil bufferen gjøre mer skade enn nytte. Fokuser derfor alltid på mengden fysisk RAM som er tilgjengelig for deg.

key_buffer_size- størrelsen på bufferen som er tildelt for indekser og tilgjengelig for alle strømmer. En veldig viktig innstilling som påvirker ytelsen. Standard er 8 MB og bør definitivt økes. 15-30 % av total RAM anbefales, men det gir ingen mening å installere mer enn den totale størrelsen på alle .MYI-filer. Observer tilstandsvariablene Key_reads og Key_read_requests, Key_reads / Key_read_requests-forholdet skal være så lite som mulig (< 0,01). Если это отношение велико, то размер буфера стоит увеличить.
max_heap_table_size- den maksimalt tillatte størrelsen på en tabell som er lagret i minnet (MEMORY type). Standard er 16 MB, hvis du ikke bruker MEMORY-tabeller, setter du denne verdien til tmp_table_size.
myisam_sort_buffer_size- størrelsen på bufferen tildelt av MyISAM for sortering av indekser når REPARASJONSBORD eller for å lage indekser når LAG INDEKS, ENDRE TABELL... Standard er 8 MB og bør økes til 30-40 % av RAM. Gevinsten i ytelse vil følgelig bare være når spørsmålene ovenfor er oppfylt.
net_buffer_length- mengden minne som er tildelt for tilkoblingsbufferen og for resultatbufferen for hver strøm. Koblingsbufferen vil være av den angitte størrelsen og resultatbufferen vil ha samme størrelse, dvs. double net_buffer_length vil bli tildelt per strøm. Den angitte verdien er startverdien og buffere vil vokse etter behov opp til max_allowed_packet. Standardstørrelsen er 16 KB. Hvis minnet er begrenset eller bare små spørringer brukes, kan verdien reduseres. Ved konstant bruk av store forespørsler og tilstrekkelig minne, bør verdien økes til forventet gjennomsnittlig forespørselsstørrelse.
read_buffer_size- hver tråd, ved sekvensiell skanning av tabellene, tildeler den angitte mengden minne for hver tabell. Tester viser at denne verdien ikke bør økes for mye. Standardstørrelsen er 128KB, prøv å øke den til 256KB og deretter til 512KB og se hastigheten på spørringer som SELECT COUNT (*) FROM table WHERE expr LIKE "a%"; på store bord.
read_rnd_buffer_size- relevant for forespørsler med " REKKEFØLGE ETTER", det vil si for spørringer hvis resultat må sorteres og som har tilgang til en tabell som har indekser. Standard er 256 KB, øk den til 1 MB eller mer hvis minnet tillater det. Merk at den angitte minneverdien også tildeles hver strøm .
sort_buffer_size- hver tråd utfører sorteringsoperasjoner ( REKKEFØLGE ETTER) eller gruppering ( GRUPPE AV), tildeler en buffer med den angitte størrelsen. Standardverdien er 2 MB hvis du bruker de angitte forespørselstypene og hvis minnet tillater det, bør verdien økes. En stor verdi for tilstandsvariabelen Sort_merge_passes indikerer at sort_buffer_size bør økes. Det er også verdt å sjekke utførelseshastigheten for spørringer som SELECT * FROM table ORDER BY name DESC på store tabeller, kanskje øker bufferen bare vil bremse arbeidet (i noen tester er dette tilfellet).
table_cache (table_open_cache siden versjon 5.1.3) - antall bufrede åpne tabeller for alle tråder. Å åpne en tabellfil kan være ganske ressurskrevende, så det er best å holde åpne tabeller i hurtigbufferen. Merk at hver oppføring i denne hurtigbufferen bruker en systembeskrivelse, så det kan hende du må øke grensen for antall deskriptorer ( ulimit). Standardverdien er 64, det er best å øke den til det totale antallet bord, hvis antallet er innenfor akseptable grenser. Opened_tables tilstandsvariabelen holder styr på antall tabeller som er åpnet utenom cachen, fortrinnsvis å holde den så lav som mulig.
tmp_table_size- den maksimale størrelsen på minnet tildelt for midlertidige tabeller opprettet av MySQL for dets interne behov. Denne verdien er også begrenset av variabelen max_heap_table_size, så til slutt vil minimumsverdien fra max_heap_table_size og tmp_table_size bli valgt, og resten av de midlertidige tabellene vil bli opprettet på disken. Standarden er systemavhengig, prøv å sette den til 32MB og observer Create_tmp_disk_tables-tilstandsvariabelen, den skal være så liten som mulig.

Verdiene i konfigurasjonsfilen er spesifisert i byte, henholdsvis kilobyte og megabyte må konverteres til byte.

InnoDB

innodb_additional_mem_pool_size- størrelsen på minnet som er tildelt av InnoDB for å lagre ulike interne strukturer. Hvis InnoDB ikke har nok av dette minnet, vil det be om minne fra operativsystemet og skrive en advarsel til MySQL-feilloggen.
innodb_buffer_pool_size er størrelsen på minnet som er tildelt av InnoDB for lagring av både indekser og data. Det betyr - jo mer jo bedre. Kan vokse opp til den totale størrelsen på alle InnoDB-tabeller, eller opptil 80 % av RAM-en, avhengig av hva som er minst.
innodb_flush_log_at_trx_commit- har tre gyldige verdier: 0, 1, 2. Når verdien er lik 0 , tømmes loggen til disk en gang i sekundet, uavhengig av pågående transaksjoner. Når verdien er lik 1 , blir loggen tømt til disk med hver transaksjon. Når verdien er lik 2 , loggen skrives med hver transaksjon, men skylles aldri til disk, og overlater den til OS-samvittigheten. Standardinnstillingen er 1, som er den sikreste innstillingen, men ikke den raskeste. Generelt kan du trygt bruke 2, data kan bare gå tapt i tilfelle et OS-krasj og bare i løpet av noen få sekunder (avhengig av OS-innstillingene). 0 er den raskeste modusen, men data kan gå tapt både under et OS-krasj og under et krasj på selve MySQL-serveren (dataene tar imidlertid bare 1-2 sekunder).
innodb_log_buffer_size- størrelsen på loggbufferen. Standarden er 1 MB og bør økes hvis du vet at det vil være et stort antall InnoDB-transaksjoner eller hvis tilstandsvariabelen Innodb_log_waits øker. Du trenger neppe å øke den over 8MB.
innodb_log_file_size- maksimal størrelse på én loggfil. Når denne størrelsen er nådd, vil InnoDB opprette en ny fil. Standarden er 5 MB, økende størrelse vil forbedre ytelsen, men øke tiden for datagjenoppretting. Sett denne verdien i området 32 MB - 512 MB avhengig av størrelsen på serveren (bedømme den subjektivt).

Den er også praktisk å bruke for å overvåke serverdrift

Jeg vil fortelle deg om innstillingene jeg brukte, men ikke alle hjalp, jeg skal bare beskrive dem.

Introduksjon

Som jeg klarte å finne ut, blir det opprettet en mysqld (daemon) hver gang du kobler til MySQL, som behandler alle tilkoblingsforespørsler. Blokken beskriver nøyaktig innstillingene til slike demoner.

Så la oss ta en titt på demoninnstillingene.

Å avdekke standardkodingen kan gjøres slik:

tegnsett-tjener = utf8
sorteringstjener = utf8_unicode_ci

Du kan beskytte serveren fra de skjeve hendene til en programmerer som er i stand til å begrave til og med en 4-prosessor server med en join for 10 millioner poster:

max_join_size = 1000000

Bufferen kan settes til 25 % av total RAM:

key_buffer_size = 2048M

slik jeg forsto, er dette utklippstavlen for alle demoner, dvs. virkelig vil være: key_buffer_size / antall demoner = ??? M

Stabelstørrelse for hver tråd (demon):

thread_stack = 512K

stabelen er et sted å lagre en liste over oppgaver (åpne en tabell, utfør en spørring, lukk osv.)

Antall tråder som serveren skal bufre for gjenbruk:

thread_cache_size = 32

de. hvis det for eksempel er en ofte gjentatt SELECT * FROM myTable, vil den gå inn i cachen for ikke å bli utført hver gang.

Nyttig innstilling: hvis størrelsen på den midlertidige tabellen overskrider størrelsen angitt av denne variabelen, blir den tømt til disk. Hvis det er nokminne på serveren, anbefales det å øke verdien av denne variabelen for å øke hastigheten på spørringene med GROUP BY-konstruksjonen

tmp_table_size = 512M

Du kan angi maksimal størrelse for MEMORY (HEAP)-tabeller som følger:

max_heap_table_size = 256M

Størrelsen på bufferen som er tildelt demonen når du utfører sorteringsoperasjoner. For å øke hastigheten på ORDER BY, GROUP BY-operasjoner, anbefales det å øke denne verdien

Størrelsen på bufferen som er tildelt for sortering av MyISAM-indekser ved å bruke REPAIR TABLE-setningen eller når du oppretter indekser med CREATE TABLE, ALTER TABLE-setningene:

myisam_sort_buffer_size = 256M

Størrelsen på bufferen som er tildelt demonen for hver skannet tabell. Med hyppig sekvensiellskanning, anbefales det å øke verdien av denne variabelen.

read_buffer_size = 4M

Størrelsen på bufferen som er tildelt for å lese linjer etter sortering, for å unngå gjentatte søk på disken. Øker verdien av dettevariabel kan øke effektiviteten til ORDER BY-klausulen betydelig.Husk, siden denne bufferen er tildelt for hver demon, bør du ikke sette denne verdien for høyt.

read_rnd_buffer_size = 8M

Størrelsen på bufferen som brukes for tabellsammenføyningsoperasjoner (hvis ingen indekser brukes).Bufferen settes én gang under hver fletteoperasjon

join_buffer_size = 8M

Størrelsen på bufferen som brukes for indekser for alle demoner. Hvis du bruker mange DELETE- eller INSERT-spørringer på tabeller med et stort antall indekser, vil økning av verdien øke hastigheten på slike spørringer. For enda høyere hastighet, bruk LOCK TABLES. Det anbefales å installereikke mer enn 1/3 av RAM og ikke mer enn volumet av alle b.d.

key_buffer = 2048M

Maksimalt antall klienttilkoblinger til serveren

max_connections = 35

Angir maksimalt antall mislykkede vertstilkoblingsforsøk. Standardverdien er 10. Når denne verdien er nådd,verten er blokkert. Du kan oppheve blokkeringen av verten med: mysql> FLUSH HOSTS

max_connect_errors = 50

Maksimalt antall samtidige tilkoblinger for én MySQL-konto. Standardverdien er 0, ingen begrensninger

max_user_connections = 25

table_cache er det gamle navnet på variabelen table_open_cache, det peker påAntall åpne tabeller for alle demoner. Å øke verdien vil øke antallet filbeskrivelser som brukes. Det anbefales å beregne etter formelen: antall samtidige tilkoblinger * antall åpne tabeller i tilkoblingen. T.e. hver tilkobling bruker sine egne celler fra hurtigbufferen. Du kan kjøre mysqltuner.pl for å sjekke

table_cache = 128

Antallet demoner som ble lansert samtidig, oppgi formelen:antall prosessorkjerner multipliseres med 2

thread_concurrency = 16

Størrelsen på tilkoblingsbufferen satt av serveren mellom forespørslene

net_buffer_length = 1024

Maksimal størrelse på én SQL-spørring til serveren. Til å begynne med er meldingsbufferen net_buffer_length ogom nødvendig økes den automatisk til verdien av denne variabelen.

max_allowed_packet = 512M

Variabelen spesifiserer antall byte ved sortering av BLOB- eller TEXT-verdier.Bare den første max_sort_length brukes, resten ignoreres

max_sort_length = 512

Nyttig innstilling: spørringer hvis resultat overstiger verdien til denne variabelen vil ikke bli plassert i spørringsbufferen.

query_cache_limit = 2M

Nyttig innstilling: mengden minne som er tildelt for hurtigbufring av spørringsresultater. Som standard er denne hurtigbufferen deaktivert, verdien er 0

query_cache_size = 16M

Nyttig innstilling: type caching:

0 - ikke cache noe (standard)
1 - cache alle spørringer unntatt SELECT SQL_NO_CACHE
2 - bare cache-spørringer som starter med SELECT SQL_CACHE-leddet

query_cache_type = 2

Innodb-innstillinger (beklager for ingen forklaring, jeg lar dem bare ligge her for ikke å glemme):

innodb_data_home_dir = / var / lib / mysql
innodb_data_file_path = ibdata1: 2000M; ibdata2: 10M: autoutvid
innodb_log_group_home_dir = / var / lib / mysql
innodb_buffer_pool_size = 64M
innodb_additional_mem_poo l_size = 32M
innodb_file_io_threads = 8
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_c ommit = 2
innodb_flush_method = O_DIRECT

transaksjonsisolasjon = LESE-KOMMITTERT

Blokker innstillinger for å lage dumper

default-character-set = utf8

Det gamle navnet på følgende innstilling: character-set-server = utf8 gir en feil: / usr / bin / mysql_upgrade: ukjent variabel "character-set-server = utf 8

default-character-set = utf8

Jeg håper noen hjalp til med å finne ut av det, lykke til med å mestre MySQL.

Oversettelse

En løs oversettelse av en ganske gammel artikkel fra MySQL Performance Blog om hva som er best å konfigurere umiddelbart etter installasjon av basisversjonen av mySQL.

Det er utrolig hvor mange som installerer mySQL på serverne sine og lar den stå med standardinnstillingene.

Til tross for at det er ganske mange innstillinger i mySQL som du kan endre, er det et sett med veldig viktige egenskaper som du definitivt trenger å optimalisere for din egen server. Vanligvis, etter en så liten tweak, er serverytelsen merkbart forbedret.

key_buffer_size er en ekstremt viktig innstilling når du bruker MyISAM-tabeller. Sett den lik ca. 30-40 % av tilgjengelig RAM hvis du kun bruker MyISAM. Riktig størrelse avhenger av størrelsen på indeksene, dataene og belastningen på serveren - husk at MyISAM bruker operativsystemets (OS) cache til å lagre dataene, så du må ha igjen nok RAM til dataene og dataene kan tar betydelig mer plass enn indeksene. Men sørg for å sjekke at hele plassen som er tildelt av direktivet key_buffer_size for cachen ble den stadig brukt - du kan ofte se situasjoner når 4 GB er tildelt for indeksbufferen, selv om den totale størrelsen på alle .MYI-filer ikke overstiger 1 GB. Å gjøre det er helt ubrukelig, du vil bare kaste bort ressurser. Hvis du praktisk talt ikke har MyISAM-tabeller, da key_buffer_size bør settes til ca. 16-32 MB - de vil bli brukt til å lagre indeksene til midlertidige tabeller opprettet på disk i minnet.
innodb_buffer_pool_size- ikke mindre viktig innstilling, men allerede for InnoDB, sørg for å ta hensyn til den hvis du hovedsakelig skal bruke InnoDB-tabeller, siden de er mye mer følsomme for bufferstørrelser enn MyISAM-tabeller. MyISAM-tabeller kan i prinsippet fungere godt selv med store datamengder og med en standardverdi key_buffer_size, men mySQL kan være veldig treg hvis verdien er feil innodb_buffer_pool_size... InnoDB bruker sin egen buffer for å lagre både indekser og data, så det er ikke nødvendig å legge igjen minne til OS-cachen - installer innodb_buffer_pool_size i 70-80 % av tilgjengelig RAM (hvis, selvfølgelig, bare InnoDB-tabeller brukes). Maksimal størrelse på dette alternativet er den samme. key_buffer_size- ikke la deg rive med, du må finne den optimale størrelsen, finne den beste bruken av det tilgjengelige minnet.
innodb_additional_mem_pool_size- dette alternativet har praktisk talt ingen effekt på mySQL-ytelsen, men jeg anbefaler å la det være ca. 20 MB (eller litt mer) til InnoDB for ulike interne behov.
innodb_log_file_size- en ekstremt viktig innstilling i et databasemiljø med hyppige skrivinger til tabeller, spesielt med store volumer. B O Større størrelser vil øke ytelsen, men vær forsiktig – gjenopprettingstiden for data vil også øke. Jeg setter den vanligvis til rundt 64-512 MB avhengig av størrelsen på serveren.
innodb_log_buffer_size- Standardverdien til dette alternativet er ganske egnet for de fleste systemer med et gjennomsnittlig antall skriv og små transaksjoner. Hvis systemet ditt opplever utbrudd av aktivitet, eller du jobber aktivt med BLOB-data, anbefaler jeg å øke verdien litt innodb_log_buffer_size... Men ikke overdriv – for stor verdi vil være sløsing med minne: bufferen tømmes hvert sekund, så du trenger ikke mer plass enn du trenger for det sekundet. Den anbefalte verdien er ca. 8-16 MB, og enda mindre for små databaser.
- klager over at InnoDB er 100 ganger tregere enn MyISAM? Du har sannsynligvis glemt innstillingene innodb_flush_log_at_trx_commit... Standardverdien "1" betyr at hver UPDATE-transaksjon (eller en lignende kommando utenfor transaksjonen) må tømme bufferen til disken, noe som er ganske ressurskrevende. De fleste applikasjoner, spesielt de som tidligere brukte MyISAM-tabeller, vil fungere bra med verdien "2" (dvs. "ikke skyll bufferen til disken, bare til OS-bufferen"). Loggen vil imidlertid fortsatt bli tømt til disk hvert 1-2 sekund, så i tilfelle en krasj vil du miste maksimalt 1-2 sekunder med oppdateringer. En verdi på "0" vil forbedre ytelsen, men du risikerer å miste data selv om mySQL-serveren krasjer, mens du angir verdien innodb_flush_log_at_trx_commit i "2" vil du bare miste data hvis hele operativsystemet krasjer.
table_cache- Å åpne bord kan være ganske ressurskrevende. For eksempel merker MyISAM-tabeller .MYI-filoverskriftene som "i bruk for øyeblikket." Det anbefales generelt ikke å åpne tabeller for ofte, så det er best å holde cachen stor nok til å holde alle bordene åpne. Til dette brukes en viss mengde OS- og RAM-ressurser, men dette er vanligvis ikke et betydelig problem for moderne servere. Hvis du har flere hundre tabeller, så startverdien for alternativet table_cache kan være "1024" (husk at hver tilkobling krever sitt eget håndtak). Hvis du har enda flere tabeller eller mange sammenføyninger, øk parameterverdien. Jeg har sett mySQL-serveren med verdien table_cache lik 100 000.
thread_cache- opprettelse/destruksjon av bekker er også en ressurskrevende operasjon som skjer hver gang en forbindelse etableres og hver forbindelse brytes. Jeg setter vanligvis dette alternativet til 16. Hvis applikasjonen din kan ha hopp i antall samtidige tilkoblinger og med variabel Tråder_opprettet det er en rask økning i antall tråder, da er det verdt å øke verdien thread_cache... Målet er å forhindre opprettelsen av nye tråder under normal serverdrift.
query_cache_size- hvis applikasjonen din leser data mye og ofte, og du samtidig ikke har en cache på applikasjonsnivå, kan dette alternativet være svært nyttig. Ikke legg for mye verdi her, siden vedlikehold av en stor spørringsbuffer vil være kostbart i seg selv. Anbefalt verdi er 32 til 512 MB. Ikke glem å sjekke hvor godt spørringsbufferen brukes - under noen forhold (med et lite antall treff i hurtigbufferen, dvs. når nesten ingen identiske data hentes), kan bruk av en stor hurtigbuffer redusere ytelsen.

Som du kan se, er dette globale innstillinger. Disse variablene avhenger av servermaskinvaren og mySQL-motorene som brukes, mens øktvariabler vanligvis konfigureres spesifikt for spesifikke oppgaver. Hvis du hovedsakelig bruker enkle søk, er det ikke nødvendig å øke verdien. sort_buffer_size selv om du har 64 GB ekstra RAM. Dessuten kan store cacher bare forringe serverytelsen. Det er bedre å la sesjonsvariabler ligge til senere, for å finjustere serveren.

PS: mySQL-installasjonen kommer med flere forhåndsinstallerte my.cnf-filer, beregnet for forskjellige belastninger. Hvis du ikke har tid til å konfigurere serveren manuelt, er det vanligvis bedre å bruke dem enn standard konfigurasjonsfilen, og velg den som er mer egnet for belastningen på serveren din.

Så i versjon 4 har en rekke variabler endelsen _size. Dette gjelder thread_cache_size-variabelen og variabler fra seksjonen Buffere... Før versjon 4 ble read_buffer_size-variabelen kalt record_buffer. Se også skip_external_locking-variabelen fra seksjonen Grunnleggende innstillinger før versjon 4 ble det kalt skip_locking.

Variabler faller inn i to hovedkategorier: verdivariabler og flaggvariabler. Variabler med verdier skrives i konfigurasjonsfilen som variabel = verdi, og flaggvariabler er ganske enkelt spesifisert. Du har kanskje også lagt merke til at i noen tilfeller brukes "-" i variabelnavn, og i noen tilfeller brukes "_". Variabler med bindestrek er startalternativene til serveren og kan ikke endres mens serveren kjører (ved hjelp av SET); understrekede variabler er serveralternativer og kan endres umiddelbart. Hvis vi snakker om en "tilstandsvariabel" eller det anbefales å observere verdien til en variabel hvis navn er skrevet i formen Variable_Name, bør du utføre SHOW STATUS LIKE "Variable_Name"-spørringen for å få verdien til denne variabelen, eller se på statusfanen i phpMyAdmin, hvor det vil være flere kommentarer etter verdien av denne variabelen.

La oss nå begynne å beskrive variablene og deres mulige verdier.

Grunnleggende innstillinger

lavprioriterte oppdateringer- Dette alternativet senker prioriteten til INSERT / UPDATE-operasjoner over SELECT. Relevant hvis dataene er viktige å lese raskere enn å skrive raskere.
skip-ekstern-låsing- alternativet er installert som standard, fra og med versjon 4. Instruerer MySQL-serveren til ikke å bruke eksterne låser når du arbeider med databasen. Eksterne låser er nødvendig i situasjoner der flere servere har tilgang til de samme datafilene, dvs. har samme datadir, som ikke brukes i praksis.
hoppe over-navn-løs- ikke definer domenenavn for IP-adresser til tilkoblede klienter. I dette tilfellet må brukertillatelser ikke konfigureres for verter, men for IP-adresser (med unntak av localhost). Hvis du bare kobler til serveren fra den lokale maskinen, så spiller det ingen rolle. For eksterne tilkoblinger vil det øke hastigheten på tilkoblingsoppsettet.
hoppe over nettverk- ikke bruk nettverket, dvs. håndterer ikke TCP/IP-tilkoblinger i det hele tatt. I dette tilfellet vil kommunikasjon med serveren skje utelukkende gjennom kontakten. Anbefales hvis du ikke har programvare som kun bruker TCP/IP for å kommunisere med serveren.

Begrensninger

binde-adresse- grensesnittet som serveren vil lytte til. Av sikkerhetsgrunner anbefales det å sette 127.0.0.1 her hvis du ikke bruker eksterne tilkoblinger til serveren.
max_allowed_packet- den maksimale størrelsen på data som kan overføres i én forespørsel. Øk hvis du får feilen "Pakken for stor".
max_connections- maksimalt antall parallelle tilkoblinger til serveren. Øk den hvis du står overfor problemet "For mange tilkoblinger".
max_join_size- forbyr SELECT-setninger som forventes å analysere mer enn det angitte antallet rader eller mer enn det angitte antallet disksøk. Brukes for å beskytte mot skjeve søk som prøver å telle millioner av rader. Standardinnstillingen er over 4 milliarder, så du vil mest sannsynlig redusere den betraktelig.
max_sort_length- indikerer hvor mange byte fra begynnelsen av BLOB- eller TEXT-feltene som skal brukes ved sortering. Standardverdien er 1024, hvis du er bekymret for feil utformede tabeller eller spørringer, bør du redusere den.

Strøminnstillinger

thread_cache_size- indikerer antall bufrede tråder. Etter å ha behandlet forespørselen vil ikke serveren avslutte tråden, men plassere den i hurtigbufferen hvis antall tråder i hurtigbufferen er mindre enn den angitte verdien. Standardverdien er 0, øk den til 8 eller umiddelbart til 16. Hvis du observerer en økning i verdien av tilstandsvariabelen Threads_Created, bør du øke thread_cache_size enda mer.
thread_concurrency- kun relevant for Solaris / SunOS, i motsetning til det som er skrevet på nettverket. "Forteller" systemet hvor mange tråder som skal startes samtidig ved å foreta et funksjonskall thr_setconcurrency... Den anbefalte verdien er dobbelt eller tredobbelt antall prosessorkjerner.

Spørringsbufring

query_cache_limit- maksimal størrelse på en bufret forespørsel.
query_cache_min_res_unit- minimumsstørrelsen på blokken som er lagret i cachen.
query_cache_size- størrelsen på cachen. 0 deaktiverer cache-bruk. For å velge den optimale verdien, er det nødvendig å overvåke tilstandsvariabelen Qcache_lowmem_prunes og sikre at verdien øker litt. Du må også huske at en unødvendig stor cache vil skape unødvendig belastning.
query_cache_type- (AV, DEMAND, PÅ). AV deaktiverer caching, KREVE- caching vil kun utføres hvis direktivet er til stede SQL_CACHE i forespørselen, PÅ slår på caching.
query_cache_wlock_invalidate- bestemmer om dataene skal hentes fra cachen hvis tabellen de refererer til er låst for lesing.

Når MySQL starter opp, tildeler en blokk med minne i størrelsen query_cache_size. Når du utfører en spørring, så snart de første radene i resultatet er mottatt, begynner serveren å bufre dem: den tildeler en minneblokk i hurtigbufferen lik query_cache_min_res_unit, skriver resultatet av valget til den. Hvis ikke hele utvalget passer inn i blokken, tildeler serveren neste blokk, og så videre. På tidspunktet for starten av opptaket vet ikke MySQL om størrelsen på det resulterende utvalget, derfor, hvis størrelsen på utvalget som er registrert i hurtigbufferen er større enn query_cache_limit, stopper opptaket og den okkuperte plassen frigjøres, derfor , hvis du vet på forhånd at resultatet av utvalget blir stort, bør du utføre det med direktivet SQL_NO_CACHE.

Tidspunkter

interactive_timeout- tid i sekunder der serveren venter på aktivitet fra siden av den interaktive tilkoblingen (ved hjelp av flagget CLIENT_INTERACTIVE) før du lukker den.
log_slow_queries- ber serveren logge lange ("langsomme") spørringer (som varer lenger enn long_query_time). Det fullstendige filnavnet sendes som en verdi (for eksempel / var / log / slow_queries).
long_query_time- hvis forespørselen tar lengre tid enn den angitte tiden (i sekunder), vil den bli ansett som "sakte".
net_read_timeout
net_write_timeout- tid i sekunder, hvor serveren vil vente på å motta data før tilkoblingen avbrytes. Hvis serveren ikke betjener klienter med veldig trege eller ustabile kanaler, vil 15 sekunder være nok her.
wait_timeout- tid, i sekunder, hvor serveren venter på at en tilkobling skal være aktiv før den avsluttes. Generelt vil 30 sekunder være tilstrekkelig.

Buffere

key_buffer_size- størrelsen på bufferen som er tildelt for indekser og tilgjengelig for alle strømmer. En veldig viktig innstilling som påvirker ytelsen. Standard er 8 MB og bør definitivt økes. 15-30 % av total RAM anbefales, men det gir ingen mening å installere mer enn den totale størrelsen på alle .MYI-filer. Observer tilstandsvariablene Key_reads og Key_read_requests, Key_reads / Key_read_requests-forholdet skal være så lite som mulig (< 0,01). Если это отношение велико, то размер буфера стоит увеличить.
max_heap_table_size- den maksimalt tillatte størrelsen på en tabell som er lagret i minnet (MEMORY type). Standard er 16 MB, hvis du ikke bruker MEMORY-tabeller, setter du denne verdien til tmp_table_size.
myisam_sort_buffer_size- størrelsen på bufferen tildelt av MyISAM for sortering av indekser når REPARASJONSBORD eller for å lage indekser når LAG INDEKS, ENDRE TABELL... Standard er 8 MB og bør økes til 30-40 % av RAM. Gevinsten i ytelse vil følgelig bare være når spørsmålene ovenfor er oppfylt.
net_buffer_length- mengden minne som er tildelt for tilkoblingsbufferen og for resultatbufferen for hver strøm. Koblingsbufferen vil være av den angitte størrelsen og resultatbufferen vil ha samme størrelse, dvs. double net_buffer_length vil bli tildelt per strøm. Den angitte verdien er startverdien og buffere vil vokse etter behov opp til max_allowed_packet. Standardstørrelsen er 16 KB. Hvis minnet er begrenset eller bare små spørringer brukes, kan verdien reduseres. Ved konstant bruk av store forespørsler og tilstrekkelig minne, bør verdien økes til forventet gjennomsnittlig forespørselsstørrelse.
read_buffer_size- hver tråd, ved sekvensiell skanning av tabellene, tildeler den angitte mengden minne for hver tabell. Tester viser at denne verdien ikke bør økes for mye. Standardstørrelsen er 128KB, prøv å øke den til 256KB og deretter til 512KB og se hastigheten på spørringer som SELECT COUNT (*) FROM table WHERE expr LIKE "a%"; på store bord.
read_rnd_buffer_size- relevant for forespørsler med " REKKEFØLGE ETTER", det vil si for spørringer hvis resultat må sorteres og som har tilgang til en tabell som har indekser. Standard er 256 KB, øk den til 1 MB eller mer hvis minnet tillater det. Merk at den angitte minneverdien også tildeles hver strøm .
sort_buffer_size- hver tråd utfører sorteringsoperasjoner ( REKKEFØLGE ETTER) eller gruppering ( GRUPPE AV), tildeler en buffer med den angitte størrelsen. Standardverdien er 2 MB hvis du bruker de angitte forespørselstypene og hvis minnet tillater det, bør verdien økes. En stor verdi for tilstandsvariabelen Sort_merge_passes indikerer at sort_buffer_size bør økes. Det er også verdt å sjekke utførelseshastigheten for spørringer som SELECT * FROM table ORDER BY name DESC på store tabeller, kanskje øker bufferen bare vil bremse arbeidet (i noen tester er dette tilfellet).
table_cache (table_open_cache siden versjon 5.1.3) - antall bufrede åpne tabeller for alle tråder. Å åpne en tabellfil kan være ganske ressurskrevende, så det er best å holde åpne tabeller i hurtigbufferen. Merk at hver oppføring i denne hurtigbufferen bruker en systembeskrivelse, så det kan hende du må øke grensen for antall deskriptorer ( ulimit). Standardverdien er 64, det er best å øke den til det totale antallet bord, hvis antallet er innenfor akseptable grenser. Opened_tables tilstandsvariabelen holder styr på antall tabeller som er åpnet utenom cachen, fortrinnsvis å holde den så lav som mulig.
tmp_table_size- den maksimale størrelsen på minnet tildelt for midlertidige tabeller opprettet av MySQL for dets interne behov. Denne verdien er også begrenset av variabelen max_heap_table_size, så til slutt vil minimumsverdien fra max_heap_table_size og tmp_table_size bli valgt, og resten av de midlertidige tabellene vil bli opprettet på disken. Standarden er systemavhengig, prøv å sette den til 32MB og observer Create_tmp_disk_tables-tilstandsvariabelen, den skal være så liten som mulig.

Verdiene i konfigurasjonsfilen er spesifisert i byte, henholdsvis kilobyte og megabyte må konverteres til byte.

InnoDB

innodb_additional_mem_pool_size- størrelsen på minnet som er tildelt av InnoDB for å lagre ulike interne strukturer. Hvis InnoDB ikke har nok av dette minnet, vil det be om minne fra operativsystemet og skrive en advarsel til MySQL-feilloggen.
innodb_buffer_pool_size er størrelsen på minnet som er tildelt av InnoDB for lagring av både indekser og data. Det betyr - jo mer jo bedre. Kan vokse opp til den totale størrelsen på alle InnoDB-tabeller, eller opptil 80 % av RAM-en, avhengig av hva som er minst.
innodb_flush_log_at_trx_commit- har tre gyldige verdier: 0, 1, 2. Når verdien er lik 0 , tømmes loggen til disk en gang i sekundet, uavhengig av pågående transaksjoner. Når verdien er lik 1 , blir loggen tømt til disk med hver transaksjon. Når verdien er lik 2 , loggen skrives med hver transaksjon, men skylles aldri til disk, og overlater den til OS-samvittigheten. Standardinnstillingen er 1, som er den sikreste innstillingen, men ikke den raskeste. Generelt kan du trygt bruke 2, data kan bare gå tapt i tilfelle et OS-krasj og bare i løpet av noen få sekunder (avhengig av OS-innstillingene). 0 er den raskeste modusen, men data kan gå tapt både under et OS-krasj og under et krasj på selve MySQL-serveren (dataene tar imidlertid bare 1-2 sekunder).
innodb_log_buffer_size- størrelsen på loggbufferen. Standarden er 1 MB og bør økes hvis du vet at det vil være et stort antall InnoDB-transaksjoner eller hvis tilstandsvariabelen Innodb_log_waits øker. Du trenger neppe å øke den over 8MB.
innodb_log_file_size- maksimal størrelse på én loggfil. Når denne størrelsen er nådd, vil InnoDB opprette en ny fil. Standarden er 5 MB, økende størrelse vil forbedre ytelsen, men øke tiden for datagjenoppretting. Sett denne verdien i området 32 MB - 512 MB avhengig av størrelsen på serveren (bedømme den subjektivt).

Det er også praktisk å bruke phpMyAdmin for å overvåke driften av serveren, fanene er av interesse Stat og Variabler... I tillegg gir phpMyAdmin råd om å justere visse variabler, avhengig av parameterne til serveren.

Ved utarbeidelsen av artikkelen, i tillegg til den offisielle dokumentasjonen og vårt eget hode, ble følgende materialer brukt.

Standard konfigurasjonsparametere i Mysql er designet for små databaser som kjører under liten belastning på svært beskjeden maskinvare. Hvis planene dine for Mysql går utover tabellgrensene med flere hundre poster, må du definitivt endre standardinnstillingene. Den optimale Mysql-konfigurasjonsprosessen består av to deler - innledende konfigurasjon og parameterjustering under drift. Justering av parametere i driftsmodus avhenger i stor grad av spesifikasjonene til systemet ditt og dets overvåking - det er ingen spesielle regler her. Det er en rekke anbefalinger for å starte konfigurasjonen:

MySQL er et gratis databasebehandlingssystem. MySQL er utviklet og støttet av Oracle Corporation, som ervervet varemerkerettighetene i forbindelse med det oppkjøpte Sun Microsystems, som tidligere kjøpte det svenske selskapet MySQL AB. Produktet distribueres under både GNU General Public License og sin egen kommersielle lisens. I tillegg lager utviklerne funksjonalitet bestilt av lisensierte brukere; det er takket være en slik rekkefølge at replikeringsmekanismen dukket opp i nesten de tidligste versjonene.

Åpne mysql-innstillingsfilen, for eksempel:

/etc/mysql/my.cnf

Her er noen vanlige parametere som du bør ta hensyn til og endre for å passe dine behov:

key_buffer_size

Hvis du kun bruker MyIsam-tabeller, sett denne verdien til 30 % ... 40 % av all tilgjengelig RAM på serveren. MyIsam bruker operativsystemets cache for data, så husk at det gjenværende ledige minnet vil være nødvendig for dette. Hvis du har få MyIsam-tabeller og deres samlede størrelse er liten, la denne verdien ligge innenfor 32 millioner.

innodb_buffer_pool_size

Hvis du bare bruker InnoDB-tabeller, sett denne verdien til høyest mulig for systemet ditt. InnoDB-bufferen cacher både data og indekser (og operativsystemets cache brukes ikke), så verdien av denne nøkkelen bør settes til 70 % ... 80 % av tilgjengelig minne.

Hvis serveren din kjører på linkus eller unix, ikke glem å sette innodb_flush_method-parameteren til "O_DIRECT" for å unngå bufring på OS-nivået til det Mysql allerede cacher.

innodb_log_file_size

Vær oppmerksom på denne parameteren hvis du har en høy rekordhastighet. Jo større størrelsen på denne nøkkelen er, desto mer effektivt vil dataene bli skrevet. Men husk at dette vil øke systemgjenopprettingstiden! Denne parameteren er vanligvis satt til 64M-512M.

innodb_flush_log_at_trx_commit

Denne parameteren påvirker arbeidshastigheten (skriving) av innoDB-tabeller betydelig.
En verdi på "1 ″ betyr at enhver fullført transaksjon vil tømme loggen til disken synkront.
Verdien "2" gjør det samme, tømmer bare loggen, ikke til disken, men til operativsystemets cache. Denne verdien er bra i de fleste tilfeller, siden utfører ikke dyre skriveoperasjoner etter hver transaksjon. I dette tilfellet blir loggen skrevet til disk med en forsinkelse på flere sekunder, noe som er veldig trygt fra et datasikkerhetssynspunkt.
En verdi på "0" vil gi best ytelse. I dette tilfellet vil bufferen tømmes til loggfilen uavhengig av transaksjoner. Sett denne parameteren til "0 ″ på egen risiko. i dette tilfellet øker risikoen for tap av data.

table_cache

Denne nøkkelen definerer minnet som er tildelt for lagring av åpne tabeller. Hvis du har flere hundre tabeller, sett denne verdien til 1024. Hvis du har et stort antall sammenføyninger, øk denne verdien gradvis. en egen journal føres for hver tilkobling.

thread_cache

Denne parameteren hjelper til med å unngå å lage/ødelegge tråder når du kobler til serveren. Sett denne parameteren til 16 og bygg opp etter behov. Sjekk "Threads_created"-beregningen, ideelt sett bør den være null:

Mysql> vis status som 'threads_created'; + -----– + --– + | Variabelnavn | Verdi | + -----– + --– + | Tråder_opprettet | 423312 | + -----– + --– +

query_cache_size

Verdien til denne parameteren bestemmer hvor mye minne som skal brukes for forespørselsbufferen. Ikke la deg rive med av å sette store verdier. Forespørselsbufferen bør ikke være stor, siden mysql vil spise opp ressurser for å administrere data i cachen. Start på 32M ... 128M, og skaler opp etter behov.