When the watchdog table is very big, clearing the dblog through the interface is very slow.

That's because it uses DELETE instead of TRUNCATE. Truncate statements execute inmediately no matter the size of the table, while DELETE gets slower as the table grows.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

david_garcia’s picture

Status: Active » Needs review
FileSize
457 bytes
david_garcia’s picture

Title: Slow Log Clear when there are lots of data » Slow Log/Watchdog clear when there are lots of data
david_garcia’s picture

Issue tags: +Performance
Xano’s picture

TRUNCATE offers major performance improvements over DELETE, because it just throws out the data at a low-level rather than deleting data per row.

The consequences of this are no or severely limited transaction logging and errors in case of DB replication and foreign keys. Both the MSSQL and MySQL documentation report that when any foreign keys reference a table, you can't truncate that table. At this moment there are no foreign keys referencing watchdog at all, but seeing as the table contains a uid column, it may not be unlikely such a key is added in the future.

TLDR; As far as I can see this change looks fine for core, but could potentially backfire in the future (in which case we can roll it back) or in custom projects (possible, but perhaps not likely).

david_garcia’s picture

Agreed, but logs can get pretty massive and is the kind of thing that you really decouple from your application - indeed many people are using other things instead of dblog.

Truncate basically means that you do not generate transaction logs for the operation, and that you cannot have implicit relationships (such as foreign keys) between the truncated table and other objects.

mgifford’s picture

Status: Needs review » Needs work

Patch no longer applies.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

dagmar’s picture

Version: 8.1.x-dev » 8.2.x-dev
Status: Needs work » Needs review
FileSize
706 bytes

Re-rolled.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.0-beta1 was released on August 3, 2016, which means new developments and disruptive changes should now be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

cilefen’s picture

dawehner’s picture

Status: Needs review » Reviewed & tested by the community

Super nice catch!

The last submitted patch, 1: slow-dblog-clear-d7-do-not-test.patch, failed testing.

  • catch committed 0a8686a on 8.3.x
    Issue #2494221 by david_garcia, dagmar: Slow Log/Watchdog clear when...
catch’s picture

Status: Reviewed & tested by the community » Fixed

Yes that's a nice find. Committed/pushed to 8.3.x and cherry-picked to 8.2.x. Thanks!

  • catch committed 395d285 on 8.2.x
    Issue #2494221 by david_garcia, dagmar: Slow Log/Watchdog clear when...
david_garcia’s picture

Version: 8.3.x-dev » 7.x-dev
Status: Fixed » Needs work

Back to D7...

david_garcia’s picture

Status: Needs work » Needs review
FileSize
457 bytes
dagmar’s picture

Status: Needs review » Reviewed & tested by the community

Works as expected.

  • catch committed 0a8686a on 8.4.x
    Issue #2494221 by david_garcia, dagmar: Slow Log/Watchdog clear when...

  • catch committed 0a8686a on 8.4.x
    Issue #2494221 by david_garcia, dagmar: Slow Log/Watchdog clear when...
stefan.r’s picture

Committed and pushed to 7.x, thanks!

stefan.r’s picture

Status: Reviewed & tested by the community » Fixed

  • 00b0726 committed on 7.x
    Issue #2494221 by david_garcia, dagmar: Slow Log/Watchdog clear when...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.