mysql - MariaDB 优化和 mysqltuner.pl - RAM 和调整

Question

我正在尝试优化正在运行的数据库，它位于具有 8GB RAM 的系统上，并且希望它能够使用大约 5GB-6GB 的内存。我已经运行了 mysqltuner.pl 脚本，并做了一些更改，但在对其进行过多操作之前，我需要一些反馈。我已经包含了 mysqltuner 的输出，它提出了一些建议，并且列出了一些警告，我想解决这些问题:

问题:

[!!] InnoDB is enabled but isn't being used 
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 473K selects)
[!!] Key buffer used: 19.0% (398M used / 2B cache)
[!!] Write Key buffer hit rate: 93.4% (362K cached / 338K writes)
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
[!!] Aria pagecache hit rate: 69.9% (308K cached / 92K reads)

我的配置(my.cnf)当前如下所示:

[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

skip-name-resolve=1

key_buffer_size     = 2000M
innodb_buffer_pool_size = 0




#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

这是 mysqltuner 的完整输出，但是我没有添加它建议在最后调整的变量。我实际上无法在 my.cnf 或包含目录中的文件中找到它们。我猜如果没有声明的话，它们会有一个默认值。我应该为这些烦恼吗？

>>  MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.1.26-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(47K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 203 warning(s).
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 6 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 2017-11-01 10:37:53 140638578129152 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 2017-11-01  9:36:43 140283458062592 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 2017-10-29  1:32:36 140572083362048 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 2017-10-25 17:00:42 140183589882112 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 2017-10-25 16:50:06 140142160664832 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 2017-10-13 16:53:26 140715390277888 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 2017-11-01 10:37:50 140283457219328 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 2017-11-01  1:04:54 140572008069888 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 2017-10-29  1:32:23 140183531240192 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 2017-10-25 17:00:31 140142159518464 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 2017-10-25 16:49:53 140715330726656 [Note] /usr/libexec/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 1G (Tables: 96)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User ‘********HIDDEN******@%’ hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 15h 30m 50s (811K q [5.705 qps], 31K conn, TX: 1G, RX: 298M)
[--] Reads / Writes: 63% / 37%
[--] Binary logging is disabled
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 53.2M
[--] Total buffers: 2.1G global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.1G (27.42% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (32.74% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/811K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Aborted connections: 0.52%  (164/31387)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 473K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 154K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 99% (92K on disk / 92K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 97% (251 open / 257 opened)
[OK] Open file limit used: 31% (324/1K)
[OK] Table locks acquired immediately: 98% (1M immediate / 1M locks)

-------- 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)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.0% (398M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.0G/619.0M
[OK] Read Key buffer hit rate: 100.0% (92M cached / 11K reads)
[!!] Write Key buffer hit rate: 93.4% (362K cached / 338K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 69.9% (308K cached / 92K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Add skip-innodb to MySQL configuration to disable InnoDB
    Restrict Host for user@% to user@SpecificDNSorIp
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
    Performance should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    performance_schema = ON enable PFS

Answer 1

以下建议需要您在每天仅实现一项之前进行研究。有些可以动态应用。下面是对 [mysqld] 部分 cfg/ini 值的建议，可以修改、添加或删除。

log_warnings=2 # for additional info in error log on failed connections
max_connections=50 # from 151, until you need more concurrent connections
have_query_cache=0 # from YES default, not used, avoid QC overhead
next TWO lines apply as a pair on the same day
tmp_table_size=64M # from default 16M for RAM use vs HDD
max_heap_table_size=64M # from default 16M for RAM use vs HDD

除非您知道需要为特定目的分配内存，否则请避免进行更改。 MySQLTuner 在许多情况下仅提供(通常有用的)信息。例如不使用QC时的query_cache建议。

实现建议并实现大约 7-10 天的连续正常运行后，请在您的问题中发布以下文本结果

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
and our complete my.cnf/ini

获取更多建议。

您花两分钟的时间浏览 mysqlcalculator.com 将非常有助于确认 RAM 利用率以及对您的服务器影响最大的值。