Muchas de las aplicaciones de hoy en día utilizan MySQL o MariaDB como motor de bases de datos para soluciones LAMP. En base a esto se necesita que los query (consultas por medio de SQL) sean lo mas rápido posible para que no tengamos retrasos en la entrega de datos. Nuestro articulo hoy sera sobre como optimizar MySQL / MariaDB de forma segura.
Ademas de tener un buen código, es buena idea tener bien optimizado tu sistema para que todo sea rápido. Buscando en internet encontré este script que nos permite optimizar el motor de bases de datos de una forma bastante sencilla.
Su nombre es MySQLTuner y esta escrito en perl pudiéndose ejecutar en cualquier distribución Linux actual que tenga perl instalado. Para este articulo lo mostrare en un servidor Linux Centos 7 con MariaDB pero esto no significa que no lo puedas ejecutarlo en otra distribución Linux cumpliendo los siguientes requerimientos.
Requerimientos para ejecutar MySQLTuner
Como optimizar MySQL / MariaDB de forma segura
-Antes de comenzar te recomiendo siempre tener un backup de la configuracion de tu MariaDB o MySQL. Ya sea copiando el /etc/my.cnf a otro carpeta o dejando una copia comentada de las variables modificadas-Instalamos para nuestro uso
yum install wget less
-El primer paso es verificar que nuestro servidor de bases de datos este ejecutándose. Ejecutamos el siguiente comandosudo systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2018-02-16 20:32:43 UTC; 12min ago
Main PID: 389 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─389 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─550 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/pl...
Feb 16 20:32:38 cenwp.drivemeca.com systemd[1]: Starting MariaDB database server...
Feb 16 20:32:38 cenwp.drivemeca.com mariadb-prepare-db-dir[358]: Database MariaDB is probably initialized i...e.
Feb 16 20:32:39 cenwp.drivemeca.com mysqld_safe[389]: 180216 20:32:39 mysqld_safe Logging to '/var/log/mar...g'.
Feb 16 20:32:39 cenwp.drivemeca.com mysqld_safe[389]: 180216 20:32:39 mysqld_safe Starting mysqld daemon w...sql
Feb 16 20:32:43 cenwp.drivemeca.com systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.
-Para conocer mas sobre MariaDB verificamos que versión tenemos instalada en nuestro Linux Centos
rpm -qa|grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
-Creamos un directorio para descargar el script y ejecutarlo desde ahi mkdir mysqltuner cd mysqltuner
-Descargamos el script MySQLTuner junto a dos archivos mas que nos pueden servir para otras tareas de verificacion wget http://mysqltuner.pl/ -O mysqltuner.pl wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv 
-Damos permisos de ejecución al script chmod +x mysqltuner.pl
-Tienes dudas de como usar el script. Su ayuda te servirá para sacarle el mayor provecho al script ./mysqltuner.pl --help
Name:
MySQLTuner 1.7.5 - MySQL High Performance Tuning Script
Important Usage Guidelines:
To run the script with the default options, run the script without
arguments Allow MySQL server to run for at least 24-48 hours before
trusting suggestions Some routines may require root level privileges
(script will provide warnings) You must provide the remote server's
total memory when connecting to other servers
Connection and Authentification:
--host <hostname> Connect to a remote host to perform tests (default: localhost)
--socket <socket> Use a different socket for a local connection
--port <port> Port to use for connection (default: 3306)
--user <username> Username to use for authentication
--userenv <envvar> Name of env variable which contains username to use for authentication
--pass <password> Password to use for authentication
--passenv <envvar> Name of env variable which contains password to use for authentication
--ssl-ca <path> Path to public key
--mysqladmin <path> Path to a custom mysqladmin executable
--mysqlcmd <path> Path to a custom mysql executable
--defaults-file <path> Path to a custom .my.cnf
Performance and Reporting Options:
--skipsize Don't enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
--skippassword Don't perform checks on user passwords(default: off)
--checkversion Check for updates to MySQLTuner (default: don't check)
--updateversion Check for updates to MySQLTuner and update when newer version is available (default: don't check)
--forcemem <size> Amount of RAM installed in megabytes
--forceswap <size> Amount of swap memory configured in megabytes
--passwordfile <path> Path to a password file list(one password by line)
Output Options:
--silent Don't output anything on screen
--nogood Remove OK responses
--nobad Remove negative/suggestion responses
--noinfo Remove informational responses
--debug Print debug information
--dbstat Print database information
--idxstat Print index information
--sysstat Print system information
--pfstat Print Performance schema
--bannedports Ports banned separated by comma(,)
--maxportallowed Number of ports opened allowed on this hosts
--cvefile <path> CVE File for vulnerability checks
--nocolor Don't print output in color
--json Print result as JSON string
--buffers Print global and per-thread buffer values
--outputfile <path> Path to a output txt file
--reportfile <path> Path to a report txt file
--template <path> Path to a template file
--verbose Prints out all options (default: no verbose)
-La forma mas sencilla de ejecutarlo es sin parámetros. De esta forma buscara un MySQL /MariaDB ejecutándose localmente. MySQLTuner nos muestra mucha información ayudado con colores - Mensajes en verde: Normal
- Mensajes en rojo: Corregir o mejorar
./mysqltuner.pl
>> MySQLTuner 1.7.5 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.56-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(3K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[OK] /var/log/mariadb/mariadb.log doesn't contain any warning.
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 2 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 180216 20:32:42 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 171127 23:14:55 [Note] /usr/libexec/mysqld: ready for connections.
[--] 1 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 171127 23:57:34 [Note] /usr/libexec/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 976K (Tables: 12)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 24m 45s (17 q [0.011 qps], 15 conn, TX: 40K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 512.0M
[--] Max MySQL memory : 836.0M
[--] Other process memory: 78.2M
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 418.8M (81.79% of installed RAM)
[!!] Maximum possible memory usage: 836.0M (163.28% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/17)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 46.67% (7/15)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 7 selects)
[OK] Query cache prunes per day: 0
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 100% (26 open / 0 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (5.5.56-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/99.0K
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[OK] InnoDB buffer pool / data size: 128.0M/976.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 91.91% (4531 hits/ 4930 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] 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 your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Set thread_cache_size to 4 as a starting value
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
thread_cache_size (start at 4)
innodb_file_per_table=ON
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
-Despues de ejecutar MySQLTuner veremos al final una serie de recomendaciones para agregar a nuestro motor de bases de datos. Editamos y agregamos
vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
thread_cache_size=4
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
-Reiniciamos el servicio y verificamos quede ejecutándose correctamente con los cambios en su configuracion
systemctl restart mariadb
systemctl status mariadb
-Tienes dudas de como mas ejecutar el script? Su ayuda te servira
./mysqltuner.pl --help
Name:
MySQLTuner 1.7.5 - MySQL High Performance Tuning Script
Important Usage Guidelines:
To run the script with the default options, run the script without
arguments Allow MySQL server to run for at least 24-48 hours before
trusting suggestions Some routines may require root level privileges
(script will provide warnings) You must provide the remote server's
total memory when connecting to other servers
Connection and Authentification:
--host <hostname> Connect to a remote host to perform tests (default: localhost)
--socket <socket> Use a different socket for a local connection
--port <port> Port to use for connection (default: 3306)
--user <username> Username to use for authentication
--userenv <envvar> Name of env variable which contains username to use for authentication
--pass <password> Password to use for authentication
--passenv <envvar> Name of env variable which contains password to use for authentication
--ssl-ca <path> Path to public key
--mysqladmin <path> Path to a custom mysqladmin executable
--mysqlcmd <path> Path to a custom mysql executable
--defaults-file <path> Path to a custom .my.cnf
Performance and Reporting Options:
--skipsize Don't enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
--skippassword Don't perform checks on user passwords(default: off)
--checkversion Check for updates to MySQLTuner (default: don't check)
--updateversion Check for updates to MySQLTuner and update when newer version is available (default: don't check)
--forcemem <size> Amount of RAM installed in megabytes
--forceswap <size> Amount of swap memory configured in megabytes
--passwordfile <path> Path to a password file list(one password by line)
Output Options:
--silent Don't output anything on screen
--nogood Remove OK responses
--nobad Remove negative/suggestion responses
--noinfo Remove informational responses
--debug Print debug information
--dbstat Print database information
--idxstat Print index information
--sysstat Print system information
--pfstat Print Performance schema
--bannedports Ports banned separated by comma(,)
--maxportallowed Number of ports opened allowed on this hosts
--cvefile <path> CVE File for vulnerability checks
--nocolor Don't print output in color
--json Print result as JSON string
--buffers Print global and per-thread buffer values
--outputfile <path> Path to a output txt file
--reportfile <path> Path to a report txt file
--template <path> Path to a template file
--verbose Prints out all options (default: no verbose)
MySQLTuner no reemplaza a un DBA pero te servirá para hacer unos cambios simples en la configuracion de MySQL / MariaDB mejorando su rendimiento
Artículos recomendados:Acelera con nginx tu servidor Ubuntu LEMP
Como instalar Adminer en Linux Centos paso a paso
Como cambiar la contraseña de root en MySQL
15 minutos para configurar un Linux Centos 7 LAMP
Como instalar phpMyAdmin paso a paso
Como instalar MySQL Workbench en Linux Ubuntu paso a paso
Quiero vivir de mi blog, de enseñar opensource, pero esto no sera posible sin tu apoyo, me ayudas?
Satisfech@ con el articulo? Bien, hazme un favor, compártelo en tus redes sociales (compartir es sexy). Escríbeme en los comentarios aquí debajo y pasa la voz compartiendo el tweet.
Sígueme en twitter , google+, facebook, email o YouTube y podrás estar enterado de todas mis publicaciones.Como optimizar MySQL / MariaDB de forma segura https://t.co/ujAT8CBNUX— Manuel Cabrera C (@drivemeca) February 17, 2018
Disfrútenlo.
