我们有一个专用服务器，上面运行着一些应用程序，包括 Vbulletin 论坛和 Wordpress 网站。主要的资源密集型应用程序是 PHP 聊天应用程序，它也使用 MYSQL。
每天大约同一时间，服务器似乎都会离线或锁定。CPU 负载较高，但仍在范围内。我没有看到任何正在运行的事件。我怀疑是 MYSQL 出了问题，它出现故障，因为每当我重新启动 MYSQL 时，一切似乎都修复了，但这可能是巧合。
服务器已安装 Cpanel。Nginx 的 Engintron 插件和数据库的 MariaDB 10.1。CentOS 7。
以下是我的MYSQLTuner结果：
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.33-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2G (Tables: 310)
[--] Data in InnoDB tables: 28G (Tables: 308)
[--] Data in MEMORY tables: 16M (Tables: 3)
[!!] Total fragmented tables: 111
[!!] failed to execute: SHOW TABLE STATUS FROM \`#mysql50#.ssh\`
[!!] FAIL Execute SQL / return code: 256
-------- 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: 2d 9h 41m 49s (37M q [182.400 qps], 12M conn, TX: 116G, RX: 12G)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Physical Memory : 62.7G
[--] Max MySQL memory : 51.0G
[--] Other process memory: 2.5G
[--] Total buffers: 35.1G global + 16.3M per thread (1000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 35.9G (57.19% of installed RAM)
[OK] Maximum possible memory usage: 51.0G (81.37% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (518/37M)
[OK] Highest usage of available connections: 4% (46/1000)
[OK] Aborted connections: 0.17% (20794/12263809)
[!!] Query cache efficiency: 10.6% (2M cached / 24M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (22 temp sorts / 6M sorts)
[!!] Joins performed without indexes: 1016
[OK] Temporary tables created on disk: 0% (5K on disk / 15M total)
[OK] Table cache hit rate: 74% (1K open / 1K opened)
[OK] Open file limit used: 0% (787/1M)
[OK] Table locks acquired immediately: 99% (52M immediate / 52M locks)
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[--] Using default value is good enough for your version (10.1.33-MariaDB)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.2% (867M used / 4B cache)
[OK] Key buffer size / total MyISAM indexes: 4.0G/617.7M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 82K reads)
[!!] Write Key buffer hit rate: 42.3% (111K cached / 64K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.9% (253K cached / 5K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 30.0G/28.9G
[!!] InnoDB buffer pool instances: 20
[!!] InnoDB Used buffer: 54.49% (1071288 used/ 1966060 total)
[OK] InnoDB Read buffer efficiency: 99.99% (13800614528 hits/ 13801405527 total)
[OK] InnoDB Write log efficiency: 95.40% (68361750 hits/ 71657060 total)
[!!] InnoDB log waits: 0.00% (39 waits / 3295310 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB 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:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 4.0M, or always use indexes with joins)
innodb_buffer_pool_instances(=30)
innodb_log_buffer_size (>= 8M)
在此先感谢你们提供的任何帮助。
更新：问题似乎是服务器负载过大。我安装了 csf，它扫描服务器太多，所以我把它降低了一点。我还对 my.cnf 做了一些调整。这似乎很有帮助。
答案1
这是我进行一些调整后的新结果：（主要使用一些 csf 调整来控制扫描服务器的时间后，我不再遇到这些崩溃）
一般建议： 运行 OPTIMIZE TABLE 来整理表碎片，以获得更好的性能 MySQL 在过去 24 小时内启动 - 建议可能不准确 调整您的连接查询以始终使用索引 要调整的变量：query_cache_size（> = 8M）join_buffer_size（> 512.0K，或始终使用带有连接的索引）
答案2
对 my.cnf-ini [mysqld} 部分的建议
key_buffer_size=3G # from 4G - only 20% used
key_cache_age_threshold=64800 # from 300 second purge to reduce key_reads
key_cache_division_limit=50 # from 100 for Hot/Warm split of RAM
key_cache_block_size=16384 # from 1024 to reduce CPU usage to manage discards
query_cache_type=0 # for No QC that is only 10% efficient
query_cache_size=0 # from ? to use RAM for more useful purpose
query_cache_limit=0 # from 1M to use RAM for more useful purpose
innodb_buffer_pool_size=38G # from 30G for 60% of RAM remember CHG % setaside
innodb_buffer_pool_instances=8 # from 20 to continue to avoid mutex contention
innodb_lru_scan_depth=100 # from 1024 to save CPU every second - see refman
innodb_log_file_size=32M # from ? for about 1hr before rotation
innodb_log_buffer_size=16M # from 8M indicated by Mysqltuner
max_connections=100 # from 1000 - max_used_connections was 46 over 2 days
. 为了让默认值为您工作并节省 RAM 占用空间， . 用 # 和空格键引导以下内容以使默认值生效。
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
请参阅我的个人资料以获取联系信息。
我们希望随时了解您的进展情况。