Now I remember why - recursive table queries.
On Sun, 5 May 2019 at 08:11, Evgenij Ryazanov wrote:
> PreparedStatement re-parses such queries during each invocation.
>
> And we also have another known problem with CTEs: temporary views from
> different statements may conflict with each other.
PreparedStatement re-parses such queries during each invocation.
And we also have another known problem with CTEs: temporary views from
different statements may conflict with each other. Such views should not be
visible outside of the command.
--
You received this message because you are
Its unfortunate that we are creating and destroying local temp tables for
each query - since we're using a PreparedStatement, they should ideally be
cached and only dropped when the connection is closed.
But I remember trying to improve our CTE architecture and not having much
luck. The lifecycle
org.h2.table.TableView.destroyShadowTableForRecursiveExpression (boolean,
org.h2.engine.Session, org.h2.table.Table)
calls
org.h2.engine.Session.removeLocalTempTable (org.h2.table.Table)
that calls
org.h2.engine.Database.lockMeta (org.h2.engine.Session)
We have a bottleneck here.
--
You
Previous message was truncated, so check the attachment.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to
I run a git bisect.
https://github.com/h2database/h2database/commit/7977b90bd13e9088a3dc1ed8b60efb2699e97724
is
the first slow commit.
It was merged in https://github.com/h2database/h2database/pull/640
--
You received this message because you are subscribed to the Google Groups "H2
Database"
With 200 threads both 1.4.197 and 1.4.199 work about 6–7 times slower than
1.4.196.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
I used the same query as in you test case. I can repeat the test with 200
threads.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Are you doing 1 times a CTE or just a simple SELECT?
200 users concurrency is needed else indeed some versions will feel similar.
I also built a ramp up of 4 seconds. It's a steep ramp up but allows to measure
how h2 recovers from sudden increase of traffic.
I would like to emphasise that
what do your test results look like with 200 threads?
I'm guessing Christian's test case is mostly testing contention on hot
locks.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from
I wrote and run a simple 4-thread test with 4 connections and 4 prepared
statements with values from population.csv. I tested all values distributed
between these threads (All) and also 10,000 randomly selected values per
thread (Random). Measured times in seconds are average times between
Correct
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to
You would need to build a thread engine sending these queries through jdbc.
Not impossible. I used JMeter as it's a java product already available I use on
daily basis.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this
No. It's the init.sql that populates the db.
JMeter only opens the pre-existing populated db, and then indeed run CTE for
all IDs in population.csv.
I wish it could have been in a shape of an automated testscase, but it's not.
This one highlights the performance issue only.
I will try to do
>From reading the .jmx file, it looks like JMeter will populate the database
from the CSV file, and then run a query (specified inside the JMX file)
from multiple simultaneous threads.
Should be able to run a profiler on the H2 instance while the JMeter test
is running.
Not as nice as standalone
We need code that can be profiled directly with any suitable tool. But not
the third-party environment that does something with the web interface of
H2.
It looks like population.csv contains the identities that should be passed
to the provided query, but I'm not sure.
--
You received this
@Evgenij looks like the way it works is to open the .jmx file in Apache
JMeter
https://jmeter.apache.org/
and run the test plan
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it,
We need a standalone test case (Java/SQL only) for further investigation.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
This script is to initialize the db and test only once the CTE query.
If you just do this with one user and no volume, the difference of performance
is difficult to notice. You might notice something if your system is under
heavy cpu or io load.
The measurement of CTE is done by the jmx file
Hello.
Your cte.sql contains
runscript from 'init.sql';
Do you measure performance of the query or performance of database
population with data?
I don't see any noticeable differences in execution time of the query
itself between 1.4.196 and 1.4.199 in both PageStore and MVStore databases
Hi,
I moved away from memory database to pure embedded database, using
pagestore format. I stick to CTE, as it was supported for the releases used
inside comparison scope, and CTE is slowish to allow comparison of graphs.
DDL and DDQ were too fast for the graphs to be differentiated.
I've
Yes in memory only for now.
I can move it to embedded files and tcp as well.
What I saw is a sudden drop of performance between 196 and 197. That drop was
halved and partially recovered in 198 and 199.
But it's still, for that particular use case (CTE), too slow compared to older
versions.
So you're testing in-memory only?
Hmmm, then your results are kind of what I expect. If you're testing
in-memory, you are testing the cost of our parsing and storage management
layers.
Which have become more expensive as we have become more sophisticated.
If you really care about in-memory
I'll do that and keep you posted
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to
Please keep in mind, that even for in-memory database, pgstore or mvstore
are still very much in use.
So it looks like apples-to-oranges comparison indeed. To make them
comparable I would switch to pgstore use in 1.4 versions, by explicitly
specifying MVSTORE=FALSE in url.
--
You received
jdbc:h2:mem:tsn
it's a memory db. Keep in mind that the standalone test case is a load
test. You'll need JMeter to run it and other versions of H2 to compare via
individual runs.
I'll get this ready soon
On Monday, April 29, 2019 at 3:28:22 AM UTC+2, andre...@gmail.com wrote:
>
> I wonder how
I wonder how your URL looks like and whether it is apples to oranges
comparison, because default settings have changed between those versions,
most notably default storage engine was pagestore in older versions vs
mvsore in recent ones. That alone can have big performance impact.
In any
On Friday, April 26, 2019 at 4:30:49 PM UTC+2, Noel Grandin wrote:
>
>
>
> On 2019/04/26 4:15 PM, Christian MICHON wrote:
> > @Evgenij : it's not rocket science to build such scenario, if you need
> such performance test scenario, I'll share it on
> > github (it's not confidential).
> >
>
>
On 2019/04/26 4:15 PM, Christian MICHON wrote:
@Evgenij : it's not rocket science to build such scenario, if you need such performance test scenario, I'll share it on
github (it's not confidential).
Yes please, let's see the test case.
--
You received this message because you are
29 matches
Mail list logo