有人能帮我调整我的WordPress站点的MySQL性能吗?2我只是想确保一切都优化到最大。3谢谢!
服务器详细信息:
CentOS 7.9版
一个WordPress站点,自动跳转
虚拟CPU/秒:1个虚拟CPU
内存:2048.00 MB
存储:64 GB NVMe
数据库大小:约60 MB
MySQL调优器:
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log (5M)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 4563 warning(s).
[!!] /var/log/mysqld.log contains 1322 error(s).
[--] 135 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-11-28 8:37:10 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2022-11-28 8:37:09 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2022-11-27 8:55:24 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2022-11-27 8:50:27 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2022-11-27 8:46:18 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2022-11-27 8:34:09 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2022-11-27 8:34:07 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2022-11-27 8:34:02 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2022-11-27 8:33:14 140012789491904 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2022-11-27 8:33:12 140338477058240 [Note] /usr/sbin/mysqld: ready for connections.
[--] 60 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-11-28 8:37:10 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2022-11-27 8:50:26 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2022-11-27 8:46:18 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2022-11-27 8:34:08 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2022-11-27 8:34:06 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2022-11-27 8:33:34 140012513359616 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2022-11-27 8:33:14 140338206811904 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2022-11-27 8:33:12 140442772428544 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2022-11-27T08:32:27.508361Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2022-11-26T18:54:46.107607Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 169.2M (Tables: 137)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 25m 30s (14K q [9.314 qps], 271 conn, TX: 138M, RX: 1M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 1.8G
[--] Max MySQL memory : 13.8G
[--] Other process memory: 0B
[--] Total buffers: 938.0M global + 264.7M per thread (50 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 9.7G (540.65% of installed RAM)
[!!] Maximum possible memory usage: 13.8G (771.06% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 3% (482/14K)
[OK] Highest usage of available connections: 68% (34/50)
[OK] Aborted connections: 0.74% (2/271)
[!!] CPanel and Flex system skip-name-resolve should be on
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 11
[!!] Temporary tables created on disk: 70% (1K on disk / 1K total)
[OK] Thread cache hit rate: 78% (58 created / 271 connections)
[!!] Table cache hit rate: 5% (17K hits / 326K requests)
[OK] table_definition_cache (400) is greater than number of tables (304)
[OK] Open file limit used: 0% (311/40K)
[OK] Table locks acquired immediately: 100% (15K immediate / 15K locks)
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (46.7M used / 256.0M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/10.2M
[OK] Read Key buffer hit rate: 98.6% (195K cached / 2K reads)
[!!] Write Key buffer hit rate: 91.7% (484 cached / 444 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] No tables are Innodb
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 512.0M / 0B
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 64.0M * 2/512.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 4 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 82.98% (2554 hits / 3078 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits / 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 90.8% (10K cached / 1K 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Check warning line(s) in /var/log/mysqld.log file
Check error line(s) in /var/log/mysqld.log file
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL was started within the last 24 hours: recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
name resolution is enabled due to cPanel doesn't support this disabled.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
For more details see: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (40000) variable
should be greater than table_open_cache (1024)
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
skip-name-resolve=0
join_buffer_size (> 4.0M, or always use indexes with JOINs)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
table_open_cache (> 1024)
performance_schema=ON
key_buffer_size (~ 48M)
My.CNF
[mysqld]
performance-schema=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
# pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 64M
# innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 10M
max_connections = 50
wait_timeout = 3600
interactive_timeout = 3600
join_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
key_buffer_size = 256M
max_allowed_packet=268435456
read_rnd_buffer_size = 256K
thread_cache_size = 8
open_files_limit=40000
table_open_cache = 1024
default-storage-engine = MyISAM
innodb_file_per_table = 1
query_cache_type = 0
query_cache_limit = 0
query_cache_size = 0
max_heap_table_size = 32M
tmp_table_size = 32M
具体来说,我如何减少“MySQL的最大内存使用”和“在磁盘上创建临时表”,因为我觉得这些都是非常错误的?
1条答案
按热度按时间dzjeubhm1#
一些问题:
SHOW VARIABLES LIKE 'default_tmp_stor%';
)是什么?您的mysqltuner输出表明您当前的数据库大小为-
和当前关键缓冲区统计数据为-
因此将key_buffer_size从256 M减小到64 M应该是安全的。
您的
max_allowed_packet=268435456
(256 M)看起来不必要的高,但不太可能导致问题。这占了mysqltuner的每个线程使用计算的大部分(97%)。注意,这是一个最大值,而不是默认分配的缓冲区的实际大小(net_buffer_length默认为16,384 - 16 K)。- read more是相当危言耸听和不正确的。它只是全局缓冲区+(每个线程 * 最大使用的连接数)-
基于max_allowed_packet(4 M)的默认值进行相同的计算会得到非常不同的结果-
您当前有512 MB分配给InnoDB的缓冲池,该缓冲池未被使用。关闭InnoDB已被弃用,因此请改为减小缓冲池的大小-
一个
由于最近的重新启动(25 m 30 s),这些数据太少,以至于不可能从中读取任何有意义的内容。某些查询条件总是需要临时磁盘表。由于WP使用TEXT列进行发布,这可能是临时磁盘表数量很高的原因。