mysql數據庫優化建議

我有一個具有這種配置的伺服器：

• 英特爾酷睿 i5-3550 4 x 3.4Ghz
• 16GB DDR3
• 2 個 1000GB

軟體：

• nginx
• php7.4
• 瑪麗亞數據庫
• WordPress
• xenforo

我每天有 30.0000 個獨立訪問者和 250.000 次瀏覽量。每天更新大約 30 個 Wordpress 文章和大約 300 個 xenforo 文章和執行緒。

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)

配置設置：

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/

使用 mysqltuner 的第一條規則是不要使用 mysqltuner。mysqltumer 的第二條規則是你不要使用 mysqltuner。

它只會為沒有經驗的 DBA 製造謊言來破壞他們的伺服器性能。

除非您確切知道自己在做什麼，否則您應該從預設值更改總共兩個設置：

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

其他一切都可能使性能變得更糟。在您的“# Buffers”部分中，幾乎可以肯定，其中的每一個都會使您的性能比預設值更差。事實上，該配置中至少有一半會使事情變得更糟，其餘的要麼是多餘的，要麼是危險的。

因此，作為第一遍，在解決方案中，將“# Buffers”之後的所有內容減少為：

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

幾乎可以肯定，那裡的其他一切都會使事情變得更糟。

代替：

innodb_locks_unsafe_for_binlog  = 1

你應該使用：

transaction_isolation = READ-COMMITTED

除了已經提出的答案和評論之外，我還有兩件事：

• 將您的表從 MyISAM 移動到 InnoDB。
• 改進 WP 中“元”表的索引：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

引用自：https://dba.stackexchange.com/questions/265800