Introduktion

Innan vi börjar måste du förstå de faktorer som påverkar MySQL-prestanda, så att du kan lära dig att ställa in och optimera MySQL-servern på rätt sätt och få rätt resultat. De viktigaste faktorerna inkluderar:


  • Volym av data som hämtas
  • Resurser tillgängliga, dvs. CPU, minne (RAM)
  • Belastning som drivs av MySQL-servern>

Orsaker till dålig MySQL-serverprestanda inkluderar:

  • Dålig databasdesign
  • Flaskhalsar för hårdvara
  • Dålig kodning
  • Dålig indexering
  • Ineffektiv konfiguration

Speciell anmärkning: Som ni ser kan flera av dessa faktorer åtgärdas genom att uppgradera till en bättre webbhotelltjänst. HostAdvice ger dig de bästa leverantörerna av MySQL-värd baserat på expert- och användarrecensioner.

Installera mysqltuner och tuning-primer

Installera mysqltuner

Mysqltuner är ett högpresterande MySQL-inställning av Perl-skript som ger en ögonblicksbild av MySQL-serverns hälsostatus och ger specifika rekommendationer för förbättrad, ökad prestanda, stabilitet och effektivitet.

Kör kommandona nedan för att installera mysqltuner

$ cd / tmp
$ sudo wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl – tyst – ingen-check-certifikat –output-document = / usr / local / bin / mysqltuner
$ sudo chmod u + x / usr / local / bin / mysqltuner

Installera tuning-primer

Tuning Primer är ett skalskript som får information från interna MySQL-server och ger rekommendationer om justeringar av servervariablerna.

Kör kommandona nedan för att installera stämprimer:

$ sudo apt install bc -y
$ sudo wget http://www.day32.com/MySQL/tuning-primer.sh –output-document = / usr / local / bin / tuning-primer
$ sudo chmod u + x / usr / local / bin / tuning-primer

Ställa in och optimera mysql-servern

Stämningen av MySQL-servern görs på MySQL-konfigurationsfilen.

/etc/mysql/mysql.conf.d/mysqld.cnf

Kör mysqltuner.

$ sudo mysqltuner
$ sudo mysqltuner
>> MySQLTuner 1.7.9 – Major Hayden
>> Bugrapporter, funktionsbegäranden och nedladdningar på http://mysqltuner.com/
>> Kör med ‘–help’ för ytterligare alternativ och outputfiltrering

[-] Hoppad versionskontroll för MySQLTuner-skript
[OK] Loggat in med referenser från debian underhållskonto.
[OK] För närvarande körs MySQL version 5.7.22-0ubuntu18.04.1
[OK] Arbetar med 64-bitars arkitektur

——– Loggfil Rekommendationer ——————————————————————
[-] Loggfil: /var/log/mysql/error.log(18K)
[OK] Loggfil /var/log/mysql/error.log finns
[OK] Loggfil /var/log/mysql/error.log är läsbar.
[OK] Loggfilen /var/log/mysql/error.log är inte tom
[OK] Loggfilen /var/log/mysql/error.log är mindre än 32 Mb
[!!] /var/log/mysql/error.log innehåller 14 varningar.
[!!] /var/log/mysql/error.log innehåller 3 fel (er).
[-] 3 start (er) detekterade i /var/log/mysql/error.log
[-] 1) 2018-06-06T17: 55: 47.928847Z 0 [Obs] / usr / sbin / mysqld: redo för anslutningar.
[-] 2) 2018-06-06T17: 55: 44.798684Z 0 [Obs] mysqld: redo för anslutningar.
[-] 3) 2018-06-06T17: 55: 41.931105Z 0 [Obs] mysqld: redo för anslutningar.
[-] 2 avstängning (er) detekterade i /var/log/mysql/error.log
[-] 1) 2018-06-06T17: 55: 46.410548Z 0 [Obs] mysqld: Shutdown complete
[-] 2) 2018-06-06T17: 55: 43.758366Z 0 [Obs] mysqld: Shutdown complete

——– Lagringsmotorstatistik —————————————————————–
[-] Status: + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MEMORY + MRG_MYISAM + MyISAM + PERFORMANCE_SCHEMA
[-] Data i InnoDB-tabeller: 16K (tabeller: 1)
[OK] Totalt fragmenterade tabeller: 0

——– Säkerhetsrekommendationer ——————————————————————
[OK] Det finns inga anonyma konton för databasanvändare
[OK] Alla databasanvändare har lösenord tilldelade
[-] Bug # 80860 MySQL 5.7: Undvik att testa lösenord när validate_password är aktiverat

——– CVE-säkerhetsrekommendationer ————————————————————–
[-] Hoppad på grund av alternativet – cvefile undefined

——– Prestandamätningar ———————————————————————–
[-] Upp till: 9m 8s (110 q [0.201 qps], 38 conn, TX: 187K, RX: 10K)
[-] Läser / skriver: 98% / 2%
[-] Binär loggning är inaktiverad
[-] Fysiskt minne: 3,6 G
[-] Max MySQL-minne: 352,4M
[-] Annat processminne: 175,4M
[-] Totala buffertar: 192,0M global + 1,1 M per tråd (151 max trådar)
[-] P_S Max minnesanvändning: 72B
[-] Galera GCache Max minnesanvändning: 0B
[OK] Maximal uppnådd minnesanvändning: 194,1 M (5,26% av installerat RAM)
[OK] Maximal möjlig minnesanvändning: 352,4M (9,54% av installerat RAM)
[OK] Övergripande möjlig minnesanvändning med annan process är kompatibel med tillgängligt minne
[OK] Långsamma frågor: 0% (0/110)
[OK] Högsta användningen av tillgängliga anslutningar: 1% (2/151)
[OK] Aborterade anslutningar: 0,00% (0/38)
[!!] namnupplösning är aktiv: en omvänd namnupplösning görs för varje ny anslutning och kan minska prestandan
[!!] Fråga-cache kan som standard inaktiveras på grund av mutex-strid.
[!!] Effektivitet för fråga cache: 0,0% (0 cache / 49 välj)
[OK] Query-cache beskär per dag: 0
[OK] Sorter som kräver tillfälliga tabeller: 0% (0 temp / 2 sorter)
[OK] Inga anslutningar utan index
[OK] Tillfälliga tabeller skapade på disken: 4% (36 på disken / 846 totalt)
[OK] Trådcachehastighet: 94% (2 skapade / 38 anslutningar)
[OK] Treffminne för tabellcache: 95% (591 öppen / 620 öppnad)
[OK] Öppen filgräns används: 1% (50 / 5K)
[OK] Bordslås förvärvade omedelbart: 100% (158 omedelbara / 158 lås)

——– Prestationsschema ————————————————————————
[-] Minne som används av P_S: 72B
[-] Sys-schema är installerat.

——– ThreadPool Metrics ————————————————————————
[-] ThreadPool-stat är inaktiverad.

——– MyISAM Metrics —————————————————————————-
[!!] Använd nyckelbuffert: 18,3% (3M använt / 16M cache)
[OK] Nyckelbuffertstorlek / totala MyISAM-index: 16,0M / 43,0K
[!!] Läs nyckelbuffert-träfffrekvens: 94,1% (118 cachelagrat / 7 läsningar)

——– InnoDB Metrics —————————————————————————-
[-] InnoDB är aktiverat.
[-] InnoDB trådgiltighet: 0
[OK] InnoDB-fil per tabell är aktiverad
[OK] InnoDB buffertpool / datastorlek: 128,0M / 16,0K
[!!] Ratio InnoDB loggfilstorlek / InnoDB buffertpoolstorlek (75%): 48,0 M * 2 / 128,0 M bör vara lika med 25%
[OK] InnoDB buffertpool instanser: 1
[-] Antal InnoDB Buffer Pool Chunk: 1 för 1 Buffer Pool Instance (s)
[OK] Innodb_buffer_pool_size anpassad till Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Läs bufferteffektivitet: 88,39% (1933 träffar / 2187 totalt)
[!!] InnoDB Skrivloggeffektivitet: 0% (5 träffar / 0 totalt)
[OK] InnoDB-logg väntar: 0,00% (0 väntar / 5 skriver)

——– AriaDB Metrics —————————————————————————-
[-] AriaDB är inaktiverat.

——– TokuDB Metrics —————————————————————————-
[-] TokuDB är inaktiverat.

——– XtraDB Metrics —————————————————————————-
[-] XtraDB är inaktiverat.

——– RocksDB Metrics —————————————————————————
[-] RocksDB är avaktiverat.

——– Spindelmetrik —————————————————————————-
[-] Spindel är inaktiverad.

——– Anslut statistik —————————————————————————
[-] Anslut är inaktiverat.

——– Galera Metrics —————————————————————————-
[-] Galera är inaktiverat.

——– Replikeringsmätningar ———————————————————————–
[-] Galera synkron replikering: NEJ
[-] Inga replikationsslavar för denna server.
[-] Binlogformat: ROW
[-] XA-support aktiverat: ON
[-] Semi synkron replikering Master: Not Activated
[-] Semi synkron replikering Slave: Inte aktiverad
[-] Detta är en fristående server

——– rekommendationer —————————————————————————
Allmänna rekommendationer:
Kontrollera varningslinjer i /var/log/mysql/error.log-filen
Kontrollera felrad (er) i /var/log/mysql/error.log-filen
MySQL startades inom det senaste dygnet – rekommendationer kan vara felaktiga
Konfigurera dina konton endast med ip eller subnät, uppdatera sedan din konfiguration med skip-name-resol = 1
Innan du byter innodb_log_file_size och / eller innodb_log_files_in_group läs detta: http://bit.ly/2wgkDvS
Variabler att justera:
fråga_cache_storlek (= 0)
query_cache_type (= 0)
query_cache_limit (> 1M, eller använd mindre resultatuppsättningar)
innodb_log_file_size ska vara (= 16M) om möjligt, så InnoDBs totala loggfilstorlek motsvarar 25% av buffertpoolstorleken.

Fokusera på den sista delen av utgången i avsnittet ”Rekommendationer” och kontrollera variablerna som måste ökas.

Skapa en säkerhetskopia av konfigurationsfilen för mysql och redigera den faktiska konfigurationsfilen.

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Justera filens variabel enligt rekommendationerna. Du kan kommentera standardvariabla värden och öka variabelvärdena till två gånger standardvärdet. Det kan faktiskt ta några dagar att komma fram till de bästa värdena för din server.

Starta om mysql-servern när du har gjort ändringarna.

$ sudo systemctl starta om mysql

Särskild anmärkning: Om det inte lyckas starta om, gå tillbaka och kommentera de nya värdena och börja justera en efter en och se vilken variabel justering som orsakar serverns fel

Om servern lyckas laddas om igen, kör mysqltuner igen och se om det finns fler rekommendationer om justering av variabla värden.

Gör liknande process för att ställa in primer.

Kör avstämningsbäraren

$ sudo tuning-primer
$ sudo tuning-primer

— MYSQL PERFORMANCE TUNING PRIMER —
– Av: Matthew Montgomery –

MySQL version 5.7.22-0ubuntu18.04.1 x86_64

Drifttid = 0 dagar 0 timmar 1 minut 49 sek
Avg. qps = 0
Totala frågor = 15
Trådar anslutna = 1

Varning: Servern har inte körts i minst 48 timmar.
Det kan inte vara säkert att använda dessa rekommendationer

För att ta reda på mer information om var och en av dessa
runtime variables påverkar prestanda besök:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
Besök http://www.mysql.com/products/enterprise/advisors.html
för information om MySQL: s Enterprise Monitoring and Advisory Service

LÅGA FRÅGOR
Den långsamma frågeloggen är INTE aktiverad.
Aktuell long_query_time = 10.000000 sek.
Du har 0 av 36 som tar längre tid än 10,000000 sek. att slutföra
Din long_query_time verkar vara bra

BINARY UPDATE LOG
Den binära uppdateringsloggen är INTE aktiverad.
Du kommer inte att kunna göra tidpunkten för återhämtning
Se http://dev.mysql.com/doc/refman/5.7/sv/point-in-time-recovery.html

ARBETARSTRÅDAR
Nuvarande thread_cache_size = 8
Nuvarande threads_cached = 0
Nuvarande threads_per_sec = 0
Historiska threads_per_sec = 0
Din thread_cache_storlek är bra

MAX-ANSLUTNINGAR
Nuvarande max_connections = 151
Nuvarande thread_connected = 1
Historiska max_used_connections = 1
Antalet använda anslutningar är 0% av det konfigurerade maximumet.
Du använder mindre än 10% av dina konfigurerade max_connections.
Att sänka max_connections kan hjälpa till att undvika en överfördelning av minne
Ser "MINNESANVÄNDNING" för att se till att du inte överfördelar

Inget InnoDB-stöd aktiverat!

MINNESANVÄNDNING
Max minne tilldelat någonsin: 177 M
Konfigurerade max per-tråd buffertar: 160 M
Konfigurerade Max Global Buffers: 176 M
Konfigurerad maxminnesgräns: 336 M
Fysiskt minne: 3,60 G
Max minnesgräns verkar ligga inom acceptabla normer

NYCKELBUFFER
Nuvarande MyISAM-indexutrymme = 43 K
Aktuell key_buffer_size = 16 M
Nyckelcache-missfrekvens är 1: 2
Nyckelbuffertfria förhållande = 81%
Din key_buffer_storlek verkar vara bra

QUERY CACHE
Fråga-cache är aktiverat
Aktuell fråga_cache_storlek = 16 M
Nuvarande query_cache_used = 16 K
Aktuell fråga_cache_limit = 1 M
Aktuell fråga-cache Minnefyllningsförhållande = .10%
Aktuell fråga_cache_min_res_unit = 4 K
Din fråga_cache_storlek verkar vara för hög.
Kanske kan du använda dessa resurser någon annanstans
MySQL cachar inte frågeställningsresultat som är större än query_cache_limit i storlek

SORTA VERKSAMHET
Aktuell sort_buffer_storlek = 256 K
Aktuell read_rnd_buffer_size = 256 K
Inga sorteringsoperationer har utförts
Sorteringsbuffert verkar vara bra

ANSLUTER SIG
Nuvarande join_buffer_size = 260,00 K
Du har haft 0 frågor där en anslutning inte kunde använda ett index korrekt
Dina sammanfogningar verkar använda index korrekt

ÖPPEN FILS-BEGRÄNSNING
Nuvarande open_files_limit = 5000 filer
Open_files_limit bör vanligtvis ställas in på minst 2x-3x
det för table_cache om du har tung MyISAM-användning.
Ditt open_files_limit-värde verkar vara bra

TABELL KASSA
Nuvarande tabell_open_cache = 2000 tabeller
Nuvarande table_definition_cache = 1400 tabeller
Du har totalt 119 bord
Du har 249 öppna bord.
Table_cache-värdet verkar vara bra

TEMPELBORD
Aktuell max_heap_table_size = 16 M
Aktuell tmp_table_size = 16 M
Av 361 temp-tabeller skapades 6% på disken
Skapat-tmp-tabellförhållandet verkar bra

Bordsskanningar
Aktuell read_buffer_size = 128 K
Aktuellt tabellskanningsförhållande = 673: 1
read_buffer_size verkar vara bra

LÅSNING TABELL
Aktuellt låsväntsförhållande = 0: 219
Ditt låsning av bordet verkar vara bra

Speciell anmärkning: Fokusera mer på den röda och gula markerade texten eftersom den ger rekommendationer om hur man optimerar och förbättrar mysql-prestanda.

Justera filens variabel enligt rekommendationerna. Du kan kommentera standardvariabla värden och justera variabelvärdena som föreslagits i rekommendationerna.

Starta om MySQL-servern när du har gjort ändringarna.

$ sudo systemctl starta om mysql

Särskild anmärkning: Om den inte startar om, gå tillbaka och kommentera de nya värdena och börja justera en efter en och se vilken variabel justering som orsakar serverfel.

Om servern framgångsrikt startar om, kör om tuneprimer och se om det finns fler rekommendationer angående justering av variabla värden.

Slutsats

Det är mycket viktigt att notera att avstämningsverktygen mysqltuner eller tuning-primer inte är lösningarna på dåligt fungerande MySQL-server, utan snarare skript som hjälper dig att finjustera MySQL-servern.

För bästa prestanda,

  • Gör en grundlig granskning av frågor som skickas till servern och optimera databasstruktur / design och SQL-fråga
  • Utvärdera faktorer som påverkar MySQL-prestanda som förklarats i inledningen.
  • Överväg att uppgradera maskinvaran på den fysiska servern för bättre prestanda och optimera MySQL-konfigurationer baserat på hårdvaruspecifikationerna.
  • Gör mer forskning om hur man ställer in MySQL-konfigurationer baserat på applikationer som använder MySQL-servern.

Det rekommenderas starkt att regelbundet köra mysqltuner eller tuningprimer-verktyg över 48 timmar efter att ha startat om MySQL-servern för att få en meningsfull användningsstatistik.

Kolla in de tre bästa MySQL-värdstjänsterna:

A2 Hosting

Startpris:
$ 3,92


Pålitlighet
9,3


Prissättning
9,0


Användarvänlig
9,3


Stöd
9,3


Funktioner
9,3

Läs recensioner

Besök A2 Hosting

FastComet

Startpris:
$ 2.95


Pålitlighet
9,7


Prissättning
9,5


Användarvänlig
9,7


Stöd
9,7


Funktioner
9,6

Läs recensioner

Besök FastComet

Hostinger

Startpris:
$ 0.99


Pålitlighet
9,3


Prissättning
9,3


Användarvänlig
9,4


Stöd
9,4


Funktioner
9,2

Läs recensioner

Besök Hostinger

Relaterade artiklar om hur man gör

  • Hur man ställer in och optimerar prestanda för MySQL 8.0 på en CentOS 7-server
    expert-
  • Säkerhetskopiera din MySQL-databas på en Ubuntu 18.04 VPS eller dedikerad server
    mellanliggande
  • Hur man installerar MYSQL 8.0 och skapar en databas på en Ubuntu 18.04 Linux VPS
    mellanliggande
  • Hur du inaktiverar MySQL 5 "Strikt läge" på en Ubuntu 18.04 VPS eller dedikerad server
    mellanliggande
  • Hur man installerar PhpMyAdmin på en Ubuntu 18.04 VPS eller en dedikerad server
    mellanliggande
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me