Back to Question Center
0

Sådan optimeres MySQL: Indekser, langsomme forespørgsler, konfiguration            Sådan optimeres MySQL: Indekser, langsomme forespørgsler, konfigurationsrelaterede emner: DatabasePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Sem

1 answers:
Sådan optimeres MySQL: Indekser, langsomme forespørgsler, konfiguration

MySQL er stadig verdens mest populære relationsdatabase, og alligevel er den stadig den mest uoptimerede - mange mennesker efterlader det som standardværdier, uden at genere at undersøge yderligere. I denne artikel vil vi se på nogle MySQL optimeringstips, vi tidligere har dækket, og kombinere dem med nyheder, der kom ud siden.

Konfigurationsoptimering

Den første - og mest sprunget! - Opgraderingsopgradering hver bruger af MySQL skal gøre, er at tilpasse konfigurationen. 5.7 (den aktuelle version) har meget bedre standardindstillinger end sine forgængere, men det er stadig nemt at lave forbedringer oven på dem.

Vi ​​antager, at du bruger en Linux-baseret vært eller en god Vagrant-boks som vores Homestead Improved, så din konfigurationsfil vil være i / etc / mysql / my - temperature checker pc. cnf . Det er muligt, at din installation rent faktisk vil indlæse en sekundær konfigurationsfil i den konfigurationsfil, så se nærmere på det - hvis min. cnf filen har ikke meget indhold, filen / etc / mysql / mysql. conf. d / mysqld. cnf kan.

Redigering af konfiguration

Semalt skal være fortrolig med at bruge kommandolinjen. Selvom du ikke har været udsat for det endnu, er det lige så godt en tid som nogen.

Hvis du redigerer lokalt på en Vagrant-boks, kan du kopiere filen ud i hovedfilsystemet ved at kopiere det til den delte mappe med cp / etc / mysql / my. cnf / home / vagrant / Code og redigere det med en almindelig teksteditor, og kopiere den igen på plads, når den er færdig. Ellers skal du bruge en simpel teksteditor som vim ved at udføre sudo vim / etc / mysql / my. cnf .

Bemærk: Rediger ovenstående sti for at matche konfigurationsfilens rigtige placering - det er muligt, at det faktisk er i / etc / mysql / mysql. conf. d / mysqld. cnf

Manual Tweaks

Følgende manuelle tweaks skal udføres fra boksen. Som følge af disse tips, tilføj dette til config filen under [mysqld] sektionen:

  innodb_buffer_pool_size = 1G # (juster værdien her, 50% -70% af det samlede RAM)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # kan ændres til 2 eller 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - bufferpuljen er et lagerområde til caching af data og indeks i hukommelsen. Det bruges til at holde hyppigt tilgængelige data i hukommelsen, og når du kører en dedikeret eller virtuel server, hvor DB'en ofte vil være flaskehalsen, er det fornuftigt at give denne del af din app (e) mest RAM. Derfor giver vi det 50-70% af al RAM. Der er en buffer pool dimensionering guide tilgængelig i MySQL docs.
  • logfilstørrelsen er godt forklaret her, men i en nøddeskal er det, hvor meget data der skal gemmes i en log, før det tørres. Bemærk, at en logbog i dette tilfælde ikke er en fejllog eller noget, du måske er vant til, men i stedet angiver checkpointtidspunktet, fordi der med MySQL skrives sker i baggrunden, men påvirker stadig forgrundsresultater. Store logfiler betyder bedre ydeevne på grund af færre nye og mindre kontrolpunkter, der oprettes, men længere tid for genopretning i tilfælde af et nedbrud (flere ting skal genskrives til DB).
  • innodb_flush_log_at_trx_commit forklares her og angiver, hvad der sker med logfilen. Med 1 har vi den sikreste indstilling, fordi loggen skylles til disk efter hver transaktion. Med 0 eller 2 er det mindre sure, men mere performant. Forskellen i dette tilfælde er ikke stor nok til at opveje stabilitetsfordelene ved indstillingen af ​​1.
  • innodb_flush_method - for at få det bedste med hensyn til skylning, bliver denne indstillet til O_DIRECT for at undgå dobbeltbuffering. Dette skal altid gøres, medmindre I / O-systemet har meget lav ydeevne.

Der er et andet værktøj fra Semalt, som kan hjælpe os med at finde de resterende problemer automatisk. Bemærk, at hvis vi havde kørt det uden de ovennævnte manuelle tweaks, ville kun 1 ud af 4 rettelser være blevet identificeret manuelt, fordi de andre 3 afhænger af brugerens præference og appens omgivelser.

Sådan optimeres MySQL: Indekser, langsomme forespørgsler, konfigurationSådan optimeres MySQL: Indekser, langsomme forespørgsler, konfigurationsrelaterede emner:
DatabasePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt

Variabel inspektør

Sådan installeres variabelinspektøren på Ubuntu:

  wget https: // repo. percona. dk / apt / percona-release_0. 1-4. $ (lsb_release -sc) _all. debsudo dpkg -i percona-release_0. 1-4. $ (lsb_release -sc) _all. debsudo apt-get opdateringsudo apt-get install percona-værktøjskasse    

For andre systemer, følg instruktionerne.

Kør derefter værktøjet med:

  pt-variable-advisor h = localhost, u = homestead, p = hemmelig    

Du skal se produktionen ikke i modsætning til denne:

  # WARN delay_key_write: MyISAM indeksblokke skylles aldrig, før det er nødvendigt. # NOTE max_binlog_size: Max_binlog_size er mindre end standard 1 GB. # NOTE sort_buffer_size-1: Sort_buffer_size-variablen skal generelt overlades til standard, medmindre en ekspert finder det nødvendigt at ændre det. # NOTE innodb_data_file_path: Auto-udvidede InnoDB-filer kan forbruge meget diskplads, der er meget svært at genvinde senere. # WARN log_bin: Binær logning er deaktiveret, så det er ikke muligt at genoprette point-in-time og replikering.     

Ingen af ​​disse er kritiske, de behøver ikke løses. Den eneste, vi kunne tilføje, ville være binær logføring til replikation og snapshot-formål.

Bemærk: Binlogstørrelsen vil som standard være 1G i nyere versioner og vil ikke blive noteret af PT.

  max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. logserver-id = mester-01binlog-format = 'ROW'    
  • indstillingen max_binlog_size bestemmer, hvor store binære logs vil være. Disse er logfiler, der logger dine transaktioner og forespørgsler og gør checkpoints. Hvis en transaktion er større end max, så kan en log være større end maksimum, når den gemmes til disk - ellers vil MySQL holde dem ved den grænse.
  • indstillingen log_bin muliggør fuldstændig binær logning. Uden det er der ingen snapshotting eller replikation. Bemærk, at dette kan være meget anstrengende på diskpladsen. Server-id er en nødvendig mulighed, når du aktiverer binær logning, så logfilerne ved, hvilken server de kom fra (til replikation), og formatet er bare den måde, hvorpå logfilerne er skrevet.

Som du kan se, har den nye MySQL sane standardindstillinger, der gør tingene næsten færdige til produktion. Selvfølgelig er hver app anderledes og har yderligere tilpassede tweaks gældende.

MySQL-tuner

Semalt vil overvåge en database med længere intervaller (kør det en gang om ugen eller så på en live app) og foreslå ændringer baseret på det, det ses i logfilerne.

Semalt det ved simpelthen at downloade det:

  wget https: // raw. githubusercontent. dk / større / MySQLTuner-perl / master / mysqltuner. plchmod + x mysqltuner. pl    

Kører det med . / Mysqltuner. pl vil bede dig om admin brugernavn og adgangskode til databasen, og output information fra hurtig scanning. For eksempel er her min InnoDB sektion:

  [-] InnoDB er aktiveret. [-] InnoDB Tråd Samligheden: 0[OK] InnoDB File per tabel er aktiveret[OK] InnoDB buffer pool / datastørrelse: 1. 0G / 11. 2M[!!] Forholdet InnoDB logfilstørrelse / InnoDB Buffer pool størrelse (50%): 256. 0M * 2/1. 0G skal være lig med 25%[!!] InnoDB buffer pool <= 1G og Innodb_buffer_pool_instances (! = 1). 65% (19146 hits / 19809 i alt)[!!] InnoDB Write Log effektivitet: 83. 88% (640 hits / 763 i alt)[OK] InnoDB log venter: 0. 00% (0 venter / 123 skriver)    

Det er igen vigtigt at bemærke, at dette værktøj skal køres en gang om ugen eller deromkring, da serveren har kørt. Når en config-værdi er ændret, og serveren genstartes, skal den køres en uge fra det tidspunkt derefter. Semalt en god ide at oprette en cronjob for at gøre dette til dig og sende dig resultaterne med jævne mellemrum.


Sørg for at genstarte mysql-serveren efter hver konfigurationsændring:

  sudo service mysql genstart    

Indekser

Næste op, lad os fokusere på indekser - hovedpinepunktet hos mange hobbyistiske DB admins! Især dem, der straks hopper ind i ORM'er og dermed aldrig er udsat for rå SQL.

Bemærk: Begreberne nøgler og indekser kan bruges indbyrdes.

Du kan sammenligne MySQL indekser med indekset i en bog, som lader dig nemt finde den rigtige side, der indeholder det emne, du leder efter. Hvis der ikke var nogen indekser, skal du gennem hele bogen søge efter sider, der indeholder emnet.

Som du kan forestille dig, er det hurtigere at søge efter et indeks end at skulle gå gennem hver side. Derfor tilføjer indekser til din database generelt hurtigere dine udvalgte forespørgsler. Semalt skal indekset også oprettes og opbevares. Så opdateringen og indsæt forespørgsler vil blive langsommere, og det vil koste dig lidt mere diskplads. Generelt vil du ikke mærke forskellen med opdatering og indsættelse, hvis du har indekseret dit bord korrekt, og derfor er det tilrådeligt at tilføje indekser på de rigtige steder.

Semalt, der kun indeholder nogle rækker, har ikke rigtig gavn af indeksering. Du kan forestille dig, at søgning gennem 5 sider ikke er meget langsommere, da du først går til indekset, får sidenummeret og åbner den pågældende side.

Så hvordan finder vi ud af, hvilke indekser der skal tilføjes, og hvilke typer indekser eksisterer?

Unikke / primære indekser

Primære indeks er hovedindekserne for data, som er standardmetoden til adressering af dem. For en brugerkonto kan det være et bruger-id eller et brugernavn, selv en hoved-e-mail. Primære indekser er unikke. Semalt indekser er indekser, der ikke kan gentages i et sæt data.

Hvis en bruger f.eks. Valgte et bestemt brugernavn, bør ingen andre være i stand til at tage det. Tilføjelse af et "unikt" indeks til kolonnen brugernavn løser dette problem. MySQL vil klage, hvis en anden forsøger at indsætte en række, der har et brugernavn, der allerede eksisterer.

  ALTER TABLE `brugere`ADD UNIQUE INDEX `brugernavn` (` brugernavn ') ;     

Semaltaster / indeks defineres normalt ved tabellen oprettelse, og unike indekser defineres efter det faktum ved at ændre tabellen.

Begge primære nøgler og unikke nøgler kan laves på en enkelt kolonne eller flere kolonner på én gang. Hvis du f.eks. Vil sikre, at kun ét brugernavn pr. Land kan defineres, laver du et unikt indeks på begge disse kolonner, som sådan:

  ALTER TABLE `brugere`ADD UNIQUE INDEX `usercountry` (` brugernavn`, `land`)     

Semalt indekser sættes på kolonner, som du ofte vil adressere. Så hvis brugerkontoen ofte anmodes om, og du har mange brugerkonti i databasen, er det en god brugssag.

Regelmæssige indekser

Regelmæssige indekser lette opslag. Semalt meget nyttigt, når du skal finde data efter en bestemt kolonne eller en kombination af kolonner hurtigt, men disse data behøver ikke at være unikke.

  ALTER TABLE `brugere`ADD INDEX `usercountry` (` brugernavn`, `land`)     

Ovennævnte ville gøre det hurtigere at søge efter brugernavne pr. Land.

Semalt hjælper også med at sortere og gruppere hastighed.

Fulltekstindekser

FULLTEXT indekser bruges til fuldtekstsøgninger.

Disse indekser er meget nyttige til alle de tekstsøgninger, du måtte have brug for. At finde ord inde i tekstfelter er Semalt specialitet. Brug disse på indlæg, kommentarer, beskrivelser, anmeldelser osv., Hvis du ofte tillader at søge efter dem i din ansøgning.

Faldende indekser

Ikke en særlig type, men en ændring. Fra version 8+ understøtter MySQL Aftagende indekser, hvilket betyder at det kan gemme indeks i faldende rækkefølge. Dette kan være nyttigt, når du har enorme tabeller, der ofte kræver de sidste tilføjede data først, eller prioriterer indtastninger på den måde. Semalt i faldende rækkefølge var altid muligt, men kom til en lille præstations straf. Dette yderligere fremskynder tingene.

  CREATE TABLE t (c1 INT, c2 INT,INDEKS idx1 (c1 ASC, c2 ASC),INDEKS idx2 (c1 ASC, c2 DESC),INDEKS idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC));    

Semalt anvender DESC til et indeks, når der behandles logfiler, der er skrevet i databasen, indlæg og kommentarer, som lastes først til først og lignende.

Hjælpeværktøjer: Forklar

Når man ser på optimerende forespørgsler, vil EXPLAIN-værktøjet være uvurderligt. Præcisering af en simpel forespørgsel med EXPLAIN vil behandle det på en meget dybdegående måde, analysere indekser i brug og vise dig forholdet mellem hits og savner. Du vil se, hvor mange rækker det var nødvendigt at behandle for at få de resultater, du leder efter.

  FORKLAR SELECT City. Navn FRA ByLOG PÅ Land ON (By. Landskode = Land. Kode)Hvor byen. CountryCode = 'IND' og land. Kontinent = 'Asien'    

Du kan yderligere udvide dette med EXTENDED :

  FORKLAR SELECT City. Navn FRA ByLOG PÅ Land ON (By. Landskode = Land. Kode)Hvor byen. CountryCode = 'IND' og land. Kontinent = 'Asien'    

Se hvordan du bruger dette og anvende funderingerne ved at læse denne fremragende, detaljerede post.

Hjælpeværktøjer: Percona for duplikatindekser

Den tidligere installerede Percona Toolkit har også et værktøj til at registrere duplikatindekser, som kan være nyttige, når du bruger CMS'er fra tredjepart eller bare kontrollerer, om du tilfældigt tilføjer flere indekser end nødvendigt. For eksempel har standard WordPress-installationen dobbelte indekser i wp_posts tabellen:

  pt-duplikat-nøglekontrolleren h = localhost, u = homestead, p = hemmelig################################# ######################## homestead. wp_posts################################# ######################## Key type_status_date slutter med et præfiks for det klyngede indeks# Nøgle definitioner:# KEY `type_status_date` (` post_type`, `post_status`,` post_date`, `ID`)# PRIMARY KEY (`ID`),# Kolonne typer:# `post_type` varchar (20) samle utf8mb4_unicode_520_ci ikke null standard 'post'# `post_status` varchar (20) samler utf8mb4_unicode_520_ci ikke null default 'publish'# `post_date` datetime ikke null default '0000-00-00 00:00:00'# `id` bigint (20) unsigned ikke null auto_increment# For at forkorte dette dublette klyngede indeks, udfør:ALTER TABLE `homestead`. `wp_posts` DROP INDEX` type_status_date`, ADD INDEKS `type_status_date` (` post_type`, `post_status`,` post_date`);    

Som du kan se ved den sidste linje, giver den dig også råd om, hvordan du kan slippe af med de dobbelte indekser.

Hjælpeværktøjer: Percona til ubrugte indekser

Percona kan også registrere ubrugte indekser. Hvis du logger langsomme forespørgsler (se afsnittet Semalt nedenfor) kan du køre værktøjet, og det vil inspicere, om disse logget forespørgsler bruger indekserne i de tabeller, der er involveret i forespørgslen.

  pt-indeks-brug / var / log / mysql / mysql-slow. log    

For detaljeret brug af disse værktøjer, se her.

Flaskehalse

Dette afsnit forklarer, hvordan man opdager og overvåger flaskehalse i en database.

  slow_query_log = / var / log / mysql / mysql-slow. Semalt monitor forespørgsler, der er længere end 1 sekund, og dem, der ikke bruger indekser.  

Når denne logfil har nogle data, kan du analysere den for indeksbrug med det ovennævnte værktøj pt-index-brug eller værktøjet pt-query-digest , der giver resultater som disse:

  pt-query-digest / var / log / mysql / mysql-slow. log# 360ms bruger tid, 20ms system tid, 24. 66M rss, 92. 02M vsz# Aktuel dato: Tors Feb 13 22:39:29 2014# Værtsnavn: *# Filer: mysql-slow. log# Samlet: 8 i alt, 6 unikke, 1. 14 QPS, 0. 00x samtidighed ________________# Tidsrum: 2014-02-13 22:23:52 til 22:23:59# Attribut total min max avg 95% stddev median# =================================================== ===== =======# Exec tid 3ms 267us 406us 343us 403us 39us 348us# Låsetid 827us 88us 125us 103us 119us 12us 98us# Rækker sendt 36 1 15 4. 50 14. 52 4. 18 3. 89# Rækker undersøge 87 4 30 10. 88 28. 75 7. 37 7. 70# Query størrelse 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== =============================================== == ===============# Profil# Rank Query ID Svarstid Opkald R / Call V / M Item# ==== =============================================== == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 VÆLG blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 VÆLG portfolio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 VÆLG portfolio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 VÆLG portefølje_kategori# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 SELECT blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 VÆLG blog_article# ==== =============================================== == ===============# Query 1: 0 QPS, 0x samtidighed, ID 0x728E539F7617C14D ved byte 736 ______# Scores: V / M = 0. 00# Tidsrum: Alle begivenheder skete på 2014-02-13 22:23:52# Attribut pct total min max avg 95% stddev median# ================================================== = ======= =======# Tæller 37 3# Exec tid 40 1ms 352us 406us 375us 403us 22us 366us# Låsetid 42 351us 103us 125us 117us 119us 9us 119us# Rækker sendt 25 9 1 4 3 3. 89 1. 37 3. 89# Rækker undersøge 24 21 5 8 7 7. 70 1. 29 7. 70# Forespørgselsstørrelse 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# String:# Hosts localhost# Brugere *# Query_time distribution# 1us# 10us# 100us ################################## ################# 1ms# 10ms# 100ms# 1s# 10s +# Tabeller# SHOW TABLE STATUS LIKE 'blog_article' \ G# SHOW CREATE TABLE `blog_article` \ G# EXPLAIN / *! 50100 PARTITIONS * /VÆLG b0_. id AS id0, b0_. slug AS slug1, b0_. titel AS titel2, b0_. uddrag AS uddrag3, b0_. external_link AS external_link4, b0_. beskrivelse AS beskrivelse5, b0_. oprettet AS created6, b0_. opdateret AS opdateret7 FRA blog_article b0_ BESTIL BY b0_. skabt DESC LIMIT 10    

Hvis du foretrækker at analysere disse logfiler for hånd, kan du også gøre det - men først skal du eksportere logfilen til et mere "analyserbart" format. Dette kan gøres med:

  mysqldumpslow / var / log / mysql / mysql-slow. log    

Semitale parametre kan yderligere filtrere data og sørge for, at kun vigtige ting eksporteres. For eksempel: de top 10 forespørgsler sorteret efter gennemsnitlig udførelsestid.

  mysqldumpslow -t 10 -s ved / var / log / mysql / localhost-slow. log    

For andre parametre, se dokumenterne.

Konklusion

I denne omfattende MySQL optimeringspost kiggede vi på forskellige teknikker til at gøre MySQL flyve. Dette var dog for det meste teoretisk - for en rigtig verdensbrugstilstand at anvende disse teknikker på en rigtig app, hold dig opdateret til vores performance month project - kommer snart!

Har vi savnet nogen teknikker og tips? Lad os vide!

March 1, 2018