{boost} table grows excessively

I've been looking for a reason for the slowness of my main website. We get thousands of hits every day and that makes for one since it needs to regenerate all those pages...

Now I checked the database and noticed that quite a few tables were enormous. The sessions table for one, which I have now fixed (i.e. the garbage collection was somehow turned off!) Then I moved on with the other tables and got to this one:

boost_cache

and before cleaning it, I checked it: 203,064 rows. On that website, I have less than 1,000 nodes, maybe 2,000 terms altogether, and some other automatic pages, say 1,000 of them (to be really large.)

Before that, I had the aggregator pumping some pages too. That grew quite a bit, but I have now uninstalled the module. So all those pages are gone. I think I had some 20,000 such pages. Even viewing large, say 50,000.

Total I get is: 1 + 2 + 1 +50 = 54 thousand. Not 203k... so I looked a little closer, you also cache "weird" entries. I guess you're not entirely responsible for those though.

Sample of "weird" filename (I know, it's code and they're trying to run an SQL command):

cache/normal/linux.m2osw.com/aggregator/sources/23_page=4 AnD 1=2
UnIoN SeLeCt CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,DaTaBaSe(),0x3a),
CoNcAt(0x3a,0x6b6f70656c65,0x3a,VeRsIoN(),0x3a,UsEr(),0x3a,
....

Okay, so... what now? Why is the boost_cache so big and so slow? I looked a little closer at the code. When you clear the cache, you don't delete anything. This is a big problem with PostgreSQL.

/**
 * Resets all entries in database.
 */
function boost_cache_clear_all_db() {
  if (BOOST_FLUSH_ALL_MULTISITE) {
    db_query("UPDATE {boost_cache} SET expire = %d", 0);
  }
  else {
    db_query("UPDATE {boost_cache} SET expire = %d WHERE base_dir = '%s'", 0, BOOST_FILE_PATH);
  }
}

The problem is that with PostgreSQL, the UPDATE command is very likely going to append to the table (i.e. mark the old row as unused and append a new valid row at the very end). With that method, the table grows forever. There is a vacuum process that will run on each table and remove unused rows. However, that process goes slower and slower as the number of rows grows. Handling of 203k is VERY SLOW.

The best way to keep table small is to delete unused rows.

Also, in your case, somehow, I had a row that had an expiration date of Feb 2010 (we're in Apr 2011!) Would that be considered as expired? Why wouldn't that row be reused?? I guess one reason is because I had many other rows with 0 in them.

Anyway, in case of PostgreSQL, I think that a DELETE instead of UPDATE to expiration = 0 would be a lot more effective. Also it looks like removing a module will not remove any of the pages in link with that module, and when that module generated thousands of entries in the cache, the cache will never be reduced back to a reasonable size. I'd expect my table to never grow over some 50k (Google finds only about 13k pages on my main site. Yeah... I have no idea how many pages we really have on this website.)

I'd like to know what you think on your end before offering a patch. If having empty entries (expire = 0) is very beneficial for MySQL, then maybe we can have a trade off and keep some of those. If larger than a certain number, then delete any extras.

Also, I'm still wondering, why would some rows be that old? (expired over 1 year ago)

Thank you.
Alexis Wilke