Index for the watchdog table

This should be addressed in 5 and backported to 4.7 and 4.6.

I'm in the process of benchmarking this. Please do roll a patch.

I've now benchmarked this with 217905 rows in my watchdog table:

So yes, we're looking at a situation where with that number of watchdog rows, making an index speeds up the query by nearly three orders of magnitude.

I just hate that issue responses don't respect the Input Format.

Standard Drupal core

• Average time: 565.329
• Standard deviation: 4.693

Made an index on column 'type'

• Average time: 0.709
• Standard deviation: 1.334

Here's a patch against 4.7.

And here's one for HEAD.

Forgot the new db case for postgres.

NOTE: because I've added this as upgrade 183 (I'm assuming it will get backported), there is NO case where the index might get added twice, so there is no need to check for the existence of the index before adding it.

Updated 4.7 version; forgot to add to the three database creation files.

While I have no HEAD pgsql Drupal, I ran the update SQL statement on a pgsql database and it worked. So, it works, the advantage is tremendous -- get it in!

I don't think it is neccessary to backport to 4.7. People with a lot of watchdog entries and a habit of looking at their logs all the time can add it themeselves. ;)

Ok in that case, the update number needs increasing to 1000-something.

Cleaned up the patch for HEAD and put the update in the 1000 series.

Gerhard: I don't agree with not backporting - aren't we planning on having another 4.7.* release at some point? Why deny that version this improvement? Is there a real reason *not* to? There are certainly good reasons why it should be backported; it is the highest quality watchdog we know how to offer.

RobRoy's patch missed the postgres case for new databases.

This patch puts the upgrade in 1015 - use it if the change won't get backported to 4.7.

This patch puts the upgrade in 183 - use it if the change will get backported to 4.7.

My reason to not backport is that it is only an admin function that is affected and so the overall impact isn't that big and that I try to avoid sql updates in minor releases if at all possible.

Oops, I updated the wrong patch. Sorry about that, and thanks for the catch Robert. I would change the double quotes to singles in the pgsql db_query but I'm too tired and don't want to seem TOO anal, but I feel like core stuff should set a good example as that's how I learned a lot of my coding standards.

Since this is such a big improvement IMHO it's worth a backport since a huge query time like that could affect your whole site's performance say if you had a bunch admins looking at the watchdog table. And if 4.7 users don't hit update.php after their core update, no big whoop. They're not going to see any db errors so the impact on the stability of that branch isn't really affected. Just my $0.02.

So unless Gerhard changes his mind, #16 is the patch to go with, and it is RTBC. Chx has tested the postgres update.

@RobRoy... since I had the editor open to the line with the double quotes, I almost changed them and re-rolled, just for your benefit. But the whole file uses double quotes... and it would have been the only line with single, so I balked.

see, that was sneaky of me! I said #16 but that's the one that assumes the backport ;-) Just trying to force killes' hand, I guess. I'm uploading a copy of 15 to this follow up... this is the patch to use.

Committed to CVS HEAD. Thanks.

oh, that was even sneakier. I uploaded the wrong friggin' patch!

deep breath...

Here is the right one.

Dries applied the right one.

I have an analogous problem in Drupal 6.0 with the same table ("watchdog"), but with the "uid" field, which isn't indexed yet.

When deleting multiple users, I can see in PostgreSQL logs that there are multiple UPDATE queries that occupy > 25 seconds:

duration: 35890.329 ms statement: UPDATE watchdog SET uid = 0 WHERE uid = 0

Adding an index fixes the problem:

CREATE INDEX watchdog_uid_idx ON watchdog USING btree (uid);

Should I file a new issue report?

olo, i have seen the same problem. in drupal 5 though. i think (in our case) it is a cron process cleaning up deleted users. but i see the same trouble -- multi-second (often way way way too long!) queries of the form "UPDATE watchdog SET uid=0 WHERE uid=...". totally unacceptable, as it is locking the watchdog table all over the site (duh, of course watchdog is used by a ton of things), so the site gets ridiculously slow.

i find it kind of baffling this hasnt been addressed before. maybe people are zeroing out watchdog all the time, or simply have incredibly zippy servers (ha).

but fwiw, i agree that an index on uid would seem to be the (simple) solution. i would guess we would want to file this as a new report. but i figured i should comment, if for no other reason than to help some frustrated drupal admin find it via google. like i wish i had done a few weeks ago. haha