I have been a fan of WordPress for quite sometime now and have used it in my teaching of Marketing on the Internet and Web Design course for many years, and as a Web developer I have deployed many WordPress sites for many clients. Over time it has matured and turned into a very popular content management system rather than being only a blog platform. The beauty of a well-designed content management system like WordPress is the ability to separate content from its presentation. The latter is handled by thousands of WordPress themes (although I am very partial to StudioPress Themes), and the former is the responsibility of the WordPress platform.
Content management systems rely on a relational database structure consisting of various tables that are connected to others with “relations”. All this happens behind the scenes and generally the user need not be concerned with the database or the tables other than regularly backing up the database with all its tables. Each additional plugin typically adds the necessary tables to the database and accumulates the content it is responsible for maintaining.
The other day I was looking into the database using phpMyAdmin software that runs on the server and out of curiosity I sorted the tables based on their size. To my big surprise one table, wp_options, appeared to be enormous, it was about 20MB in size and dwarfed all other tables in the database. I was really surprised since it mainly keeps, as its name suggests, WordPress options. Little did I know that it is also used as the proverbial garbage can. There were hundreds of lines of entries, perhaps even thousands, starting with “_transient” and continuing with long string of characters. I am not a WordPress programmer although I have done quite a bit of programming using relational databases and have a reasonably good understanding of the general concepts. These entries appeared to be multiplying like rabbits and some were paired with another plugin-based line with similar cryptic characters. I was puzzled and curious and started researching the role of “transients” in WordPress.
My findings may not be complete but they have given me a good idea of what is going on. Transient entries are added to this table as a matter of efficiency but that did not make much sense either. You see, transients are kept in the table until next time they are accessed. Think about it, you are putting used glasses on a shelf after using them until you need the glass again at which time you break the glass! Some of these transients contain code snippets over 1,500 lines and there are many, many of these entries.
I inquired about it from one of the plugin developers, NextGEN Gallery and the reply came as I described above: a matter of efficiency. The only “efficiency” I can envision, with my limited WordPress coding knowledge, is the ability to access the code from the transient while the visitor is still viewing that gallery. After that user leaves, that transient seems to serve no purpose whatsoever and there is no “garbage collection” built-in to WordPress. During my coding days, we were fanatical about keeping a tidy database. There are some plugins (which keep their own transients of course in the same table) that can clean the crud but none seems to do, or dare to do “deep cleaning”. Consequently, your wp_options table keeps bloating to huge sizes. Just to give you an idea, when the table is trimmed to keep real WordPress options, clear from the crud, its size is under 200KB. Over time, the table on my installation reached 20MB! That is 100 times its normal size and it would have kept bloating had I not intervened. Now, within minutes after I trim the table size to under 200KB, it magically balloons to 2-3 MB. With each visit, with each internal function execution like checking for updates there are multiple transients added to the table. To make matters even worse, some plugins are so ill-behaved they don’t bother to remove the entries they had created in wp_options table even after uninstalling the plugin. I have scraped such crud from this table with careful research of the entries and making sure that they belonged to a plugin long gone. There is a support forum thread related to this issue with NextGEN Gallery, but I use this link only as an example. They are not the only ones doing this.
A word of caution here, I do not recommend that you jump into trimming the entries in this table on your site. It is a tedious process and you can easily render your site dead if you delete the wrong line(s). Also, do not install all the plugins you come across just to try, you don’t know what kind of crud they may leave. I keep a separate WordPress installation as a test bed and try new things there. I will be even more careful from now on.
Some may argue that 20MB is not a big table, the server should have ample memory to handle that. I will disagree with that assessment first on the principle of tidy coding, this is far too sloppy. Secondly, you will find thousands of sites writing that many posts on how to speed up your site load. I cannot see how loading a 20MB table instead of 1% of it can help improve site load speeds. This is probably an issue WordPress team need to address and the user community to react to the poorly constructed and ill-behaved plugins by reporting them. If there are things I need to learn, please let me know. I am always open to learning new things.
All with civility, of course.
Jim says
My options table hit 1GB, showing 1.7M rows. I tried various ways to clean it up without success so I recreated the entire site into a new DB, which comes in at a trim 2.5MB
No idea what plugin did the damage over a few years, but it took me a full week to replace the database
A. Cemal Ekin says
WP Options table seems to be the proverbial dumping ground. All sorts of plugins add records as “transients” which is a concept I have difficulty appreciating. Transients are records, they say, that would be deleted next time a request for the same thing comes from a visitor. If that is the case why bother keeping them and doing so with no housekeeping whatsoever. I did my share of coding in the days where “tight coding” was a virtue, now trash all over the place that nobody picks up. I manually go and clean the transients probably about once a month or so, a chore for sure.