针对小型服务器的Mysql调优

Question

我有一个运行在Ubuntu 2内核、2GB ram和2GB交换程序下的小型云服务器，它使用Panel、Apache、Nginx作为反向代理和Mysql 5.5。我这里有大约18个域名，每个域名都有自己的数据库。大多数网站都是在WordPress或Joomla下运行的。

所有网站加在一起每天的访问量不超过2000次。我最大的问题是mysql，因为它看上去正在消耗我的RAM，服务器只需要1天的交换时间，而且服务器看起来不需要使用超过70%的RAM，但仍然使用交换。Swappines设置为10。

我已经使用mysqltuner.pl来调优Mysql，但是我无法让它按其应有的方式工作。

下面是mysqltuner输出：

>>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0ubuntu0.14.04.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MEMORY tables: 0B (Tables: 2)
[--] Data in MyISAM tables: 21M (Tables: 284)
[--] Data in InnoDB tables: 170M (Tables: 920)
[!!] Total fragmented tables: 129

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 15h 23m 44s (632K q [4.458 qps], 11K conn, TX: 2B, RX: 167M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Total buffers: 1.0G global + 3.6M per thread (100 max threads)
[OK] Maximum reached memory usage: 1.1G (55.03% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (70.29% of installed RAM)
[OK] Slow queries: 0% (0/632K)
[OK] Highest usage of available connections: 16% (16/100)
[!!] Aborted connections: 4.23%  (473/11192)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 100K sorts)
[!!] Joins performed without indexes: 10841
[!!] Temporary tables created on disk: 49% (48K on disk / 98K total)
[OK] Thread cache hit rate: 99% (16 created / 11K connections)
[OK] Table cache hit rate: 80% (1K open / 2K opened)
[OK] Open file limit used: 7% (628/8K)
[OK] Table locks acquired immediately: 100% (802K immediate / 802K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.5% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/10.8M
[OK] Read Key buffer hit rate: 96.0% (2M cached / 115K reads)
[!!] Write Key buffer hit rate: 89.0% (27K cached / 3K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 512.0M/170.5M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 33.34% (10925 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 99.99% (78464393 hits/ 78474138 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 28642 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB 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
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 1.0M, or always use indexes with joins)

免费提供-m：

             total       used       free     shared    buffers     cached
Mem:          1995       1727        267        282        245        692
-/+ buffers/cache:        789       1205
Swap:         1951        370       1581

我非常感谢你的任何建议。谢谢你的时间:)

Mysql全局变量由于大约有307行，所以我将它们添加到Pastebin：http://pastebin.com/bVUUG9zL中

我在斯库班图上发现了这个，有可能吗？因为我检查了服务器监视器日志，最大内存使用量为25%。

Answer 1

我同意已经做过的建议--只有当你完全理解发生了什么，但最重要的是其他建议时，才会检查跑步者的脚本建议。

配置参数只给出了问题的一个侧面视图，另一个问题是--到底发生了什么，什么才是真正的加载？

我只是建议我们从运行中的服务器上检查和收集统计数据的工具之一：

• MONyog - https://www.webyog.com/product/monyog
• JetProfiler - https://www.jetprofiler.com
• Neor Profiler - http://www.profilesql.com

它允许您通过80/20规则1-2查询识别和拆分问题，可以轻松地承担80-90%的加载，简单的查询不使用索引。

这给您提供了准确的信息--哪个参数更适合由runner来调整。