> Great, thanks a lot Sebastien. Interesting results. H2 kicks ass when
> inserting data, but not when performing selects over a lot of rows,
> correct?
Well ... it's embedded and there is many commands send to the database
(200 batch of 1000 inserts). Putting H2 in it's own server shows quite
different result (similar to other dbms if i remember well).
I have an oltp test where H2 is similar to innodb, myisam or
postgresql. Good work.
> Are you sure its cpu bound when scanning the table?
doing iostat while running :
scan (creating temp table)
avg-cpu: %user %nice %system %iowait %steal %idle
31,93 0,00 15,03 13,16 0,00 39,88
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 285,00 29,89 10,86 149 54
There is a lot of time in system due to FileObjectDisk having no
cache, but the system has one.
It seems to me that it wait not so much for I/O. The drive can go up
to 70MB/s reads.
scan (reading temp table)
avg-cpu: %user %nice %system %iowait %steal %idle
29,58 0,00 23,80 0,70 0,00 45,92
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 119,20 13,99 0,00 69 0
Should be a lot of client/server communication.
sum
avg-cpu: %user %nice %system %iowait %steal %idle
30,72 0,00 19,62 5,75 0,00 43,90
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 365,60 42,90 0,02 214 0
Not so bad, but the hard drive is still sleeping. Postgresql achieve
60MB/s and 34% iowait. Reading from the system cache cost too. It
reads more than 50% useless data (.BLOCK_SIZE = 128 bytes and my row
takes all-in 64 bytes), so it's 20MB/s of real data.
join fact 1 dim
avg-cpu: %user %nice %system %iowait %steal %idle
40,53 0,00 12,52 6,90 0,00 40,04
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 195,00 22,49 0,02 112 0
The dimension table is in memory, there is just a table scan. I
suppose the btree took most of the time.
Postgresql is at 40MB/s and 10% iowait.
join fact 2 dim
avg-cpu: %user %nice %system %iowait %steal %idle
31,64 0,00 9,11 13,42 0,00 45,83
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 224,20 21,50 0,05 107 0
The parser does random read in the fact table using the btree. The OS
cache is less used (as it's more random).
I can get better results using a cache in FileObjectDisk (reading 16KB
each time), up to 10% in some queries, a little bit slower when it's
more random. This allow me to use DiskFile.BLOCK_SIZE = 16 instead of
128 without any cost (and thus cutting my DB size by 2 in my case as a
row takes 64 bytes in my case).
But the I/O seems not the main problem in this case.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
