Mysql Database optimize suggestions

mariadbMySQLoptimization

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/

Best Answer

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

Related Solutions

Mysql – Users complain that system runs slow when thesqldump is in progress

There is a known issue in Windows, that when you push a large file to another server all the memory ends up getting allocated to the System cache instead of the user processes. You can look in the Physical Memory (MB) section of task manager to see how much memory is allocated to the system cache.

This can be solved by backing up to a local disk, then having the remote machine pull that file.

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Best Answer

Related Solutions

Mysql – Users complain that system runs slow when thesqldump is in progress

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

Related Question