Improved indexes for Notifications during: Notifications cancelling/User blocking/User delete operations

It happened to me too, once. Also in my case, when I refreshed the page, the user resulted blocked.

I've not seen this but I suspect the same bug on g.d.o that causes the blocking to fail there.

Or it could be a general timeout, we need to process quite a few subsites now.

That's because Bakery has to ping all the subsites. If one of them doesn't reply in a timely manner, Varnish gives up and return the Guru meditation page.

I agree this is likely due to g.d.o.

I believe the subsite ping is a default drupal_http_request that will time out in a max of 30 seconds, so it should never take longer than 30*subsites.

Perhaps we should decouple the actions so the request to block happens quickly and the actual block happens on cron?

I'd rather see the bug on g.d.o fixed...

Regardless it might make sense to desynchronize this.

(here's some research on the g.d.o issue, which I think is fair to say is the cause of this main issue, so I'm hijacking).

When we block a user their notifications get canceled as well. The operation of canceling a user's subscriptions has the following long queries:

52000.231notifications_event_clean
DELETE FROM notifications_event WHERE created < 1280409541 AND eid < (SELECT MIN(eid) FROM notifications_queue)
51324.161notifications_queue_delete
DELETE FROM notifications_queue WHERE uid = 58
50999.011notifications_event_clean
DELETE FROM notifications_event WHERE counter = 0 AND created < 1280409541
50998.551notifications_event_clean
UPDATE notifications_event e SET counter = (SELECT COUNT(*) FROM notifications_queue q WHERE q.eid = e.eid ) WHERE e.created < 1280409541

That first query is the real winner: it takes 52 seconds.

I added create index ix_ne_created on notifications_event (created);
and create index ix_nq_eid on notifications_queue (eid);

I then re-ran the disable notifications for my g.d.o user:

0.551notifications_event_clean
DELETE FROM notifications_event WHERE created < 1280410503 AND eid < (SELECT MIN(eid) FROM notifications_queue)

The query is now a half a millisecond. Seems like some good indexes to have.

Leaving this issue here for a bit, but this should be fixed for the long run in notifications.

Here are the indexes I created manually:

create index ix_ne_created on notifications_event (created);
create index ix_nq_eid on notifications_queue (eid);

This probably applies to all versions, but we should start with 6.x-4.x.

On another site I manage where the notifications_event table gets to 200 entries and the notifications_queue gets to 100,000+ records the "Clean Queue" operation was taking in excess of Apache's execution timeout (5 minutes). Running the queries manually they were running for 15+ minutes before I stopped them to look for better indexes. I found that these two indexes make those queries run in seconds.

Since this is about a feature failing to do its intended job reclassifying as a bug.

6.x-2.x-dev needs these indexes as well. Don't know how to address this though, perhaps it is just easiest to comment on it like this.

Duplicate of #1070762: notifications_queue() slow query? One of the indexes here is different from that issue, but it might be a good idea to roll them all up in a single patch.

I am closing this issue, which is for a not supported Drupal version.