Heart Internet :: wp_options table creates lots of transient records

One of my clients received an email from Heart Internet recently stating that a database on their account had exceeded limit of 500MB and had ballooned to 3,561MB. This was a bit of a shock as the database holds a WordPress install which only has 82 posts.

My initial thought was that the comments hadn't been disabled and were most likely filling up the database, but when I checked phpMyAdmin I found that everything appeared to reasonably small in table size except for `wp_options` which although only had 370 records was somehow using up 3.4GB by itself.

When I looked into the table I found the usual `transient` records at the end of the table, these look to be temporary records with a short ttl. So the space which is being used appears to be overhead, although it doesn't show as such. But rebuilding the table via `OPTIMIZE TABLE` brings down the table size to a mere 1.5MB.

As this was the second time this client got an email from Heart with this issue I put together a scheduled task which optimises the `wp_options` table each day. Hopefully this will be useful for you.

I placed the scheduled task file above the `public_html` folder, within a folder called `crons` and called the file `optimise-db.php`. You can change the location and name of the file but you'll need to update the paths used in the code and control panel.

File: ~/crons/optimise-db.php

<?php

require_once $_SERVER['HOME'] . '/public_html/wp-config.php';

// Connect to Database
//
try {
    $dbh = new PDO( 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET, DB_USER, DB_PASSWORD );
    $dbh->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ );
} catch( PDOException $e ) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

// Optimise wp_options table
//
try {
    print_r( $result = $dbh->query( 'OPTIMIZE TABLE `' . $table_prefix . 'options`' )->fetch() );
} catch( PDOException $e ) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

The file will use the table prefix as set within `wp-config.php` so this should be a case of just copy and paste. You can use an include file within `public_html` if you want to test this file works.

File ~/public_html/test.php

<?php

require_once '../crons/optimise-db.php';

Next you'll need to add the `optimise-db.php` file to a scheduled task.

The command you use should be similar to the below but you'll need to update the path to the file.

/usr/bin/php56 -f /home/sites/example.org/crons/optimise-db.php

Here I'm using PHP 5.6 as I know Heart will continue to support this for sometime and something as simple as running a single MySQL query won't require PHP 7.

You can set the task to run once per week or once per day (usually at midnight). I opted for each midnight.


With this set, you can click on the `Test Command` button and you should receive the following output:


And that's it. This scheduled task will keep your transient records from filling up your database table.

If you have any questions I might be able to help, so tweet me @WilliamIsted.

MySQL Datetime vs Timestamp

Taken from http://stackoverflow.com/a/409305/1089331

Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.
- blivet, stackoverflow.com
An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. This could be very important if your application handles time zones. How long ago was '2010-09-01 16:31:00'? It depends on what timezone you're in. For me it was just a few seconds ago, for you it may represent a time in the future. If I say 1283351460 seconds since '1970-01-01 00:00:00 UTC', you know exactly what point in time I talk about. (See Nir's excellent answer below). [Downside: valid range].
- MattBianco, stackoverflow.com
In MYSQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described here: MySQL Server Time Zone Support
- Nir, stackoverflow.com
I always use DATETIME fields for anything other than row metadata (date created or modified).
As mentioned in the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
...
The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.


You're quite likely to hit the lower limit on TIMESTAMPs in general use -- e.g. storing birthdate.
- scronide, stackoverflow.com