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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 <[hidden email]> 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 >> [hidden email] >> 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 > <[hidden email]> 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 > >> [hidden email] > >> 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 > > [hidden email] > > 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 <[hidden email]> 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 >> <[hidden email]> 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 >> >> [hidden email] >> >> 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 >> > [hidden email] >> > 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 >> [hidden email] >> 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 > <[hidden email]> 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 > >> <[hidden email]> 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 > >> >> [hidden email] > >> >> 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 > >> > [hidden email] > >> > 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 > >> [hidden email] > >> 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 > > [hidden email] > > 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 <[hidden email]> 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 >> <[hidden email]> 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 >> >> <[hidden email]> 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 >> >> >> [hidden email] >> >> >> 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 >> >> > [hidden email] >> >> > 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 >> >> [hidden email] >> >> 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 >> > [hidden email] >> > 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 >> [hidden email] >> 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
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 > <[hidden email]> 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 > >> <[hidden email]> 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 > >> >> <[hidden email]> 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 > >> >> >> [hidden email] > >> >> >> 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 > >> >> > [hidden email] > >> >> > 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 > >> >> [hidden email] > >> >> 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 > >> > [hidden email] > >> > 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 > >> [hidden email] > >> 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 > > [hidden email] > > 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
All right. Thank you very much for your help!
Viktor On Thu, Dec 19, 2013 at 12:29 PM, Fred Toussi <[hidden email]> wrote: > 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 >> <[hidden email]> 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 >> >> <[hidden email]> 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 >> >> >> <[hidden email]> 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 >> >> >> >> [hidden email] >> >> >> >> 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 >> >> >> > [hidden email] >> >> >> > 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 >> >> >> [hidden email] >> >> >> 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 >> >> > [hidden email] >> >> > 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 >> >> [hidden email] >> >> 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 >> > [hidden email] >> > 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 >> [hidden email] >> 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 > [hidden email] > 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 [hidden email] https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
Free forum by Nabble | Edit this page |