Re: Fwd: HSQL/MySQL query performance

Fred Toussi <[email protected]> Thu, 19 Dec 2013 11:29:37 +0000
Newsgroups	gmane.comp.java.hsqldb.user
Message-ID	<[email protected]>
I don't know if the overhead might be reduced further. Your test is
unusual as it selects 20000 rows each time.

To ensure the tests are realistic, perform a getString() on all the rows
and columns returned by the result set to make sure actual values are
fetched.

Fred

On Thu, Dec 19, 2013, at 10:11, Viktor Ádám wrote:
> I see. There is definitely an improvement:
> Select.Walk #1: 1290 -> 669
> Select.Walk #2:  762 -> 503
> Select.Walk #3:  805 -> 487
> Select.Walk #4:  743 -> 494
> Select.Walk #5:  776 -> 493
> Select.Walk #6:  776 -> 486
> Select.Walk #7:  760 -> 488
> Select.Walk #8:  758 -> 486
> Select.Walk #9:  775 -> 487
> Select.Walk #10: 756 -> 488
> 
> Do you think this is about as good as it gets? (To compare, MySQL
> produces about 150 ms execution times on these queries on this
> machine).
> 
> Thanks,
> Viktor
> 
> On Thu, Dec 19, 2013 at 11:02 AM, Fred Toussi
> <[email protected]> wrote:
> > Try again. The settings are not being applied if you don't see any speed
> > difference.
> >
> > The easy way to do this is create the database with an in-process
> > connection that applies the properties then performs SHUTDOWN without
> > populating it. You then start the server without setting the database
> > properties and perform the tests. As stated before, use lowrecase for
> > peroperties.
> >
> > Fred
> >
> > On Thu, Dec 19, 2013, at 9:48, Viktor Ádám wrote:
> >> Thanks for clearing that up!
> >> However changing the code does not help on HSQL server mode speed for us.
> >>
> >> StringBuilder pathBuilder = new
> >> StringBuilder("file:/home/swn/db/testdb2");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx).append("=").append("MVCC");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_level).append("=").append("READ_COMMITTED");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback).append("=").append(true);
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_default_table_type).append("=").append("CACHED");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_rows).append("=").append(100000);
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_size).append("=").append(100000);
> >> pathBuilder.append(";").append("user").append("=").append(user);
> >> pathBuilder.append(";").append("password").append("=").append(password);
> >> server.setDatabasePath(0, pathBuilder.toString());
> >>
> >> Viktor
> >>
> >> On Thu, Dec 19, 2013 at 10:15 AM, Fred Toussi
> >> <[email protected]> wrote:
> >> > You are not setting the properties for the database on the server
> >> > correctly. The lines starting with the ones below have no effect.
> >> >
> >> >                         HsqlProperties props = new HsqlProperties();
> >> >                         props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
> >> >                         "MVCC");
> >> >
> >> > The properties for a server can contain only server properties. These
> >> > properties are like server.silent=false, etc.
> >> > The database properties for a server are set on the URL of the database
> >> > in this call: server.setDatabasePath(0, "file:db/testdb2"); The
> >> > properties should be lowercase.
> >> >
> >> > In addition, you should never access HSQLDB's classes that are not
> >> > documented for external use. These include org.hsqldb.DatabaseManager
> >> > etc.
> >> >
> >> > Fred
> >> >
> >> > On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:
> >> >> Hi Fred,
> >> >>
> >> >> thanks for your help! It is true that inserts are much faster and that
> >> >> is very impressive.
> >> >> I wasn't entirely informative about our setup. We would like to use
> >> >> HSQLDB in server mode. I've attached a new test and its result where
> >> >> we executed the sample with a server HSQLDB instance, an in-process
> >> >> instance (file:) and with MySQL. The parameters you suggested
> >> >> definitely helps in in-process mode (our select times has gone down to
> >> >> about 10 ms from about 150-200 ms). In server mode there is a slight
> >> >> improvement but it is still slower for us than MySQL.
> >> >> Do you think that it should be faster even in server mode with HSQL
> >> >> protocol?
> >> >>
> >> >> Thanks again for your help!
> >> >>
> >> >> Viktor
> >> >>
> >> >> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
> >> >> <[email protected]> wrote:
> >> >> > The log shows the inserts are 3.5 times faster with HSQLDB.
> >> >> >
> >> >> > As for your SELECT statements, you are selecting all the rows in the
> >> >> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
> >> >> > fewer rows, so each time you select, lots of rows are fetched from the
> >> >> > disk.
> >> >> >
> >> >> > Just increase the above settings until SELECT speed is what you want. I
> >> >> > think it should be faster than MySQL.
> >> >> >
> >> >> > Fred
> >> >> >
> >> >> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
> >> >> >> Hi!
> >> >> >>
> >> >> >> We are migrating our application from MySQL to HSQLDB to have greater
> >> >> >> control over the database. Everything works great except that queries
> >> >> >> are slower than they were on MySQL.
> >> >> >> I've attached a simple test running the same CREATE/INSERT/SELECT
> >> >> >> statements on HSQL and MySQL and the produced output also. In the
> >> >> >> attached output MySQL queries are 4-5x times faster and on another
> >> >> >> development machine this ratio is about 8-10x.
> >> >> >> Can you give as a hint about what we're doing wrong? Are there any
> >> >> >> configuration parameters which could be tweaked?
> >> >> >>
> >> >> >> Our test PC was:
> >> >> >> CPU: Intel Core i5-2400 @ 3.30GHz
> >> >> >> RAM: 4GB
> >> >> >> OS: Windows 7 Home Premium
> >> >> >> MySQL: 5.5.11
> >> >> >>
> >> >> >> Thank you in advance!
> >> >> >>
> >> >> >> Best regards,
> >> >> >> Viktor Ádám
> >> >> >> ------------------------------------------------------------------------------
> >> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> >> organizations don't have a clear picture of how application performance
> >> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> >> >> your
> >> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> >> >> Pro!
> >> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> >> _______________________________________________
> >> >> >> Hsqldb-user mailing list
> >> >> >> [email protected]
> >> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> >> Email had 2 attachments:
> >> >> >> + dbperformance.txt
> >> >> >>   3k (text/plain)
> >> >> >> + DBPerformanceTester.java
> >> >> >>   8k (text/x-java)
> >> >> >
> >> >> > ------------------------------------------------------------------------------
> >> >> > Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> > organizations don't have a clear picture of how application performance
> >> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
> >> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> >> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> > _______________________________________________
> >> >> > Hsqldb-user mailing list
> >> >> > [email protected]
> >> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> ------------------------------------------------------------------------------
> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> organizations don't have a clear picture of how application performance
> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> >> your
> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> >> Pro!
> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> _______________________________________________
> >> >> Hsqldb-user mailing list
> >> >> [email protected]
> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> Email had 2 attachments:
> >> >> + dbperformance2.txt
> >> >>   5k (text/plain)
> >> >> + DBPerformanceTester.java
> >> >>   9k (text/x-java)
> >> >
> >> > ------------------------------------------------------------------------------
> >> > Rapidly troubleshoot problems before they affect your business. Most IT
> >> > organizations don't have a clear picture of how application performance
> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> > _______________________________________________
> >> > Hsqldb-user mailing list
> >> > [email protected]
> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >>
> >> ------------------------------------------------------------------------------
> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> organizations don't have a clear picture of how application performance
> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> your
> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> Pro!
> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> _______________________________________________
> >> Hsqldb-user mailing list
> >> [email protected]
> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ------------------------------------------------------------------------------
> > Rapidly troubleshoot problems before they affect your business. Most IT
> > organizations don't have a clear picture of how application performance
> > affects their revenue. With AppDynamics, you get 100% visibility into your
> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Hsqldb-user mailing list
> > [email protected]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> 
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT 
> organizations don't have a clear picture of how application performance 
> affects their revenue. With AppDynamics, you get 100% visibility into
> your 
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT 
organizations don't have a clear picture of how application performance 
affects their revenue. With AppDynamics, you get 100% visibility into your 
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk