Forum Pretty URLs

[EEVblog]

Holy crap. I just noticed that the MySQL cache entry (smf_pretty_urls_cache) for the "Pretty URL's" plugin I am using on the forum is 1GB in size, with 5.5 million entries!
WTF?!

Anyone know is hitting "Empty" in MySQL on that entry will cause an issue?

Dave.

[Monkeh]

I gather from some googling that there's a maintenance option to clean that up somewhere in the mod.

If you wipe that table, renamed topics won't be accessible via their original prettified URL. And of course you might implode the forum, I'm not entirely sure.

[mariush]

After looking at the source code of the plugin, I'm 90% sure nothing wrong will happen if you empty that table.

Before a page is shown to users and search engines, the plugin goes through all the links on the page and if they're forum urls, it tries to form pretty urls for them.  It makes a list of all forum links, generates a sort of hash for each link, then searches based on those hashes in the cache table for precomputed pretty links.
If those pretty links are not found (cache table empty), the plugin computes on the spot those pretty links and inserts them in the table).

So if the cache table is empty, for a while it will take a bit more time for the pages to pop on users' screens, for users it may seem the server is overloaded or that the pages are slow to load. This is because the script may do a few mysql queries to generate those pretty links. However, this is theoretically, because at the same time it's quite possible that mysql needed a lot of time to seek through those 5.5 million records when the script searches for precomputed links, which may be more time than the time it would take to actually compute those pretty urls... In this case the cache may actually hurt performance due to its large size and improper use of primary keys on the table. 

The script itself is pretty horrible when it comes to that table ... it uses as primary key a varchar(255) variable, which means the mysql database can't really optimize and keep in memory with efficiency the location of records it previously accessed.

So before you do that, IF you decide to empty it... i'd suggest you determine the maximum length of the strings in that url_id column to see if you can adjust it to a lower value so that the indexes will be smaller. If they're smaller, MySQL may be able to keep more records cached and not have to constantly search that 1 GB of data. It's not a permanent fix, just something that may slightly improve the performance.

To do that, if you have phpMyAdmin ... select the table to see the contents, then click on Structure tab, and on that page you should see "Propose table structure"  link somewhere on the page.

That link will analyze the records in the database and tells you what's the maximum length of that field.

So if it says for url_id  Min_Length = 20  and Max_Length =100 ... then it's obvious that if it didn't get close to the maximum of 255 after 5.5 million records, it's unlikely it will happen ... so set it to a more realistic 100 + 25%  ... therefore 125-140 would be better.

You can change the legth of that field by going back to structure and click on that edit button by the field.

After you alter the field length, you should go to Operations and select "Defragment table"... This will take a while... minutes... the table is basically recreated with all the records in a continuous way on disk and reduce the disk space usage of the table, so the engine won't have to seek all over the hard drive for records.


ps. as with any such operations what you should do is set up a subdomain for this forum .. for example tests.eevblog.com . Make a copy of the forum files in that subdomain's folder, make a copy of the database (you have the option for that in phpMyAdmin or whatever you use),  edit the cloned forum's configuration so that it will access the database copy, then do these tests on the copy of the forum.

It literally takes a few minutes for the subdomain records to propagate and a few minutes to get a copy of the database.

[Devian]

Holy crap. I just noticed that the MySQL cache entry (smf_pretty_urls_cache) for the "Pretty URL's" plugin I am using on the forum is 1GB in size, with 5.5 million entries!
WTF?!

Anyone know is hitting "Empty" in MySQL on that entry will cause an issue?

Dave.

I just know when you visit your web, there would be MySQL cache?and when the MySQL cache gets to the Maximum?they would be cleaned automatically. If use Wordpress, the plugin need to be removed. Hope more experts can help.