Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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.
Comment | File | Size | Author |
---|---|---|---|
#17 | 2494221-D7-SLOW-DBLOG.patch | 457 bytes | david_garcia |
#8 | slow-dblog-clear-2494221-8.patch | 706 bytes | dagmar |
#1 | slow-dblog-clear-d7-do-not-test.patch | 457 bytes | david_garcia |
slow-dblog-clear-d8.patch | 706 bytes | david_garcia | |
Comments
Comment #1
david_garcia CreditAttribution: david_garcia commentedComment #2
david_garcia CreditAttribution: david_garcia commentedComment #3
david_garcia CreditAttribution: david_garcia commentedComment #4
XanoTRUNCATE
offers major performance improvements overDELETE
, 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 auid
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).
Comment #5
david_garcia CreditAttribution: david_garcia commentedAgreed, 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.
Comment #6
mgiffordPatch no longer applies.
Comment #8
dagmarRe-rolled.
Comment #10
cilefen CreditAttribution: cilefen commentedComment #11
dawehnerSuper nice catch!
Comment #14
catchYes that's a nice find. Committed/pushed to 8.3.x and cherry-picked to 8.2.x. Thanks!
Comment #16
david_garcia CreditAttribution: david_garcia commentedBack to D7...
Comment #17
david_garcia CreditAttribution: david_garcia commentedComment #18
dagmarWorks as expected.
Comment #21
stefan.r CreditAttribution: stefan.r commentedCommitted and pushed to 7.x, thanks!
Comment #22
stefan.r CreditAttribution: stefan.r commented