De la misma forma que queremos saber cuánto tráfico soporta un sitio web y para el que podemos hacer un test de estrés, también podemos aplicar la misma técnica a la base de datos.
En este caso vamos a montar una máquina de 4 CPU y 8 GB de RAM exclusivamente para la base de datos, y configurada con MariaDB 10.5.
Este tutorial ha sido creado en un VPS de Clouding.io. Puedes crear tu propio VPS desde 3€/mes.
Además, tienes la posibilidad de crear tu VPS con la imagen de WordPress en un clic.
COLABORACIÓN
Configurando el servidor
Vamos a aplicar actualizaciones y configuraciones básicas iniciales. Lo primero será establecer la hora del servidor.
timedatectl set-timezone 'UTC'
timedatectl set-ntp onY después haremos una actualización completa del sistema.
lsb_release -a
apt -y update && apt -y upgrade && apt -y dist-upgrade && apt -y autoremoveInstalaremos algunas herramientas habituales.
apt -y install ca-certificates software-properties-common curl vim zip unzip apt-transport-https fail2ban build-essential unattended-upgradesY el idioma español por defecto.
locale-gen es_ES.UTF-8 ca_ES.UTF-8 en_US.UTF-8
update-locale LANG=es_ES.UTF-8 LANGUAGE="es_ES:en" LC_ALL=es_ES.UTF-8Activaremos el swap de memoria.
cat /proc/sys/vm/swappiness
echo "vm.swappiness=60" | tee -a /etc/sysctl.d/99-swappiness.conf
swapoff -a && swapon -aInstalando MariaDB
Activaremos el repositorio de MariaDB.
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.5" --skip-maxscaleY posteriormente instalaremos MariaDB.
apt -y install mariadb-server mariadb-client mariadb-backupHaremos la configuración y podremos una contraseña al root.
mysql_secure_installationY activaremos el sistema para que funcione tras un reinicio.
systemctl stop mysql.service
systemctl enable mysql.service
systemctl start mysql.service
systemctl status mysql.servicePrueba de estrés
Para hacer la prueba de estrés utilizaremos la herramienta sysbench.
apt -y install sysbenchAccederemos al MariaDB y crearemos una base de datos vacía.
CREATE DATABASE sbtest;A partir de este momento podemos ejecutar los 3 comandos que nos permitirán hacer pruebas. El primero es el de montar las tablas y elementos.
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-user=root --mysql-password=password --threads=16 --events=0 --time=60 --report-interval=1 --tables=25 --table_size=100000 --range_selects=off --db-ps-mode=disable prepareUna vez acabe, tendremos las tablas con los datos sobre los que se realizarán las pruebas.
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-user=root --mysql-password=password --threads=16 --events=0 --time=60 --report-interval=1 --tables=25 --table_size=100000 --range_selects=off --db-ps-mode=disable runEste comando es el que nos dará un informe del funcionamiento. Y finalmente, una vez hayamos acabado, podemos limpiar todo.
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-user=root --mysql-password=password --threads=16 --events=0 --time=60 --report-interval=1 --tables=25 --table-size=100000 --range_selects=off --db-ps-mode=disable cleanupResultado sin configuración
Si no hacemos cambios en la configuración, lo más probable es que no esté nada actualizado ni optimizado, y los resultados pueden ser muy pobres.
SQL statistics:
queries performed:
read: 321740
write: 128696
other: 64348
total: 514784
transactions: 32174 (533.92 per sec.)
queries: 514784 (8542.66 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.2571s
total number of events: 32174
Latency (ms):
min: 1.61
avg: 29.96
max: 1289.31
95th percentile: 84.47
sum: 963966.69
Threads fairness:
events (avg/stddev): 2010.8750/18.69
execution time (avg/stddev): 60.2479/0.00Como datos interesantes:
- Transacciones por minuto: 32.174 (533 por segundo)
- Consultas por minuto: 514.784 (8.542 por segundo)
Configuración básica
Si revisamos la configuración que viene y que en muchos casos encontramos por Internet cuando buscamos cómo optimizar un WordPress, tendremos un server.cnf similar a este:
[server]
[mysqld]
user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
lc-messages = en_US
bind-address = 127.0.0.1
skip-external-locking
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine = InnoDB
skip-name-resolve = 1
# Logs
log-error = /var/lib/mysql/mysql-error.log
expire_logs_days = 14
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
# BinLog
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# MyISAM
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# InnoDB
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 4G
innodb-buffer-pool-instances = 3
innodb-large-prefix = true
innodb-file-per-table = true
# Safety
max-allowed-packet = 16M
max-connect-errors = 1000000
sql-mode = ""
# Cache y Limits
tmp-table-size = 128M
max-heap-table-size = 128M
query-cache-type = 0
query-cache-size = 00
query_cache_limit = 256K
query_cache_min_res_unit = 2k
max-connections = 800
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 32768
table-open-cache = 32768
[embedded]
[mariadb]Una configuración como esta nos devolverá unos datos tales que estos:
SQL statistics:
queries performed:
read: 487580
write: 195032
other: 97516
total: 780128
transactions: 48758 (812.36 per sec.)
queries: 780128 (12997.80 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0190s
total number of events: 48758
Latency (ms):
min: 5.55
avg: 19.69
max: 360.06
95th percentile: 29.72
sum: 960016.16
Threads fairness:
events (avg/stddev): 3047.3750/11.35
execution time (avg/stddev): 60.0010/0.00Como datos interesantes:
- Transacciones por minuto: 48.758 (812 por segundo)
- Consultas por minuto: 780.128 (12.997 por segundo)
Configuración específica
En el momento en el que configuras el servidor de base de datos para una configuración concreta de CPU y memoria RAM, además de adaptar el sistema (en este caso está pensado para un WordPress MultiSite), tienes una configuración algo más compleja para el server.cnf.
[server]
[mysqld]
#
# Global / Idioma
#
basedir = /usr
bind_address = 0.0.0.0
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
connect_timeout = 60
datadir = /var/lib/mysql
default_storage_engine = InnoDB
default_tmp_storage_engine = InnoDB
lc_messages = es_ES
lc_messages_dir = /usr/share/mysql
lc_time_names = es_ES
pid_file = /run/mysqld/mysqld.pid
skip_external_locking = 1
skip_name_resolve = 1
tmpdir = /tmp
user = mysql
#
# General
#
div_precision_increment = 6
host_cache_size = 128
join_buffer_size = 256M
lock_wait_timeout = 3600
max_allowed_packet = 128MB
max_connections = 400
max_connect_errors = 1024
max_digest_length = 1024
max_error_count = 1024
max_prepared_stmt_count = 16382
max_sort_length = 1M
max_user_connections = 0
preload_buffer_size = 32M
query_alloc_block_size = 32K
range_alloc_block_size = 32K
sort_buffer_size = 4M
stored_program_cache = 1024
table_open_cache = 1024
table_open_cache_instances = 4
thread_cache_size = 12 # =8+(max_connections/100)
thread_stack = 1M
transaction_alloc_block_size = 32K
transaction_prealloc_size = 16K
#
# Logs
#
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 0
long_query_time = 10
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
#
# MyISAM
#
bulk_insert_buffer_size = 8M
concurrent_insert = 1
delay_key_write = on
key_buffer_size = 1G
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
myisam_data_pointer_size = 6
myisam_max_sort_file_size = 2G
myisam_mmap_size = 4G
myisam_recover_options = FORCE,BACKUP
myisam_repair_threads = 1
myisam_sort_buffer_size = 128M
read_buffer_size = 4M
read_rnd_buffer_size = 256M
#
# InnoDB
#
innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 1
innodb_adaptive_hash_index_parts = 32
innodb_adaptive_max_sleep_delay = 120000
innodb_autoextend_increment = 64
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_now = 0
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_instances = 16
innodb_buffer_pool_load_abort = 0
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_load_now = 0
innodb_buffer_pool_size = 2G
innodb_change_buffer_max_size = 25
innodb_change_buffering = 5
innodb_cmp_per_index_enabled = 0
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 6144
innodb_deadlock_detect = 1
innodb_disable_sort_file_cache = 0
innodb_doublewrite = 1
innodb_fast_shutdown = 1
innodb_file_per_table = 1
innodb_fill_factor = 80
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = 4
innodb_flush_neighbors = 1
innodb_flush_sync = 1
innodb_flushing_avg_loops = 100
innodb_force_load_corrupted = 0
innodb_force_recovery = 0
innodb_ft_cache_size = 16M
innodb_ft_enable_diag_print = 0
innodb_ft_enable_stopword = 1
innodb_ft_max_token_size = 84
innodb_ft_min_token_size = 3
innodb_ft_num_word_optimize = 4000
innodb_ft_result_cache_limit = 4G
innodb_ft_sort_pll_degree = 4
innodb_ft_total_cache_size = 2G
innodb_idle_flush_pct = 100
innodb_io_capacity = 512
innodb_io_capacity_max = 576
innodb_lock_wait_timeout = 30
innodb_log_buffer_size = 32M
innodb_log_checksums = 1
innodb_log_compressed_pages = 1
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_log_write_ahead_size = 8K
innodb_lru_scan_depth = 1024
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 2000000
innodb_max_undo_log_size = 1G
innodb_old_blocks_pct = 20
innodb_old_blocks_time = 1000
innodb_online_alter_log_max_size = 256M
innodb_optimize_fulltext_only = 0
innodb_page_cleaners = 4
innodb_purge_batch_size = 512
innodb_purge_threads = 8
innodb_purge_rseg_truncate_frequency = 128
innodb_random_read_ahead = 0
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_replication_delay = 0
innodb_rollback_on_timeout = 0
innodb_rollback_segments = 128
innodb_sort_buffer_size = 4M
innodb_spin_wait_delay = 6
innodb_stats_auto_recalc = 1
innodb_stats_include_delete_marked = 0
innodb_stats_persistent = 1
innodb_strict_mode = 1
innodb_sync_array_size = 1
innodb_sync_spin_loops = 30
innodb_table_locks = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000
innodb_undo_log_truncate = 1
innodb_use_native_aio = 1
innodb_write_io_threads = 4
[embedded]
[mariadb]En este caso, con la misma configuración de infraestructura, tenemos unos datos que son aproximadamente el doble mejor.
SQL statistics:
queries performed:
read: 903150
write: 361260
other: 180630
total: 1445040
transactions: 90315 (1505.00 per sec.)
queries: 1445040 (24079.93 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0093s
total number of events: 90315
Latency (ms):
min: 1.55
avg: 10.63
max: 1277.20
95th percentile: 18.95
sum: 959869.91
Threads fairness:
events (avg/stddev): 5644.6875/28.41
execution time (avg/stddev): 59.9919/0.00Como datos interesantes:
- Transacciones por minuto: 90.315 (1.505 por segundo)
- Consultas por minuto: 1.445.040 (24.079 por segundo)
Resumen
| trans./min. | trans./seg. | cons./min. | cons./seg. | |
| sin configurar | 32.174 | 533 | 514.784 | 8.542 |
| básica | 48.758 | 812 | 780.128 | 12.997 |
| específica | 90.315 | 1.505 | 1.445.040 | 24.079 |
Cuando hagas optimización en tu WordPress, recuerda que la base de datos es uno de los elementos más importantes de la configuración.
Sobre este documento
Este documento está regulado por la licencia EUPL v1.2, publicado en WP SysAdmin y creado por Javier Casares. Por favor, si utilizas este contenido en tu sitio web, tu presentación o cualquier material que distribuyas, recuerda hacer una mención a este sitio o a su autor, y teniendo que poner el material que crees bajo licencia EUPL.