Com afinar i optimitzar el rendiment de MySQL 8.0 en un servidor CentOS 7

Introducció

Abans de començar, heu d’entendre els factors que afecten el rendiment de MySQL, de manera que podreu aprendre a ajustar i optimitzar el servidor MySQL de la manera correcta i obtenir els resultats adequats. Els principals factors són:


  • Volum de dades a recuperar>
  • Recursos disponibles, és a dir, CPU, memòria (RAM)
  • Càrrega gestionada pel servidor MySQL

Entre les causes del mal rendiment del servidor MySQL es troben:

  • Disseny deficient de bases de dades
  • Bloqueigs d’ampolla de maquinari
  • Mala codificació
  • Mala indexació
  • Configuració ineficient

En aquest article, il·lustrem com ajustar i optimitzar el rendiment de MySQL 8.0 a CentOS 7 Linux VPS.

Pre-requisits:

  • Configuració CentOS 7 VPS
  • S’ha instal·lat el servidor MySQL 8.0
  • Usuari creat amb privilegis sudo

Instal·lació de mysqltuner i primer d’afinació

Instal·leu mysqltuner

Mysqltuner és un script Perl d’ajustament de MySQL d’alt rendiment que proporciona una instantània de l’estat de salut del servidor MySQL i ofereix recomanacions específiques per millorar, augmentar el rendiment, l’estabilitat i l’eficiència..

Executeu les ordres a continuació per instal·lar mysqltuner

Primer instal·leu epel-release ja que el paquet mysqltuner es troba al dipòsit de release epel

$ sudo yum install -y-llançament de l’epel

A continuació, instal·la mysqltuner

$ sudo yum install -y mysqltuner

Instal·leu sintonia-primer

Tuning Primer és un script de shell que obté informació des del servidor MySQL intern i fa recomanacions sobre els ajustaments de les variables del servidor.

Executeu les ordres a continuació per instal·lar el primer d’afinació:

$ sudo yum instal·lar bc -y
$ git clone https://github.com/RootService/tuning-primer.git

Nota especial: Incase git no està instal·lat, podeu instal·lar-lo utilitzant

$ sudo yum instal·lar git -y

Accediu al directori d’inici de sintonització i feu que el script de primer d’afinació sigui executable de la següent manera:

$ cd afinació-primer
$ sudo chmod + x sintonització-primer.sh

Sintonització i optimització del servidor mysql

L’ajustament del servidor MySQL es fa al fitxer de configuració de MySQL.

/etc/my.cnf

Executa mysqltuner.

$ sudo mysqltuner

Introduïu l’usuari mysql amb tots els privilegis (en el nostre cas “root”) i contrasenya al sol·licitar-ho

[linuxuser @ centos7-hostadvice4 ~] $ sudo mysqltuner
Introduïu el vostre login administratiu de MySQL: root
Introduïu la vostra contrasenya administrativa de MySQL: >> MySQLTuner 1.6.0 – Major Hayden
>> Informes d’errors, sol·licituds de funcions i descàrregues a http://mysqltuner.com/
>> Executeu amb “–help” per obtenir opcions addicionals i filtrar la sortida
[-] Comprovació de la versió saltada de l’escriptura de MySQLTuner
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
[!!] Actualment s’executa la versió 8.0.11 de MySQL no compatible
[D’acord] Funcionant amb l’arquitectura de 64 bits

——– Estadístiques del motor d’emmagatzematge ——————————————-
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
[-] Estat: + ARXIU + BLACKHOLE + CSV -FEDERATED + InnoDB + MRG_MYISAM
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
[-] Dades de les taules InnoDB: 16K (Taules: 1)
[D’acord] Total taules fragmentades: 0
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.

——– Recomanacions de seguretat ——————————————-
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
[D’acord] No hi ha cap compte anònim en tots els usuaris de la base de dades
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
ERROR 1054 (42S22) a la línia 1: “contrasenya” de la columna desconeguda a “on es troba la clàusula”
[D’acord] Tots els usuaris de la base de dades tenen assignades contrasenyes
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
ERROR 1064 (42000) a la línia 1: teniu un error en la sintaxi SQL; consulteu el manual que correspon a la vostra versió del servidor MySQL per trobar la sintaxi adequada per utilitzar a prop de ‘(usuari) O CAST (contrasenya com a binària) = PASSWORD (UPPER (usuari)) O CAST (contrasenya com a B’ a la línia 1
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
[!!] No hi ha cap llista de fitxers de contrasenya bàsica !
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
mysql: [Advertència] L’ús d’una contrasenya a la interfície de la línia de comandaments pot ser insegur.
Utilització de valor addicional (+) addicionalment (+) a / bin / mysqltuner línia 1934, <>
línia 2 (# 1)
(W desinicialitzat) Es va utilitzar un valor no definit com si ja ho fos
definit. Es va interpretar com a "" o un 0, però potser va ser un error.
Per suprimir aquest avís, assigneu un valor definit a les vostres variables.

Per ajudar-vos a esbrinar què no estava definit, Perl tractarà de dir-vos
el nom de la variable (si n’hi ha) que no s’ha definit. En alguns casos
no pot fer-ho, així que també us indica quina operació heu fet servir
valor indefinit. Tingueu en compte, però, que el perl optimitza el vostre programa
ajudeu que l’operació mostrada en l’avís pot no necessàriament aparèixer
literalment al vostre programa. Per exemple, "que $ foo" sol ser
optimitzat en "això " . $ foo i l’avís es referirà a
operador de concatenació (.), tot i que no n’hi ha. a
el vostre programa.

Ús del valor no inicialitzat en la línia 1934 de divisió (/) a / bin / mysqltuner, <>
línia 2 (# 1)
Ús del valor no inicialitzat $ mystat {"Qcache_lowmem_prunes"} en eq numèric (==)
a / bin / mysqltuner línia 1949, <> línia 2 (# 1)

——– Mètriques de rendiment ————————————————-
[-] Potència: 1h 37m 3s (12 q [0.002 qps], 20 conn, TX: 20K, RX: 2K)
[-] Lectures / escrits: 100% / 0%
[-] La sessió binària està habilitada (GTID MODE: OFF)
[-] Total buffers: 168,0M global + 1,1M per fil (151 màxims de fil)
[D’acord] Ús màxim de la memòria assolida: 169,1M (4,78% de la memòria RAM instal·lada)
[D’acord] Ús màxim de memòria possible: 341,4M (9,65% de la memòria RAM instal·lada)
[D’acord] Consultes lentes: 0% (0/12)
[D’acord] Major ús de connexions disponibles: 0% (1/151)
[!!] Connexions anul·lades: 40,00% (8/20)
Ús de valor no inicialitzat $ myvar {"query_cache_size"} en lt numèric (<) a
/ bin / mysqltuner línia 2281, <> línia 2 (# 1)
[!!] La memòria cau de consulta està desactivada
[D’acord] Taules temporals creades al disc: 0% (0 al disc / 2 en total)
[D’acord] La taxa de col·locació de la memòria cau del fil: 95% (1 creades / 20 connexions)
[D’acord] Taxa de cop de memòria cau de la taula: 83% (118 oberts / 142 oberts)
[D’acord] Límit de fitxer obert que s’utilitza: 0% (2/10 K)
[D’acord] Bloqueig de taules adquirides immediatament: 100% (2 bloqueigs immediats / 2)
[D’acord] Accés a la memòria cau Binlog: 0% (0 Memòria / 0 Total)

——– MyISAM Metrics —————————————————–
[!!] Es fa servir el buffer clau: 18,2% (1M usat / 8M de memòria cau)
[!!] No es pot calcular la mida de l’índex MyISAM: torna a executar l’script com a usuari root

——– Mètriques InnoDB —————————————————–
[-] InnoDB està habilitat.
[D’acord] El tampon de buffer InnoDB / mida de dades: 128.0M / 16.0K
[D’acord] Instàncies de l’agrupació de buffers InnoDB: 1
[!!] Buffer d’ús InnoDB: 11,08% (908 usats / 8192 en total)
[D’acord] Eficiència del buffer de lectura InnoDB: 94,07% (12243 visites / 13015 en total)
[!!] Eficiència del buffer d’escriptura InnoDB: 0,00% (0 visites / 1 en total)
[D’acord] El registre InnoDB espera: 0,00% (0 espera / 12 escrits)

——– AriaDB Metrics —————————————————–
[-] AriaDB està desactivat.

——– Mètriques de replicació ————————————————-
[-] No hi ha esclaus de replicació per a aquest servidor.
[-] Aquest és un servidor autònom..

——– Recomanacions —————————————————–
Recomanacions generals:
MySQL es va iniciar en les darreres 24 hores. És possible que les recomanacions siguin incorrectes
Reduïu o elimineu problemes de xarxa i connexions no tancades
Variables a ajustar:
query_cache_size (>= 8M)
[linuxuser @ centos7-hostadvice4 ~] $

Centreu-vos en l’última part del resultat a la secció “Recomanacions” i comproveu les variables que cal augmentar.

Creeu una còpia de seguretat del fitxer de configuració mysql i editeu el fitxer de configuració real.

$ sudo cp /etc/my.cnf /etc/my.cnf.bak
$ sudo vim /etc/my.cnf

Ajusteu la variable del fitxer segons les recomanacions. Podeu comentar els valors de la variable predeterminada i augmentar els valors de la variable fins a dos cops del valor predeterminat. De fet, poden trigar uns dies a trobar els millors valors per al vostre servidor.

Després de fer els canvis, reinicieu el servidor mysql.

$ sudo systemctl reinicia mysql

Nota especial: si no es pot reiniciar, torneu enrere i comenteu els nous valors i comenceu a ajustar-vos un per un i vegeu quina regulació variable causa la fallada del servidor..

Si el servidor es torna a carregar, torna a executar mysqltuner i comproveu si hi ha més recomanacions sobre l’ajust de valors variables.

Feu un procés similar per afinar l’imprimació.

Executeu el primer d’afinació

$ sudo afinació-primer
[linuxuser @ centos7-hostadvice4 sintonitzador-primer] $ sudo ./tuning-primer.sh
— MYSQL PRIMER Sintonitzador de rendiment 2.0.1-r1 —
– De: Matthew Montgomery –
– De: Markus Kohlmeyer –

MySQL Versió 8.0.11 x86_64

Horari = 0 dies 0 hores 2 min 52 s
Mitjana qps = 0
Total de preguntes = 4
Fils connectats = 1

Avís: el servidor no ha estat en marxa durant almenys 48 hores.
És possible que no sigui segur utilitzar aquestes recomanacions

Per obtenir més informació sobre com funcionen cadascun d’aquests
Visites de rendiment de efectes de variables d’execució:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
Visiteu http://www.mysql.com/products/enterprise/advisors.html
per obtenir informació sobre el servei d’assessorament i assessorament empresarial de MySQL

PREGUNTES LENTES
El registre de consulta lenta NO està activat.
Long_query_time actual = 10.000000 s.
Tens 0 de 25 que duren més de 10.000000 s. completar
Pot ser que el vostre long_query_time sigui massa alt, normalment el configuro a menys de 5 segons.

LLOGUER DE L’actualització binària
El registre d’actualització binari està habilitat
El procés expire_logs_days no està definit.
El mysqld conservarà el registre binari sencer fins que les ordres RESET MASTER o PURGE MASTER LOGS s’executin manualment
Configuració de expire_logs_days us permetrà eliminar els registres binaris antics de forma automàtica
Vegeu http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html

TREBALLS DE TREBALLADORS
Fil actual_cache_size = 9
Fils actuals_cached = 0
Fils actuals_per_sec = 0
Fils històrics_per_sec = 0
La vostra mida_cache_size està bé

CONEXIONS MÀXIMES
Connexions màximes actuals = 151
Fils actuals_conectats = 1
Històric max_used_connections = 1
El nombre de connexions utilitzades és del 0% del màxim configurat.
Feu servir menys del 10% de les vostres connexions max_configurades.
Reduir max_connexions pot ajudar a evitar una sobreassignació de memòria
Veure "ÚS DE LA MEMORYRIA" secció per assegurar-vos que no esteu sobre-assignant

ESTATUS D’INNODB
Espai d’índex InnoDB actual = 240 K
Espai de dades actual InnoDB = 2 M
La piscina de tampons actual InnoDB gratuïta = 88%
Innodb_buffer_pool_size actual = 128 M
Segons la quantitat d’espai que ocupen els vostres índexs innodb, pot ser que sigui segur
per augmentar aquest valor fins a 2/3 de la memòria total del sistema

ÚS DE LA MEMORYRIA
Memòria màxima assignada des de sempre: 153 M
Buffers per fil màxim configurats: 178 M
Buffers màxims configurats: 152 M
Límit de memòria màxima configurat: 330 M
A més, es creen 16 M per taula temporal
Memòria física: 3,45 G
Sembla que el límit màxim de memòria està dins de normes acceptables

CONDUCTOR CLAU
No es llegeix cap clau?!
Mireu seriosament l’ús d’alguns índexs
Espai d’índex MyISAM actual = 0 bytes
Key_buffer_size actual = 8 M
El percentatge de faltes de la memòria cau clau és d’1: 0
Relació lliure de buffer de claus = 81%
La vostra mida key_buffer_ està bé

CACHE DE QUERY
La memòria cau de consulta és compatible i no habilitada
Potser haureu de definir query_cache_size

SORTIDES OPERACIONS
Tipus actual_buffer_size = 256 K
Lectura actual_rnd_buffer_size = 256 K
No s’han realitzat operacions de tipus
Sembla que el buffer d’ordenació està bé

UNITES
Actual join_buffer_size = 260,00 K
Heu tingut 3 consultes en què una unió no podia utilitzar un índex correctament
Hauríeu d’activar "log-queries-no utilitzant-índexs"
A continuació, busqueu ajuntaments no indexats al registre de consultes lentes.

LÍMIT DE FITXES OBRES
Actual open_files_limit = 10000 fitxers
El programa open_files_limit hauria d’estar normalment com a mínim 2x-3x
la de table_open_cache si teniu un gran ús de MyISAM.
El vostre valor open_files_limit sembla estar bé

TAULA CACHE
Taula actual_open_cache = 4.000 taules
Taula actual_definition_cache = 2000 taules
Teniu un total de 136 taules
Teniu 165 taules obertes.
El valor table_open_cache sembla estar bé

TAULES DE TEMP
Actual màx_heap_table_size = 16 M
Actual tmp_table_size = 16 M
De 95 taules temporals, el 0% es van crear al disc
La relació de taules de discs creades sembla que va bé

ESCANS DE TAULA
Lectura actual_buffer_size = 128 K
Relació actual d’exploració de taula = 793: 1
read_buffer_size sembla estar bé

TABLA DE BLOQUEJAMENT
Relació d’espera actual de bloqueig = 0: 286
El vostre bloqueig de taula sembla estar bé

[linuxuser @ centos7-hostadvice4 afinador-primer] $

Nota especial: centreu-vos més en el text destacat en vermell i groc, ja que proporciona recomanacions sobre com optimitzar i millorar el rendiment de mysql.

Ajusteu la variable del fitxer segons les recomanacions. Podeu comentar els valors de les variables predeterminades i ajustar els valors de les variables tal com es suggereix a les recomanacions.

Després de fer els canvis, reinicieu el servidor MySQL.

$ sudo systemctl reinicia mysql

Nota especial: si no es pot reiniciar, torneu enrere i comenteu els nous valors i comenceu a ajustar-vos un per un i vegeu quina regulació variable causa la fallada del servidor..

Si el servidor es reinicia correctament, torneu a executar la configuració del tuneprimer i comproveu si hi ha més recomanacions sobre l’ajust dels valors variables.

Conclusió

És molt important tenir en compte que les eines d’ajust mysqltuner o sintonitzador-primer no són les solucions per al mal funcionament del servidor MySQL, sinó scripts per orientar-vos a l’ajustament del servidor MySQL..

Per obtenir el millor rendiment,

  • Feu una revisió exhaustiva de les consultes enviades al servidor i optimitzeu l’estructura de la base de dades / disseny i consulta SQL
  • Avaluar els factors que afecten el rendiment de MySQL tal i com s’explica a la introducció.
  • Penseu en la millora del maquinari del servidor físic per obtenir un millor rendiment i optimitzar les configuracions de MySQL en funció de les especificacions del maquinari. Consulteu HostAdvice per obtenir les millors opcions d’allotjament MySQL.
  • Feu més investigacions sobre com ajustar les configuracions de MySQL basades en aplicacions que utilitzen el servidor MySQL.

És molt recomanable executar regularment mysqltuner o eina d’imprimació d’ajustament de més de 48 hores després de reiniciar el servidor MySQL, per obtenir una estadística d’ús significativa..

Consulteu aquests tres millors serveis d’allotjament MySQL:

A2 Hosting

Preu inicial:
3,92 dòlars


Fiabilitat
9.3


Preu
9.0


Usuari amigable
9.3


Assistència
9.3


Característiques
9.3

Llegiu comentaris

Visita Hosting A2

FastComet

Preu inicial:
2,95 dòlars


Fiabilitat
9.7


Preu
9.5


Usuari amigable
9.7


Assistència
9.7


Característiques
9.6

Llegiu comentaris

Visita FastComet

Hostinger

Preu inicial:
0,99 USD


Fiabilitat
9.3


Preu
9.3


Usuari amigable
9.4


Assistència
9.4


Característiques
9.2

Llegiu comentaris

Visita Hostinger

Articles relacionats amb la informació

  • Com ajustar i optimitzar el rendiment de MySQL 5.7 en un servidor Ubuntu 18.04 VPS o dedicat
    expert
  • Com instal·lar phpMyAdmin en un servidor VOS CentOS 7 o dedicat
    intermèdia
  • Com fer una còpia de seguretat de la vostra base de dades MySQL en un servidor Ubuntu 18.04 VPS o dedicat
    intermèdia
  • Com instal·lar MYSQL 8.0 i crear una base de dades en un servidor VPS o Linux dedicat CentOS 7
    intermèdia
  • Com crear nous comptes d’usuaris MySQL i gestionar els privilegis de MySQL
    principiant
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map