Early in the 4.7 release cycle we accepted a patch that changed the way url aliases are retrieved. Instead of putting them all in a giant serialized array we opted to look up each alias separately. This is a major problem for some people with limited SQL resources and therefore a bug. I propose the attached patch (which is not complete yet). What the patch does is to store all the aliases that have been requested on a page view inside the database in a dedicated cache_path table. Next time that page is viewed we retireve all these aliases and first look up the aliases there before checking the db for single path aliases that might have been added later.
On each page view the updated list of aliases is stord in the database again.
What is missing:
1) SQL updates, for testing simply copy one of the other cache tables
2) a cleanup procedure. A site like drupal.org has a lot of pages and the path cache should be cleared once a day or so.
3) testing and benchmarks