Because _nodewords_detect_type_and_ids() makes a call to _nodewords_get_pages_data(), nodewords_load_tags() gets called for every single page in the nodewords_custom table. For a site with 1000 entries in the nodewords_custom table this results in an extra 1000 queries on every page load, just to find out if a path in nodewords_custom matches the current path.
Loading just the content of the nodewords_custom table when determining path matches will greatly cut down on unneeded queries.
| Comment | File | Size | Author |
|---|---|---|---|
| #8 | Avoid_loading_all_tag_data-1232498.patch | 2.31 KB | adamfranco |
| #1 | Issue-1232498.patch | 2.1 KB | adamfranco |
Comments
Comment #1
adamfranco commentedI'm attaching a patch with resolves this issue.
Comment #2
adamfranco commentedI have done some load-testing with this patch on a site that has 1,194 rows in nodewords_custom and 114,293 rows in the nodewords table and the throughput on my site has jumped from an average of 9.7 requests/second to 21.2 requests/second.
Comment #3
damienmckennaI think this should be included in the next release.
Comment #4
damienmckennaComment #6
damienmckennaThe current _nodewords_get_custom_pages_data() function includes static caching already, making this redundant. Thank you for the effort, though!
Comment #8
adamfranco commentedHi Damien,
This issue still exists and is not solved by the static caching. The problem is very severe when a site has many entries in the
nodewords_customtable. Even with the caching changes, aSELECT * FROM nodewords WHERE type = 10 AND id = ?query gets run for every entry in nodewords_custom. Our site has about 2000 entries in nodewords_custom and this results in an extra [unnecessary] 2000 queries on every page load, enough to take down our database host._nodewords_detect_type_and_id()doesn't need all of the metadata for every custom path, just the paths and ids, and this patch allows_nodewords_detect_type_and_id()to access just the data it needs in a single query.I have recreated the patch against the 6.x-1.x HEAD (and hopefully formatted it correctly with git-format-patch).
Best,
Adam
Comment #9
damienmckennaYou've convinced me. Committed, it'll be in the next release.