Recently I ran into an issue where WordPress would take a long time to update rewrite rules after saving a page or post and my PHP error log would contain entries similar to:
WordPress database error MySQL server has gone away for query INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('rewrite_rules', '<serialized array>, 'yes')
In addition to various defaults the serialized array contains 9 regular expressions for every page and post.
(PAGENAME)/trackback/?$
(PAGENAME)/feed/(feed|rdf|rss|rss2|atom)/?$
(PAGENAME)/(feed|rdf|rss|rss2|atom)/?$
(PAGENAME)/page/?([0-9]{1,})/?$
(PAGENAME)(/[0-9]+)?/?$
PAGENAME/attachment/([^/]+)/?$
PAGENAME/attachment/([^/]+)/trackback/?$
PAGENAME/attachment/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$
PAGENAME/attachment/([^/]+)/(feed|rdf|rss|rss2|atom)/?$ |
Each post you add increases the size of the rewrite_rules array and puts you one step closer to the breaking point – MySQL's max_allowed_packet variable. By default this is 1MB which means that the size of your statements must be less than that, and the largest row you can retrieve is 1MB. I hit the 1MB limit at around 820 pages.
It's an easy fix; add max_allowed_packet=16M (or however large you'd like to make it) to your MySQL configuration in the [mysqld] section.
April 13th, 2008 at 11:15 am
I'm pretty sure this isn't an issue anymore in 2.5, rewrite rules have been minimized.
April 13th, 2008 at 11:25 am
Ah that's great news Matt! I'd been following bug 3614 and noticed it had been fixed in 2.5. On 2.3.3 I'm replacing the WP_Rewrite class during init with a slightly modified version that doesn't write any feed or trackback entries resulting in about a 70% performance increase. I haven't begun to test the site with 2.5 yet, but I'll make sure to file a bug report if I'm still seeing the issue.
July 16th, 2010 at 1:20 pm
Hi
I have run into this max_allowed_packet bottleneck on WP 3.0 recently
My rewrite rules are currently 1.6MB
It seems that it is not pages that are the culprit, but image attachment pages that are generating so many rewrite rules.
I only have about 200 pages on the site. But looking at the rewrite rules am seeing a lot of rules like:
s:50:"index.php?attachment=$matches[1]&cpage=$matches[2]";s:77:"(world-cup-2010-galleries/world-cup-review/argentina-11-2)(/[0-9]+)?/?$";
which are from image attachments.
It seems like rewrite_rules are still a bottleneck in wordpress scalability….
Increasing the max_allowed_packet is only a temporary fix, as if your rewrite_rules get to be too large your server will be in danger of running out of memory every time the rewrite_rules are flushed and refreshed…
April 18th, 2011 at 3:37 am
WordPress is typically run on shared web hosting environments, where you don't have access to MySQL settings. This is a real problem.