1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

The right way to batch process

Discussion in 'MySQL' started by sarahk, Apr 5, 2018.

0
  1. #1
    I have calls table that logs all interactions with people. It also logs all outbound emails that aren't transactional.

    At the moment when a newsletter/invoice/message is sent records get created in calls and marked as "Queued". Then a batch process runs through and sends them 25 at a time. Each newsletter is generated individually and sent to 3rd party mail tool via http.

    On the front end I just have a page that does a refresh every 500 milliseconds. Emails are only marked as sent once they've actually gone.

    I've recently added in stats to our site that show how many emails have actually been sent and, whoops, way more than we should have. Turns out that even after the refresh happens some of the emails keep getting sent but not marked as sent so some people have received the email several times.

    Is there a way in MySql I can lock records for a prescribed interval?
    Is there a better way to approach batch processing?
     
    sarahk, Apr 5, 2018 IP
  2. RomanEpo

    RomanEpo Active Member

    Messages:
    127
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    78
    #2
    It seems to me,while refreshing there is some thing wrong in event distribution , inconsistency between queue and refresh script,if your problem is traceable,half the task will be completed.I dont know the details about the system,So i can not say anything to make sure.
     
    Last edited: Apr 6, 2018
    RomanEpo, Apr 6, 2018 IP
  3. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,151
    Likes Received:
    1,656
    Best Answers:
    29
    Trophy Points:
    475
    #3
    @sarahk I am not an expert (and you know it!) but I created a new column (sent_email) in my DB that gets updated from 1 to 0 once an email is sent. Obviously, I added AND sent_email = '1' clause to the sending part of the script. Refresh won't send the sent emails again. Then I just run an UPDATE SET query to reset it back to 1 once all emails are sent.

    Also, I used to do batches, but just recently I started sending the whole shabang with a 2-3 second delay (usleep()) between each email. The deliverability is pretty high. Maybe it's not the best thing for everyone, for me it's a HUGE time saver.
     
    qwikad.com, Apr 6, 2018 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    I'm changing the status so that happens but for a significant number they appear to get sent but the write to the database doesn't happen.
    I'm thinking that maybe a pulling all the ids at the beginning into a javascript array and then just get the array to chunk through and send.
    The problem that could then arise is if a second person tried to process (which we allow) so we'd still need to check the status of the email.
    A possible fix would be to change the status to Pending and timestamp it and if it's been pending for X minutes then reset the status to Queued.

    Better would be to be able to do some sort of record locking at the database level so the records can't be selected until unlocked or the lock times out.
    https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html
     
    sarahk, Apr 6, 2018 IP