What to do if your WordPress Database gets too large – especially when you try to reinstall it

phpMyAdmin Error

Upload too large..

I currently ran into a problem with a customers website when I tried to reinstall an afore backupped version. The problem was that the database grew HUGE due to transients not getting deleted – the whole database was getting “spammed” with unneccessary transients. The concrete problem in numbers:

  1. The database was about 450MB large. On a one-year-old site (!).
  2. After optimization which resulted in a relatively healthy 40MB you could watch the DB grow.
  3. And that solely with:
    1. Around 250 published posts 7maxdqf.
    2. 12 pages.
    3. ~ 1500 comments.
    4. 25 active plugins. All of them performing fine due to PS3 Plugin Profiler. Sure, that doesn´t allow any speculations on what they write to the database..
    5. A commercial theme with my absolute (not!) fav plugin WP Bakerys´ “Visual Composer”.
      The reasons why I don´t like Visual Composer will be discussed in an upcoming post. Promised.
  4. Upload on the customers host was restricted to 20MB – which could be upgraded to 50MB with some custom php.ini .
  5. phpMyAdmin had its own limits concerning upload limits but even more frustrating – script timeouts.

´Nuff said. So the question remains: “How can we solve that problem?” Skip to the relevant part for yourself, because I´ll take a spin from the very basics to the experts stuff..

Why a WordPress Database grows and grows and..

If you run a well-designed theme (like our “CustoMaterial” Theme for example 😉 ) and do not bloat your install with a sh*t-load of plugins, your database should be tight and lean.

But let´s face reality: Most likely it´s NOT.  And it´s not the reason of a not-so-well-programmed theme, it´s more or less a general WordPress “problem” – WordPress saves any new revision (That means if you save a new version of a post/page it will generate a new version of that post/page) and keep those. Eternally, if no precautions are taken.

This will bloat your DB, guaranteed. Really, if you need to keep more than 10 revisions per post, you have a conceptional problem with writing posts at large. But that may just be a personal opinion again. Anyway, you may take precautions in your general configuration to save no more than (X) revisions. Take a look at the WordPress Codex on how to limit post revisions.

You like to try out new plugins here and there? Well fine, admittedly we´re curious too. But I guess it´s comprehensible that each and every plugin needs to write it´s own configuration into the database – in most cases into the wp_options table. While most well-designed plugins will delete those entries upon deactivation, some other not-so-well-programmed don´t.

My advice would be to set up a staging site as a playground. Instructions on how to copy your site follow below. Use that to verify the usefulness of any plugin you´re interested in and make sure that it doesn´t cause any incompatibilities with your actual install. If you care, you should also take a deeper look at it´s performance with tools like the above mentioned PS3 Plugin Profiler.

Cleaning up your WordPress Database

So you config´ed your functions.php up to our recommendations. Still, the DB keeps on growing and you don´t know why.

Notice: This is a fix for the symptons. but not for the cause of the whole thing. We´ll take that into account in a special post shedding some light on that maybe later..

CLEAN UP AND FIX YOUR DATABASE! And yes, there are tools for it and also Tutorials like the one from ElegantThemes or WPBeginner . But PLEASE take any precautions as mentioned above (MAKE A BACKUP! In general, it all comes back to that Go Here..)

Aside from the recommendations that those two links give, I´ll advise some more:

How to farther reduce WordPress Database-load

Above mentioned features should help for the future. But it doesn´t help reducing the actual Database-load. Well, some of them do, some of them don´t. But what would really help is to get rid of the overhead now, right? So, that´s our next step..

Most likely, it´s a plugin running “Amok” thats bloating your Database with transients. And we´ll figure how to find out those in an upcoming article, because that´s not the point of the current topic. Sorry! Transients are a nice way to “cache” certain configs, as you may alreay got to know through above links.

To put it short: If you delete a “transient”, it will get re-generated if neccessary, so (in general) they can be deleted safely.

So those following plugins should work without any problems:

Backing up WordPress

There are numerous ways of backing up WordPress. And a gazillion of available tutorials, so here´s just a starter straight from the WordPress Codex: “WordPress Backups.

But there are just three plugins I would personally recommend, depending on your personal requirements:

1. Duplicator

Duplicator is fine for every single site install. But take the name seriously: It´s meant for DUPLICATING sites, not for BACKUPS. That means you can perfectly copy your Dev-Site to the Live-Site or vice-versa. Duplicator makes that a no-brainer. But it´s not supposed to make regular backups of your site.

Pros:
  • Easy one-click backups of your whole site.
  • Easy one-click install on your target sites. It will also rewrite file-pathes etc. pp.
  • I (personally) never ran into any difficulties with large DB´s. The reinstall script handles that without any problems.
  • IF any problems occur, you´ll get notified and may re-run the install after fixing those.

Hints: If you get any errors according too large file sizes, these may most probably occur in the “/wp-content/uploads” directory. Just exclude those and manually download/upload them via FTP before re-installing the backup on a new host. It doesn´t really matter if you do it before or after – it will just save you the headache that you seem to miss anything on the new installation.

Cons:
  • Not meant for WordPress multi-site/network installations. Works just with single-site installs. A downer, really. Hope they´ll improve that anytime soon..
  • No schedulable backups possible. You´ll have to manually use the plugin to generate a new backup.

2. UpdraftPlus Backup and Restoration

Really nice plugin for single sites and schedulable backups that can transfer backups to any imaginable storage like Amazon S3 (or compatible), Dropbox, Google Drive, Rackspace Cloud, DreamObjects, FTP, Openstack Swift, UpdraftPlus Vault and email.

Pros:
  • Schedulable
  • Amazon S3 (or compatible), Dropbox, Google Drive, Rackspace Cloud, DreamObjects, FTP, Openstack Swift, UpdraftPlus Vault and email
Cons:
  • According to my information it needs the PRO version to re-initiate backups. But I may be wrong with that. Please check the UpdraftPlus basic/pro comparison for details!
  • UpdraftPlus does not work with Multisite/Network installs!

3. BackWPup Free – WordPress Backup Plugin

According to my very personal experiences, this plugin fulfills anyone´s needs.

Pros:
  • Schedulable
  • Works with Multisite-Installs
  • Easy access on any file, plugin (will be saved as text-file if you want to), etc. pp.
Cons:
  • None that I personally knew of. If you know any, please shed some light in the comments, really!

Cool, I got a stripped-down WordPress Database-File now but I still cannot upload it to my host..

Easy, your half-way on it!

Let´s assume that Duplicator fails reinstalling and that your .sql-file is too large to be uploaded.

Raise the servers upload limit, memory limit, execution time

In most cases you´ll have no access to the host configuration, but your provider will allow uploading a “php.ini” to a directory that will overwrite the hosts config. This php.ini will just work for the current directory and it´s subdirectories, so you´ll may just want to drop it into the phpmyadmin folder.

Raise post_max_size to a higher setting. You´ll may want to try something like 64MB first and see what happens.
Raise upload_max_filesize and notice that post_max_size must be higher than upload_max_filesize !
Raise memory_limit to a higher value than post_max_size.
Raise execution time as well as input time or your script will time out.

So your php.ini could look like that:

memory_limit        = 128M
post_max_size       = 64M
upload_max_filesize = 50M
max_execution_time  = 5000
max_input_time      = 5000

Please notice that memory_limit must be relatively high if you try to upload zip-files to your phpmyadmin.

Still, phpmyadmin fails reinstalling the database..

Script execution timeout? Well, then there is one last recommendation from me: Just split that file up in smaller chunks that your webserver will process without a timeout. There is a tool available named “SQLDumpSplitter2” that will allow you to just to that.

It will generate a set of sql files that you can upload one after the other. If your phpmyadmin fails to install one of those sql-chunks, just watch the error-message: try to re-upload it and the processing of that file will be resumed just where it timed out!

This may be a little time-consuming and not the most elegant way to solve it, but if you have no access to the console it may be the only way for you to solve that problem.

At least it saved my day and hopefully serves some of you as well.

Related ressources:

TAR MediaDesign

Author Info: Thomas A. Reinert

Thomas is mainly a Frontend-Developer always interested in the latest trends. After nearly 10 years of Wordpress he also got a good insight into the Wordpress Backend, Theme- as well as Plugin-Development and to fulfill nearly any customers wish according to the projects requirements.

He´s also working as a freelancing tutor in vocational training and a member of Colognes IHK board of examiners for "Mediengestalter Digital und Print, Fachrichtung Digital".

Visit his Portfolio @www.tarcgn.de

Notice: The author description block is enabled by filling in your "Biographical Info" in Admin > Your Profile. You can also completely disable it via the customizer.

Contact Thomas A. Reinert:

Leave a Reply

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

You may use the following HTML-Tags:
<a href="" title=""> <abbr title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>