Is it okay to delete _site_transient_browser_ ? Yes.

WordPress – mySQL / MariaDB – Slow queries accessing  wp_options? This is a short article about transient entries in the WordPress _options table. Can I delete them? Yes.

(….it would seem so….)

We had noticed a dip in performance for one of the dedicated MariaDB servers. We saw the same suspects there, but not a great deal going on – however, the big hint number one was that they were all stuck on “writing to network” as opposed to querying or similar. It looked like they were writing a lot out back to the server running the site.

Now, this had been going on for a while and was affecting others as the response times of the DB server started to go down… despite not apparently doing all that much. mysqltuner.pl was wheeled out as you would expect, as well as increasing the amount of RAM in the host as it was chewing through this alarmingly fast despite not actually doing “that much” – apparently – and spilling into swap.

Interest piques.

Checking over monitoring we see a huge spike in traffic heading out. Strange things are afoot at the Circle-K.

Running the query that I am seeing sent on

root@ThisDBServer:~# mysql -u root -p -e “SELECT option_name, option_value FROM TroublesomeDatabase.wp_options WHERE autoload = ‘yes'” > /tmp/how_big_is_this

root@ThisDBServer:~# ls -lh /tmp/how_big_is_this

-rw-r–r– 1 root root 86M Oct 19 15:38 /tmp/how_big_is_this

Checking the lengthy output of that at the console it ends looking a lot like this:

+——————————————————————————+

2151 rows in set (0.05 sec)

O_O

Wow, that’s quite a chunk to be sending to the server every time a page is pulled – there appear to be 15 concurrent connections asking the same thing at the moment (“thanks”, genuine Google bot). We have our winner.

So looking at the table it is full of Option_Name with values like  _site_transient_browser_f9694186c5800b9905943d3f44ede836 – when I say full I mean ‘thousands‘.

The name suggests they are transient. Let’s see how many there are:

MariaDB [TroublesomeDatabase]>SELECT * FROM wp_options WHERE option_name LIKE ‘%transient%’;

2050 rows in set (0.05 sec)

Right – that is *most* of the wp_options table (sometimes called _options with a table prefix to make life a little more secure – keep that in mind)…. how about we have a quick google around and drop that?

MariaDB [TroublesomeDatabase]> DELETE from wp_options where option_name like ‘%transient%’;

Query OK, 2050 rows affected (0.03 sec)

…that pucker feeling always when you see that many rows purged.

Cue almost instantaneous pick up in speed for that site, and other sites that were having issues…. only to assist in this highlighting another site doing the same thing.

Two things left dwelling on my mind:

1. Can I pretty please have a decent visualization tool for the performance_schema in MariaDB… maybe grow a pair WorkBench and allow that?

2. I wonder what hateful plugin is common between those two sites?

Public service announcement ends. It appears safe to delete these – WordPress will recreate these if required. Happy days.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this:
Skip to toolbar