periodic purging of watchdog and sessions tables?

By gordyhulten on 1 Jun 2007 at 01:54 UTC

My mysql database is growing very quickly, and it's getting to be a problem. My hosting company recommended:

Also, I noticed in the 'drupal' database, the 'watchdog' table is over 300 MB and the 'sessions' table is about 550 MB. Seems that it should be safe to purge old data out of those occasionally.

I'm using a VPS with shell, but I'm still a novice at much of this. Any advice as to how I can purge these table regularly and safely?

Thanks.

Comments

=-=

VM commented 1 June 2007 at 02:27

got administer -> access log settings

set Discard access logs older than: to a rational length. Then set up a cron job on your server and your logs will clear per the cron job you have set and the Discard access logs older than: you have set.

Sessions tables you would do manually, though doing so will log everyone out of your site. You can set a lower session lifetime in your settings.php file

I had administration -->

gordyhulten commented 1 June 2007 at 02:39

I had administration --> error reporting --> discard log entries set to one week, and I've changed it to three days. I already have cron running for search, etc. Do I need to do anything to manually ensure that cron will clear these?

What's the best way to do sessions manually? Can I set it to automatically be cleared once per week, at 3 AM on a Sunday just after a database backup, for example?

Thanks.

I had administration -->

VM commented 1 June 2007 at 02:53

I had administration --> error reporting --> discard log entries set to one week, and I've changed it to three days. I already have cron running for search, etc. Do I need to do anything to manually ensure that cron will clear these?

No, these should clear with each cron run.

db tables can be cleared manually using your DB tool, if using phpmyadmin; check off your tables to clear, then click the empty tab at the top of right pane.

a 300 MB watchdog table is quite a bit, I'd clear this manually to insure its empty, then pay strict attention to it and insure its clearing as expected.

db tables can be cleared

gordyhulten commented 1 June 2007 at 03:33

db tables can be cleared manually using your DB tool, if using phpmyadmin; check off your tables to clear, then click the empty tab at the top of right pane.

a 300 MB watchdog table is quite a bit, I'd clear this manually to insure its empty, then pay strict attention to it and insure its clearing as expected.

Is there any easy way to automate it?

Thanks again for all your help.

=-=

VM commented 1 June 2007 at 21:19

again , the watchdog table, should clear as per the setting previously discussed when a cronjob is run.

The sessions table is not automatically cleared. When users log out, their session is killed 15 mins later. You can shorten the lifetime of session in settings.php, as previously stated, though, this will log out all users, even those logged in at that time. which can be quite annoying for users.

I find it hard to believe your sessions table is causing any problem, what is its size ?

Last week, my sessions table

gordyhulten commented 4 June 2007 at 16:10

Last week, my sessions table was 550MB. As of this moment, it's 13 MB.

I've got a cron job set up to clear out the watchdog and cache tables once per week, right after a backup. Hopefully that'll keep things manageable.

Right now my watchdog table is 378 MB, and cache is 46 MB.

=-=

VM commented 4 June 2007 at 17:48

550 MB seems totally uncontrollable, and as per my last post, you should set up a session lifetime in settings.php which should kill sessions after the time you set up.

Here's what I've got: /** *

gordyhulten commented 5 June 2007 at 03:09

Here's what I've got:

/**
 * PHP settings:
 *
 * To see what PHP settings are possible, including whether they can
 * be set at runtime (ie., when ini_set() occurs), read the PHP
 * documentation at http://www.php.net/manual/en/ini.php#ini.list
 * and take a look at the .htaccess file to see which non-runtime
 * settings are used there. Settings defined here should not be
 * duplicated there so as to avoid conflict issues.
 */
ini_set('arg_separator.output',     '&amp;');
ini_set('magic_quotes_runtime',     0);
ini_set('magic_quotes_sybase',      0);
ini_set('session.cache_expire',     200000);
ini_set('session.cache_limiter',    'none');
ini_set('session.cookie_lifetime',  2000000);
ini_set('session.gc_maxlifetime',   200000);
ini_set('session.save_handler',     'user');
ini_set('session.use_only_cookies', 1);
ini_set('session.use_trans_sid',    0);
ini_set('url_rewriter.tags',        '');

/**
 * We try to set the correct cookie domain. If you are experiencing problems
 * try commenting out the code below or specifying the cookie domain by hand.
 */
if (isset($_SERVER['HTTP_HOST'])) {
  $domain = '.'. preg_replace('`^www.`', '', $_SERVER['HTTP_HOST']);
  // Per RFC 2109, cookie domains must contain at least one dot other than the
  // first. For hosts such as 'localhost', we don't set a cookie domain.
  if (count(explode('.', $domain)) > 2) {
    ini_set('session.cookie_domain', $domain);
  }
}

What do you recommend?

=-=

VM commented 5 June 2007 at 12:49

work with lowering:

ini_set('session.cookie_lifetime',  2000000);
ini_set('session.gc_maxlifetime',   200000);
ini_set('session.cache_expire',     200000);

until you can gain control of the situation.

What I'd do in this situation.

Put site in maintainance mode.

truncate the sessions table
truncate all cache tables
truncate the entire watchdog table

now these tables should be entirely empty, This will allow you to watch and see whats going on within the DB itself from a fresh start when you move your site out of maintenance mode. All sessions will be killed, all cache will regenerate, and all watchdog messages will be cleared. Watchdog can grow as everything that happens on your site is logged by watchdog, errors, logins, failed logins so on so forth.

does not work

Prabhash-1 commented 24 July 2013 at 11:10

i write all these line in my code but i still have a problem,session is clear after some time but when user is in active state then this code also logout ,i want to logout user when he is in idle state.

=-=

VM commented 24 July 2013 at 14:03

which version of Drupal? considering the discussion in this thread is for Drupal 5 and is 6 years old.. you shouldn't have to write any code. the settings are/were included in the file discussed.

Make sure that php.ini's

badrange commented 13 November 2013 at 11:56

Make sure that php.ini's session.gc_probability is not set to 0. It is default in some linux distributions and will cause drupal to never clear out session tables by itself.

Is there something that needs to be put into cron.php?

greywolfsspirit commented 14 November 2010 at 15:40

I'm still having the overwhelming watchdog sizes as well. Limit was set for 1000, like some of the others, set for 1 hour as well.. I run the cron.php to clear it, and nothing is being cleared out. Even though I get the 'Cron completed successfully. I am enclosing my cron.php file to see if I am missing a command or statement or whatnot.

<?php
// $Id: cron.php,v 1.36 2006/08/09 07:42:55 dries Exp $

/**
 * @file
 * Handles incoming requests to fire off regularly-scheduled tasks (cron jobs).
 */

include_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
drupal_cron_run();

Any help is appreciated!

NOW working for meI too had the same trouble.

garg_art commented 12 April 2008 at 00:01

My admin menu does not show error settings,...

Set new entries under........
Discard log entries older than:

I found it by typing

http://www.mysite.com/admin/settings/error-reporting
or you can try
http://mysite.com/admin/settings/error-reporting

Replace mysite.com with your IP address or actual domain you are using.

Thanks to all the Drupal helping volunteers....

Drupal rocks and Drupal helpers are Rock Stars.

Same problem

b.hughes commented 20 June 2007 at 09:21

I was having the same problem, to a lesser degree. My watchdog log is 22 pages long, 1059 entries on a site that isn't even live yet.

I was under the impression that the watchdog logs size would be controlled by the "Access Log" settings, they aren't. The size of that log is controlled by Administer > Site Configuration > Error Reporting and the length of time to keep these logs. I just reset mine from 1 week to 12 hours and ran cron manually. It went from 1059 entries to 203 entries. Just wish there was a clear logs control.

If you set the watchdog

catch

he/him

English

commented 20 June 2007 at 09:58

If you set the watchdog lifetime to an hour or two, then it'll clear everything but the past hour of entries within an hour assuming you have cron set up, or as you said you can run cron manually. I reckon that's close enough to a "clear logs" control no?

Thanks for tip on watchdog configuration

mennonot commented 19 December 2010 at 18:00

B.hughes, thanks, this differentiation between the access log and error expiration was very helpful for me in figuring out why my watchdog table was constantly filling up when I'd set the access log expiration quite low.

Suspect log retention time defect in Drupal 5.1

hgmichna commented 5 July 2007 at 08:14

In my installation I just reduced the retention time from 4 to 2 weeks, but the access log remains 4 weeks long.

Old entries are actually purged, but after 4 weeks, not 2. I suspect that Drupal ignores the changed access log setting, but other logs are correctly reduced to 2 weeks. Of the ones I checked, the setting is only ignored for the access log.

Hans-Georg

Drupal 5.1 with add-on modules: ACL, Forum Access, Poormanscron

Site: http://elephanttrust.net/

added index to watchdog's timestamp column

Greg Go commented 27 February 2010 at 02:05

I noticed this query was killing our box (Drupal 5 site):

DELETE FROM watchdog WHERE timestamp < 1266626877;

With 3m rows and no index on that column (!) it was taking minutes to complete, which backed everything else up, eventually pushing the box into swap.

Here's a discussion of why the index hasn't been added (still valid at D7): http://drupal.org/node/165061

I created it directly in mysql using:

truncate watchdog;
create index timestamp on watchdog (timestamp);

Tables to watch in Drupal 6

Manuel Garcia commented 14 July 2010 at 09:17

Watchdog entries are controled in drupal 6 on /admin/settings/logging/dblog
Cache lifetime is controlled in /admin/settings/performance
Sessions table you cannot control besides using settings.php settings, and if your server has php garbage collection properly running. A good alternative is to use the
session_expire module, if your site is big enough for this table to cause trouble.

I used "drush wd-delete"

damarist commented 25 October 2010 at 16:11

I used drush watchdog-delete "cron run succesful"
Now my database is clear, I'll try configure a cron job with this tool!

There are another options: drush wd-delete --help
Examples:
drush watchdog-delete all Delete all messages.
drush watchdog-delete 64 Delete messages with id 64.
drush watchdog-delete "cron run Delete messages containing the string "cron
succesful" run succesful".
drush watchdog-delete --severity=notice Delete all messages with a severity of
notice.
drush watchdog-delete --type=cron Delete all messages of type cron.

Arguments:
--severity Delete messages of a given severity level.
--type Delete messages of a given type.

Aliases: wd-del, wd-delete

watchdog in the hizouse

Stoob commented 4 November 2010 at 22:23

I had my logging set to "1000" rows. Cron runs twice a day. Still my watchdog table was over 200MB. Wowza!

I reduced it to 100 rows. Hopefully better now.

Overheads were the culprit

kartik.gmf commented 15 July 2011 at 18:32

Today I received a warning from my hosting (shared) that excessive resources were being used by my mySQL engines. It pointed out to me that there was a database which was >3GB !!

On looking closely I found that most of this 3GB was basically being taken up by my watchdog table. I checked all the settings to find that only 1000 entries were being logged.

Further investigation showed me that the culprit was the overheads. Almost all of the 3GB was being taken up by overheads. Although I have easily fixed this using the optimize button in my phpMyAdmin I am worried that these tables would again inflate.

Can I somehow automate this by adding to the cron or even better make sure that un-unnecessary overheads are not created with the tables?

-Kartik
www.getmenift.com

subscribing .. One of my

electronicmonkey commented 20 August 2011 at 20:36

subscribing .. One of my sites is being crippled by watchdog table on a regular monthly basis now. I desperately want to sort this out. I have a couple of other D6 sites which are not giving me this problem. What modules can cause this ?

subscribing .. One of my

electronicmonkey commented 20 August 2011 at 20:36

Look at your watchdog...

LoMo commented 20 August 2011 at 21:22

The watchdog tracks issues. Try to sort them out. You can run a cron job that empties those tables or use Drush to purge them, but ONLY doing this is a bit like unplugging the annoying blinking red light on the dashboard of your car ... instead of adding oil. ;-)

Best of luck.

Lowell

See you at the Drupalcon!