设置数据库服务器以处理10K插入和频繁的select操作

Question

目前，我有一个系统(服务器)，每次接收来自1000个设备(客户端)的消息，每个设备每分钟发送一次消息。

每条消息将有2000条以上的记录。

因此，系统每分钟将接收1000 X 2000 => 2,000,000条记录

同时，我期待频繁的select请求(大约1000 )

查询为

设置数据库服务器(Mysql/postgres)来处理这种频繁的批量插入和读取操作的最佳方式是什么？

Answer 1

所以我只是批处理了1M个伪行(一个字符和一个从1到1000000的整数)，并将其插入到我笔记本电脑上的postgresql的一个事务中。花了4秒钟。每分钟一百万次插入是很容易的，至少在一开始是这样。然而，还有更多需要担心的问题。例如，更新索引的代价可能很高。我在整数字段上添加了一个唯一索引，插入时间从4秒缩短到9秒。另外插入1M行花费了14秒。随着指数的增长，这个数字将继续攀升。一旦索引不再适合存储在内存中，它就会急剧增加。

通常，处理如此多的数据的最佳方法是将其填充到离散的文本文件中，并在以后没有索引的情况下批量上传。然后添加索引。

奥利在回答中提到的关于MySQL的所有内容也适用于大多数其他数据库。

特定于PostgreSQL的内容：

使用连接池和持久连接，将总连接数保持在较低水平，比如低于100。

使用快速存储介质。这里的问题是，您可能需要大量存储，因此SSD可能无法工作。如果你必须使用旋转驱动器，那么为了获得最佳的性能和良好的可靠性，将它们放在RAID-10中具有电池后备硬件缓存的RAID控制器下。RAID-5或RAID-6在这里将是一场灾难，因为它们的写入性能非常糟糕。

将尽可能多的写入内容一起批处理。与一些数据库不同的是，PostgreSQL在一个事务中一次处理一百万或更多行非常方便。

根据需要使用尽可能少的索引。

Answer 2

您的1000台设备是如何连接的？如果每个服务器都建立了到服务器的TCP/IP连接，则需要确保它们所连接的计算机中有足够的文件描述符。查看/proc/sys/fs/file-max以查看最大值。到一台服务器的一千个客户端连接被认为是一个很大的数字。

每条记录中有多少数据？您的网络硬件会不堪重负吗？如果每条记录是10个字节，那么您讨论的是每秒2,000,000字节，或1.6亿比特。100兆位每秒的以太网接口是远远不够的。即使是千兆位的接口也是有问题的:很难保持巨大的吞吐量。请记住，如果DBMS与接收数据的服务器位于不同的计算机上，则这些记录必须同时传入和传出，从而使网络吞吐量加倍。

您将如何处理DBMS或系统的其他部分将在其工作负载中落后的可能性？DBMS在接受INSERT命令时偶尔出现30秒的延迟是很有可能的，但在此期间将积累大量未处理的数据。

您应该考虑将此问题划分为多个组，可能为50或100个设备，以及20个或10个收集数据的中央服务器设置。如果这样做，您将不会有单点故障，您将不会非常努力地推动您的网络硬件，并且如果您丢失了一些硬件，您可能能够制定出某种故障转移策略。您还可以使用更便宜、更具成本效益的服务器和网络设备。

在MySQL上，使用尽可能少的索引来支持需要执行的查询。请记住，执行摘要查询(如SELECT COUNT(*) FROM raw WHERE timestamp > NOW() - INTERVAL 1 HOUR)会极大地降低正在运行的插入操作的速度。

您可能希望考虑使用诸如ActiveMQ之类的排队系统来处理数据流。

Answer 3

您通常可以针对写入或读取进行优化，但不能同时针对两者进行优化。因此，在这种情况下，仔细修剪表的大小，只声明所需的索引，并将其放在一台速度较快的机器上，可能是您唯一的选择。您是否需要长期保存数据，或者是否可以清除较旧的数据？否则你的表会变得很大。