在Serverfault上遇到这个问题几天没有运气。

我已经在 VPS 上运行了 mysqltuner.pl，并且对于要更改的变量的建议有很多问题。我敢肯定，这些都是具有复杂答案的一般性问题。

我没有足够的知识来编写查询并在服务器上对其进行测试，但我只是想从运行五个 WordPress 网站的服务器中获得更高的性能，每月浏览量 >200,000 次。

我已经通过 phpmyadmin 优化了数据库(并且经常这样做)，但是调谐器仍然说有碎片表。而且因为这是 WordPress，所以我无法更改核心代码中的查询。

但是我应该将 query_cache_size 和 innodb_buffer_pool_size 等变量增加多少？其他 innodb 变量呢？

建议的一些变量在 my.cnf 中不存在，例如 table_cache，并且在调谐器报告中被标记等。我可以将它们添加到 my.cnf 中吗？

(And why is this block duplicated in my.cnf? Can I delete the duplicate?)

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

下面是my.cnf和mysqltuner的输出:

my.cnf 的内容:

query-cache-type = 1
query-cache-size = 8M

set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

old_passwords=1

skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

mysqltuner 的输出:

------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 133M (Tables: 637)
[--] Data in InnoDB tables: 10M (Tables: 344)
[--] Data in MEMORY tables: 126K (Tables: 2)
[!!] Total fragmented tables: 69

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 24m 13s (2M q [22.135 qps], 116K conn, TX: 4B, RX: 530M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 35.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 303.7M (8% of installed RAM)
[OK] Slow queries: 0% (4/2M)
[OK] Highest usage of available connections: 53% (53/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/46.1M
[OK] Key buffer hit rate: 99.6% (749M cached / 2M reads)
[OK] Query cache efficiency: 32.2% (685K cached / 2M selects)
[!!] Query cache prunes per day: 948863
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 660K sorts)
[!!] Temporary tables created on disk: 46% (400K on disk / 869K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 24K opened)
[OK] Open file limit used: 10% (109/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 10.6M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 10M)

最佳答案

我会尽力提供帮助。 MysqlTuner 报告暗示您在此 VPS 中有 4GB RAM，因此我的建议基于此。

query_cache_size - 这是 MySQL 可用于缓存数据库查询结果的 RAM 量。存储在查询缓存中的结果返回速度比正常选择快得多，因此这个变量可以显着加快速度(比任何其他建议的更改都快)。

您需要做一些实验才能确定正确的值。您当前将此设置为 8M。如果你在这个盒子里有 4GB 的 RAM，我会从 64M 开始，如果需要的话增加到 128M 和 256M。每次更改后，放置几天，然后再次运行 MysqlTuner 并将“查询缓存效率”的百分比与之前的百分比进行比较。对于主要托管 5 个 Wordpress 博客的服务器，我怀疑您是否会看到超过 256M 的任何改进，并且我不建议超过总 RAM 的八分之一。

就我个人而言，我发现 Munin(一种免费的服务器监控工具)对于密切关注此类事情非常方便，因为它会绘制缓存命中率与其他查询的对比图。

tmp_table_size - 对于一些复杂的查询(特别是那些使用 GROUP BY 或复杂排序的查询)，MySQL 需要首先创建一个包含数据的临时表，然后对其运行一些操作以创建结果集。它将尝试在内存中创建这些临时表，因为这比在磁盘上创建它们快得多；但对于大型结果集，这并不总是可能的。 tmp_table_size 控制这个阈值。

我无法想象 Wordpress 会执行任何非常复杂的查询，所以我不会过度使用这个查询。 MysqlTuner 建议值大于 32MB，因此从 64M 开始，几天后查看这对“在磁盘上创建的临时表”值有何影响。按照建议设置 max_heap_table_size。

thread_cache_size - 默认情况下 Wordpress 不使用持久连接(这很好)，因此每个请求都会与您的数据库建立新连接，然后在页面生成后关闭它。这个开销并不大，但是使用 thread_cache_size 允许 MySQL 重用这些连接线程，这会有所帮助。

我会采用建议值 4，除非您获得大量并发用户，否则我认为这会很好。

table_cache - 这个有点看不懂，好像跟MySQL的表结构缓存有关。为此，我会选择 128。

innodb_buffer_pool_size - 这是 MySQL 可用于缓存 InnoDB 表的索引和数据的内存量。这让我有点困惑，因为我认为 Wordpress 根本不使用 InnoDB——你在这台服务器上还有其他网站吗？

为了回答您的其他问题，[mysqld_safe] 之后的配置仅适用于安全模式下的 MySQL 守护程序，而不适用于整个 MySQL，因此这就是某些变量重复的原因。如果你确实改变了 innodb_buffer_pool_size，你会想要改变第一个。您可以添加不在文件中的变量，是的，但是出于同样的原因将它们添加到 [mysqld_safe] block 之上。

最后，既然您想要优化，如果您还没有使用 PHP 字节码缓存，例如 APC那么这就值得探讨了。 APC 可以在没有任何负面影响的情况下显着提高 PHP 应用程序的速度。

关于mysqltuner对my.cnf的建议和修改，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/3753504/