MySQL Mysqltuner 建议和对 my.cnf 的更改

Mysqltuner suggestions and changes to my.cnf

提问by markratledge

Had this question on Serverfault for a few days with no luck.

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

I've run mysqltuner.pl on a VPS and have a bunch of questions as to the suggestions on variables to change. I'm sure these are general questions with complex answers.

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

I'm not knowledgable enough to write queries and test them against the server, but am just trying to get a bit more performance out of the server that runs five WordPress sites with >200,000 page views/month.

我的知识不够丰富，无法编写查询并针对服务器对其进行测试，但我只是想从运行五个 WordPress 站点的服务器中获得更高的性能，每个月的页面浏览量超过 200,000。

I've optimized the database via phpmyadmin (and do that regularly), but the tuner still says there are fragmented tables. And because this is WordPress, I can't change queries in core code.

我已经通过 phpmyadmin 优化了数据库（并定期执行此操作），但是调谐器仍然说存在碎片化的表。因为这是 WordPress，我无法更改核心代码中的查询。

But how much should I increase the variables like query_cache_size and innodb_buffer_pool_size? What about the other innodb variables?

但是我应该增加多少像 query_cache_size 和 innodb_buffer_pool_size 这样的变量？其他 innodb 变量呢？

Some of the variables suggested don't exist in my.cnf, like table_cache, and are flagged in tuner report, etc. Can I add them to my.cnf?

一些建议的变量在 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 中重复？我可以删除重复的吗？）

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

Below is the my.cnf and the output of mysqltuner:

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

Contents of my.cnf:

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

Output of mysqltuner:

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)

回答by Tim Fountain

I'll do my best to help here. The MysqlTuner report implies that you have 4GB of RAM in this VPS, so my suggestions are based on that.

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

query_cache_size- This is the amount of RAM MySQL can use to cache the results of database queries. Results stored in the query cache are returned much faster than normal selects, so this variable can significantly speed things up (more so than any of the other suggested changes).

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

Exactly what the correct value is for you will take some experimentation. You currently have this set to 8M. If you have 4GB of RAM in this box I'd start at 64M, increasing to 128M and then 256M if required. After each change, leave things for a few days and then run MysqlTuner again and compare the percentage for 'Query cache efficiency' to what it was before. For a server mainly hosting 5 Wordpress blogs I doubt you'd see any improvement beyond 256M, and I wouldn't recommend going beyond an eighth of your total RAM.

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

Personally I find Munin (a free server monitoring tool) quite handy for keeping an eye on this sort of thing, as it will graph the cache hits vs. other queries.

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

tmp_table_size- for some complex queries (particularly those using GROUP BY or complex sorting), MySQL needs to first create a temporary table containing the data and then run some operations on it in order to create the result set. It will try and create these temp tables in memory, as this is much faster than creating them on disk; but for large result sets this isn't always possible. tmp_table_size controls this threshold.

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

I can't imagine Wordpress is doing any hugely complex queries so I wouldn't go overboard with this one. MysqlTuner is suggesting a value greater than 32MB, so start with 64M and see how this affects the 'Temporary tables created on disk' value after a few days. Set max_heap_table_size while you're at it as it suggests.

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

thread_cache_size- Wordpress doesn't use persistent connections by default (which is good), so each request is making a new connection to your database and then closing this once the page has been generated. This overhead is not significant, but using thread_cache_size allows MySQL to reuse these connection threads which will help a little.

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

I'd go with the suggested value of 4 which I'd imagine will be fine unless you get a high number of concurrent users.

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

table_cache- I'm a little hazy on this one, it seems to relate to MySQL's cache of table structure. I'd go with 128 for this.

table_cache- 我对这个有点模糊，它似乎与 MySQL 的表结构缓存有关。为此，我会选择 128。

innodb_buffer_pool_size- this is the amount of memory MySQL can use to cache indexes and data for InnoDB tables. This one puzzles me a bit as I don't think Wordpress uses InnoDB at all - do you have some other sites on this server as well?

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

To answer your other questions, the configuration after [mysqld_safe]only apply to the MySQL daemon in safe mode, rather than MySQL overall, so that's why some of the variables are duplicated. If you do change innodb_buffer_pool_size, you'll want to change the first one. The variables not in the file you can add, yes, but add them above the [mysqld_safe] block for the same reason.

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

Lastly, since you're in the mood for optimising, if you are not already using a PHP bytecode cache such as APCthen this is worth exploring. APC can give some significant speed improvements to PHP apps without any negative effects.

最后，由于您有优化的心情，如果您还没有使用 PHP 字节码缓存（如APC），那么这值得探索。APC 可以显着提高 PHP 应用程序的速度，而不会产生任何负面影响。

回答by chris

There are more tools out there to tune your mysql database: http://www.day32.com/MySQL/and http://www.maatkit.org/doc/and http://hackmysql.com/mysqlsla

有更多工具可以调整您的 mysql 数据库：http: //www.day32.com/MySQL/和http://www.maatkit.org/doc/和http://hackmysql.com/mysqlsla

In most cases you don't no need to write queries and test them against the server. Just enable the slow query log to identify your slow queries aggregate them with mysqlsla and explain them with maatkit:

在大多数情况下，您不需要编写查询并针对服务器对其进行测试。只需启用慢查询日志来识别你的慢查询，用 mysqlsla 聚合它们并用 maatkit 解释它们：

You could paste the slowest queries from the mysqla results to a text file and execute them with maatkit.

您可以将 mysqla 结果中最慢的查询粘贴到文本文件中，然后使用 maatkit 执行它们。

mk-visual-explain –host hostname –user username –password passwort –database \
databasename -c query1.sql >> query1_data.txt

-

——

mk-query-profiler –host hostname –user username –password passwort –database \
databasename query1_data.txt >> query1_data.txt

Often coosing a newer mysql version is critical to performance. I experienced that the execution plans for complex queries are very different when you compare for example mysql 5.0.23 to 5.1.4. They are executed in our environment much faster with 5.1.4.

通常，更新较新的 mysql 版本对性能至关重要。当您比较例如 mysql 5.0.23 和 5.1.4 时，我发现复杂查询的执行计划非常不同。使用 5.1.4，它们在我们的环境中执行得更快。

Lot's of useful information about mysql can be found at http://www.mysqlperformanceblog.com/and in the book "High Performance MySQL".

在http://www.mysqlperformanceblog.com/和“高性能 MySQL”一书中可以找到许多关于 mysql 的有用信息。

Tabe Cache:According to the book "the table cache stores objects that represent tables. Each object in the cache contains the associated table's parsed .frm file plus other data, depending on the table's storage engine.

Tabe Cache：根据书“表缓存存储表示表的对象。缓存中的每个对象都包含关联表的解析.frm文件以及其他数据，具体取决于表的存储引擎。

The table cache's design is a little MyISAM centric - this is one of the areas where the seperation between the server and the storage engine is not completely clean, for historical reasons. The table cache is a little less important for InnoDB, because InnoDB doesn't rely on it for as many purposes(such as holding file descriptors; it has its own version of a table cache for this purpose). However, even InnoDB benefits from caching the parsed .frm files.".

表缓存的设计有点以 MyISAM 为中心 - 由于历史原因，这是服务器和存储引擎之间的分离不完全干净的区域之一。表缓存对于 InnoDB 不太重要，因为 InnoDB 不依赖它用于许多目的（例如保存文件描述符；为此目的它有自己的表缓存版本）。但是，即使是 InnoDB 也可以从缓存解析的 .frm 文件中受益。”。

If you raise the table cache, there might be errors with the open files limit. You also need to increase the open_files_limit variable on the server and perhaps the operating system open files limit: http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-files/.

如果您提高表缓存，则打开文件限制可能会出错。您还需要增加服务器上的 open_files_limit 变量以及操作系统打开文件限制：http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-files/。

Thread Cache:

线程缓存：

The thread cache holds the threads that aren't currently associated with a connection but are ready to serve new connections. As long as MySQL has a free thread in the cache, it can respond very rapidly to connect requests, because it doesn't have to create a new thread for each connection.

线程缓存保存当前未与连接关联但已准备好为新连接提供服务的线程。只要 MySQL 在缓存中有空闲线程，它就可以非常快速地响应连接请求，因为它不必为每个连接创建一个新线程。

[!!] Temporary tables created on disk: 46% (400K on disk / 869K total)If tmp_table_size and max_heap_table_size are not set yet, increase them. Disk operations are very slow compared to RAM-operations. Does wordpress use lots of blob/text columns? then you won't see much benefits, because BLOB and Text columns are not allowed in memory tables.

[!!] 在磁盘上创建的临时表：46%（磁盘上 400K / 总共 869K）如果尚未设置 tmp_table_size 和max_heap_table_size，请增加它们。与 RAM 操作相比，磁盘操作非常慢。wordpress 是否使用大量 blob/text 列？那么您将看不到太多好处，因为内存表中不允许使用 BLOB 和 Text 列。

[OK] Highest usage of available connections: 53% (53/100)To save RAM you could decrease the allowed max connections. On the other hand you might run out of connections in peak times.

[OK] 可用连接的最高使用率：53% (53/100)为了节省 RAM，您可以减少允许的最大连接数。另一方面，您可能会在高峰时间用完连接。

Using an opcode cache for PHP is a very good idea!

为 PHP 使用操作码缓存是一个非常好的主意！

回答by Todd Moses

My recomendations for tunning MySQL for WP:

我为 WP 调整 MySQL 的建议：

Database tables should be periodically optimized (and repaired if necessary) for optimum performance.

应定期优化数据库表（并在必要时进行修复）以获得最佳性能。

I recommend using WP-DBManagerplugin which provides this functionality as well as database backup, all crucial for any blog installation.

我建议使用WP-DBManager插件，它提供此功能以及数据库备份，对于任何博客安装都至关重要。

WP-DBManager allows you to schedule and forget, and it will take care of all the work automatically.

WP-DBManager 允许您安排和忘记，它会自动处理所有工作。

Other alternative is manually optimizing and repairing your table through a tool like phpmyadmin.

其他替代方法是通过phpmyadmin 之类的工具手动优化和修复您的表。

The MySQL Query Cache saves results of queries in case the query comes by again. However, it only knows how to save the byte-text of queries, not their compiled versions, so small changes to the query will create different cache entries. Turn this on if you don't have unique ids in every query. You can enable it by adding the following to /etc/my.cnf:

MySQL Query Cache 保存查询的结果，以防再次出现查询。但是，它只知道如何保存查询的字节文本，而不知道它们的编译版本，因此对查询的微小更改将创建不同的缓存条目。如果您在每个查询中都没有唯一的 ID，请启用此功能。您可以通过将以下内容添加到 /etc/my.cnf 来启用它：

query_cache_type = 1
query_cache_size = 26214400

回答by Christoph Strasen

This is not a direct answer to your question but in my experience wordpress can be verywell optimized using caching on the frontend servers. Also mostly wordpress seems to be CPU-bound on the frontend machines not on the database. (you might want to double check that indeed you are optimizing the bottleneck).

这不是一个直接回答你的问题，但以我的经验的WordPress可以很使用前端服务器上的缓存很好的优化。此外，大多数 wordpress 似乎在前端机器上受 CPU 限制，而不是在数据库上。（您可能需要仔细检查您是否确实在优化瓶颈）。

Have a look at "w3 total cache" for example. Using it with APC should be the most simple approach. Make sure you have a look at the size of apc.shm_size (in php.ini) and cache-hit ratio (some apc info utility should be supplied with w3tc).

例如，看看“w3 total cache”。将它与 APC 一起使用应该是最简单的方法。确保您查看了 apc.shm_size 的大小（在 php.ini 中）和缓存命中率（w3tc 应提供一些 apc 信息实用程序）。

I have seen wordpress instances running smoothly on a single server with that setup and way more than just 200.000 page views per month.

我已经看到 wordpress 实例在具有这种设置的单个服务器上顺利运行，并且每月的页面浏览量超过 200.000。