4

I have seen similar questions to this before but they do not solve my problem. This is Drupal 8.

Today the watchdog table on one of our sites exploded to 12GB in size. No other table in the DB is above a few dozen MBs if that.

Logging was only set to record the last 1000 events. I then turned even that down to 100 and ran cron a few times.

It dropped back down to 1GB... then up to 5.9GB in about 30min. When I try to inspect it with brush wd-show --count=1000 or something like that it only shows about 100 entries.

What is all the size coming from?

Additionally, the mysql database is still showing a disk as reported by du of 12GB...

I've also tried flushing and clearing the cache query in mysql and restarting mysql.

I only have 20GB of space on this server and it is not a very big site, what is going on and how can I stop it? If this goes on it will crash the site.

I was messing around with a view that used a contextual filter to a taxonomy term name with multiple values. Ok, I could see that using up some cache or throwing some errors... but 12GB!

EDIT: Ok the size on disk after deletion can be fixed by Optimizing the table: OPTIMIZE TABLE sitedb.watchdog. However, I still would like to know if there is someway to stop watchdog growing out of control this way? I had already limited the entries so I do not understand how it could get so big...

Improve this question
4
  • 3
    The watchdog table size is controlled by cron. If it is growing so fast you need to run cron more often. A better approach would be to test the View in a dev environment until it works without errors. In general for high traffic sites you might consider sending the log entries to a different place than the database, for example to syslog.
    – 4uk4
    Apr 5, 2019 at 6:27
  • How many log entries do you get per day?
    – ya.teck
    Apr 5, 2019 at 8:36
  • 2
    The problem probably isn’t the number, it’s that Drupal DELETEs records from that table, and DELETE doesn’t reclaim disk space until you OPTIMIZE. If you have a lot of turnover on that table, it’ll use a lot of disk space. Fixing the underlying problems that are causing it to fill up would be best, but you could also consider running a cron job to truncate the table (which reclaims the disk space immediately), or just turn it off altogether and replace with syslog as 4k4 mentioned
    – Clive ♦
    Apr 5, 2019 at 8:43
  • I am using syslog and I will be turning off watchdog. What surprised me was how quickly watchdog grew. I turned on Aggregation in a view to try and handle duplicates and it just exploded
    – tanbog
    Apr 6, 2019 at 10:52

1 Answer 1

Reset to default
1

As mentioned by Clive you need to reclaim the diskspace, however when I had a similar issue in the past OPTIMIZE was too slow and crashed the site.

According to the MariaDB knowledge base it should be possible to run the following commands:

CREATE TABLE watchdog_new LIKE watchdog;
INSERT INTO watchdog_new SELECT * FROM watchdog;  -- This could take a long time
RENAME TABLE watchdog TO watchdog_old, watchdog_new TO watchdog;   -- Atomic swap
DROP TABLE watchdog_old;   -- Space freed up here

This ran pretty quickly and didn't cause any major problems to the site

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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