缩放数据库中的临时记录以计算总数以停止超额订阅 [英] Scaling temporary records in database to count totals to stop over-subscribing

问题描述

我正在寻找一个系统，人们可以免费参加一次免费的网络会议，这些网络会议可能会引起很多兴趣，而且席位有限，因此必须有某种方法来确保它不会获得超额订购...因此，我们需要从他们预定座位到完成并确认订单的时间进行倒计时.这样，这是实际单击出席"即可获得会议席位的第一批X人数.如果用户未完成预订，则分配给他们的座位将被放回池中，以供其他人抢夺...

I'm looking at a system where people RSVP to a free web conference, these web conferences can get really busy with a lot of interest and there are limited places, so there has to be some way to ensure it doesn't get over-subscribed... So, we need to have a countdown from the time they book their seat to the time that their order is completed and confirmed. This way it's the first X amount of people to actually click 'Attending' that get the seats for the conference. If the user does not complete their booking their allocated seat is put back in the pool for someone else to grab...

发生的事情的概述(我如何看待这种最好的方法)

An overview of what happens (how I see this best working)

1)用户在事件上单击"RSVP"，这向/rsvp/{event_id}/

1) User clicks "RSVP" on event, this makes an AJAX request to /rsvp/{event_id}/

这会存储唯一的ID(在本问题的其余部分中称为令牌)以及时间戳.它将令牌也存储在会话中.

This goes and stores a unique id (refered to as token in the rest of this question) along with a timestamp. It stores that token in a session too.

然后通知用户他们有X倍的时间(比如说5分钟)来完成其余的详细信息(姓名/电子邮件/d.o.b等)

The user is then notified they have X amount of time (let's say 5 mins) to complete the rest of their details (name/email/d.o.b, etc)

但是:在将任何内容放入数据库之前，它会检查挂起的订单数量是否少于可用的总座位数；如果不是(没有剩余会议席位)，则返回对不起，没有座位"可用，请继续检查，因为预订尚未完成，并且有更多席位可供使用."

However: before anything is put in the database, it checks if the amount of pending orders is less than the total seats available, if not (there are no seats left for the conference) then it returns saying "sorry, no seats available any more, keep checking as bookings aren't completed and more seats become available"

2)如果用户及时填写，它将其详细信息存储为参加"....

2) If the user fills this out in time, it stores their details in database as "attending"....

但是，如果他们未能及时填写表格，则有一项cron作业每秒运行一次，并仔细检查并删除时间戳超过5分钟的令牌，这样他们就会失去参加的机会，这是'然后将会议上的座位"放回泳池. (系统会通知他们未成功，并返回到第一步)

If however they fail to fill the form out in time, there is a cron job running every second and go through and delete any tokens that have a timestamp of over 5 minutes ago so they will loose their chance to attend, this 'seat' on the conference is then put back in to the pool. (They'll be notified they were unsuccessful and taken back to the first step)

只需将记录放入数据库中，进行COUNT(*) FROM pending_bookings WHERE conference_id = {x}并确定要确认的席位或待处理的订单数量，然后从会议可用的总席位中减去，就可以轻松编写代码.

All easy enough to write with simply putting records in a database, doing a COUNT(*) FROM pending_bookings WHERE conference_id = {x} and working out how many seats are either confirmed or orders pending, then subtracting from the total seats available for the conference.

但是我不认为使用MySQL具有很强的可扩展性-这些人(而且我肯定还会再有)超过20万的人试图抓住大约200个席位，为此做COUNT(*)这些人中的每个人都将变得非常昂贵，我们无法进行任何体面的缓存，因为它需要实时检查此过程中有多少人.

But I don't feel that using MySQL would be very scalable on this - these have (and I'm sure will have again) in excess of 200k people trying to grab around 200 seats, doing a COUNT(*) for each one of those people will get pretty expensive, and we can't do any decent caching as it needs to be checking in real time how many people are in the process.

我已经考虑为此使用 Amazon SimpleDB 可伸缩性，但是我以前使用过它，并且看到COUNT()不一定准确(我想它的本质是可伸缩性)-出于显而易见的原因，COUNT需要100％准确，我需要能够添加记录带有时间戳，并且能够从中删除超过5分钟的记录.

I've looked into using Amazon SimpleDB for this, just for it's deploy-and-go scalability, but I've used it before and seen that COUNT() isn't necessarily accurate (nature of it's scalability I guess) - for obvious reasons that COUNT needs to be 100% accurate, I need to be able to add records along with a timestamp to it and be able to delete records from it older than five minutes.

推荐答案

为每个人做一个COUNT(*)会变得非常昂贵

doing a COUNT(*) for each one of those people will get pretty expensive

您是否发现了这一点，或者是一种直觉?我宁愿对其进行基准测试(以及您的Web服务器是否可以承受该负载)，而不要事先进行假设.编制索引(并正确选择存储引擎)在这里可能会很有帮助.

Have you found that out, or is it a gut feeling? I'd rather benchmark that (and also whether your web servers can carry that load) than assume it on beforehand. Indexing (and properly selecting a storage engine) might help a lot here.

此外，让人们在注册活动之前进行注册并输入他们的帐户数据是否更加方便用户使用?因为现在您似乎喜欢可以快速输入实物或使用过体面的浏览器的人.

Furthermore, wouldn't it be a lot more user-friendly to let people register and enter their account data before registering for an event? Because now you seem to favor people who can type real quick, or who use a decent browser.

这篇关于缩放数据库中的临时记录以计算总数以停止超额订阅的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！