Introducción

Antes de comenzar, debe comprender los factores que afectan el rendimiento de MySQL para que pueda aprender a ajustar y optimizar correctamente el servidor MySQL y obtener los resultados correctos. Los principales factores incluyen:

  • Cantidad de datos que se recuperan>
  • Recursos disponibles, es decir, CPU, memoria de trabajo (RAM)
  • Proceso de carga realizado por el servidor MySQL

Las causas del bajo rendimiento del servidor MySQL son:

  • Diseño de base de datos incorrecto
  • Cuellos de botella de hardware
  • Codificación incorrecta
  • Mala indexación
  • Configuración ineficiente

En este artículo, demostraremos cómo ajustar y optimizar el rendimiento de MySQL 8.0 en CentOS 7 Linux VPS.

Requerimientos:

  • Configuración de CentOS 7 VPS
  • Servidor MySQL 8.0 instalado
  • Usuario creado con permisos de sudo

Instalar mysqltuner y tuning primer

Instalar mysqltuner

Mysqltuner es un poderoso script Perl de ajuste de MySQL que proporciona una instantánea del estado del servidor MySQL y brinda recomendaciones específicas para mejorar y aumentar el rendimiento, la estabilidad y la eficiencia.

Ejecute los siguientes comandos para instalar mysqltuner

Instale epel-release primero porque el paquete mysqltuner está en el repositorio de epel-release

$ sudo yum install -y epel-release

Luego instale mysqltuner

$ sudo yum install -y mysqltuner

Instale el cebador de afinación

Tuning Primer es un script de shell que toma información de los componentes internos del servidor MySQL y ofrece recomendaciones sobre cómo ajustar las variables del servidor.

Ejecute los siguientes comandos para instalar Tuning Primer:

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

Nota especial: si Git no está instalado, puede instalarlo con

$ sudo yum install git -y

Cambie al directorio tuning-primer y haga que el script tuning-primer sea ejecutable de la siguiente manera:

$ cd tuning-primer
$ sudo chmod +x tuning-primer.sh

Tuning y optimización del servidor MySQL

El servidor MySQL se ajusta mediante el archivo de configuración de MySQL.

/etc/my.cnf

Ejecute mysqltuner.

$ sudo mysqltuner

Ingrese el usuario de mysql con todos los derechos (en nuestro caso, “root”) y la contraseña cuando se le solicite

[linuxuser@centos7-hostadvice4 ~]$ sudo mysqltuner
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:  >>  MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[!!] Currently running unsupported MySQL version 8.0.11
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 16K (Tables: 1)
[OK] Total fragmented tables: 0
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
 
-------- Security Recommendations  -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
[OK] There is no anonymous account in all database users
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as B' at line 1
mysql: [Warning] Using a password on the command line interface can be insecure.
[!!] There is not basic password file list !
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Use of uninitialized value in addition (+) at /bin/mysqltuner line 1934, <>
    	line 2 (#1)
  (W uninitialized) An undefined value was used as if it were already
  defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
  To suppress this warning assign a defined value to your variables.
 
  To help you figure out what was undefined, perl will try to tell you
  the name of the variable (if any) that was undefined.  In some cases
  it cannot do this, so it also tells you what operation you used the
  undefined value in.  Note, however, that perl optimizes your program
  anid the operation displayed in the warning may not necessarily appear
  literally in your program.  For example, "that $foo" is usually
  optimized into "that " . $foo, and the warning will refer to the
  concatenation (.) operator, even though there is no . in
  your program.
 
Use of uninitialized value in division (/) at /bin/mysqltuner line 1934, <>
    	line 2 (#1)
Use of uninitialized value $mystat{"Qcache_lowmem_prunes"} in numeric eq (==)
    	at /bin/mysqltuner line 1949, <> line 2 (#1)
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 37m 3s (12 q [0.002 qps], 20 conn, TX: 20K, RX: 2K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 168.0M global + 1.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 169.1M (4.78% of installed RAM)
[OK] Maximum possible memory usage: 341.4M (9.65% of installed RAM)
[OK] Slow queries: 0% (0/12)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 40.00%  (8/20)
Use of uninitialized value $myvar{"query_cache_size"} in numeric lt (<) at
        /bin/mysqltuner line 2281, <> line 2 (#1)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 95% (1 created / 20 connections)
[OK] Table cache hit rate: 83% (118 open / 142 opened)
[OK] Open file limit used: 0% (2/10K)
[OK] Table locks acquired immediately: 100% (2 immediate / 2 locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)
 
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
 
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 11.08% (908 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 94.07% (12243 hits/ 13015 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12 writes)
 
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
 
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
 
-------- Recommendations -----------------------------------------------------
General recommendations:
  MySQL started within last 24 hours - recommendations may be inaccurate
  Reduce or eliminate unclosed connections and network issues
Variables to adjust:
  query_cache_size (>= 8M)
[linuxuser@centos7-hostadvice4 ~]$

Concéntrese en la última parte del problema en la sección “Recommendations” y verifique las variables que deben incrementarse.

Cree una copia de seguridad del archivo de configuración de MySQL y edite el archivo de configuración real.

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

Ajuste la variable del archivo de acuerdo con las recomendaciones. Puede comentar los valores de las variables predeterminadas y aumentar los valores de las variables al doble del valor predeterminado. De hecho, puede llevar algunos días obtener los mejores valores para su servidor.

Después de realizar los cambios, reinicie el servidor MySQL.

$ sudo systemctl restart mysql

Nota especial: si el reinicio falla, retroceda, comente los nuevos valores y comience a ajustar uno por uno y vea qué ajuste de variable está causando la falla del servidor.

Si el servidor se recarga correctamente, ejecute mysqltuner nuevamente y vea si hay otras recomendaciones para ajustar los valores de las variables.

Realice un proceso similar al cebador de afinación.

Ejecute el cebador de ajuste

$ sudo tuning-primer
[linuxuser@centos7-hostadvice4 tuning-primer]$ sudo ./tuning-primer.sh
 -- MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 --
      	- By: Matthew Montgomery -
      	- By: Markus Kohlmeyer   -
 
MySQL Version 8.0.11 x86_64
 
Uptime = 0 days 0 hrs 2 min 52 sec
Avg. qps = 0
Total Questions = 4
Threads Connected = 1
 
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
 
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
 
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 25 that take longer than 10.000000 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.
 
BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html
 
WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
 
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
 
INNODB STATUS
Current InnoDB index space = 240 K
Current InnoDB data space = 2 M
Current InnoDB buffer pool free = 88 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
 
MEMORY USAGE
Max Memory Ever Allocated : 153 M
Configured Max Per-thread Buffers : 178 M
Configured Max Global Buffers : 152 M
Configured Max Memory Limit : 330 M
Plus 16 M per temporary table created
Physical Memory : 3.45 G
Max memory limit seem to be within acceptable norms
 
KEY BUFFER
No key reads?!
Seriously look into using some indexes
Current MyISAM index space = 0 bytes
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
 
QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
 
SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
No sort operations have been performed
Sort buffer seems to be fine
 
JOINS
Current join_buffer_size = 260.00 K
You have had 3 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
 
OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_open_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
 
TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 2000 tables
You have a total of 136 tables
You have 165 open tables.
The table_open_cache value seems to be fine
 
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 95 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine
 
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 793 : 1
read_buffer_size seems to be fine
 
TABLE LOCKING
Current Lock Wait ratio = 0 : 286
Your table locking seems to be fine
 
[linuxuser@centos7-hostadvice4 tuning-primer]$

Nota especial: céntrese más en el texto resaltado en rojo y amarillo, ya que contiene recomendaciones para optimizar y mejorar el rendimiento de MySQL. .

Ajuste la variable del archivo de acuerdo con las recomendaciones. Puede comentar los valores de las variables predeterminadas y ajustar los valores de las variables como se sugiere en las recomendaciones. .

Después de realizar los cambios, reinicie el servidor MySQL.

$ sudo systemctl restart mysql

Nota especial: si el reinicio falla, retroceda, comente los nuevos valores y comience a ajustar uno por uno y vea qué ajuste de variable está causando la falla del servidor.

Si el servidor se reinicia correctamente, ejecute tuneprimer nuevamente y vea si hay otras recomendaciones para ajustar los valores de las variables.

Conclusión

Es muy importante tener en cuenta que las herramientas de ajuste mysqltuner o tuning-primer tuning no son soluciones para servidores MySQL de bajo rendimiento, sino scripts que lo guían en el ajuste fino del servidor MySQL.

Para un mejor desempeño,

  • Haga una revisión exhaustiva de las consultas enviadas al servidor y optimice la estructura / diseño de la base de datos y la consulta SQL.
  • Evalúe los factores que afectan el rendimiento de MySQL como se explica en la introducción.
  • Considere actualizar el hardware del servidor físico para un mejor rendimiento y ajustar las configuraciones de MySQL según las especificaciones del hardware. Consulte a Hosting mas Baratopara conocer las mejores opciones de hosting MySQL.
  • Obtenga más información sobre cómo optimizar las configuraciones de MySQL basadas en aplicaciones que utilizan servidores MySQL.

Se recomienda encarecidamente ejecutar mysqltuner o la herramienta tuning-primer con regularidad durante un período de 48 horas después de reiniciar el servidor MySQL para obtener estadísticas de uso significativas.