sqlite具有非常大的数据库文件的性能特征是什么？ [英] What are the performance characteristics of sqlite with very large database files?

问题描述

我知道sqlite对于极大的数据库文件不能很好地执行，即使它们被支持（以前在sqlite网站上有一个注释，说明如果你需要大于1GB的文件大小，你可能需要考虑使用企业rdbms。找不到它，可能与旧版本的sqlite相关）。

但是，为了我的目的，我想了解如何

我在说的是从几GB到几个GB的sqlite数据文件，从2GB开始。
任何人都有这方面的经验吗？任何提示/想法？

解决方案

所以我做了一些测试与sqlite非常大的文件，并得出一些结论对我的具体应用程序）。

测试涉及单个sqlite文件与单个表或多个表。每个表有大约8列，几乎所有的整数和4个索引。

这个想法是插入足够的数据，直到sqlite文件大约50GB。

单表

我试图用一个表插入多行到sqlite文件。当文件是大约7GB（对不起，我不能具体的行计数）插入花费太长时间。我估计我的插入我的所有数据的测试将需要24小时左右，但即使在48小时后也没有完成。

这使我得出结论，单个非常大的sqlite表将有插入问题，以及其他操作。

我想这并不奇怪，表越大，插入和更新所有索引的时间越长。

strong>

然后我尝试按时间对数据表进行分割，每天一个表。原始1表的数据被分割为〜700表。

此设置对插入没有任何问题，因为每天创建一个新表格，所以不会花费更长的时间。

真空问题

如i_like_caffeine指出，VACUUM命令是一个问题，sqlite文件越大。随着更多的插入/删除完成，磁盘上的文件碎片将变得更糟，所以目标是定期VACUUM优化文件和恢复文件空间。

但是，如文档所指出，数据库的完整副本是做一个真空，需要很长时间才能完成。

$ b p>对于我的具体应用，我可能会拆分出几个数据库文件，每天一个，以获得最好的真空性能和插入/删除速度的数据。

这使查询复杂化，但对我来说，这是一个值得折衷的是能够索引这么多的数据。另一个优点是，我可以删除整个db文件来删除一天的数据（我的应用程序的常见操作）。

我可能需要监视每个文件的表大小以及看到速度将成为一个问题。

这太糟糕了，似乎没有一个增量真空方法，除了< a href =http://www.sqlite.org/pragma.html#pragma_auto_vacuum =noreferrer>自动真空。我不能使用它，因为我的真空目标是对文件进行碎片整理（文件空间不是一个大问题），这种自动真空不做。事实上，文档声明它可能使碎片更糟，所以我不得不采取定期做一个完全真空的文件。

I know that sqlite doesn't perform well with extremely large database files even when they are supported (there used to be a comment on the sqlite website stating that if you need file sizes above 1GB you may want to consider using an enterprise rdbms. Can't find it anymore, might be related to an older version of sqlite).

However, for my purposes I'd like to get an idea of how bad it really is before I consider other solutions.

I'm talking about sqlite data files in the multi-gigabyte range, from 2GB onwards. Anyone have any experience with this? Any tips/ideas?

解决方案

So I did some tests with sqlite for very large files, and came to some conclusions (at least for my specific application).

The tests involve a single sqlite file with either a single table, or multiple tables. Each table had about 8 columns, almost all integers, and 4 indices.

The idea was to insert enough data until sqlite files were about 50GB.

Single Table

I tried to insert multiple rows into a sqlite file with just one table. When the file was about 7GB (sorry I can't be specific about row counts) insertions were taking far too long. I had estimated that my test to insert all my data would take 24 hours or so, but it did not complete even after 48 hours.

This leads me to conclude that a single, very large sqlite table will have issues with insertions, and probably other operations as well.

I guess this is no surprise, as the table gets larger, inserting and updating all the indices take longer.

Multiple Tables

I then tried splitting the data by time over several tables, one table per day. The data for the original 1 table was split to ~700 tables.

This setup had no problems with the insertion, it did not take longer as time progressed, since a new table was created for every day.

Vacuum Issues

As pointed out by i_like_caffeine, the VACUUM command is a problem the larger the sqlite file is. As more inserts/deletes are done, the fragmentation of the file on disk will get worse, so the goal is to periodically VACUUM to optimize the file and recover file space.

However, as pointed out by documentation, a full copy of the database is made to do a vacuum, taking a very long time to complete. So, the smaller the database, the faster this operation will finish.

Conclusions

For my specific application, I'll probably be splitting out data over several db files, one per day, to get the best of both vacuum performance and insertion/delete speed.

This complicates queries, but for me, it's a worthwhile tradeoff to be able to index this much data. An additional advantage is that I can just delete a whole db file to drop a day's worth of data (a common operation for my application).

I'd probably have to monitor table size per file as well to see when the speed will become a problem.

It's too bad that there doesn't seem to be an incremental vacuum method other than auto vacuum. I can't use it because my goal for vacuum is to defragment the file (file space isn't a big deal), which auto vacuum does not do. In fact, documentation states it may make fragmentation worse, so I have to resort to periodically doing a full vacuum on the file.

这篇关于sqlite具有非常大的数据库文件的性能特征是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！