2

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/
  • Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 7 at 21:43
  • If you could post Additional information requested Sep 7 at 21:43 some additional advice could be suggested. – Wilson Hauck Oct 12 at 20:03
2

The first rule of using mysqltuner is you do NOT use mysqltuner. The second rule of mysqltumer is you DO NOT USE mysqltuner.

It only produces lies for inexperienced DBAs to wreck their server's performance with.

Unless you know exactly what you are doing, there is a total of two settings you should be changing from defaults:

innodb_buffer_pool_size = (smaller of data size and 75% of your RAM, round up to nearest GB)
innodb_file_per_table = 1

Everything else is likely to make the performance worse. In your "# Buffers" section, every single one of those is almost certainly going to make your performance worse than defaults. In fact, at least half of what you have in that config will make things worse, the rest is either redundant or dangerous.

So as a first pass, at a solution, reduce everything after "# Buffers" to:

table-definition-cache  = 1024
table-open-cache        = 2048
innodb_buffer_pool_size = 1G # according to output above, your data is only 650MB
innodb_file_per_table   = 1
query-cache-type        = 0
query-cache-size        = 0

Everything else in there is almost certainly going to make things worse.

Instead of:

innodb_locks_unsafe_for_binlog  = 1

you should be using:

transaction_isolation = READ-COMMITTED
| improve this answer | |
0

Aside from the Answers and Comments already made, I have two more things:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.