HyperSQL（HSQLDB）：大量的插入性能 [英] HyperSQL (HSQLDB): massive insert performance

问题描述

我有一个应用程序必须将大约1300万行大约10个平均长度的字符串插入到嵌入式HSQLDB中。我一直在调整东西（批量大小，单线程/多线程，缓存/非缓存表，MVCC事务，log_size / no日志，定期调用 checkpoint ，... 。）并且在16核12 GB的机器上仍然需要7个小时。

I have an application that has to insert about 13 million rows of about 10 average length strings into an embedded HSQLDB. I've been tweaking things (batch size, single threaded/multithreaded, cached/non-cached tables, MVCC transactions, log_size/no logs, regular calls to checkpoint, ...) and it still takes 7 hours on a 16 core, 12 GB machine.

我之所以选择HSQLDB，是因为我认为如果我放入所有的话，我可能会有很大的性能提升那些核心很好用，但我真的开始怀疑我的决定。

I chose HSQLDB because I figured I might have a substantial performance gain if I put all of those cores to good use but I'm seriously starting to doubt my decision.

有谁能告诉我银弹？

推荐答案

使用CACHED表，磁盘IO占用大部分时间。不需要多个线程，因为您要插入同一个表中。显着提高性能的一件事是重用单个参数化的PreparedStatment，为每个行插入设置参数。

With CACHED tables, disk IO is taking most of the time. There is no need for multiple threads because you are inserting into the same table. One thing that noticably improves performance is the reuse of a single parameterized PreparedStatment, setting the parameters for each row insert.

在您的计算机上，通过对内存映射IO使用大的NIO限制，可以显着提高IO。例如 SET FILES NIO SIZE 8192 。较大的尺寸需要64位JVM才能生效。

On your machine, you can improve IO significantly by using a large NIO limit for memory-mapped IO. For example SET FILES NIO SIZE 8192. A 64 bit JVM is required for larger sizes to have an effect.

http://hsqldb.org/doc/2.0/guide/management-chapt.html

减少批量插入持续时间的IO使用 SET FILES LOG FALSE ，并且在插入结束之前不执行检查点。详情请参见此处：

To reduce IO for the duration of the bulk insert use SET FILES LOG FALSE and do not perform a checkpoint until the end of the insert. The details are discussed here:

http://hsqldb.org/doc/2.0/guide/deployment-chapt.html#dec_bulk_operations

更新：插入测试下面1600万行产生了1.9 GigaByte .data文件，并且在平均2核心处理器和7200 RPM磁盘上只花了几分钟。关键是大量的NIO分配。

UPDATE: An insert test with 16 million rows below resulted in a 1.9 GigaByte .data file and took just a few minutes on an average 2 core processor and 7200 RPM disk. The key is large NIO allocation.

connection time -- 47
complete setup time -- 78 ms
insert time for 16384000 rows -- 384610 ms -- 42598 tps
shutdown time  -- 38109 

这篇关于HyperSQL（HSQLDB）：大量的插入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！