Berkeley DB SQL与SQLite相比有多快? [英] How fast is Berkeley DB SQL compared to SQLite?

问题描述

Oracle最近发布了到SQLite的Berkeley DB后端.我碰巧有一个数百兆的SQLite数据库，可以从改进的性能，并发性，可伸缩性和可靠性"中受益，但Oracle的站点似乎缺乏任何改进措施.这里有没有人做过一些基准测试?

Oracle recently released a Berkeley DB back-end to SQLite. I happen to have a hundreds-of-megabytes SQLite database that could very well benefit from "improved performance, concurrency, scalability, and reliability", but Oracle's site appears to lack any measurements of the improvements. Has anyone here done some benchmarking?

推荐答案

我参加了BDB SQLite代码的beta评估，其中之一是 我试图解决的问题是性能差异.在此刻， 在我至少有另外一个人之前，我无法确切发布发现的内容 评估我的代码，运行测试，并确认我得到的数字(这是 完毕).但是，我可以在这里概括地说，在某些情况下，BDB 与SQLite相比，提供了显着的性能改进，特别是在 处理涉及写并发的重负载的区域.

I participated in the beta evaluation of the BDB SQLite code and one of the things I tried to get a handle on was the performance difference. At this point, I cannot publish exactly what I found until I have at least one other person evaluate my code, run the tests, and confirm the numbers I got (which is being done). However, I can generalize here and say that there are cases where BDB offers significant performance improvements over SQLite, specifically in the area of handling heavy loads that involve write-concurrency.

通常，有两种快速"使用权的措施-(1)效率:持续多长时间 一个流程执行XYZ所需的时间与(2)并发:多少次 许多进程每单位时间可以执行XYZ. BDB解决的主要问题是 并发-大规模事务处理.因此，您会想到很多 并发连接写入和/或修改数据库的内容.

There are, generally, two measures of "fast" right -- (1) efficiency: how long does it take for a single process to do XYZ vs. (2) concurrency: how many times can many processes do XYZ per unit time. The main problem BDB addresses is concurrency -- large-scale transaction processing. Thus you think of many concurrent connections writing to and/or modifying the contents of the database.

SQLite在设计上使用数据库级锁定，因此最多只能有一个 一次可以在数据库中工作的作家.因此，SQLite的交易 速率随并发连接数保持恒定，因此 它在写密集型应用程序中的可扩展性实际上是由其 效率(1).

SQLite by design uses database-level locking so there is a maximum of one writer who can be working in the database at a time. Thus, SQLite's transaction rate stays more or less constant with the number of concurrent connections, so it's scalability in write-intensive applications is really measured by its efficiency (1).

BDB使用页面级别锁定，这允许多个编写者执行以下操作: 在给定的时间在数据库中工作(前提是他们正在 单独的页面).因此，BDB的利率可能会随着 连接，因此它的可扩展性既是效率问题(1)，又是 并发(2)，它们可以加起来.

BDB on the other hand uses page level locking, which allows multiple writers to be working in the database at a given time (provided that they are working on separate pages). Thus BDB's rate potentially increases with the number of connections and so its scalability is both a matter of efficiency (1) and concurrency (2), which can add up.

主要归结为(写入)并发. BDB可以提供比TPS更多的TPS 适用于多位作者的SQLite.交易，是指可以修改 数据库(它们对只读操作有何实际帮助?).那就是 对于读取并发(主要执行SELECT的应用程序)，SQLite可以很好地进行 与BDB面对面，因为锁定不再是一个关键问题.

Mainly what it boils down to is (write) concurrency. BDB can push more TPS than SQLite for multiple writers. By transaction, I mean something that modifies the database (how are they of any real help for read-only operations?). That said, for read concurrency (apps that mainly do SELECTs), SQLite could very well go head to head with BDB because locking is no longer a critical issue.

对于数据集的大小，我不确定.我没有调查 那.最终，它们都使用B树进行存储.可能有因素 他们各自的实现考虑，但我还没有对此进行调查.一世 知道SQLite可以优雅地将数据集处理为数百MB，并且 两位数的GB(脏页映射实现现在可能更多) 已更改).

As for the size of the dataset, I am not sure. I've not looked into that. Ultimately, they both use B-trees for storage. There may be factors in their respective implementations to consider, but I've not investigated that. I know that SQLite can gracefully handle data sets into the hundreds of MBs and double digit GBs (and perhaps more now that the dirty page map implementation has been changed).

因此，如果您的应用程序使用了许多可以修改的连接 给定的数据库和页面争用相对较低，那么BDB可以提供 显着的性能改进.但是页面争用是至关重要的 多变的.在极限情况下，如果您有一个BDB数据库，其数据由一个 单页，那么在所有情况下其性能都将与SQLite的性能相匹配 因为此处的页面级锁定有效地退化为 数据库级锁定-每个人都在为一件事而战.但是，由于 BDB中的页面数增加了(页面争用减少了)，然后 随着并发连接数的增加，最大TPS将开始增长.然后 从那时起，内存成为下一个限制因素.但这是另一回事 故事.

Therefore, if you have an application which employs many connections that modify a given database and page contention is relatively low, then BDB can offer significant performance improvements. But page contention is a critical variable. In the limit, if you had a BDB database whose data consisted of a single page, then its performance would match that of SQLite in all cases because page-level locking here effectively degenerates into the equivalent of database level locking -- everybody is fighting over one thing. However, as the number of pages increases in BDB (and page contention decreases), then the maximum TPS will start to grow with the number of concurrent connections. Then from that point, memory becomes the next limiting factor. But that's another story.

BTW，我正在写一篇有关将BDB用于即将到来的项目的文章 来自SQLite.

BTW, I am in the process of writing an article about using BDB for those coming from SQLite.

文章链接:

Oracle Berkeley DB SQL API与SQLite API –技术评估

Oracle Berkeley DB SQL API与SQLite API –集成，收益和差异

这篇关于Berkeley DB SQL与SQLite相比有多快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！