Re: H2 In-Memory Database Cache and H2 Performance vs. MySQL Performance

[Ryan How]

Maybe this is due to the JIT compiler optimising it?

On 2/10/2012 6:31 PM, snookerms wrote:
> I'm running a SQL-Query the first time it takes 1540ms. The Second
> time it take 749ms and the third time 503ms and so on.

[snookerms]

Thank You. But a Difference of 41 times faster?

[Ryan How]

That does sound a bit drastic!. Would be good if the JIT compiler made everything 41 times faster :).

I'm really not sure sorry. I think if you can post a reproducible test case then someone will work it out!

Perhaps it might be that the OS is swapping your data to disk, and on repeated querying it swaps it back and keeps it in memory so is faster.

What I mean is, if you can post some more details then that should help us work it out.

Thanks, Ryan

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/YP3SrTKKfvIJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[snookerms]

Here a description of the test case:

Hardware: Quad-Core AMD, 1.6GHz
                4GB RAM
                10 GB HDD

Software: Ubuntu Server 12.04
               Openjdk-7-jdk
               H2 v.2010.07.13

In the database are 24 tables. But only one table has 250.000 datasets (rows of data). The table structure of this table is like this:

I'm using MySQL-Mode:

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_model` varchar(64)   DEFAULT NULL,
  `products_str` varchar(64)   NOT NULL,
  `products_hausnr` varchar(50)   NOT NULL,
  `products_plz` varchar(32)   NOT NULL,
  `products_ort` varchar(64)   NOT NULL,
  `products_bndl` int(1) NOT NULL,
  `products_refnr` varchar(32)   NOT NULL,
  `products_bj` varchar(32)   NOT NULL,
  `products_haus` int(11) NOT NULL,
  `products_bhaus` int(11) NOT NULL,
  `products_wohn` int(11) NOT NULL,
  `products_einzelhandel` int(11) NOT NULL,
  `products_gastgewerbe` int(11) NOT NULL,
  `products_land_forst` int(11) NOT NULL,
  `products_rooms` decimal(3,1) NOT NULL,
  `products_bathrooms` int(3) NOT NULL,
  `products_etagen` int(11) NOT NULL,
  `products_wf` decimal(6,2) NOT NULL,
  `products_nf` decimal(6,2) NOT NULL,
  `products_gf` decimal(6,2) NOT NULL,
  `products_beb` int(11) NOT NULL,
  `products_ersch` int(11) NOT NULL,
  `products_nutz` int(11) NOT NULL,
  `products_verma` int(11) NOT NULL,
  `products_heiz` int(11) NOT NULL,
  `products_ensth` int(11) NOT NULL,
  `products_enstw` int(11) NOT NULL,
  `products_stat` int(11) NOT NULL,
  `products_miet` int(11) NOT NULL,
  `products_image` varchar(64)   DEFAULT NULL,
  `products_image_title` varchar(100)   NOT NULL,
  `products_price_anfra` varchar(5)   NOT NULL DEFAULT 'false',
  `products_price` decimal(15,4) NOT NULL,
  `products_nebkost` decimal(15,4) NOT NULL,
  `products_kaut` decimal(15,4) NOT NULL,
  `products_provision` decimal(3,2) NOT NULL,
  `products_provision_type` varchar(6)   NOT NULL DEFAULT 'normal',
  `products_discount_allowed` decimal(3,2) NOT NULL DEFAULT '0.00',
  `products_date_added` datetime NOT NULL,
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_from` int(11) NOT NULL,
  `products_from_id` varchar(32)   NOT NULL,
  `products_fertig` varchar(5)   NOT NULL DEFAULT 'false',
  `products_roll` varchar(5)   NOT NULL DEFAULT 'false',
  `products_gara` varchar(5)   NOT NULL DEFAULT 'false',
  `products_einl` varchar(5)   NOT NULL DEFAULT 'false',
  `products_ebk` varchar(5)   NOT NULL DEFAULT 'false',
  `products_bt` varchar(5)   NOT NULL DEFAULT 'false',
  `products_gmb` varchar(5)   NOT NULL DEFAULT 'false',
  `products_paz` varchar(5)   NOT NULL DEFAULT 'false',
  `products_bw` varchar(5)   NOT NULL DEFAULT 'false',
  `products_kell` varchar(5)   NOT NULL DEFAULT 'false',
  `products_wbs` varchar(5)   NOT NULL DEFAULT 'false',
  `products_kurzbeb` varchar(5)   NOT NULL DEFAULT 'false',
  `products_noprov` varchar(5)   NOT NULL DEFAULT 'false',
  `products_bgn` varchar(5)   NOT NULL DEFAULT 'false',
  `products_share` varchar(5)   NOT NULL DEFAULT 'false',
  `products_afa` varchar(5)   NOT NULL DEFAULT 'false',
  `products_bezfrei` varchar(9)   NOT NULL DEFAULT 'value',
  `products_bezfrei_value` varchar(255)   NOT NULL,
  `pay_ad` varchar(5)   NOT NULL DEFAULT 'false',
  `payed` varchar(5)   NOT NULL DEFAULT 'false',
  `ad_type` varchar(7)   NOT NULL DEFAULT 'partner',
  `valid_date` datetime DEFAULT NULL,
  `extended` varchar(5)   NOT NULL DEFAULT 'false',
  `extension_date` datetime DEFAULT NULL,
  `openimmo_id` varchar(200)   NOT NULL,
  PRIMARY KEY (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`)
)  ;

And I run the following SQL-Queries:

First one:    SELECT * FROM products WHERE products_ort = 'Berlin'

second: SELECT * FROM products AS a, products_wohn AS b WHERE a.products_ort = 'Dresden'              AND a.products_wohn = b.products_wohn_id              AND a.products_rooms = 2.0              AND b.products_wohn_name ='Erdgeschoss' First query they need first time = 1543 ms, second = 630, third= 497, and so on Second Query first run =173 ms, second = 122 ms, third= 121 I use Apache Jmeter for Benchmarks with JDBC. The queries run in a row, without a break. And five times in a rows without a break. If I run only the second Query without the first one, it begins with 123 ms and after that ist needs only 6 ms, 7 ms and so on.

[Ryan How]

Thanks!

Did you try a more recent version of H2? Or is that not an option?

[snookerms]

Oh, sorry. I mean H2 v.2012.07.13.

 

[Ryan How]

Have you tried running explain analyze on your query? That might shed some light.

Have you got an index on products_ort for the first query?

http://www.h2database.com/html/grammar.html#explain

On 4/10/2012 1:10 AM, snookerms wrote:

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/sT7eBsUoyIoJ.

[snookerms]

Yes, INDEX is on Column products_ort. The problem is the index, when he is not set optimized, then the effekt like in the description is there.

[Ryan How]

On 11/10/2012 4:25 PM, snookerms wrote:
> The problem is the index, when he is not set optimized, then the
> effekt like in the description is there.
>

Sorry, what do you mean by this? It is not using the index?

[Brian]

Correct, your query is not using any index because your WHERE clause(s) do not reference any indexed fields.
In H2, if your query doesn't use an index it's fairly catastrophic for performance, when idexes are used it's very fast (Provided sufficient CACHE_SIZE).
In this case, it looks like you should add an index on products_ort.
After the table is established and before you run your queries, run ANALYZE; so it can calculate statistics on your data/indexes to enable it to choose the best index to use.
Try your benchmarks again and see how they go.
In order to verify / determine if your query is or is not using an index, precede your query with EXPLAIN and run it. 
If you see tableScan in the results, that means it's not using an index, it's reading the entire table - which is not ideal.

What we weren't able to tell from your post was
1.) How much data of what types are in the table - affecting the statistics and query plan
2.) How much memory the JVM was launched with, and what you've set (or not) the CACHE_SIZE to for H2 - which plays a huge role in performance - usually second only to not using any index.

-Brian L

[Ryan How]

I'm pretty sure he is using an in memory db. So this shouldn't matter?

[snookerms]

I think there is no Cache by in-memory mode? Or I'm wrong??

[Thomas Mueller]

Hi,

> I think there is no Cache by in-memory mode? Or I'm wrong??

This question was answered in another mail thread: yes, because there are various caches (query cache, object cache,...)

Regards,

Thomas

[snookerms]

Hello Thomas,

sorry for my confused posts.

But I'm in stress. I need the evaluation of H2 fpr my master thesis. I compare MySQL as disk-based database vs. H2 as In-Memory Database for an property agency service.

Now I tested the query with the Cache you discribed and it is better. But it was confusing, because the "no effect by in-memory ...".