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.

CommentFileSizeAuthor
#17 2494221-D7-SLOW-DBLOG.patch457 bytesdavid_garcia
#8 slow-dblog-clear-2494221-8.patch706 bytesdagmar
#1 slow-dblog-clear-d7-do-not-test.patch457 bytesdavid_garcia
slow-dblog-clear-d8.patch706 bytesdavid_garcia

Comments

david_garcia’s picture

david_garcia commented
Status: Active » Needs review
StatusFileSize
new slow-dblog-clear-d7-do-not-test.patch457 bytes
david_garcia’s picture

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

david_garcia commented
Issue tags: +Performance
xano’s picture

xano
he/they
Primary language English
Location Southampton
commented

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

david_garcia commented

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

mgifford
he/him
Primary language English
commented
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

dagmar
Primary language Spanish
Location Argentina
commented
Version: 8.1.x-dev » 8.2.x-dev
Status: Needs work » Needs review
StatusFileSize
new slow-dblog-clear-2494221-8.patch706 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

cilefen commented
Related issues: +#2782269: dblog_cron() is slow
dawehner’s picture

dawehner
Primary language German
commented
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

catch
he/him
Primary language English
commented
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

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

Back to D7...

david_garcia’s picture

david_garcia commented
Status: Needs work » Needs review
StatusFileSize
new 2494221-D7-SLOW-DBLOG.patch457 bytes
dagmar’s picture

dagmar
Primary language Spanish
Location Argentina
commented
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

stefan.r commented

Committed and pushed to 7.x, thanks!

stefan.r’s picture

stefan.r commented
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.