I'm dumping the database every 2 days, but I noticed that at some point last month the database jumped from around 500 MB to 4 GB!
What can be the cause of it?
3
-
2its probably best that you go into the database through mysql browser, phpmyadmin or whatever and see which tables have a massive amount of records and then update your question to ask what is causing that massive amount of records in that particular table.– 7wondersCommented Feb 26, 2012 at 9:29
-
1A frist step would be to clear your cache and watchdog tables. Sometimes this helpes a lot.– BetaRideCommented Feb 26, 2012 at 12:24
-
@Moshe – please post back here with what tables were large so that other users can get help from this.– Charlie SchliesserCommented Nov 28, 2012 at 0:14
Add a comment
|
3 Answers
One reasons that a database can grow w/o bound is when you don't have cron setup to run. Check your "Recent Log messages" at /admin/reports/dblog and filter for cron, and make sure it is running (nightly is a good start).
On of the things cron does is prune the cache, watchdog, and sessions tables. These can fill up quickly with data that isn't critical to Drupal running.
Occasionally, clearing these out can take too long for a cron run. If this is the case, running a manual query, such as
DELETE FROM watchdog;
may be needed. As mentioned above, you can run this on the cache and tables beginning with cache_, watchdog, and sessions. It is always best to take a backup first, before running DELETE queries.
answered Feb 26, 2012 at 13:15
-
I have cron jobs, and I ran it manually and immediately dumped and it's still huge. deleted manually the watchdog, and still no help...– MosheCommented Feb 28, 2012 at 10:38
-
Did you run the query that @Letharion gave to see which tables are big?– mpdonadio ♦Commented Feb 29, 2012 at 14:11
-
Thanks for posting these great ideas. I had a database dump getting truncated using mysqldump. The resulting file was 500M. 500M limit somewhere? After clearing cache and session tables the dump is 21M New best practice, clear cache before backing up data :-)– user11696Commented Nov 27, 2012 at 21:35
-
Turns out the Devel module had to be turned off before creating a database backup with Backup & Migrate (otherwise you'll end up with a large database file). Commented Apr 12, 2016 at 8:08
Run
show table status;
on your database, and take a look at the "Data_length" column. There you will see which tables take up the space. I don't know how you dump your data, but if you use Backup and Migrate, the cache tables are by default not included, saving quite a bit of space.
answered Feb 26, 2012 at 12:26
If you have drush installed, run drush cron followed by drush cc all. That should fix the problem. Just reduced my db size from 4.3G to 65M.
