如果我考虑将我的WordPress DB表更改为InnoDB，会对WordPress的工作方式产生影响吗？

时间：2012-07-18 作者：turbonerd

我找到了一个很棒的职位here 关于针对大型安装优化WordPress。

其中一个步骤涉及lighthttpd 这听起来很好，但目前DirectAdmin不支持。上次我搞砸了一个非DA的安装，我设法弄坏了我的服务器，不得不付钱给专业人员修理它。。。

它的另一个步骤是ALTER 我的所有MySQL表都使用InnoDB引擎而不是MyISAM。

这对我很有吸引力，因为它似乎可以修复the major problems I\'ve been having - i、 e.桌锁。下面是my MySQL的一个示例SHOW PROCESSLIST; 包括SQL语句：

| 17181 | my_db | localhost | my_db | Query   | 35117 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'boraras-brigittae\' AND wp_po |
| 17182 | my_db | localhost | my_db | Query   | 35118 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17183 | my_db | localhost | my_db | Query   | 35117 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'boraras-brigittae\' AND wp_po |
| 17184 | my_db | localhost | my_db | Query   | 35117 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'boraras-brigittae\' AND wp_po |
| 17185 | my_db | localhost | my_db | Query   | 35041 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'akysis-vespa\' AND wp_posts.p |
| 17186 | my_db | localhost | my_db | Query   | 35050 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'images\',\'profiledotline-gif\') A |
| 17187 | my_db | localhost | my_db | Query   | 35050 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'images\',\'picarrow-gif\') AND (po |
| 17188 | my_db | localhost | my_db | Query   | 35043 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17189 | my_db | localhost | my_db | Query   | 35041 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'m\',\'marginal\') AND (post_type = |
| 17190 | my_db | localhost | my_db | Query   | 34989 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17191 | my_db | localhost | my_db | Query   | 34989 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'w\',\'wet-dry-filter\') AND (post_ |
| 17192 | my_db | localhost | my_db | Query   | 34990 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17193 | my_db | localhost | my_db | Query   | 34954 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'corydoras-sterbai\' AND wp_po |
| 17194 | my_db | localhost | my_db | Query   | 34954 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17195 | my_db | localhost | my_db | Query   | 34955 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17196 | my_db | localhost | my_db | Query   | 34954 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17197 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17198 | my_db | localhost | my_db | Query   | 34955 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17199 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17200 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17201 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\',\'lofiversion\',\'index-ph |
| 17202 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 17204 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17205 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'sawbwa-resplendens\' AND wp_p |
| 17206 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'badis-sp-buxar\' AND wp_posts |
| 17207 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'melanotaenia-boesemani\' AND  |
| 17208 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'betta-prima\' AND wp_posts.po |
| 17209 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 17210 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17211 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17213 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17214 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17215 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'knowledge-base\') AND (post_type |
| 17216 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17217 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17218 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'badis-sp-buxar\' AND wp_posts |
| 17219 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\',\'lofiversion\',\'index-ph |
| 17220 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'betta-prima\' AND wp_posts.po |
| 17221 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17222 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17223 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17224 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'hemichromis-lifalili\' AND wp |
| 17225 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17226 | my_db | localhost | my_db | Query   | 34862 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'apistogramma-borellii\' AND w |
| 17227 | my_db | localhost | my_db | Query   | 34863 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'melanotaenia-lacustris\' AND  |
| 17228 | my_db | localhost | my_db | Query   | 34862 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'melanotaenia-lacustris\' AND  |
| 17229 | my_db | localhost | my_db | Query   | 34861 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'mikrogeophagus-altispinosus\' |
| 17230 | my_db | localhost | my_db | Query   | 34861 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.ID = 25503 AND wp_posts.post_type = \'atta |
| 17231 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'piaractus-brachypomus\' AND w |
| 17232 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17233 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'knowledge-base\') AND (post_type |
| 17234 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17235 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 17236 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17237 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 17238 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'m\',\'migrate\') AND (post_type =  |
| 17239 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'hemichromis-lifalili\' AND wp |
| 17240 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17241 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 17242 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17243 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17244 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\',\'lofiversion\',\'index-ph |
| 17245 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17246 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = \'publish\' AND post_type IN (\'post\', \'page |
| 17247 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 17248 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'geophagus-brokopondo\' AND wp |
| 17249 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = \'hyphessobrycon-amandae\' AND  |
| 17253 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = \'post\' AN |
| 17255 | my_db | localhost | my_db | Query   | 34786 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'knowledge-base\') AND (post_type |
| 17260 | my_db | localhost | my_db | Query   | 34786 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'s\',\'silver-sand\') AND (post_typ |
| 17262 | my_db | localhost | my_db | Query   | 34786 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN (\'forums\') AND (post_type = \'page |
| 25505 | root      | localhost | NULL      | Query   |     2 | NULL                         | SHOW PROCESSLIST
我已启用slow-query-log 并且可以找到输出（已通过分析脚本运行）here.

然而，正如我所尝试的ALTER “我的表”，上发生了以下错误wp_posts: ERROR 1214 (HY000) at line 14: The used table type doesn\'t support FULLTEXT indexes

所以，我的问题有三个，但只有一个：

我可以使用InnoDB并保留WordPress似乎使用的全文索引吗

2 个回复
SO网友:Damien

最近，我尝试了Ubuntu的LAMP堆栈和WordPress安装，并考虑改为InnoDB或NGINX。

为了设置场景，我运行了Next Gen Gallery插件，我注意到它的表“不同”。所以我想调查一下改成InnoDB的情况。

Can I use InnoDB without having an adverse effect on my WordPress installation?<首先，不要更改为InnoDB，它会以你无法想象的方式破坏内容。你正试图优化你的网站，而不必从头开始。你最好先用你拥有的东西工作。

如果您想了解发生了什么，请设置一个测试站点并使用当前数据库的导出，以及所有相同的插件等。它将生成一个DB failed connection错误，您只需转到SQLyog或Sequel Pro并从SQL备份中还原即可。

Optimisation
我已经设置了一个站点，使用hyperDB 3台前端服务器，负载平衡和一个CDN。每天对2000名concurrents/30000名访客进行了负载测试。

所有这些都通过W3 Total Cache、MemCached服务器、Hyper DB和CDN得到了很好的管理。我正在为一个中型网站使用默认的mysql配置文件。

就我而言，我很幸运，内容每周都会更改，所以我可以缓存所有内容。

Apache Max ConnectionsApache的默认配置文件将允许最多150个客户端和1000个请求，因此，如果您有150个访问者，那么您的一台服务器上可能有150000个请求。

将此设置为5或15个最大客户端，以将最大请求数增加到2000（这意味着在Apache生成新进程之前）

将备用线程设置为1或2-这是Apache保留的内存保留。

将Keep-Alive-timeout设置为15，即15秒，因此如果apache收到相同的请求，则不会启动新线程

看见http://www.devside.net/articles/apache-performance-tuning

out of memory - apache / database / timeout
关于锁定的表，我了解到数据库会话保持活动状态的时间过长，这会导致内存不足。从痛苦的经历中。这是一个一/二的组合-您认为您需要更多的数据库连接，因此您增加了这些连接，连锁效应是数据库内存不足。

您没有说为什么不能缓存更多数据并减少数据库请求？另外，DB服务器与apache位于不同的盒子上，这会产生很大的不同。

mqsql.cnf
默认情况下，此项为空。。。在usr/share/doc/mysql-server-5.0/examples/my-medium下进行扫描。cnf。广州

我知道我是从我的小朋友开始的。cnf和将DB与Apache分离后，一切都稳定了下来

Alternative PHP Cache (APC)
参考您的5个基本优化步骤http://www.619cloud.com/blog/5-essential-steps-for-hosting-wordpress/
您可能会发现APC没有在您的服务器上运行。当您转到WordPress>Performance>General并单击兼容性检查时，W3 Total Cache会告诉您什么？结果是什么？

最后这是什么网站我设置是相同的太
http://www.rackspace.com/blog/deploying-scalable-wordpress/

SO网友:anu

这里有很多关于切换到InnoDB的信息

https://wordpress.stackexchange.com/search?q=innodb

有很多事情需要考虑：

InnoDB有助于解决您面临的争用问题，即当您有正在写入和读取的表时not 支持全文索引，因此依赖于该索引的插件将中断或工作缓慢

您really 需要记录慢速查询，然后EXPLAIN 您可能只需要优化表或添加额外的索引，就可以让他们了解发生了什么，很可能您的一些插件正在执行查询，这些查询会强制对数据库进行顺序搜索。如果是这样的话，找出并添加索引来加速这些操作

结束
标签： mysql   optimization   innodb   小码农  

相关推荐

MySQL查询。按元关键字排序

我已经为此奋斗了很长时间，开始觉得我错过了一些重要的东西。CPT帖子可以有元数据，也可以没有元数据（_vip_post [0|1], _thumbnail_id [null|numeric] ). 如果我查询_thumbnail_id 没关系，而且有效：SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta m ON ( wp_posts.ID = m.post_id AND m.meta_k