I have inherited a client site that has an extremely large database for no reason. There is a moderate amount of content and very little enabled modules. However, the database is too large for moving around easily and I want to clean it out.

I have cleared out the standard cache tables, syslog and accesslog.

Are there any other tables I can safely truncate in a standard Drupal site?

  • 1
    You can sort the tables based on their size in phpmyadmin. Try that and then look which tables are the biggest and report that here. I've for example seen huge session tables which aren't cleaned up for some reason. That's something you could clear if you can live with users having to log in again (and possibly loosing entered form data if they are on the site, so you might want to coordinate this with the users) – Berdir Feb 9 '12 at 14:08
  • Just a side note there, that all of the answers below that mention truncating {cache_form} are not really correct. This is not a true cache table. It contains in progress form submissions. If you delete all of the data in this table, your user may lose data. The proper thing to do with this table is to expire entries. – mpdonadio Jan 10 '17 at 14:12

11 Answers 11

up vote 21 down vote accepted

Use the backup & migrate module, it comes with good defaults for skipping not necessary data. By default it generates a DB backup without cache, watchdog and some other tables.

If this does not help have a look with phpMyAdmin and tell us which tables have a lot of entries.

  • 1
    This is the first place I went. However, the database is over a gig and will not backup through this method. My intention is to clear out the database so that I can use backup and migrate on a regular basis. Essentially I am wondering if there are any more tables I can clear out (that aren't by default skipped by BAM). – Nigel Waters Feb 9 '12 at 6:43
  • If you have command line access your can use drush to start backup and migrate. Or access mysql on the command line (example: mysqldump --host=your.host.com --user=db_user --compress --password your_pw >dump.sql) This way you will not run into timeouts. In general clearing out without having a backup isn't very save. You can easily end up with a broken page and no way to go back. – BetaRide Feb 9 '12 at 6:49
  • The problem is not with the timeouts. I know I can easily run backups through ssh/drush. I would like to clean up the database as it has seen one to many hands over the last few years and there is a lot of unnecessary crud in there. I just need to know which tables I can safely clear out, (not know how to backup or move my site). – Nigel Waters Feb 9 '12 at 6:52
  • @BetaRide is correct, the default ones that BAM excludes are the safe ones. The others may or may not have actual data. – mpdonadio Feb 9 '12 at 11:40

Drupal 7 tables that can be excluded

Here is a list of tables in Drupal 7 that you can either clear (to reduce the database size) or safely exclude to do a migration (as in the question about How to reduce the locally exported database size to get around my server import limit?):

  • accesslog
  • batch
  • all cache related tables, such as:
    • cache*
    • cache_block
    • cache_content
    • cache_filter*
    • cache_form
    • cache_calendar_ical
    • cache_menu*
    • cache_page*
    • cache_views
    • *_cache, such as features_cache or views_data_object_export_cache
  • ctools_views_cache
  • ctools_object_cache
  • devel_queries
  • devel_times
  • flood
  • history
  • queue
  • various search_* tables, such as:
    • search_dataset
    • search_index
    • search_keywords_log
    • search_total
  • semaphore
  • sessions
  • watchdog
  • webform_submitted_data

Usually tables such as search_index and watchdog use a lot of database space, so just eliminating those 2 tables can make a huge difference already.

Other tables that might be excluded

Check the size of your remaining tables and identify which one of them are the biggest in size.

Typically you might find session tables for which no cleanup procedure is in place. Such tables you can probably also exclude.

Module Backup and Migrate

To further reduce the challenge as detailed in "How to reduce the locally exported database size to get around my server import limit?", look at the Backup and Migrate module also. Here is a quote from its project page (bold markup added here):

Back up and restore your Drupal MySQL database, code, and files or migrate a site between environments. Backup and Migrate supports gzip, bzip and zip compression as well as automatic scheduled backups.

With Backup and Migrate you can dump some or all of your database tables to a file download or save to a file on the server or offsite, and to restore from an uploaded or previously saved database dump. You can choose which tables and what data to backup and cache data is excluded by default.

And there is even more: if your local environment (e.g. Win or Mac) differs from the OS that the server of your hosted website is running (like Linux), then these differences between OS-es imply potential extra challenges. I've had good experiences with the Backup and Migrate module between different OSes, which didn't cause any problems (worked fine) in situations where the typical MySql export / import failed before.

  • Good to add that any tables with cache_ prepended or _cache appended are safe to truncate as well, such as features_cache or views_data_object_export_cache etc. – Beebee Sep 2 '15 at 11:12
  • 1
    Word of waring, the search table data can be excluded, but it can take a very, very long time to rebuild the indexes on big sites. Judge this on a case-by-base basis. – mpdonadio Sep 2 '15 at 19:07
  • 2
    Also, the B&M excerpt about cached data is slightly incorrect. When enabled on a site, it will exclude cache tables. However, if you add a module after B&M is set up, cache tables may not get added to to the exclude data list. I have seen this happen many, many times, typically when I override the settings on the default profile. – mpdonadio Sep 2 '15 at 19:10
  • @MPD : thanks for this interesting feedback (didn't know about that yet!). About search table: valid point. But personally I'd always go for the rebuild approach: it helps to get around the limitation and it ensures the index matches the actual content in the target. About your 2nd comment: the excerpt is a cut-and-past from project page, so maybe you want to file an issue about that in its issue queue (Drupal.SE is not the place to reports about bugs, etc, right?). – Pierre.Vriens Sep 2 '15 at 19:23
  • @Pierre.Vriens Matching the content shouldn't matter, assuming you have cron running and make sure indexing happens. B&M, pretty sure that is a known issue. Also, the section about session data is not 100% correct. That table gets large because the default session time is about three weeks; _drupal_session_garbage_collection will keep that table tidy, based on system settings. – mpdonadio Sep 2 '15 at 19:37

In my experience, I purge all of the "cache_*" tables.

  • plus "watchdog" if I don't care about past Drupal logs
  • plus "accesslog" if I don't care about logged-in users
  • plus "search" if I don't care about indexed nodes contents
  • 1
    Same here, i'd also sessions. – Alex Weber Feb 14 '12 at 18:49
  • 2
    A note to anyone attempting this: Create a backup first. And don't Drop the tables, rather Empty or Truncate. – timofey Sep 25 '12 at 2:17

I sometimes run this SQL to keep an eye on the growth of the top tables:

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA =  'yourdbnamehere'
ORDER BY table_rows DESC 
  • What column should I check for growth ?, You mean TABLE_ROWS – Bala Oct 20 '13 at 7:54

Watchdog and sessions can also be cleared, keep in mind that all users will be logged out.

With mySQL you can do fun things with the mysqldump program to export the database in its entirety or in parts. For instance this just exports the structure:

mysqldump -u root -pBatteryHorseStapleObviously -h some_host --no-data dbname > ~/dbname.sql

You can then use the 'ignore table' option to further export data, e.g.

mysqldump -u root -pBatteryHorseStapleObviously -h some_host --ignore-table=dbname.huge_table --ignore-table=dbname.massive_table --ignore-table=dbname.useless_table some_host >> ~/dbname.sql

That places the data on the end of the earlier file ignoring some massive tables.

If you then need the massive tables then you can export them to a different file using the above approach, you can then import them in chunks (although f k checks off might be needed).

You did gzip your file before uploading, or is that a silly question?

Use OptimizeDB module to clean the cache tables. The Database Administration is also helpful.

Don't forget to have a backup of databases.

  • database is now 14Mo, I used OptimizeDB, Thak you again – Mitch Sep 2 '15 at 8:31
  • @Mitch you welcome – Drupalist Sep 2 '15 at 8:56

not the super expert on this but sharing my experience... if you're not using the backup and migrate module and manually export them some of the tables you could empty/truncate would be watchdog, cache, cache_menu, cache_block, cache_content, cache_form as they might contain a large amount of cached stuff clearing which I suppose wouldn't hurt... but again this is my experience and I haven't encountered troubles or data loss because of this.

Some ideas:

  • A completely different approach would be to create RSS feeds using views of the data you want to keep. Then create a fresh Drupal installation and import this data with Feed API.
  • And just an other approach: Hire a student and let him/her transfer the data manually into your fresh installation.
  • Or this one: Tell us more about what tables are very huge and what is the reason for this (if you know).

Check the example.drushrc.php which list these:

$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'search_*', 'sessions', 'watchdog');
$options['skip-tables']['common'] = array('migration_*');

It's safe to clear them in terms of moving the database between different environments (especially when you're working with big databases). However you still need to understand what you're clearing.

Additional tables that can be cleared:

  • batch
  • webform_submitted_data

Other things that might take up quite some space: - older versions of your content (not possible to clean with a simple truncate). - locales_source and locales_target. If you have languages that are not used anymore or string translations for modules that you don't use anymore. These tables seem to never get cleaned.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.