Test de estrés para MariaDB / MySQL

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 on

Y después haremos una actualización completa del sistema.

lsb_release -a
apt -y update && apt -y upgrade && apt -y dist-upgrade && apt -y autoremove

Instalaremos algunas herramientas habituales.

apt -y install ca-certificates software-properties-common curl vim zip unzip apt-transport-https fail2ban build-essential unattended-upgrades

Y 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-8

Activaremos el swap de memoria.

cat /proc/sys/vm/swappiness
echo "vm.swappiness=60" | tee -a /etc/sysctl.d/99-swappiness.conf
swapoff -a && swapon -a

Instalando 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-maxscale

Y posteriormente instalaremos MariaDB.

apt -y install mariadb-server mariadb-client mariadb-backup

Haremos la configuración y podremos una contraseña al root.

mysql_secure_installation

Y activaremos el sistema para que funcione tras un reinicio.

systemctl stop mysql.service
systemctl enable mysql.service
systemctl start mysql.service
systemctl status mysql.service

Prueba de estrés

Para hacer la prueba de estrés utilizaremos la herramienta sysbench.

apt -y install sysbench

Accederemos 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 prepare

Una 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 run

Este 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 cleanup

Resultado 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.00

Como 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.00

Como 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.00

Como 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 configurar32.174533514.7848.542
básica48.758812780.12812.997
específica90.3151.5051.445.04024.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.