@garthomite (Garth) posted a new topic, subject "Cleaning up e-mail logs". It read:

   I was looking to free up some disk space on our discourse installation and I noticed that the email_logs table was using a substantial amount of space compared to everything else. For context all other tables amount to about 100mb but the e-mail logs are in the range of 12GB
    
    I'm curious, is there a process to limit the amount in this table? Can older entries be removed on a regular basis?
    
    Thanks!
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @codinghorror (Jeff Atwood) replied

    Sounds like there was a dire problem with your email config at one point?
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @garthomite (Garth) replied

    Diving in to this a little more there are a lot of logs with the type ‘email_reject_destination' with a lot of spam content, ~ 500k rows in one week.
    
    Is there any harm in selectively trimming the table either by just date or perhaps just removing any ‘email_reject_destination' logs over x days?
    
    **EDIT** \- Yep, a lot of reject logs!
    [code] 
          count   |              email_type              
        ----------+--------------------------------------
         24086049 | email_reject_destination
          6293124 | email_reject_auto_generated
           639048 | mailing_list
            55853 | digest
             7398 | user_posted
        
    [/code]
    
    `
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @zogstrip (Régis Hanol) replied

    garthomite:
    
    > Is there any harm in selectively trimming the table either by just date or perhaps just removing any ‘email_reject_destination' logs over x days?
    
    You can safely trim all the `email_logs` which have an **empty** `reply_key` (eg. “`email_reject_*`” and “`digest`”).
    
    For the ones that have a `reply_key`, if you delete them, you'll prevent users from replying to that email. So I guess you could be fine deleting some that are older than X months.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @codinghorror (Jeff Atwood) added

    Should we be doing this automatically?
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @zogstrip (Régis Hanol) added

    We could, but I'm not sure what threshold to use. Add a new site setting which defaults to 1 year?
    
    Note: this will also affect the stats we have on the dashboard.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @garthomite (Garth) replied

    After trimming the rejects our database shrunk from 13gb -> 900mb so our database is happy again.
    
    Thanks!
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

@codinghorror (Jeff Atwood) replied

    Sure default to 1 year is safe lets do that!
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @zogstrip (Régis Hanol) added

    https://github.com/discourse/discourse/commit/ac863bab915a1b808e259d3bfd0d23b7bbba13ca
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @sam (Sam Saffron) replied

    Suppress? Isn't the “purge” or “delete”?
    
    I think the word is a bit confusing in this context
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @zogstrip (Régis Hanol) replied

    I was being consistent with https://github.com/discourse/discourse/blob/master/config/site_settings.yml#L503 but I agree, `delete` is much better. ("`purge`" might be too technical)
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

Then @codinghorror (Jeff Atwood) said

    Yeah it should be delete.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

@zogstrip (Régis Hanol) replied

    https://github.com/discourse/discourse/commit/460665895c91b2f9018e361b393d7e00dc86b418
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @Falco (Falco) added

    Hey @zogstrip,
    
    When you changed from `suppress_email_logs_after_days` to `delete_email_logs_after_days` are the changed values lost on upgrades?
    
    I just sent 15k e-mail to people  when digest ran.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

Then @zogstrip (Régis Hanol) said

    Having a second look at it, I think you're right. I forgot a migration… 
    
    What did you set it to?
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @Falco (Falco) replied

    I have 110k users created trough API, and only 15k using the forum.
    
    Just enabled digests last week, and set it to 60 days, because my non-users where created 5 months ago.
    
    When the update ran, the default was set back to 360 days, and many e-mails sent.
    
    I got back from my lunch and got to kill the sidekiq queue with 15k sent .
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

Then @zogstrip (Régis Hanol) said

    So sorry about that 
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

@sam (Sam Saffron) replied

    Can we fix it so people who upgrade today no longer have this problem
    
    Ahhh re read … Odds on someone bring on that exact version with the bad name is really low
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @tgxworld (Alan Tan) added

    The default for `SiteSetting.delete_email_logs_after_days` has been reduced to 90 days as per
    
    https://github.com/discourse/discourse/commit/186623acd070b71422dfe61e083fa3aeb67844ac
    
    For busy sites that send out alot of emails, this table can bloat fairly quickly. There are some optimizations that we can do on the table to make it use less disk space but that'll have to wait till Discourse Version 2.1.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @tgxworld (Alan Tan) replied

    A couple of changes have been made here
    
      1. https://github.com/discourse/discourse/commit/01a63f8b4b3d193ecf9d25a8f7a0365c2902f0aa  
    This column was not required as we can obtain the topic through the post.
    
      2. https://github.com/discourse/discourse/commit/ae8b0a517ffda0bb57a67bb9773bfb441181dcee  
    Emails that have been skipped are logged into another table `skipped_email_logs` from now on so that we can avoid creating indexes like this. Also, we were previously storing the skipped reasons as strings in the database which was inefficient and the “locale” of the reason is decided when the record is created. Going forward, each skipped email log contains a `reason_type` which is stored as an integer in the database. The reason is then translated on demand based on the given `reason_type`.
    
      3. https://github.com/discourse/discourse/commit/fad9c2b97113ddbac16d968c38edb7ab47aa8eb9  
    Previously, we couldn't delete `email_logs` with a `reply_key` because it would break the reply to feature. To overcome this limitation, we're storing the reply keys in a new table that contains less columns making it more light weight.
    
    
    
    
    Note that I'm playing it safe here and have yet to drop the old columns from the tables so you'll actually see an increase in disk space used for a short period of time.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @tgxworld (Alan Tan) replied

    The old columns on `email_logs` table have been dropped.
    
    https://github.com/discourse/discourse/commit/87537b679c74da58e9439c322f009f7bbd09966a
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @tgxworld (Alan Tan) replied

    This topic was automatically closed after 3 days. New replies are no longer allowed.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @tgxworld (Alan Tan) added

    *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

@tgxworld (Alan Tan) replied

    Another fix that would improve things in the future for sites with mailing list mode enabled.
    
    https://github.com/discourse/discourse/commit/ba6f11c521b66870a6cd2c39785aa0dbcdabcecf
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

@codinghorror (Jeff Atwood) replied

    Can this be considered complete now @tgxworld?
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @tgxworld (Alan Tan) added

    Not quite I still have to work on cleaning up the `PostReplyKey` table which used to be a column on the `EmailLog` table. Pretty straight forward to clean up the records but I just need to read through the code path to make sure that we send the right email when a user tries to reply to a post with a deleted post_reply_key. I have this on my list this week.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

Then @tgxworld (Alan Tan) said

    tgxworld:
    
    > I still have to work on cleaning up the `PostReplyKey` table
    
    This is done in
    
    https://github.com/discourse/discourse/commit/36a7028f19b2b511862eb753b3ec72fccaffd2d7
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

So @sam (Sam Saffron) replied

    Can you paste a screenshot here of what email you get when you reply to something and there is no Post Reply Key found, it may make sense to refine copy and UI there.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @tgxworld (Alan Tan) replied

    This is how the email looks like
    
    Screenshot from 2018-08-23 11-09-13.png1118×190 21.2 KB
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @sam (Sam Saffron) replied

    Do we need that “security reasons” phrase there … I think without those words its actually a bit better.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

After which, @tgxworld (Alan Tan) added

    Hmm got to check with @zogstrip regarding this. Based on what I know, there isn't any security reason to restrict it but this is mainly done so that we keep disk space usage sane on large forums.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @zogstrip (Régis Hanol) replied

    that sentence.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

To which @tgxworld (Alan Tan) replied

    Done  https://github.com/discourse/discourse/commit/f28a53db48ad7ab0af63ad2614134839b687527f
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint

Then @tgxworld (Alan Tan) said

    This topic was automatically closed after 4 days. New replies are no longer allowed.
      *[. ]: I do not recommend that in this case, however.
      *[MWE]: minimal working example
      *[LCP]: largest contentful paint
      *[FCP]: first contentful paint


{'source': 'discourse', 'url': 'https://meta.discourse.org/t/cleaning-up-e-mail-logs/39132', 'title': 'Cleaning up e-mail logs', 'views': 3332, 'has_accepted_answer': False, 'word_count': 1106, 'like_count': 54, 'reply_count': 26, 'participant_count': 6, 'posts': 34, 'tags': '', 'author': '@garthomite (Garth)', 'participants': '@garthomite (Garth), @codinghorror (Jeff Atwood), @zogstrip (Régis Hanol), @sam (Sam Saffron), @Falco (Falco), @tgxworld (Alan Tan)', 'image_url': [None], 'created_at': '2016-02-06T01:51:51.080Z', 'last_posted_at': '2018-08-27T10:00:15.202Z'}