In the same way that we want to know how much traffic a website supports and for which we can do a stress test, we can also apply the same technique to the database.
In this case we are going to mount a machine with 4 CPU and 8 GB of RAM exclusively for the database, and configured with MariaDB 10.5.
This tutorial has been created on a Clouding.io VPS. You can create your own VPS from 3€/month.
In addition, you have the possibility to create your VPS with the WordPress image in one click.
PARTNERSHIP
Configuring the server
We will apply initial basic updates and configurations. The first thing will be to set the server time.
timedatectl set-timezone 'UTC'
timedatectl set-ntp onAnd then we will do a complete update of the system.
lsb_release -a
apt -y update && apt -y upgrade && apt -y dist-upgrade && apt -y autoremoveWe will install some common tools.
apt -y install ca-certificates software-properties-common curl vim zip unzip apt-transport-https fail2ban build-essential unattended-upgradesAnd the Default Spanish language.
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-8We will activate the memory swap.
cat /proc/sys/vm/swappiness
echo "vm.swappiness=60" | tee -a /etc/sysctl.d/99-swappiness.conf
swapoff -a && swapon -aInstalling MariaDB
We will activate the MariaDB repository.
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.5" --skip-maxscaleAnd then we will install MariaDB.
apt -y install mariadb-server mariadb-client mariadb-backupWe will make the configuration and we will be able to a password to the root.
mysql_secure_installationAnd we will activate the system to work after a restart.
systemctl stop mysql.service
systemctl enable mysql.service
systemctl start mysql.service
systemctl status mysql.serviceStress test
To do the stress test we will use the sysbench tool.
apt -y install sysbenchWe will access the MariaDB and create an empty database.
CREATE DATABASE sbtest;From this moment we can execute the 3 commands that will allow us to do tests. The first is to assemble the boards and elements.
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 prepareOnce finished, we will have the tables with the data on which the tests will be carried out.
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 runThis command is the one that will give us a report of the operation. And finally, once we’re done, we can clean everything up.
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 cleanupResult without configuration
If we don’t make changes to the settings, chances are nothing up to date or optimized, and the results can be very poor.
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.00As interesting facts:
- Transactions per minute: 32,174 (533 per second)
- Queries per minute: 514,784 (8,542 per second)
Basic configuration
If we review the configuration that comes and that in many cases we find on the Internet when we look for how to optimize a WordPress, we will have a server.cnf similar to this:
[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]A configuration like this will return some data such that these:
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.00As interesting facts:
- Transactions per minute: 48,758 (812 per second)
- Queries per minute: 780,128 (12,997 per second)
Specific settings
At the moment you configure the database server for a specific configuration of CPU and RAM, in addition to adapting the system (in this case it is designed for a WordPress MultiSite), you have a somewhat more complex configuration for the 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]In this case, with the same infrastructure configuration, we have data that is approximately twice as good.
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.00As interesting facts:
- Transactions per minute: 90,315 (1,505 per second)
- Queries per minute: 1,445,040 (24,079 per second)
Summary
| trans./min. | trans./sec. | cons./min. | cons./sec. | |
| unconfigured | 32.174 | 533 | 514.784 | 8.542 |
| basic | 48.758 | 812 | 780.128 | 12.997 |
| specific | 90.315 | 1.505 | 1.445.040 | 24.079 |
When optimizing on your WordPress, remember that the database is one of the most important elements of the configuration.
About this document
This document is regulated by the EUPL v1.2 license, published in WP SysAdmin and created by Javier Casares. Please, if you use this content in your website, your presentation or any material you distribute, remember to mention this site or its author, and having to put the material you create under EUPL license.