Improving Read/Write performance for 30 million records per hour
vips
We have been evaluating various databases including H2 to use as an
internal indexing database.
The requirement being -
1. CSV Files will continuously be generated containing approx 39,000
records each. Each file would have a size of approx. 5 MB.
2. These will be continuously be loaded into a database table and be
indexed.
3. Every hour 30 million records will be generated and will need to be
uploaded into the database.
That is approximately 8333 records/sec will be generated.
The approach followed for H2 is -
1. Create one table in one database per hour.
2. Every hour close the existing database and create a new database
and hence obviously a new table.
Keep dumping records from the CSV files into the same database table
for one hour.
This was done to limit the size of each database and hence limit
issues because of huge db sizes
3. The command used to insert into H2 table has been -
insert into table (select from csvread(<filename>));
4. Occasioanlly, searches will happen on one days data at a time.
5. In that case, 24 databases will be opened (1 for each hour of that
day) and searched. The output of the search will again be written into
a csv file.
6. We use csvwrite to select from database table and write into csv
file.
Not sure if this is the best approach. Need help and advise on the
approach.
We did a few runs with the above approach and with partial load.
These runs were on Windows as well as on Sun Solaris, 8 core (64
kernel threads) machines with 32GB RAM, with disk arrays and with ZFS
file system.
While the files were being generated at approx. 8000 records/second,
the files were uploaded into the H2 database at approx. 2775 records/
second. There has been a huge backlog of 5225 records/second.
Also, read performance has been pretty slow - Read with index too
approx 2 seconds to return.
Out of which 200 milliseconds were spent in the actual search and the
rest in opening the database.
We tried a few optimizations but results were not too different.
Amongst the various options tried -
1.
FILE_LOCK=NO;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;LOG=1;LOCK_MODE=3
2. LOG=0 and LOG=2 were also tried
3. The files were made as read only while reading from the old
databases
4. Commits were made at various intervals
The performance page of H2 says approx 29000 records/sec. We are
currently struggling at 2775 records/sec. There must be something
obviously wrong in our approach.
Please suggest options and whether it is really possible to reach 8000
recs/sec write performance.
bob mcgee
inserts & reads a LOT faster than that, especially with a beefy
machine like that.
After consulting the code in org.h2.tools.csv, I believe that the H2
CSVREAD function may be your bottlenecked due to its unoptimized I/O
routines (see below). Can you use a CSV library to parse one of the
files into memory, and then measure insert time with a
PreparedStatement doing inserts in batches of ~1000 records at a time?
I suggest trying SuperCSV (http://supercsv.sourceforge.net/) or
openCSV (http://opencsv.sourceforge.net/) for this test. If inserting
from memory is MUCH faster, then the CSV code needs a fix -- in the
meantime, you can see if calling one of these libraries in a user-
defined function is faster than CSVREAD.
On Jul 17, 11:49 am, vips <vips4l...@gmail.com> wrote:
> The performance page of H2 says approx 29000 records/sec. We are
> currently struggling at 2775 records/sec. There must be something
> obviously wrong in our approach.
include reading/parsing time to get data from a file.
> Also, read performance has been pretty slow - Read with index too
> approx 2 seconds to return.
> Out of which 200 milliseconds were spent in the actual search and the
> rest in opening the database.
and check indexes. This is the most common cause for very slow DB
opening. If query performance still seems slow, try running ANALYZE
to update index statistics.
*** Why I think the CSV reader may be slow: ***
org.h2.tools.Csv.java, Lines 322-336: method readChar()
This method runs input.read() on a BufferedReader for
every single character. That method is synchronized
and includes a lot of safety checks.
This overhead can be reduced by reading characters in bulk to a
char[] and tracking position there. I have used this technique
or NIO to get massive performance boosts.
Cheers,
Bob McGee
Thomas Mueller
> The performance page of H2 says approx 29000 records/sec.
No, it says "statements per second". It's a mix of statements.
> There must be something
> obviously wrong in our approach.
I would try to avoid CSV files, and insert the data directly into the
database. That should double the performance, because it's about half
the I/O.
> insert into table (select from csvread(<filename>));
Try using: CREATE TABLE ... AS SELECT
This should improve performance.
> FILE_LOCK=NO;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;LOCK_MODE=3
This will not help. See http://h2database.com/html/performance.html#fast_import
> with 32GB RAM
Maybe you could use in-memory databases?
> CSVREAD ... unoptimized I/O routines
That's true, CSVREAD is relatively slow. I'm not sure if this is the
main reason for your problem, but I will try to improve the
performance for the next release.
Regards,
Thomas
Ewald
How large is the db Cache ? I checked, and the default seems to be
16mb, enough for most scenarios, but I found that as a database grows,
it's wise to allocate a bit more memory to the cache.
See http://www.h2database.com/html/features.html#cache_settings for
more information on setting the cache size.
I had quite a problem with selects being quite slow on a very large
database in an application I built, and resolved the issue by
allocating 1024mb of RAM to the cache, and the improvements were
immense.
Just curious if this will have an impact in your scenario, given the
size of the index required for such a large database.
Ewald
bob mcgee
On Jul 20, 4:52 am, Ewald <ewaldh...@gmail.com> wrote:
> Out of interest...
> immense.
increased by an order of magnitude, and the second-level soft
reference
cache should be enabled (to use any excess memory for caching).
Setting max_operation_memory to several times the file size, and
max_memory_undo to the same would reduce disk paging on insert
as well. For faster reads of large result sets, setting
max_memory_rows
larger than your result set will make things an order of magnitude
faster.
I'm not entirely sure how to modify the values, but H2 also has
settings for:
h2.objectCacheSize (defult=1024)
h2.objectCacheMaxPerElementSize (default=4096)
Increasing these moderately (say, 2x to 5x) would reduce memory use
when duplicate values exist between rows.
> That's true, CSVREAD is relatively slow. I'm not sure if this is the
> main reason for your problem, but I will try to improve the
> performance for the next release.
my eclipse install, I'd like to take a stab at this one myself. It
is
relatively self-contained (changes only in one file, impacts on few
others),
and less complicated than other parts of the DB. If I/O or algorithm
tweaks
prove worthwhile, they could be propagated to the similar script file
reader.
Why I think I could be helpful here:
I've still got good code from previous experiments with optimized
CSV parsing, and I've done a fair bit with optimizing I/O over the
last couple years. I don't know if you're willing to allow NIO use,
or if that's too big a break with the 1.4 libraries, but I can work
either way.
Ask me some time how I managed to read arrays of primitives from file
faster
than the best of the NIO methods.
Cheers,
Bob McGee
Thomas Mueller
> all the caches should be
> increased by an order of magnitude,
By default the JVM allocates 64 MB heap. If more is available, then H2
could use more, that's true. I will add a feature request:
"CACHE_SIZE: automatically use a fraction of Runtime.maxMemory - maybe
automatically the second level cache."
> and the second-level soft reference
> cache should be enabled (to use any excess memory for caching).
No, because it slows down the performance, unless you have a lot of
memory (see above).
> I'm not entirely sure how to modify the values, but H2 also has
> settings for:
> h2.objectCacheSize (defult=1024)
> h2.objectCacheMaxPerElementSize (default=4096)
> Increasing these moderately (say, 2x to 5x) would reduce memory use
> when duplicate values exist between rows.
I think those are reasonable settings. I don't plan to change those
unless there is prove it does reduce memory usage for common use
cases.
>> CSV
> I'd like to take a stab at this one myself.
Sorry, I already fixed the problem (committed to the trunk). Now CSV
reading is about the same speed as using LineNumberReader (about 0.8s
instead of over 2s for 33 MB). I still use FileReader by default.
Maybe there is a faster way to read from a file, or convert bytes to
characters?
> I don't know if you're willing to allow NIO use
It depends. It is not available on all platforms, so I like to keep
this 'optional'.
> Ask me some time how I managed to read arrays of primitives from file
> faster than the best of the NIO methods.
Well, that's interesting (specially for the page store mechanism).
Regards,
Thomas
Chris Schanck
int max = (int) (Runtime.getRuntime().maxMemory() / 1024); // max mem in K
int cacheSize = (max / H2_CACHE_DIVISOR) * 1024; // use 1/H@_CACHE_DIVISOR of mem for cache
s.executeUpdate("SET CACHE_SIZE " + cacheSize);
s.commit();
s.close();
Chris
C. Schanck
bob mcgee
wrote:
> automatically the second level cache."
advantage of using Java rather than a compiled-language DB. Shanck's
suggestion of 1/5 heap sounds reasonable, but I think it shouldn't go
below the current default unless explicitly specified.
> > and the second-level soft reference
> >cache should be enabled (to use any excess memory for caching).
> No, because it slows down the performance, unless you have a lot of
> memory (see above).
when accessed,
and the additional call to the Reference.get() method?
I've been using this option for a while when the cache is often
needed, but don't want to
sacrifice the ability to run large operations.
> [problems with large object caches]
I'm certainly not suggesting this for standard use, only in cases with
a lot of duplicate values
and plenty of RAM to burn. Is the cache in any way linked with index
selectivity (prevents
caching unique items)?
> Sorry, I already fixed the problem (committed to the trunk). Now CSV
> reading is about the same speed as using LineNumberReader (about 0.8s
> instead of over 2s for 33 MB). I still use FileReader by default.
very happy.
DOUBLY good since I see that it has been applied to the script reader
as well!
Taking a look at the revised code, I can see a couple of other
potential optimizations,
but they would either be harder to execute or less sure of boosting
performance.
> Maybe there is a faster way to read from a file, or convert bytes to
> characters?
> > faster than the best of the NIO methods.
> Well, that's interesting (specially for the page store mechanism).
You might find the following article of interest:
it basically takes *all* of the various ways to read from a file and
benchmarks them:
http://nadeausoftware.com/articles/2008/02/java_tip_how_read_files_quickly
Surprisingly, even though this is old, most of the I/O optimization
rules
here hold true (which is apparent on examining the above):
http://java.sun.com/developer/technicalArticles/Programming/PerfTuning/
I had a really long post written up here explaining the above,
and why I was able to boost performance. I tried to cut to just
the practicals, and it ended up long again. Mea culpa.
Yell at me if I'm being pompous or verbose.
The goodies: ObjectInputStream & DataInputStream are slow as crap for
reading primitives, no matter what they read from. If you're doing
lots
with converting bytes to primitives, using NIO or a custom byte[]
processing
should much faster.
For bulk byte[] --> char[] conversions, I'm not sure.
In theory, NIO should be faster, and makes is faster/easier to work
with
things containing part test & part primitives/bytes. For all-text
files, NIO
is probably more of a pain than it is worth, assuming you use a char[]
to reduce synchronized method calls.
I think the biggest performance boost you may get is running I/O and
processing of blocks in parallel using threads. It's tricky to design
& debug but fantastic in cases where you have to read a lot of data
then
do a complex (CPU-intensive) operation on bits of it. I've got a
fast
file hashing utility that I'm working on using this method.
More explanations of I/O performance: Java has a high cost to set
up an I/O op (due to JNI overhead), but raw I/O performance is just
fine.*
Buffering amortizes cost of I/O,
but then your problem is how fast each operation
on the buffer executes.
There are 3 ways to handle buffers.
1)BufferedInputStreams/Readers are relatively inefficient
for raw sequential access, because every operation is
synchronized, safety checked, and bounds checked.
This gets worse the more you split
the stream into individual items, and the more complex processing
the stream does on each item. Nesting streams increases
complexity, and you may have multiple redundant buffers
and safety checks.
2) Grabbing a big chunk of data to array and iterating on the array
is faster but much less safe. You have seen an example here
of how big the difference is, compared to InputStreams.
3) NIO falls somewhere in between -- Buffers are like arrays, and
allow
for pretty efficient bulk operations on elements (converting to
primitives), but have an additional level of safety checks/method
calls.
NIO may use JNI in *some* cases, maybe giving faster read and bulk
operations, but slower element-wise access.
Before learning NIO, I did option 2: read a buffer directly to byte[]
with FileInputStream,
which in my case executed as a single I/O call (I checked). Then I
rolled the
fastest optimized loop I could for elements, and it worked well.
When I finally hit a good NIO tutorial (the NIO javadocs are
criminally poor)
I recoded my stuff with NIO. Lo and behold, the original was
somewhat
faster, once the method ran enough to compile to JIT. The NIO was
cleaner and more comprehensible. Easier to use with mixed primitives
types too.
*No, really. If anyone wants to argue, I will.
Cheers,
Bob McGee
vips
Thank you Bob, Thomas, Ewald, Chris.
We were able to achieve much better write and read performances.
For the same requirement as in the first post in this chain, we were
able to achieve -
Writes -
Inserts that were earlier running at 2775 records/sec, are now running
at 6500 records/sec.
We did the following changes -
1. Slightly smaller table - removed couple of unwanted fields
2. Committed after one whole files records were uploaded in database -
i.e. committed after apprx 39,000 records.
3. Used both SuperCSV and our own CSV reader to read from CSV files
instead of using CSVRead function directly - this caused the biggest
gain in performance.
4. Changed the JVM startup parameters of our program which reads from
CSV and dumps into table.
Used the following JVM params -
-server -XX:+AggressiveOpts -XX:CompileThreshold=2 -XX:
+UseParallelOldGC -XX:+UseParallelGC -XX:ParallelGCThreads=8
5. Increased Cache size, max operations memory and changed few H2 URL
parameters. Used the following parameters -
MAX_OPERATION_MEMORY=268435456;CACHE_SIZE=262144;FILE_LOCK=NO;TRACE_LEVEL_FILE=0;LOG=0;LOCK_MODE=0;UNDO_LOG=0
Reads -
Reads that were taking 200 milliseconds, now take 30 milliseconds.
The same parameters as above were used.
Another question -
We are building the index as an offline process after the inserts have
been finished.
Is there a way to speed up insert creation?
Can I force parallelism while creating indexes?
Regards,
Vipul
Chris Schanck
Is there a way to speed up insert creation?
Can I force parallelism while creating indexes?
Cool results.
As far as I know, index creation in H2 is a weak spot as far as performance. Not so much because it is slow as such, but because it does not do anything special when doing bulk indexing. I've made some gains by indexing separate columns in separate threads, but for a given table/column, it is not significantly faster to bulk index at the end of a load. Contrast this with Postgres/Oracle, where the bulk index process is *much* faster.
On the other hand, this seems like a reasonable design choice for H2. So it is hard to get really exercised about it.
C. Schanck
bob mcgee
On Jul 22, 5:57 am, vips <vips4l...@gmail.com> wrote:
> Inserts that were earlier running at 2775 records/sec, are now running
> at 6500 records/sec.
> Reads that were taking 200 milliseconds, now take 30 milliseconds.
> The same parameters as above were used.
> Another question -
operations (if you don't already)?
I've got a design (below) but it requires some threading and isn't
guaranteed to boost performance.
Use the java.util.concurrent.* classes, specifically the
ExecutorService classes.
First ExecutorService takes Runnables that process CSV file tasks for
insertion.
This ExecutorService uses limited max threads, but unlimited work
queue.
Second ExecutorService takes Runnables for DB inserts/operations, and
has
limited max threads, but VERY limited work queue (blocking when task
submitted, if queue is full).
Write factory methods to generate 3 sets of Runnables:
1) CSV parse operation:
2) Bulk record insert
3) Indexing
Factory method 1 (CSV parsing): takes as arguments a Connection (or
DataSource, or DB URL, whatever), batch size, and file path.
Factory method 2 (DB insert): takes as arguments a Connection or
whatnot & an object with a big chunk of records (1000+)
Factory method 3 (final DB insert+indexing): takes the same arguments
as method 2
Runnables from 1: start reading/parsing the CSV file and storing into
an records object usable by method2.
Every $BATCH_SIZE records, they periodically use factory method 2 to
create a Runnable for inserting this batch.
They submit this task to ExecutorService 2 for asynchronous execution,
then start building a fresh batch of records to insert.
For the final record chunk, they use method 3.
Runnables from 2: use the given Connection to insert the given records
batch with a single PreparedStatement and addBatch. At end, they do
executeBatch() and commit. This reduces the burden for H2 of holding
lots of uncommitted stuff.
Runnables from 3: same as 2, except at the end the DB gets indexed
(and ANALYZE is run, maybe).
ExecutorService #2 avoids memory problems from excessive parsed record
batches piling up by the limited work queue and blocking when
submitting a task to full queue. The methods to specify queue type /
policy are a little tricky to find in the javadocs, but useful here!
*** Performance/design notes: ***
You should use ConnectionPools or something like that for the
Runnables in #1, to reduce Connection creation overhead for the same
DB. It will also be tricky to determine what the optimum balance of DB
& CSV parsing theads is, because I/O and CPU use must be
balanced.
The objects for holding a bunch of parsed records for insertion should
be pooled and reused, since they will be large and memory-expensive.
Also, the closer they are to the raw DB data (I.E. numbers/dates fully
parsed, not raw string for H2 parsing) the better. Primitives are the
fastest, if you don't have null values. Your goal is to make your
multiple CPUs work hard here, not H2.
Depending on how much the CSV reading is I/O bound vs. CPU-bound, it
may be helpful to multithread the CSV parsing, separate from file
reading. One reader thread, which reads chunks in parallel & spits
raw byte[] chunks to the parsing threads. These threads do the actual
parsing and pass on tidied results to the DB. There are a couple
different ways to handle row boundaries not falling at constant byte
intervals, but I'm sure you can figure this out.
> [Speeding up indexing]
Always going to be kind of slow and read-heavy, because you have to
read all the data back to index.
My money says this is mostly I/O bound, and faster storage will be
most important.
*** Additional storage optimizations: ***
You could tinker with mixing SSDs & RAID0 SSD arrays into the storage
blend to make specific parts faster,
when full redundancy is not needed. IIRC ZFS has an option which uses
SSDs as caches to boost performance a lot.
If that doesn't suit, you could use the SSD or speeding the current
hour's processing.
I'm thinking specifically of accumulating the current hour of data
CSV, storing the DB as it
is built, and holding the last DB with last hour of data while it is
indexed.
Then you can copy the finished DB to the array for permanent, safe
storage and delete it from the SSD to make room for more.
To protect against failure of the SSD (much less likely than HD
failure, but does happen) you set a routine to use the disk array for
the being-built DB if SSD is not accessible, and when writing CSV to
SSD you write to disk array too.
To give an idea of the performance involved here: the Intel X-25E
boasts of 250 MB/s write, 170 MB/s read speed, with virtually 0 seek
time. It also costs like $300-400 for a 30 GB version, but if you've
got the cash for a server like that, you can probably afford to buy
one or two as a performance booster.
Cheers,
Bob McGee
vips
But for now - Continuing on improving performance of index creation -
why are so many h2 log files created while creating an index on a huge
table - even when my jdbc url says LOG=0 and UNDO_LOG=0?
Even if I created using a different thread, the index creation is a
huge bottleneck.
For about 5.7 million records in 1 table -
it took about 17 mins to insert data
And 32 mins to create an index on the data.
Index creation is taking twice as much time as inserting data into the
table.
If the index is pre-created the inserts straight away take thrice the
time.
Hence inserting after creating indexes is out of the question.
Bob, on your particular suggestion, we have been using SSDs for a few
years now.
Though, havent tried SSD in this particular case.
Should be worthwhile trying it.
Unfortunately, do not have SSD available immediately to do tests.
Will need to wait for a few weeks before doing that.
We did a comparision between H2 and another opensource db - gigabase.
Here are the comparision results -
Gigabase H2
DB creation & open - first time 106 ms 4.5 sec
DB rotate 374 ms 2.3 sec
DB creation & open - next time 106 ms 900 ms
Table creation time 1 ms 1 ms
index creation (empty table) 1 ms NA
total insert time (156 files) 1651 sec 1140 sec (h2
without indexes)
index creation (full table) NA 1920 sec
total time (insert + index in sequence) 1651 sec 3060 sec
CPU 1.60% 1.60%
Memory 1048M 750 M
DB size (data + index + logs) 857 M 514 M
H2 insertion is faster because it is on a non-indexed table.
Memory and disk footprint of H2 is better.
But, DB creation, opening and index creation are problematic areas.
We have set log to 2 before closing the db connection.
Any ideas/suggestions are welcome.
Cheers,
Vipul.
bob mcgee
and-oranges, since the features and performance characteristics differ
so greatly. Still, H2 might be able to do better on indexing.
Can you provide some additional information about your record
structures and index types?
I'm currently doing some profiling on index creation to locate
potential hotspots to optimize. I'll post if I find anything
definitive.
*** Particular items of interest: ***
Is one index taking significantly longer to create than the others?
Do you have LOBs in your rows being indexed?
(From what I've seen of the source, LOBs may have some glitches
that can hurt performance.)
Are you using a lot of columns?
Are your rows very large in size due to the data types stored?
Are you indexing a lot of string-type data (or LOBs)?
(In some cases a unique or unique hash index may be faster to
create than a B-tree index. Strings in particular.)
Are you creating a lot of indexes on one table?
Are you creating indexes using multiple columns?
On Jul 23, 2:43 pm, vips <vips4l...@gmail.com> wrote:
> why are so many h2 log files created while creating an index on a huge
> table - even when my jdbc url says LOG=0 and UNDO_LOG=0?
> Even if I created using a different thread, the index creation is a
> huge bottleneck.
I have seen this too, even with smaller tables. As discussed, each
indexing triggers a full rowscan, which is one cause.
I think the B-tree indexes may all be covering indexes, accounting for
their size and relatively slow creation time (lots of I/O).
> We did a comparision between H2 and another opensource db - gigabase.
> Here are the comparision results -
Cheers,
Bob McGee
vips
Table Structure is as follows -
CREATE TABLE TestTable
(Field1 Varchar2(20), Field2 Number(20), Field3 Number(20),
Field4 INT, Field5 INT, Field5 INT, Field6 Number(8), Field7 Number
(15),
Field8 Number(10), Field9 INT);
Only 1 index is being created on 6 million records.
Create index test_idx on TestTable(Field7) ;
Btw, Field7 has about 2-5% entries which are null.
I will also be doing a profiling from my side and check.
Queries are about 5x to 10x faster in Gigabase.
Our main concern is insert speed hence concentrating on that part for
now.
~
Vipul
bob mcgee
it!
First, you must be heavily exercising the string parsing routines to
get all those numbers from the CSV -- definitely multithread the
parsing if you can. Java's built-in number parsing routines can be
appallingly slow unless someone rolls their own version (a lot of
libraries do).
Second, you're heavily using the DECIMAL type (Number maps to this).
This can be an order of magnitude slower than a DOUBLE or REAL or
INTEGER type, because how it has to be implemented.
Are these columns monetary amounts, or otherwise in need or precise
decimal mathematics?
If not, DOUBLE is faster, and sometimes smaller too.
If profiling shows a hotspot in comparison of values or sorting, blame
DECIMAL.
For my profiling, the org.h2.util.BitField methods show as the main
performance hotspot, with 30% of runtime, all together. Next up are
storage-related classes and methods.
I suspect that (after glancing over the code), BitField can be
optimized some with the fine art of bit-twiddling.
I love bit twiddling, so I'll poke at it when I have time later and
let Thomas know if I find anything good.
Cheers,
Bob McGee
Thomas Mueller
> why are so many h2 log files created... when my jdbc url says LOG=0 and UNDO_LOG=0?
I can't reproduce this. In my test only one file is created:
<dbname>.0.log.db. Could you create a reproducable test case please?
It would be great if the test case does not have any dependencies
except the H2 jar file (one Java class uses the JDBC API and is run
using a static main method). Please include any initialization code
(CREATE TABLE, INSERT and so on) in the Java class or in a .sql script
file.
One possible optimization is to use the 'nio' or 'nioMapped' file
system. File systems are not yet documented, I will do that in the
next release:
== Pluggable File System ==
This database supports a pluggable file system API. The file system
implementation is selected using a file name prefix. The following
file systems are included:
* zip: read-only zip-file based file system. Format:
zip:/zipFileName!/fileName.
* nio: file system that uses FileChannel instead of
RandomAccessFile (faster in some operating systems).
* nioMapped: file system that uses memory mapped files (faster in
some operating systems).
* split: file system that splits files in 1 GB files (stackable
with other file systems).
* memLZF: compressing in-memory file system (experimental; used
for testing).
* memFS: in-memory file system (experimental; used for testing).
As an example, to use the the nio file system, use the following
database URL: jdbc:h2:nio:~/test.
To register a new file system, extend the classes
org.h2.store.fs.FileSystem and FileObject, and call the method
FileSystem.register before using it.
> FILE_LOCK=NO
I don't think this has an impact on performance. It is a bit dangerous
to use, because multiple processes could open the same database file
at the same time, which could lead to corruption.
> We are building the index as an offline process after the
> inserts have been finished.
In theory that should help, however for H2 currently it could be
better to create the indexes before inserting data.
> Can I force parallelism while creating indexes?
No.
> does not do anything special when doing bulk indexing
It does a little bit (pre-sorting batches of rows in memory), but it
doesn't help much. I plan to improve index creation when using the
page store.
> DB creation & open - first time: 4.5 sec
I guess that's the JVM 'warm up' time (compiling the byte codes). I
don't think there is anything I can do in H2 to solve this.
Regards,
Thomas
vips
> > why are so many h2 log files created... when my jdbc url says LOG=0 and UNDO_LOG=0?
>
> I can't reproduce this. In my test only one file is created:
> <dbname>.0.log.db. Could you create a reproducable test case please?
Since we are creating indexes after finishing off with the inserts,
the index creation takes a cool 30+ minutes to complete.
In this 30 minutes more than a 100 files for the log are created.
But when the index creation is over, only 1 log file remains.
My question was that since index creation is a DDL activity it should
not have been logged anyways.
This does not look like transaction logging, but more like temporary
space used for sorting etc. while creating indexes.
Is there any optimizations that can be done there?
> In theory that should help, however for H2 currently it could be
> better to create the indexes before inserting data
twice as slow. This causes a huge bottleneck for the main processing
thread.
Hence we are just inserting in one thread and once the insertion is
over, creating indexes using another thread.
That way we are not only speeding up the inserts, but also keeping the
insert thread relatively free and light to process more inserts/sec,
and also utilizing multi-core systems slightly more better. Now the 2
threads (one insertion and another index creation) can run
simultaneously on 2 different cores thus speeding up the whole
process.
Let me know if you see any flaw in the above process or any other
better ideas to parallelize and increase performance.
The biggest bottleneck right now is the index creation time after the
inserts are over.
Will try nio also and check.
Regards,
Vipul.
On Aug 4, 11:31 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
Chris Schanck
> In theory that should help, however for H2 currently it could be
> better to create the indexes before inserting data
That's not my experience...
If we are creating indexes before the inserts, the inserts become
twice as slow. This causes a huge bottleneck for the main processing
thread.
Hence we are just inserting in one thread and once the insertion is
over, creating indexes using another thread.
That way we are not only speeding up the inserts, but also keeping the
insert thread relatively free and light to process more inserts/sec,
and also utilizing multi-core systems slightly more better. Now the 2
threads (one insertion and another index creation) can run
simultaneously on 2 different cores thus speeding up the whole
process.
Let me know if you see any flaw in the above process or any other
better ideas to parallelize and increase performance.
[ .. deletia ...]
This tracks with what I have seen; leaving multiple indexes up while inserted absolutely crushes bulk insert speed at larger table sizes.
On the other hand, indexing a single index after the fact is not significantly faster than having a single index up during the insert phase.
The degredation seems to come in two parts :
1) bulk inserts which touch several indexes (more than 10?) seemt o get creamed by something. Maybe locality of reference for the indexes? Got me.
2) bulk indexing is not that much faster -- unlike Postgres/MySQL/etc H2 does not seem to have smarter algorithm for bulk indexing than for incremental indexing.
Chris
Thomas Mueller
> The files are created while the index creation is in progress.
I can't reproduce this problem, see my test case below. Could you
modify the test case so the files are created?
> Since we are creating indexes after finishing off with the inserts,
> the index creation takes a cool 30+ minutes to complete.
For me it takes about 3 min (using dummy data).
> H2 does not seem to have smarter algorithm for bulk indexing
That's true.
> This does not look like transaction logging, but more like temporary
> space used for sorting etc. while creating indexes.
Creating indexes shouldn't doesn't create temporary objects files.
> If we are creating indexes before the inserts, the inserts become
> twice as slow.
If you use CREATE TABLE ... AS SELECT ... ORDER BY then it shouldn't
be a big problem. But it depends on the data, but for me it's only 50%
slower than creating the table without index. Unless you create a
unique index, in which case you can create a primary key, you would
need to use the MySQL mode and the syntax:
CREATE TABLE TestTable(Field1 Varchar2(20), ..., index(field7)) as select ...
"order by" in the select statement on field7 may improve performance.
> Field1 Varchar2(20), Field2 Number(20), Field3 Number(20), Field4 INT, Field5 INT, Field5 INT, Field6 Number(8), Field7 Number(15), Field8 Number(10), Field9 INT);
Not using the precision helps a few percent bit in my test:
Field1 Varchar, Field2 Number, Field3 Number, Field4 INT, Field5 INT,
Field5 INT, Field6 Number, Field7 Number, Field8 Number, Field9 INT);
More than 10% better is BIGINT instead of NUMBER, specially for the
indexed column:
Field1 Varchar, Field2 Number, Field3 Number, Field4 INT, Field5 INT,
Field6 int, Field7 bigint, Field8 int, Field9 INT)
My test case:
package db;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import org.h2.store.FileLister;
import org.h2.tools.DeleteDbFiles;
// java -Xmx512m
// create table 232 s, create index 183 s
public class TestCreateIndex {
public static void main(String[] args) throws Exception {
Class.forName("org.h2.Driver");
DeleteDbFiles.execute("data", "test", true);
String url = "jdbc:h2:nio:data/test;LOG=0;UNDO_LOG=0;" +
"CACHE_SIZE=262144";
final Connection conn = DriverManager.getConnection(url);
System.out.println("creating table...");
long t = System.currentTimeMillis();
conn.createStatement().execute(
"CREATE TABLE TestTable(" +
"Field1 Varchar2(20), Field2 Number(20), " +
"Field3 Number(20), Field4 INT, Field5 INT, " +
"Field6 Number(8), Field7 Number(15), " +
"Field8 Number(10), Field9 INT) " +
"as select space(15), x, x, x, x, x, x, x, x " +
"from system_range(1, 6000000)");
System.out.println((System.currentTimeMillis() - t) + " ms");
new Thread() {
public void run() {
try {
while (!conn.isClosed()) {
System.out.println();
for (String s : FileLister.
getDatabaseFiles("data", "test", true)) {
System.out.println(s + ": " +
(new File(s).length() / 1024) + " KB");
}
Thread.sleep(20000);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}.start();
System.out.println("creating index...");
t = System.currentTimeMillis();
conn.createStatement().execute(
"Create index test_idx on TestTable(Field7)");
System.out.println((System.currentTimeMillis() - t) + " ms");
conn.close();
}
}
Regards,
Thomas