Ajustar y mejorar el rendimiento de un servidor Mysql con el script perl mysqltuner.pl
En este caso un servidor Linux Ubuntu 24, con Base de datos
Mariadb 10.x.xx
#Obtengo el script, descargándolo en mi servidor.
Root@Sever_Name:~#
wget raw.github.com/ ...qltuner.pl -O mysqltuner.pl
--2025-05-09 03:26:08--
raw.github.com/ ...qltuner.pl
Resolving raw.github.com (raw.github.com)... 2606:50c0:8000::154, 2606:50c0:8001::154, 2606:50c0:8003::154, ...
Connecting to raw.github.com (raw.github.com)|2606:50c0:8000::154|:443... Connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location:
raw.githubusercontent.com/ ...qltuner.pl [following]
--2025-05-09 03:26:08--
raw.githubusercontent.com/ ...qltuner.pl
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 2606:50c0:8002::154, 2606:50c0:8003::154, 2606:50c0:8000::154, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|2606:50c0:8002::154|:443... Connected.
HTTP request sent, awaiting response... 200 OK
Length: 265070 (259K) [text/plain]
Saving to: ‘mysqltuner.pl’
Mysqltuner.pl 100%[===>] 258.86K --.-KB/s in 0.02s
2025-05-09 03:26:09 (13.8 MB/s) - ‘mysqltuner.pl’ saved [265070/265070]
Root@Sever_Name:~#
# Ajusto permisos:
Root@Sever_Name:~#
chmod +x mysqltuner.pl
# Ejecutamos el script:
Root@Sever_Name:~#
./mysqltuner.pl
MySQLTuner 2.6.2
* Jean-Marie Renouard <jmrenouard@gmail.com>
* Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at
mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
ℹ Skipped version check for MySQLTuner script
✘ Failed to execute: SHOW REPLICAS\G
✘ FAIL Execute SQL / return code: 256
✔ Operating on 64-bit architecture
--- Storage Engine Statistics ---
ℹ Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
ℹ Data in MyISAM tables: 24.3G (Tables: 1079)
ℹ Data in Aria tables: 32.0K (Tables: 1)
ℹ Data in InnoDB tables: 8.6G (Tables: 365)
✔ Total fragmented tables: 0
✔ Currently running supported MySQL version 10.11.11-MariaDB
--- Log file Recommendations ---
✘ Log file doesn't exist
--- Analysis Performance Metrics ---
ℹ innodb_stats_on_metadata: OFF
✔ No stat updates during querying INFORMATION_SCHEMA.
....
--- Replication Metrics ---
ℹ Galera Synchronous replication: NO
ℹ No replication slave(s) for this server.
ℹ Binlog format: MIXED
ℹ XA support enabled: ON
ℹ Semi synchronous replication Master: OFF
ℹ Semi synchronous replication Slave: OFF
ℹ This is a standalone server
--- Recommendations ---
General recommendations:
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See
dev.mysql.com/ ...uffer_size
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
MyISAM engine is deprecated, consider migrating to InnoDB
Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
skip-name-resolve=ON
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache (400) > 1736 or -1 (autosizing if supported)
performance_schema=ON
innodb_buffer_pool_size (>= 8.6G) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
Root@Sever_Name:~#
#Editar /etc/mysql/my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
Skip-name-resolve=ON
Join_buffer_size=512K
Tmp_table_size = 512M
Max_heap_table_size = 512M
Table_definition_cache = 1736
Key_buffer_size = 12M
Performance_schema=ON
Innodb_buffer_pool_size = 9G
Innodb_log_file_size = 1G
Innodb_log_buffer_size = 1G
# Reiniciar y volver a pasar el script varias veces:
Root@Sever_Name:~#
service mysqld restart
Root@Sever_Name:~#
./mysqltuner.pl
Nota: en negrita los comandos
RSS - Últimos Mensajes
