加速(缓慢的)巨大的wordpress数据库 [英] Speeding up a (slow) huge wordpress database
问题描述
我正在测试一个新项目，该项目涉及使用wordpress安装程序，该程序具有超过150万个帖子，通常，帖子内容/标题仅为一两行-相当短.
I'm testing out a new project which involves the use of a wordpress installation which has over 1.5 million posts, generally the post content/title is only a line or two - so quite short.
我已经获得了强烈推荐的W3-cache插件，该插件对您有很大帮助-但是，当您首次登陆页面时，加载并生成它的缓存大约需要40-60，并且该站点具有包含超过100万个帖子，我想将它们全部缓存起来将是一场灾难-因为只有5％的帖子会定期查看.
I've already got the highly recommended W3-cache plugin, which helps a lot - but when you first land on a page it takes a good 40-60 to load and generate a cache of it, and with a site that contains over 1 million posts I'm guessing having them all cached is going to be a disaster - as only about 5% of these will be viewed on a regular basis.
以下是帖子的标准构建的过程，我可以做些什么来改变/加快明显的瓶颈吗?我什至不确定JOIN在做什么?当然，所有需要发生的事情都是通过ID发帖.耗时很长的查询看起来像是显示多个帖子并根据元数据对其进行排序的查询-我在帖子页面上不需要这些信息吗?
Below is what's going on for a standard build of a post, is there anything I can do to change/speed up the obvious bottle neck? I'm not even sure what the JOIN is doing? surely all that needs to happen is get post by ID. The query that's taking so long looks like it's a query to show a number of posts and order them based on metadata - which I don't need on the post page?
[5] => Array
(
[0] => SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'wpfp_favorites' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC LIMIT 0, 1
[1] => 43.2097918987
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, start_post_rel_link, get_boundary_post_rel_link, get_boundary_post, get_posts, WP_Query->query, WP_Query->get_posts, W3_Db->query
)
[6] => Array
(
[0] => SELECT p.* FROM wp_posts AS p WHERE p.post_date < '0000-00-00 00:00:00' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1
[1] => 7.29560852051E-5
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, adjacent_posts_rel_link_wp_head, adjacent_posts_rel_link, get_adjacent_post_rel_link, get_adjacent_post, W3_Db->query
)
[7] => Array
(
[0] => SELECT p.* FROM wp_posts AS p WHERE p.post_date > '0000-00-00 00:00:00' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1
[1] => 1.78813934326E-5
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, adjacent_posts_rel_link_wp_head, adjacent_posts_rel_link, get_adjacent_post_rel_link, get_adjacent_post, W3_Db->query
)
[8] => Array
(
[0] => SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentyeleven' LIMIT 1
[1] => 1.00135803223E-5
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, _custom_background_cb, get_background_image, get_theme_mod, get_theme_mods, get_option, W3_Db->query
)
[9] => Array
(
[0] => SELECT option_value FROM wp_options WHERE option_name = 'mods_Twenty Eleven' LIMIT 1
[1] => 8.82148742676E-6
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, _custom_background_cb, get_background_image, get_theme_mod, get_theme_mods, get_option, W3_Db->query
)
[10] => Array
(
[0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1034759) ORDER BY t.name ASC
[1] => 1.31130218506E-5
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, body_class, get_body_class, get_post_format, get_the_terms, wp_get_object_terms, W3_Db->query
)
[11] => Array
(
[0] => SELECT DISTINCT post_author FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' LIMIT 2
[1] => 1.31130218506E-5
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, body_class, get_body_class, apply_filters, call_user_func_array, twentyeleven_body_classes, is_multi_author, W3_Db->query
)
[12] => Array
(
[0] => SELECT * FROM wp_posts WHERE (post_type = 'page' AND post_status = 'publish') AND ( ID <> 1232798 ) ORDER BY menu_order,wp_posts.post_title ASC
[1] => 1.00135803223E-5
[2] => require, require_once, include, get_header, locate_template, load_template, require_once, wp_nav_menu, call_user_func, wp_page_menu, wp_list_pages, get_pages, W3_Db->query
)
[13] => Array
(
[0] => SELECT * FROM wp_users WHERE ID = 4031 LIMIT 1
[1] => 2.00271606445E-5
[2] => require, require_once, include, the_post, WP_Query->the_post, setup_postdata, get_userdata, W3_Db->query
)
[14] => Array
(
[0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1034759) ORDER BY t.name ASC
[1] => 1.78813934326E-5
[2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_the_tags, get_the_terms, wp_get_object_terms, W3_Db->query
)
[15] => Array
(
[0] => SELECT * FROM wp_comments WHERE comment_approved = '1' AND comment_post_ID = 1034759 ORDER BY comment_date_gmt ASC
[1] => 2.09808349609E-5
[2] => require, require_once, include, comments_template, get_comments, WP_Comment_Query->query, W3_Db->query
)
[16] => Array
(
[0] => SELECT post_id, meta_value, post_status FROM wp_postmeta LEFT JOIN wp_posts ON post_id=wp_posts.ID WHERE post_status='publish' AND meta_key='wpfp_favorites' AND meta_value > 0 ORDER BY ROUND(meta_value) DESC LIMIT 0, 5
[1] => 1.50203704834E-5
[2] => require, require_once, include, get_sidebar, locate_template, load_template, require_once, dynamic_sidebar, call_user_func_array, wpfp_widget_view, wpfp_list_most_favorited, W3_Db->query
)
不管上面的问题如何，我现在都在共享主机上-很明显，它不会减少它，我想问的是，如果您在哪里运行这种网站-什么样的服务器规格/主机您打算考虑处理这种安装规模的计划吗?每周都有几千个访问者开始上班.
Regardless of the above question, I'm on shared hosting at the momment - so obviously it's not going to cut it, what I want to ask is if you where running this sort of site - what sort of server specs/hosting plan would you be looking at to handle this size of installation? With a few thouand vistors to start per week moving up.
推荐答案
1)在WAMP上使用mysqltuner(如果没有root用户，则无法安装在共享主机上)调整MySQL并更改查询缓存，内存等. WAMP和最终的实时服务器上的巨大差异. https://github.com/rackerhacker/MySQLTuner-perl
1) Use mysqltuner on WAMP (you can't install on shared hosting without root) to tune MySQL and change query cache, memory, etc. Will make a huge difference on WAMP and the eventual live server. https://github.com/rackerhacker/MySQLTuner-perl
2)确保从数据库中删除帖子/页面修订. WP可以存储许多修订，这些修订极大地影响数据库的速度.我看到删除帖子/页面修订后，数据库大小减少了90％.
2) Be sure to delete post/page revisions from the database. WP can store many revisions that greatly impact DB speed. I've seen DB sizes drop 90% after deleting post/page revisions.
在phpmyadmin中作为SQL查询运行以删除修订版本；必要时更改表前缀:
Run as SQL query in phpmyadmin to delete revisions; change table prefix as necessary:
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'
然后优化所有表.然后添加
Then optimize all tables. And then add
define ('WP_POST_REVISIONS', FALSE);
wp-config.php顶部附近(在打开<?php ...之后的某个位置)以禁用将来的修订.
near the top of wp-config.php (somewhere after the opening <?php ... ) to disable future revisions.
3)增加php和WP的内存以获得更好的性能:
3) Increase memory for php and WP for better performance:
将php.ini中的memory_limit行编辑为128M:
Edit the memory_limit line in your php.ini to 128M:
memory_limit = 128M;
或在.htaccess文件顶部添加以下行:
Or add this line at the top of your .htaccess file:
php_value memory_limit 128M
如果这不起作用或引发错误，请在打开<?php
If that doesn't work or throws an error, add this line near the top of your wp-config.php file right after the opening <?php
define('WP_MEMORY_LIMIT', '128M');
4)在最终的VPS上，配置http.conf以获得性能，并且可能对MySQL服务器本身使用不同的框.
4) On the final VPS, configure http.conf for performance and possibly use different box for the MySQL server itself.
这篇关于加速(缓慢的)巨大的wordpress数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
