MySQL 具有最佳插入/秒性能的数据库？

DB with best inserts/sec performance?

提问by Nenad

We deploy an (AJAX - based) Instant messenger which is serviced by a Comet server. We have a requirement to store the sent messages in a DB for long-term archival purposes in order to meet legal retention requirements.

我们部署了一个（基于 AJAX 的）即时通讯工具，它由 Comet 服务器提供服务。我们需要将发送的消息存储在数据库中以用于长期存档目的，以满足法律保留要求。

Which DB engine provides the best performance in this write-once, read never (with rare exceptions) requirement?

哪个数据库引擎在这种一次写入、从不读取（极少数例外）的要求中提供最佳性能？

We need at least 5000 Insert/Sec. I am assuming neither MySQL nor PostgreSQL can meet these requirements.

我们至少需要 5000 次插入/秒。我假设 MySQL 和 PostgreSQL 都不能满足这些要求。

Any proposals for a higher performance solution? HamsterDB, SQLite, MongoDB ...?

关于更高性能解决方案的任何建议？HamsterDB、SQLite、MongoDB ......？

回答by Nenad

Please ignore the above Benchmark we had a bug inside.

请忽略上面的基准我们有一个错误。

We have Insert 1M records with following columns: id (int), status (int), message (140 char, random). All tests was done with C++ Driver on a Desktop PC i5 with 500 GB Sata Disk.

我们有插入 1M 条记录，其中包含以下列：id (int)、status (int)、message (140 char, random)。所有测试均在具有 500 GB Sata 磁盘的台式机 i5 上使用 C++ 驱动程序完成。

Benchmark with MongoDB:

使用MongoDB 进行基准测试：

1M Records Insert without Index

1M 条记录插入，无索引

time: 23s, insert/s: 43478

1M Records Insert with Indexon Id

100 万条记录插入，在 Id 上有索引

time: 50s, insert/s: 20000

next we add 1M records to the same table with Index and 1M records

接下来我们将 1M 条记录添加到同一个表中，其中有 Index 和 1M 条记录

time: 78s, insert/s: 12820

that all result in near of 4gb files on fs.

所有这些都导致 fs 上有接近 4gb 的文件。

Benchmark with MySQL:

使用MySQL 进行基准测试：

1M Records Insert without Index

1M 条记录插入，无索引

time: 49s, insert/s: 20408

1M Records Insert with Index

1M 条记录插入，带索引

time: 56s, insert/s: 17857

next we add 1M records to the same table with Index and 1M records

接下来我们将 1M 条记录添加到同一个表中，其中有 Index 和 1M 条记录

time: 56s, insert/s: 17857

exactly same performance, no loss on mysql on growth

完全相同的性能，mysql 在增长上没有损失

We see Mongo has eat around 384 MB Ram during this test and load 3 cores of the cpu, MySQL was happy with 14 MB and load only 1 core.

我们看到 Mongo 在这次测试中消耗了大约 384 MB 的内存并加载了 3 个 CPU 内核，MySQL 对 14 MB 感到满意，并且只加载了 1 个内核。

Edorian was on the right way with his proposal, I will do some more Benchmark and I'm sure we can reach on a 2x Quad Core Server 50K Inserts/sec.

Edorian 的建议是正确的，我会做更多的基准测试，我相信我们可以达到 2x 四核服务器 50K 插入/秒。

I think MySQL will be the right way to go.

我认为 MySQL 将是正确的方法。

回答by Alexander Kj?ll

If you are never going to query the data, then i wouldn't store it to a database at all, you will never beat the performance of just writing them to a flat file.

如果您永远不会查询数据，那么我根本不会将其存储到数据库中，您将永远无法超越将它们写入平面文件的性能。

What you might want to consider is the scaling issues, what happens when it's to slow to write the data to a flat file, will you invest in faster disk's, or something else.

您可能要考虑的是扩展问题，当将数据写入平面文件时会发生什么，您是否会投资于更快的磁盘或其他东西。

Another thing to consider is how to scale the service so that you can add more servers without having to coordinate the logs of each server and consolidate them manually.

要考虑的另一件事是如何扩展服务，以便您可以添加更多服务器，而无需协调每个服务器的日志并手动整合它们。

edit: You wrote that you want to have it in a database, and then i would also consider security issues with havening the data on line, what happens when your service gets compromised, do you want your attackers to be able to alter the history of what have been said?

编辑：您写道，您希望将其保存在数据库中，然后我还会考虑在线保存数据的安全问题，当您的服务遭到破坏时会发生什么，您是否希望您的攻击者能够改变历史说了些什么？

It might be smarter to store it temporary to a file, and then dump it to an off-site place that's not accessible if your Internet fronts gets hacked.

将它临时存储到一个文件中，然后将其转储到一个场外的地方，如果您的 Internet 前端遭到黑客攻击，该地方将无法访问，这可能更明智。

回答by jmz

If you don't need to do queries, then database is not what you need. Use a log file.

如果您不需要进行查询，那么数据库不是您所需要的。使用日志文件。

回答by Frank Heikens

it's only stored for legal reasons.

它仅出于法律原因存储。

And what about the detailed requirements? You mention the NoSQL solutions, but these can't promise the data is realy stored on disk. In PostgreSQL everything is transaction safe, so you're 100% sure the data is on disk and is available. (just don't turn of fsync)

那么详细的要求呢？您提到了 NoSQL 解决方案，但这些并不能保证数据确实存储在磁盘上。在 PostgreSQL 中，一切都是事务安全的，因此您 100% 确定数据在磁盘上并且可用。（只是不要关闭fsync）

Speed has a lot to do with your hardware, your configuration and your application. PostgreSQL can insert thousands of record per second on good hardware and using a correct configuration, it can be painfully slow using the same hardware but using a plain stupid configuration and/or the wrong approach in your application. A single INSERT is slow, many INSERT's in a single transaction are much faster, prepared statements even faster and COPY does magic when you need speed. It's up to you.

速度与您的硬件、配置和应用程序有很大关系。PostgreSQL 可以在良好的硬件上每秒插入数千条记录并使用正确的配置，使用相同的硬件但在您的应用程序中使用简单的愚蠢配置和/或错误的方法可能会非常缓慢。单个 INSERT 很慢，单个事务中的许多 INSERT 速度要快得多，准备好的语句甚至更快，当您需要速度时，COPY 会发挥神奇作用。由你决定。

回答by Brent Baisley

I don't know why you would rule out MySQL. It could handle high inserts per second. If you really want high inserts, use the BLACK HOLE table type with replication. It's essentially writing to a log file that eventually gets replicated to a regular database table. You could even query the slave without affecting insert speeds.

我不知道你为什么要排除 MySQL。它可以每秒处理高插入量。如果您真的想要高插入，请使用带复制的 BLACK HOLE 表类型。它本质上是写入日志文件，最终将其复制到常规数据库表中。您甚至可以在不影响插入速度的情况下查询从站。

回答by Andrej Kireje?

Firebird can easily handle 5000 Insert/sec if table doesn't have indices.

如果表没有索引，Firebird 可以轻松处理 5000 次插入/秒。

回答by edorian

Depending in your system setup MySql can easily handle over 50.000 inserts per sec.

根据您的系统设置，MySql 每秒可以轻松处理超过 50.000 次插入。

For tests on a current system i am working on we got to over 200k inserts per sec. with 100 concurrent connections on 10 tables (just some values).

对于我正在处理的当前系统的测试，我们每秒插入超过 20 万次。在 10 个表上有 100 个并发连接（只是一些值）。

Not saying that this is the best choice since other systems like couch could make replication/backups/scaling easier but dismissing mysql solely on the fact that it can't handle so minor amounts of data it a little to harsh.

并不是说这是最好的选择，因为像 couch 这样的其他系统可以使复制/备份/扩展更容易，但是仅仅因为 mysql 无法处理如此少量的数据而忽略它，这有点苛刻。

I guess there are better solutions (read: cheaper, easier to administer) solutions out there.

我想有更好的解决方案（阅读：更便宜，更易于管理）解决方案。

回答by tmdoit

Use Event Store (https://eventstore.org), you can read (https://eventstore.org/docs/getting-started/which-api-sdk/index.html) that when using TCP client you can achieve 15000-20000 writes per second. If you will ever need to do anything with data, you can use projections or do the transformations based on streams to populate any other datastore you wish. You can create even cluster.

使用 Event Store ( https://eventstore.org)，您可以阅读 ( https://eventstore.org/docs/getting-started/which-api-sdk/index.html) 使用 TCP 客户端时可以达到 15000每秒 -20000 次写入。如果您需要对数据做任何事情，您可以使用投影或基于流进行转换来填充您希望的任何其他数据存储。您甚至可以创建集群。

回答by Antony

I believe the answer will as well depend on hard disk type (SSD or not) and also the size of the data you insert. I was inserting a single field data into MongoDB on a dual core Ubuntu machine and was hitting over 100 records per second. I introduced some quite large data to a field and it dropped down to about 9ps and the CPU running at about 175%! The box doesn't have SSD and so I wonder if I'd have gotten better with that.

我相信答案也取决于硬盘类型（SSD 与否）以及您插入的数据大小。我在双核 Ubuntu 机器上将单个字段数据插入到 MongoDB 中，每秒处理超过 100 条记录。我向一个字段引入了一些相当大的数据，它下降到大约 9ps，CPU 运行速度大约为 175%！盒子没有SSD，所以我想知道我是否会变得更好。

I also ran MySQL and it was taking 50 seconds just to insert 50 records on a table with 20m records (with about 4 decent indexes too) so as well with MySQL it will depend on how many indexes you have in place.

我还运行了 MySQL，仅在具有 20m 记录（也有大约 4 个不错的索引）的表中插入 50 条记录就需要 50 秒，因此对于 MySQL，这将取决于您拥有多少索引。

回答by BitKFu

If money plays no role, you can use TimesTen. http://www.oracle.com/timesten/index.html

如果金钱不起作用，您可以使用 TimesTen。 http://www.oracle.com/timesten/index.html

A complete in memory database, with amazing speed.

一个完整的内存数据库，速度惊人。