I found out a very simple D7 site with less than 150 nodes had database dumps of 65M (gzipped). Which caused my entire server to become slow during the nightly database dumps.
After some searching I found this useful query:
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "DRUPAL_DATABASENAME_GOES_HERE";
With that I immediately found the culprit: the
TRUNCATE sessions my dump is now <1M (gzipped).
How can I make sure this site's dump doesn't blow up again in the future? I found periodic purging of watchdog and sessions tables? on drupal.org, but it's from 2007.