Speeding (slow) huge wordpress database

Question

I am testing a new project that includes a wordpress installation that contains more than 1.5 million posts, usually a post-council / headline - only a line or two - so short.

I already have the recommended W3-cache plugin that helps a lot - but when you first land on the page, it takes $ 40-60 to download and create a cache, and the site contains more than 1 million messages, which I assume all of them caching will be a disaster - since only about 5% of them will be viewed on a regular basis.

Below is information on what happens for standard message assembly, is there anything I can do to change / speed up the obvious neck of the bottle? I don’t even know what JOIN does? Of course, all that should happen is to receive the message by ID. A query that takes so long looks like a query showing the number of messages and sorting them based on metadata that I don’t need on the message 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 am on a shared hosting for the moment - so obviously this is not going to reduce it, I want to ask if you have where this site is running, what types of servers / hosting plan you would consider so that handle this installation size? With several thousand whistors to start moving in a week.

Answer 1

1) Use mysqltuner for WAMP (you cannot install it on shared hosting without root) to configure MySQL and change the query cache, memory, etc. Will be of great importance for WAMP and a possible server in real time. https://github.com/rackerhacker/MySQLTuner-perl

2) Be sure to delete the changes in the post / page form from the database. WP can store many changes that greatly affect database speed. I saw that the size of the database is reduced by 90% after deleting the post / pages.

Run as a SQL query in phpmyadmin to remove changes; if necessary, change the table prefix:

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 the tables. And then add

define ('WP_POST_REVISIONS', FALSE);

at the top of wp-config.php (somewhere after opening <?php ... ) to disable future versions.

3) Increase memory for php and WP for better performance:

Edit the line memory_limit in php.ini to 128M:

memory_limit = 128M;

Or add this line at the top of your .htaccess file:

php_value memory_limit 128M

If this does not work or causes an error, add this line at the top of your wp-config.php file immediately after opening <?php

define('WP_MEMORY_LIMIT', '128M');

4) On the latest VPS, configure http.conf for performance and maybe use a different field for the MySQL server itself.

Answer 2

The shared host is clearly in the performance limit. The problem is the lack of hardware, not the requests themselves, so you will need to make a dedicated machine for yourself. The bottleneck here is similar to MySQL, which is usually associated with the disk, but if your site grows very large, I would start to prepare a slightly different architecture with load balancers for HTTP and a powerful machine for MySQL (I think that i7 with 12 gigabytes of ram is not too expensive, and I would use this for a MySQL server if you come with a monolithic data warehouse).