MySQL服务器配置，短时间，极端使用-场景

Question

我想为下面的场景配置一个MySQL服务器。

大量的用户将需要注册一个大学课程，选择个别的“时隙”。注册页面将在大约200个用户试图访问该页面的特定时间打开。事实上，在此之前几分钟，他们可能会疯狂地刷新页面。

将有大约150个可供用户选择的时隙(行)。web应用程序将处理试图LOCK IN SHARE MODE一行("timeslot")的每个请求的事务，以检查它是否仍然可用，如果可用，则将其设置为“已预订”状态。我的数据库正在使用InnoDB引擎。

这个场景是很多用户做了大量的SEELCTs，随后在几秒钟内完成了大量的UPDATEs。考虑到一个基本的Ubuntu服务器，您建议使用哪些配置选项来使这个“事件”尽可能顺利地发生。例如，我希望避免的是服务器在此期间对硬盘进行过度持久的写入更改。

-编辑

仅供参考:下面被接受的答案显示了如何在不进行任何锁定的情况下实现这一点，并很好地解释了在尝试锁定方法时MySQL将如何工作。

但是，通过将MySQL的提交行为更改为每秒钟只写一次磁盘，并允许2GB的内存使用，我们成功地完成了这个场景。我们还有8个瘦实例运行一个rails应用程序，运行在一个4 worker nginx下面(基本上是使用SSL的默认设置)。我们在一个12 i5内存的桌面i5系统上运行它(这太大了)，服务器几乎没有达到1( 4)的负载。因此，如果您正在运行类似的场景:不要惊慌。

Answer 1

希望我遗漏或误解了一些东西，因为我不认为一个明确的SELECT ... LOCK IN SHARE MODE是一种可行的方法来解决这个问题。

使用SELECT ... LOCK IN SHARE MODE显式地在匹配的行(S)上设置IS锁(“意图共享”)，但其他线程也可以同时设置同一行的锁(S)，而服务器没有反馈是否其他人已经在该行上设置了这样的锁。

因此，假设两个线程在行上设置了一个IS锁，然后它们都发出一个更新。

一条不幸的线索看到了这一点：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这是一个死锁，因为两个线程都在请求相互排斥的东西。线程#1请求将其is锁升级为X(独占)锁，以便它可以更新行.但是这是不允许的，因为线程#2已经有一个IS锁，这会阻止#1获得它的X锁.线程#2也试图升级到一个X锁，这个锁被线程#1's是锁所阻止。

您可能会假设请求锁的第一个线程将得到更新行的线程，但情况也不是这样。我的测试表明，在这种情况下，获胜的线程是首先注意到更新的线程，而不是先获得is锁的线程。这是明智的，因为该线程所做的工作比另一个线程稍微多一些，而且，毕竟，这两个线程之前只表示了只锁定行的意图。

但是..。尽管如此，在我看来，有一种更简单的方法来解决这个问题，在您的服务器上要容易得多。

如果您的站点的访问者试图声明一个特定的时隙(由唯一的timeslot_id标识)，那么我不知道为什么这种方法不能工作：

START TRANSACTION;
UPDATE timeslot_guest_map SET guest_id = ? WHERE timeslot_id = ? AND guest_id IS NULL;
SELECT ROW_COUNT();
COMMIT;

如果ROW_COUNT()返回'1‘，那么就得到了时隙。如果ROW_COUNT()返回0，那么很抱歉，其他人首先到达了那里。在此设置中，InnoDB隐式地处理行锁定，而线程#2将被阻塞，直到线程#1提交，此时guest_id在该行上不再为空，因此不会被更新。

另一方面，如果“时隙”实际上是相同的，例如在礼堂有150个“一般入场”座位时，便可以这样做：

START TRANSACTION;
UPDATE timeslot SET user_id = ? WHERE user_id IS NULL LIMIT 1;
SELECT ROW_COUNT();
COMMIT;

在这里，线程#1隐式地获取第一个可用行的X锁，更新它并提交。线程#2必须等待第一行的隐式IS锁，因为线程#1持有X.但是当线程#1提交时，线程#2得到它的IS锁，这样它就可以检查该行，并发现该行不再匹配.因此，下一个可用行将被匹配、锁定和更新(如果有)。

在我看来，这两种场景中涉及的工作负载对于MySQL来说都是微不足道的，前提是您要小心地提交(或回滚)事务。否则，您将以等待由废弃事务持有的锁的积压线程结束。

正如@Valor建议的那样，您更可能在为web服务器提供处理并发连接所需的资源时遇到问题.就像这里和这里的情况一样，举几个最近的例子，MySQL是服务器上内存耗尽的受害者，而不是犯罪者。

Answer 2

首先，我建议您获得一个工具，或者自己制作一个工具，将mysql置于类似的负载下，因为mysql没有神奇的配方，您应该能够测试每个不同的优化，并能够决定它是否对您有好处。

我假设您已经完成了基本的无害数据库调优(缓冲池和日志大小)。

我的第一个建议是将mysql服务器移出应用程序所在的web服务器。您的瓶颈可能是web应用程序的并发性，而不是mysql。

对于可用行的数量，我不认为您会对MySQL施加如此大的压力，如果您想对其进行调优(先测量！)，只有当您的mysql与webapp不同(不存在崩溃的高风险)时，您可以通过只在每秒对磁盘进行无害的to刷新更改一次，从而获得更好的性能，而在这一秒内的所有更改都应该只发生在无害内存中。

与往常一样，您应该确保这些用户的所有工作数据集都在内存中(这也是将mysql移出运行其他内存不足应用程序的服务器的一个非常好的理由。

祝好运!