I have a server with this configuration:
- Intel Core i5-3550 4 x 3.4Ghz
- 16GB DDR3
- 2 x 1000GB
Software:
- nginx
- php7.4
- mariadb
- wordpress
- xenforo
I have 30.0000 unique visitors and 250.000 pageviews a day. Daily updated with about 30 Wordpress posts and about 300 xenforo posts and threads.
Mysqtuner
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 10.1.26-MariaDB-0+deb9u1
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 445.1M (Tables: 42)
[--] Data in MEMORY tables: 10.3M (Tables: 3)
[--] Data in InnoDB tables: 611.7M (Tables: 339)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'mmooddd@%' does not specify hostname restrictions.
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 10h 11m 10s (55M q [453.672 qps], 646K conn, TX: 210G, RX: 8G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 15.6G
[--] Max MySQL memory : 149.7G
[--] Other process memory: 0B
[--] Total buffers: 2.3G global + 30.2M per thread (5000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 5.2G (33.14% of installed RAM)
[!!] Maximum possible memory usage: 149.7G (960.05% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/55M)
[OK] Highest usage of available connections: 1% (97/5000)
[OK] Aborted connections: 0.00% (1/646942)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 38.0% (31M cached / 82M selects)
[!!] Query cache prunes per day: 1426905
[OK] Sorts requiring temporary tables: 0% (44 temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 67% (1M on disk / 1M total)
[OK] Thread cache hit rate: 97% (19K created / 646K connections)
[OK] Table cache hit rate: 50% (1K open / 2K opened)
[OK] table_definition_cache(1024) is upper than number of tables(544)
[OK] Open file limit used: 2% (361/16K)
[OK] Table locks acquired immediately: 97% (23M immediate / 23M locks)
[OK] Binlog cache memory access: 99.67% (219836 Memory / 220560 Total)
-------- 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 (10.1.26-MariaDB-0+deb9u1)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (16M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/62.6M
[OK] Read Key buffer hit rate: 99.8% (1B cached / 2M reads)
[!!] Write Key buffer hit rate: 91.8% (3M cached / 2M writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/611.7M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 256.0M * 2/2.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (380674808 hits/ 380690007 total)
[!!] InnoDB Write Log efficiency: 51.6% (256941 hits/ 497923 total)
[OK] InnoDB log waits: 0.00% (0 waits / 240982 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 91.3% (12M cached / 1M reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Restrict Host for 'mmoodd'@% to naughty@SpecificDNSorIp
UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='mmoodd' AND host ='%'; FLUSH PRIVILEGES;
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
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
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
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_size (> 16M)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
performance_schema = ON enable PFS
innodb_buffer_pool_instances(=2)
Configuration settings:
my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
syslog
[mysqld]
# Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Engine
default-storage-engine = InnoDB
# Listening IP
bind-address = 127.0.0.1
skip-name-resolve
# Safety
max-connect-errors = 1000000
max_allowed_packet = 64M
skip-name-resolve
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
wait_timeout = 60
interactive_timeout = 60
# Buffers
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 8M
read_rnd_buffer_size = 16M
thread_concurrency = 8 # Max CPU * 2
# MyISAM
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
myisam_sort_buffer_size = 64M
# CACHES AND LIMITS #
tmp-table-size = 128M
max-heap-table-size = 128M
query-cache-type = 0
query-cache-size = 0
query_cache_limit = 1M
max-connections = 5000
thread-cache-size = 50
thread_stack = 192K
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# Bin logs
binlog-format = ROW
log-bin = /var/lib/mysql/mysql-bin
log-slave-updates = 1
expire-logs-days = 5
sync-binlog = 1
max_binlog_size = 100M
server-id = 1 # randomize it incase of multiple servers
# InnoDB
innodb-buffer-pool-size = 2048M
innodb_buffer_pool_instances = 8
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-file-per-table = 1
innodb-flush-log-at-trx-commit = 1
innodb-flush-method = O_DIRECT
# With virtual synchrony redundancy, make write queries faster
innodb_doublewrite = 1
# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog = 1
# LOGGING
general_log_file = /var/log/mysql/mysql.log
log-error = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes = 1
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
