@Hoang Ngo

Autoblog (now 4.1, but we've been using it for a while) seems to be responsible for an excessively large database footprint unless the DBA proactively manages it in a way that isn't necessary for most other applications (or plugins, for that matter).

It may be worth reconsidering how Autoblog uses transients, perhaps by using a separate table with an appropriately designed schema. It would also be nice to see an option that deletes log entries older than X days. (If there is that option, I guess I missed it.)

Firstly, it's not the number of posts created by Autoblog.

There's wp_autoblog_log, but there's nothing there that matters overmuch and it can be truncated without undue pain (though manually, for now, it seems, which is annoying).

The main problem seems to be what Autoblog (I think it's Autoblog) is doing to wp_x_options. See below for some data from our WPMS installation, the TL;DR- of which is:

• Site 4 is the one (and only one, of about 40 sites) running Autoblog.

• The DB engine in use is InnoDB, and we have innodb_file_per_table = true.

• Sites 2 and 4 have been around since 2009 and 2014 respectively (reflected in the number of posts and comments on site 2 and also in the size of tables wp_2_{posts,comments}. Although wp_4_posts has 2921 rows (vs 7638 rows in wp_2_posts), most of them are Autoblog posts and are therefore small.)

• There are only 345 rows in wp_4_options, the sum data length of which is about 1.7 MB yet MySQL reports that wp_4_options is consuming 3,251 MB. That's 3.2 gigabytes. That's over 4.6 times the size of the main posts table of a site that has been running for nearly 6 years.

• However, an OPTIMIZE TABLE command shrinks that down to a much more respectable 3.6 MB.

I mention InnoDB and the fact that we use innodb_file_per_table because I'm no MySQL guru (much less InnoDB guru) and it's possible (in fact, AIUI, likely) that optimize trick may not work unless innodb_file_per_table is set true. This is not the default for many installations, and Autoblog, as currently written, is likely adding to a lot of admins' (and WPMUdev customers', who don't have this level of understanding) headaches.

Either way, the upshot is that wp_x_options uses type LONGTEXT for option_value and although that may be useful for serialised PHP data structures, it's not very efficient for frequent INSERT/DELETE, especially with values as long as several hundred kB each.

Data from MySQL:

mysql> USE information_schema;
mysql> SELECT table_name, data_length FROM tables
                WHERE table_schema = 'wpms' AND data_length > 100000000
                ORDER BY data_length;
+---------------+-------------+
| table_name    | data_length |
+---------------+-------------+
| wp_2_comments |   255590400 |
| wp_2_posts    |   699039744 |
| wp_4_options  |  3251863552 |
+---------------+-------------+

mysql> use wpms;
mysql> SELECT count(*) FROM wp_4_options;
+----------+
| count(*) |
+----------+
|      345 |
+----------+

mysql> SELECT count(*) FROM wp_4_posts WHERE post_status = 'publish';
+----------+
| count(*) |
+----------+
|     2921 |
+----------+

mysql> SELECT count(*) FROM wp_2_posts WHERE post_status = 'publish';
+----------+
| count(*) |
+----------+
|     7638 |
+----------+

mysql> SELECT option_name, LENGTH(option_value) FROM wp_4_options
                WHERE LENGTH(option_value) > 100000
                ORDER BY LENGTH(option_value);
+--------------------------------------------------+----------------------+
| option_name                                      | LENGTH(option_value) |
+--------------------------------------------------+----------------------+
| _transient_feed_9cd2f75a9ebb72b18adbb123390a20d6 |               150022 |
| _transient_feed_d117b5738fbd35bd8c0391cda1f2b5d9 |               235282 |
| _transient_feed_da8cde559baa5aef366030cf29057b2e |               292635 |
| _transient_feed_0f2fcbbf42b03f8075711198a37f74ab |               292647 |
+--------------------------------------------------+----------------------+

mysql> SELECT SUM(LENGTH(option_value)) FROM wp_4_options;
+---------------------------+
| SUM(LENGTH(option_value)) |
+---------------------------+
|                   1736655 |
+---------------------------+

mysql> OPTIMIZE TABLE wp_4_options;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| wpms.wp_4_options | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| wpms.wp_4_options | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+

mysql> SELECT table_name, data_length FROM tables
                WHERE table_schema = 'wpms' AND data_length > 100000000
                ORDER BY data_length;
+---------------+-------------+
| table_name    | data_length |
+---------------+-------------+
| wp_2_comments |   255590400 |
| wp_2_posts    |   700088320 |
+---------------+-------------+

mysql> SELECT table_name, data_length FROM tables
                WHERE table_schema = 'wpms' AND table_name = 'wp_4_options';
+--------------+-------------+
| table_name   | data_length |
+--------------+-------------+
| wp_4_options |     3686400 |
+--------------+-------------+