Drupal key_value_expire Table is very large because of Honeypot Module
I had a very large key_value_expire table, even though Honeypot module had been uninstalled.
When investigating why I had such a large database on mark.ie (about 50MB - not huge, but a lot for a personal website) this evening, I noticed that the
key_value_expire table in the database was about 40MB (after clearing cache). And that most of the entries in it (over 160,000) were for
As it turns out, honeypot had been putting a time to expire of Jan 2038, so the table was just getting bigger and bigger. Then I did some Googling and found this: Honeypot key value entries never expire (effectively). That issue has been fixed for newer versions of honeypot, but all the old entries before that update are still in the DB.
There's another issue - Write update hook to clean up old honeypot_time_restriction values - to clean up all the old entries, but it's only at RTBC at the moment, and when it gets into the Honeypot module, it could take a long time for the update hook to run depending on how many entries you have in the table.
For my set up, I just ran
DELETE FROM key_value_expire WHERE collection = "honeypot_time_restriction"; directly on the DB. It only took about 10 seconds to delete the 160,000+ rows, and reduced that table from about 40MB to 3MB.
It might be worth checking this on your site to try reduce your DB size.
Sorry about this issue! It was something I didn't even notice when the initial implementation was added, and the main thing that was holding up merging that fix (deleting expired entries) was the fact that some people's sites could end up getting a 'white screen of death' during updates if the query took long enough that the update script timed out.
It seems like it might be worth adding that update task now, and adding a warning in the release notes to make sure you test it out first and make sure you have a database backup (and give people the option of running the query directly).
Thanks for the comment, Jeff.
I hope you don't think I was making any criticism of you or Honeypot. This post was just for information in case anyone else was googling the issue. It took me a little while to track it down.