Wikia/app — pull request #2436 by aquilax (2014-01-10 12:01:03+00:00)
Title: CONN-3 Remove expired notifications

DO NOT MERGE!!! This must be Code reviewed, SQL reviewed and QAd first.

All four queries should be executed from a background job on daily basis. Currently the last two queries only are executed on cron-s4 by the script /usr/local/bin/message-wall-cleanup.py.

@SQLReview

* These are esencially all the notifications per user which are older than 7 days; As wall_notification_queue is trimmed regulary, and wall_notification from time to time, i expect the rows returned to be notifications for all users one day;

```
mysql> EXPLAIN SELECT /* WallNotificationsEveryone::getExpiredNotifications */
-> wn.id, wn.user_id, wn.wiki_id, wn.unique_id
-> FROM `wall_notification` `wn`
-> JOIN `wall_notification_queue` `wnq` ON ((wnq.wiki_id = wn.wiki_id) AND (wnq.page_id = wn.unique_id))
-> WHERE (datediff(NOW(), event_date) > 7)
-> ;
+----+-------------+-------+------+---------------+-----------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+----------------------+------+-------------+
| 1 | SIMPLE | wnq | ALL | wiki_id | NULL | NULL | NULL | 84 | Using where |
| 1 | SIMPLE | wn | ref | unque_id3 | unque_id3 | 4 | dataware.wnq.wiki_id | 115 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+----------------------+------+-------------+
2 rows in set (0.00 sec)
```

* The delete is split in batches of 100 ids to prevent overflowing the query buffer. I'm not sure what is the practical limit in our setup. If 100 should be changed, please advise on a new limit;

```
DELETE /* WallNotificationsEveryone::deleteNotificationsFromDB */ FROM `wall_notification` WHERE (id IN (5926,5925,5927))
mysql> EXPLAIN SELECT 1 FROM `wall_notification` WHERE (id IN (5926,5925,5927));
+----+-------------+-------------------+-------+-----------------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+-----------------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | wall_notification | range | PRIMARY,unque_id2,is2 | unque_id2 | 4 | NULL | 3 | Using where; Using index |
+----+-------------+-------------------+-------+-----------------------+-----------+---------+------+------+--------------------------+
1 row in set (0.01 sec)
```

* This query is currently used by the python script but it's marked for SQL Review in the code;
* The wall_notification_queue table is trimmed daily and contains the pages that create notifications;

```
delete from wall_notification_queue where datediff(NOW(), event_date) > 7
mysql> EXPLAIN SELECT 1 FROM wall_notification_queue WHERE datediff(NOW(), event_date) > 7;
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | wall_notification_queue | ALL | NULL | NULL | NULL | NULL | 84 | Using where |
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
```

* This query is currently used by the python script but it's marked for SQL Review in the code;
* The wall_notification_queue_processed table is trimmed daily and contains already processed notifications for users;

```
delete from wall_notification_queue_processed where datediff(NOW(), event_date) > 7
mysql> EXPLAIN SELECT 1 FROM wall_notification_queue_processed WHERE datediff(NOW(), event_date) > 7;
+----+-------------+-----------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | wall_notification_queue_processed | ALL | NULL | NULL | NULL | NULL | 8699 | Using where |
+----+-------------+-----------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
```