WordPress permalinks and MySQL limits

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.

 

Tags: ,

2 Responses to “WordPress permalinks and MySQL limits”

  1. Matt Says:

    I'm pretty sure this isn't an issue anymore in 2.5, rewrite rules have been minimized.

  2. Corey Says:

    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.

Leave a Reply


© 2007-2010, Corey Gilmore | Posts RSS Feed | Comments RSS Feed | Contact

 

The views expressed on these pages are mine alone and not those of any past or present employer. All information presented on this site was obtained lawfully and not through disclosure under the terms of an NDA.