Cleanup notification_notice

Bug #1637632 reported by kaputtnik on 2016-10-28
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Widelands Website
Fix Released
Medium
kaputtnik

Bug Description

The database is 'spammed' with notices from notification. I've made some query's:

select count(*) from notification_notice;
+----------+
| count(*) |
+----------+
| 6973731 |
+----------+

There are overall over 6 millions notices stored in the database. A comparison to pybb_post and pybb_topic:

pybb_post: 17375
pybb_topic: 1873

Accessing the notifications page (http://wl.widelands.org/notification/) is terrible slow because the huge amount of notices.
On IRC we talked about it and the suggestion was to remove the notice list in the view as first step and wait until someone complains about it. If not we should omit creating of such notices.

I totally agree on that suggestion. But first i want to to delete the amount of notices, just to speed up accessing the notifications page by deleting all notices added before 1.1.2016. Are you ok with that?

In the end we may have notices only for setting the e-mails about the things a user want to be noticed and no list of notices anymore. I am fine with that. If someone complains about this, we could make a django command to delete all notices older than 1 year or so.

Any other suggestions?

Related branches

GunChleoc (gunchleoc) wrote :

Sounds good to me.

SirVer (sirver) wrote :

sgtm too. But when deleting the notices, make sure they do not have any links to other objects. If that is the case, we need to delete them through django's ORM, otherwise we might have dangling links.

kaputtnik (franku) wrote :

> mysql [wl_django]>delete from notification_notice where added < "2016-01-01";
> Query OK, 3644335 rows affected (36 min 35.26 sec)

Whow... 36 minutes...

> mysql [wl_django]>select count(*) from notification_notice;
> +----------+
> | count(*) |
> +----------+
> | 3467639 |
> +----------+

There are over 3 Millions notices left. This is the amount of notices only from year 2016. At least the corresponding admin site is available again: https://wl.widelands.org/admin/notification/notice/ (prior it results in a gateway timeout)

Next thing is to omit the view of notices on the users page.

GunChleoc (gunchleoc) wrote :

The admin notice page still takes forever to load. How about making the cutoff 6 months?

We could also reduce the amount of notices generated by deleting dead user accounts - start with users who never logged in and see how many we can lose that way.

kaputtnik (franku) on 2017-01-19
Changed in widelands-website:
importance: Undecided → Medium
kaputtnik (franku) on 2017-01-19
Changed in widelands-website:
status: New → Triaged
kaputtnik (franku) wrote :

I am just deleting all notices before 2017-01-01 ...

kaputtnik (franku) wrote :

The notices list is now commented out in https://wl.widelands.org/notification/

kaputtnik (franku) on 2017-04-20
Changed in widelands-website:
assignee: nobody → kaputtnik (franku)
kaputtnik (franku) on 2017-04-21
Changed in widelands-website:
status: Triaged → Won't Fix
status: Won't Fix → In Progress
kaputtnik (franku) on 2017-05-10
Changed in widelands-website:
status: In Progress → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers