我有一个 12GB RAM 的 vps。目前它托管了一个 wordpress 网站。根据 Jetpack 的数据，该网站每天的 UV 约为 10k，浏览量约为 30k。
我收到很多建立数据库连接错误。
这是我的 my.cnf 文件:(我知道我可能做错了什么，但我从互联网上复制了该文件并将其添加到我的服务器）
[mysqld]
performance-schema=0
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"
[mysqld]
performance-schema=0
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=10G
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
log-bin=mysql-bin
myisam_sort_buffer_size=64M
expire_logs_days=7
query_cache_size=128M
thread_cache_size=12
max_allowed_packet=15M
skip-federated
table_definition_cache=2048
local-infile=0
table_open_cache=8192
max_connections=60
read_buffer_size=2M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
thread_concurrency=16
sort_buffer_size=2M
port=3306
join_buffer_size=16M
key_buffer_size=600M
query_cache_limit=10M
socket="/var/lib/mysql/mysql.sock"
skip-external-locking
query-cache-type=1
long_query_time=5
default-storage-engine=InnoDB
tmp_table_size=384M
max_heap_table_size=384M
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
这是 mysqltuner.pl 的输出：
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.35-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ----------------------------------------------------- -------------
[--] Log file: /var/lib/mysql/host1.cloudserverpanel.com.err(1M)
[OK] Log file /var/lib/mysql/host1.cloudserverpanel.com.err exists
[OK] Log file /var/lib/mysql/host1.cloudserverpanel.com.err is readable.
[OK] Log file /var/lib/mysql/host1.cloudserverpanel.com.err is not empty
[OK] Log file /var/lib/mysql/host1.cloudserverpanel.com.err is smaller than 32 Mb
[!!] /var/lib/mysql/host1.cloudserverpanel.com.err contains 3340 warning(s).
[!!] /var/lib/mysql/host1.cloudserverpanel.com.err contains 22 error(s).
[--] 6 start(s) detected in /var/lib/mysql/host1.cloudserverpanel.com.err
[--] 1) 2017-05-21 20:34:30 16325 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-05-21 20:19:27 11180 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-05-21 20:03:04 7569 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-05-21 19:49:21 5230 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2017-05-20 06:10:06 713 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2017-05-19 19:37:13 5772 [Note] /usr/sbin/mysqld: ready for connections.
[--] 15 shutdown(s) detected in /var/lib/mysql/host1.cloudserverpanel.com.err
[--] 1) 2017-05-21 20:29:32 14760 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-05-21 20:28:23 13925 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-05-21 20:28:19 11180 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2017-05-21 20:14:38 9938 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2017-05-21 20:14:28 9693 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2017-05-21 20:10:17 8761 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2017-05-21 20:10:14 7569 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2017-05-21 20:03:03 5230 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2017-05-21 19:48:03 4757 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2017-05-21 19:47:54 4531 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics ---------------------------------------------------- -------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +P ERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 229K (Tables: 6)
[--] Data in InnoDB tables: 138M (Tables: 94)
[OK] Total fragmented tables: 0
-------- 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: 20h 22m 22s (3M q [41.069 qps], 61K conn, TX: 13G, RX: 515M)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 11.6G
[--] Max MySQL memory : 12.3G
[--] Other process memory: 1.4G
[--] Total buffers: 11.1G global + 20.5M per thread (60 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 12.3G (106.49% of installed RAM)
[!!] Maximum possible memory usage: 12.3G (106.31% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/3M)
[!!] Highest connection usage: 100% (61/60)
[!!] Aborted connections: 15.70% (9695/61744)
[!!] name resolution is active : a reverse name resolution is made for each new connect ion and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 88.9% (2M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 86% (8K on disk / 10K total)
[OK] Thread cache hit rate: 98% (951 created / 61K connections)
[OK] Table cache hit rate: 96% (186 open / 193 opened)
[OK] Open file limit used: 0% (61/65K)
[OK] Table locks acquired immediately: 99% (275K immediate / 275K locks)
[OK] Binlog cache memory access: 99.95% (17590 Memory / 17599 Total)
-------- Performance schema ----------------------------------------------------------- -------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ----------------------------------------------------------- -------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics --------------------------------------------------------------- -------------
[!!] Key buffer used: 18.1% (113M used / 629M cache)
[OK] Key buffer size / total MyISAM indexes: 600.0M/211.0K
[OK] Read Key buffer hit rate: 99.4% (28K cached / 163 reads)
[!!] Write Key buffer hit rate: 53.2% (8K cached / 4K writes)
-------- InnoDB Metrics --------------------------------------------------------------- -------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 10.0G/138.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.9375 %): 48.0M * 2/10.0G s hould be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (14114607 hits/ 14119114 total)
[!!] InnoDB Write Log efficiency: 61.48% (39497 hits/ 64247 total)
[OK] InnoDB log waits: 0.00% (0 waits / 24750 writes)
-------- AriaDB Metrics --------------------------------------------------------------- -------------
[--] AriaDB is disabled.
-------- 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/lib/mysql/host1.cloudserverpanel.com.err file
Control error line(s) into /var/lib/mysql/host1.cloudserverpanel.com.err file
MySQL started within 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.
Reduce or eliminate persistent connections to reduce connection usage
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration wit h skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 60)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (=0)
query_cache_type (=0)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer p ool size (=5G) if possible.
innodb_buffer_pool_instances(=10)
答案1
看起来你为 Mysql 使用了太多内存，而你的其他进程，比如应用程序服务器、PHP 处理器，甚至 Mysql 连接器都处于饥饿状态。
如果没有错误日志，我猜测问题出在这里：
innodb_buffer_pool_size=10G
将其更改为类似数据库的总大小（仅当您的系统有大量密集查询时）。
我会将其更改为 1GB 左右，并在高负载下测试响应时间，然后增加该值并再次检查。也许您不需要为 innodb 缓存保留所有内存。
如需更完整的解释，请参见此处：
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
[]的
答案2
你刚才提到了服务器的内存，那么 VPS 上的 CPU 核心数等是多少？首先，当你收到错误时建立数据库连接时出错你应该检查你的内存使用情况免费-m和你的 mysql 进程列表显示进程列表这将让你了解系统上正在发生的事情，同时使用 top 检查进程列表并检查 apache 进程
我还可以看到你的 my.cnf 配置使用了 10 gbinnodb_buffer_pool_size=10G12 GB 的 RAM 中，请记住，您还需要内存来配置其他参数，例如表定义缓存=2048以及其他系统进程，如 apache 等
对于 my.cnf 配置参数，您可以使用佩科纳配置工具将指导你根据系统配置设置 my.cnf 配置，这里只提到 mysql 的内存为 8gb（12gb 中），因为你还有 apache，它需要内存
其他可能影响性能的因素包括系统硬盘速度和网络带宽