我首先有 6 个站点都在同一个专用服务器上，其中 2 个站点获得非常稳定的流量。我想说的是，在这两个站点上始终至少有 90 到 120 人，其余站点上至少有 15 到 25 人。
这些站点目前运行良好，直到最近 3 天调用付费流量共享公司，为其他 4 个站点提供与主要站点一样多的流量都托管在同一台服务器上 - 导致站点在发出正确请求之前立即超时，如果它确实决定在 2 到 3 次刷新后加载，则需要几加 30 秒才能拉起。一旦您开始向内单击，这些网站将开始加载得更快，但总是会很快回落。
我一直在使用 mysqltuner 对 my.cnf 文件进行更改 - 但设置没有按预期工作。以及将它们调整到正确的曲调。服务器为20TB，并具有更多带宽来支持其自身。
这些网站都是安装了WP-超级缓存的文字印刷机，所以它应该更快。 下面是我的配置设置女巫，我敢肯定已经有点失去平衡了。请注意，用于清理的表错误不是来自WP站点的表，与性能无关。
Mysqltuner 结果
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 23m 10s (11M q [295.373 qps], 666K conn, TX: 77G, RX: 1G)
[--] Reads / Writes: 50% / 50%
[--] Binary logging is disabled
[--] Total buffers: 4.8G global + 4.6M per thread (1000 max threads)
[OK] Maximum reached memory usage: 5.3G (67.58% of installed RAM)
[!!] Maximum possible memory usage: 9.3G (119.39% of installed RAM)
[OK] Slow queries: 0% (678/11M)
[OK] Highest usage of available connections: 10% (104/1000)
[OK] Aborted connections: 0.08% (528/666673)
[OK] Query cache efficiency: 69.3% (2M cached / 3M selects)
[!!] Query cache prunes per day: 130766
[OK] Sorts requiring temporary tables: 0% (109 temp sorts / 179K sorts)
[!!] Temporary tables created on disk: 58% (111K on disk / 191K total)
[OK] Thread cache hit rate: 99% (120 created / 666K connections)
[OK] Table cache hit rate: 40% (2K open / 5K opened)
[OK] Open file limit used: 19% (1K/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 29.0% (19M used / 68M cache)
[OK] Key buffer size / total MyISAM indexes: 65.0M/64.1M
[OK] Read Key buffer hit rate: 100.0% (30M cached / 13K reads)
[!!] Write Key buffer hit rate: 13.0% (239K cached / 208K writes)
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 4.0G/8.7G
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 23.71% (62152 used/ 262144 total)
[OK] InnoDB Read buffer efficiency: 99.99% (390201875 hits/ 390245584 total)
[!!] InnoDB Write Log efficiency: 85.21% (1596393 hits/ 1873419 total)
[OK] InnoDB log waits: 0.00% (0 waits / 277026 writes)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 128M) [see warning above]
innodb_buffer_pool_size (>= 8G) if possible.
innodb_buffer_pool_instances(=4)
这是 my.cnf 文件
[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/dev/shm
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
open_files_limit=10000
query_cache_size=128M
query_cache_type=1
max_connections=1000
max_user_connections=25
wait_timeout=300
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size=64
key_buffer_size=65M
max_allowed_packet=268435456
table_cache=2048
table_definition_cache=2048
#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
myisam_sort_buffer_size=32M # can be increased per sessions if needed for alter tables (indexes, repair)
query_cache_limit=2M # leave at default unless there is a good reason
join_buffer=2M # leave at default unless there is a good reason
sort_buffer_size=2M # leave at default unless there is a good reason
#read_rnd_buffer_size=256K # leave at default unless there is a good reason
#read_buffer_size=2M # leave at default unless there is a good reason
collation_server=utf8_unicode_ci
character_set_server=utf8
general_log=0
slow_query_log=1
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
long_query_time=5 # select * from mysql.slow_log order by start_time desc limit 10;
low_priority_updates=1
innodb_file_per_table=1
innodb_buffer_pool_size=4G # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
innodb_additional_mem_pool_size=62M
innodb_log_buffer_size=62M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
default-storage-engine=MyISAM
[mysqld_safe]
我在这里做的流量共享网站可能会伤害你，因为过度 ping 你的服务器来获取他们发送给你的流量的统计数据 - 不确定这是一个值得去的兔子洞，还是它只是我的配置不佳。任何帮助，想法或想法将不胜感激。
非常感谢！
"碎片表" -- 假的;不要运行优化。
"危险的高内存使用率" - 不，不是。 但要关闭它，请将max_connections减少到 200。
query_cache_size有点高，为128米;不要提高它。
许多基于磁盘的 tmp 表 -- 将long_query_time降低到 1（秒）并打开 SlowLog。 一两天后回来，让我们看看顽皮的查询是什么。 请注意，这也会将"慢查询"提高到 0% 以上。 我看到您已将其打开并发送到表。 因此，请使用select * from mysql.slow_log order by query_time desc limit 5来获取有趣的查询。 让我们一起讨论它们，SHOW CREATE TABLE.
MyISAM和InnoDB的指标还不错;不需要采取任何行动。
tmp_table_size=512M和max_heap_table_size=512M高得危险;将它们保持在RAM的1%以下。
table_cache=2048 - 即使在起床的前10个小时里也有一些捶打; 增加到4K。
您似乎同时使用MyISAM和InnoDB。