0

Basically, I have a table called event:

id | title | countdown | description | active | expired | created_at | updated_at

I was starting to concept how to create "LIVE" events using MySQL or PHP. So I was thinking I could use either a Cron referencing PHP or an event using straight SQL.

So my ideas were the following:

Restructure my tables to:

events: id | title | description | created_at | updated_at

event_queue: id | event_id

active_events: event_id | countdown | iteration

expired_events: event_id | expired_at

So having that, how can I create a live timer to run every second to update, at most, 100 rows in active_events every second?

Example data to go inside active_events is as follows:

event_id: 1, countdown: 20 (for 20 minutes), iteration: 90

I want to decrement countdown every second and when it hits 0, to decrement the iteration and start the countdown back to 20. Once the iteration is at 0 and countdown is at 0, just remove the entry completely, then archive the event to expired_events. Again, only a max of 100 rows will be inside active_events.

What's the best way to go about doing this?

Basically this is an internal application that people can vote on in the office and if no votes are done, then it goes away. The above structures are how I want it and see it working.

Any thoughts? (I have to go to lunch and will update this or leave comments on any posts later)

EDIT: Back from lunch!... I forgot to note that the countdown needs to reset every time an action is placed on it (a user votes on it). For the sake of my brain and thinking, let's say every time someone accepts one of the events, it is stored into accepted_events, and the active_events countdown has to be reset to 20.

Here is my server's top running the MySQL event and not:

RUNNING IN VM INSTANCE ON MACBOOK PRO

This is without the event

top - 11:56:59 up 1:29, 1 user, load average: 0.11, 0.06, 0.01 Tasks: 92 total, 2 running, 88 sleeping, 2 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 510552k total, 499364k used, 11188k free, 74316k buffers Swap: 1048568k total, 0k used, 1048568k free, 251872k cache

This is with the event scheduled and running the following SQL on 200 rows:

update test set timer = timer-1

top - 12:00:08 up 1:32, 1 user, load average: 0.06, 0.04, 0.00 Tasks: 92 total, 2 running, 88 sleeping, 2 stopped, 0 zombie Cpu(s): 0.3%us, 0.3%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.3%hi, 0.0%si, 0.0%st Mem: 510552k total, 501720k used, 8832k free, 74996k buffers Swap: 1048568k total, 0k used, 1048568k free, 253244k cached

EDIT # 2: Here's a sketch of what I mean -- http://oi51.tinypic.com/29c1bp4.jpg

Improve this question
1
  • Please can you format your code to be a bit more readable as to what is code, what is data, etc? Thank you. Commented Mar 11, 2020 at 21:34

2 Answers 2

Reset to default
1

Don't store the actual time for the timer store the time it is due to finish and always compare it to the current time. update the time to finish when required.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The tricky thing is that when someone "votes" or does an action on the event, the timer resets (similar to how quibids resets). Can I still do this?
yes. think about each reset just changes the timestamp for when the timer finishes.. when displaying the time in code just compare the current time to the end time and display the difference that way. so you are only updating the end time on change not changing with real time which is very impractical in a db.
I still don't see how you can do all that logic and create a "live-like" timer without having to update the start times of all the events that are supposed to go after the current "live" event.
The only way to achieve what i want with your solution is to have an expiration_time and start_time.. when a user sends a post to the event, the event's timer is increased by X seconds and ALL of the queued events start times HAVE to be increased by X seconds as well. I am envisioning the QUEUE table to have about 30,000 records in the future.. so ON POST of the event, that would be less efficient than a timer every second (esp. with a lot of traffic). So I was thinking you should only update the next event only, but doing it that way removes the ability to have "ongoing 24/7 live events"
0

I ended up creating a script that runs every second and updates a small table. I have about 2.5 million unique daily views on my server and I barely saw a performance hit doing so.

I just have a table of 200 records that I decrement the values every second.

timer | location_id | queue_id

If the record hits 0, grab the next queue_id and insert it into there then reset the timer... I'll think of another way to do this later, for now, i'm not seeing a bad performance hit at all... If I need to, I can just have my own server / database for this.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.