Note: The db usage in my case may not be typical.
did not work well for me:
administration tools are not user friendly
could not quite figure out how to load data from a file
Nice adminstration tools
Deployment is very simple and well explained in documentation
Speed: in my case slower than MySQL
* Preferered choice in my case
Very nice db, but deployment is rather heavy
I was considering HSQLDB as my primary choice of in memory database. After reading its history on http://en.wikipedia.org/wiki/HSQLDB :
HSQLDB is a relational database management system written in Java. It is based on Thomas Mueller's discontinued Hypersonic SQL Project.[1] He later developed H2 as a complete rewrite.
I decided to give H2 a try. Bellow is the benchmark from the H2's website, which looks quite nice. Hopefully my application will achieve similar results.
My application. I am currently running my algorithm on the top of Taste recommender engine using MySQL. A complete set of experiments for my algorithm and existing to produce results takes 3 days on a Core2 Duo machine. I need to try more than 20 different settings = 60 days. So I decided to try to speed it up. In addition I don't have permission to install db on the supercomputing cluster at my university so in memory db hopefully will solve both of my problems.
Since memory's I/O is much faster than disk I/O this should translate into significant speed up (assuming your db can fit in memory)
Could be run in embeded mode -- all you need is java and you can run it locally inside of your application (no additional sotware needs to be installed on the server etc.).
Note: I am using this setup for explorative learning algorithm; it is not a typical usage of the recommender system.
Single pass: 138 - 3,819 ms (depending on configuration)
# Create Table
CREATE TABLE taste_preferences (
user_id VARCHAR(10) NOT NULL,
item_id VARCHAR(10) NOT NULL,
preference FLOAT NOT NULL,
time_stamp VARCHAR(10),
PRIMARY KEY (user_id, item_id)
)
# Create Indexes
CREATE INDEX IDX_USER_ID ON TASTE_PREFERENCES ( USER_ID )
CREATE INDEX IDX_ITEM_ID ON TASTE_PREFERENCES ( ITEM_ID )
CREATE INDEX IDX_PREFERENCE ON TASTE_PREFERENCES ( PREFERENCE )
# Load data from file
INSERT INTO TASTE_PREFERENCES SELECT * FROM CSVREAD('/home/neil/tmp/u.txt');
see AL_CF.java for more details
Some parts of code are not well documented; the javadoc seems to be out of sync. Had to browse for this one for a while, untill found it.
// Create DB Connection
// H2
String driverName = "org.h2.Driver";
String url = "jdbc:h2:~/test";
String user = "sa";
String pwd = "";
org.h2.jdbcx.JdbcDataSource db = new org.h2.jdbcx.JdbcDataSource();
db.setUser(user);
db.setPassword(pwd);
db.setURL(url);
java.util.NoSuchElementException: Can't retrieve more due to exception: org.h2.jdbc.JdbcSQLException: The result set is not scrollable and can not be reset. You may need to use conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY). [90128-66]
Changed com.planetj.taste.impl.model.jdbc.AbstractJDBCDataModel
* NEIL -- Modified ResultSetUserIterator to fix the following Exception:
* TODO: Do it in a better way
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement INSERT INTO TASTE_PREFERENCES SET[*] USER_ID=?, ITEM_ID=?, PREFERENCE=? ON DUPLICATE KEY UPDATE PREFERENCE=? ; expected ., (, DEFAULT, VALUES, (, SELECT, FROM; SQL statement:
INSERT INTO taste_preferences SET user_id=?, item_id=?, preference=? ON DUPLICATE KEY UPDATE preference=? [42001-66]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.getSQLException(Message.java:93)
at org.h2.message.Message.getSyntaxError(Message.java:103)
at org.h2.command.Parser.getSyntaxError(Parser.java:454)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1445)
at org.h2.command.Parser.parseSelectSub(Parser.java:1366)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1249)
at org.h2.command.Parser.parseSelect(Parser.java:1237)
at org.h2.command.Parser.parseInsert(Parser.java:826)
at org.h2.command.Parser.parsePrepared(Parser.java:343)
at org.h2.command.Parser.parse(Parser.java:265)
at org.h2.command.Parser.parse(Parser.java:241)
at org.h2.command.Parser.prepareCommand(Parser.java:209)
at org.h2.engine.Session.prepareLocal(Session.java:213)
at org.h2.engine.Session.prepareCommand(Session.java:195)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:970)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:1206)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:161)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
at com.planetj.taste.impl.model.jdbc.AbstractJDBCDataModel.setPreference(AbstractJDBCDataModel.java:420)
at com.planetj.taste.impl.recommender.AbstractRecommender.setPreference(AbstractRecommender.java:81)
at org.hrstc.taste.al.AL_CF.evaluate(AL_CF.java:243)
at org.hrstc.taste.al.AL_CF.main(AL_CF.java:80)
Solution:
INSERT INTO TASTE_PREFERENCES SET USER_ID='22', ITEM_ID='378', PREFERENCE='5.0' ON DUPLICATE KEY UPDATE PREFERENCE='5.0'
Was giving the above exception. Replaced it with:
INSERT INTO TASTE_PREFERENCES (user_id, item_id, preference) VALUES ('22', '378', '5.0') ON DUPLICATE KEY UPDATE PREFERENCE='4.0'
Then ... ON DUPLICATE KEY UPDATE PREFERENCE='4.0' part was not a standard SQL syntax (perhaps specific to MySQL).
A better way would be to use an ANSI/ISO standard command MERGE ( instead of other db specific variants ) e.g.:
MERGE INTO TASTE_PREFERENCES(user_id, item_id, preference) key(user_id,item_id) VALUES ('22', '378', '4.0')
wrote H2JDBCDataModel.java
I finaly got H2 to run (most of the changes where migrating incompatible SQL from MySQL to standard SQL)
The performance of it was rather slow:
INFO: It took ms: 2,119,766
Used memory-only mode http://www.h2database.com/html/features.html#memory_only_databases
INFO: It took ms: 140,568
Thats a 10 fold improvement
Let JVM use more memory; let db use more memory
Did not help
Installed TPTP for eclipse but does not work; could not figure out why
Using NetBeans to do profiling; is throwing some of the old error for some reason.
Fix: The problem with error was that class in jar was not correctly overwritten by NetBeans
For some reason when running from NetBeans userNeighbourhood.size = 0; but when running from command line the same files it is not
Fix: The problem with error was that class in jar was not correctly overwritten by NetBeans
Surprisingly the peformance of MySQL MyISAM and MEMORY engine are almost identical.
Performance of H2 is 10x slower than MySQL. The difference of code between two implementations is that for
H2: MERGE INTO
MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INFO: It took ms: 20,574
Mar 24, 2008 11:05:09 AM org.hrstc.taste.al.AL_CF <init>
INFO: log running..
943
Mar 24, 2008 11:05:11 AM org.hrstc.taste.al.AL_CF getTestUsers
INFO: Selected userID: 421
Mar 24, 2008 11:05:11 AM org.hrstc.taste.al.AL_CF evaluate
INFO: AL Type: random
Mar 24, 2008 11:05:13 AM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}]
Mar 24, 2008 11:05:13 AM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 1577
Mar 24, 2008 11:05:34 AM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}, {MAE=0.8614681}]
Mar 24, 2008 11:05:34 AM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 20574
CREATE TABLE `taste`.`taste_preferences` (
`user_id` varchar(10) NOT NULL,
`item_id` varchar(10) NOT NULL,
`preference` float NOT NULL,
PRIMARY KEY (`user_id`,`item_id`),
KEY `user_id` (`user_id`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INFO: It took ms: 19,781
Mar 24, 2008 2:55:58 PM org.hrstc.taste.al.AL_CF <init>
INFO: log running..
943
Mar 24, 2008 2:56:00 PM org.hrstc.taste.al.AL_CF getTestUsers
INFO: Selected userID: 421
Mar 24, 2008 2:56:00 PM org.hrstc.taste.al.AL_CF evaluate
INFO: AL Type: random
Mar 24, 2008 2:56:02 PM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}]
Mar 24, 2008 2:56:02 PM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 1522
Mar 24, 2008 2:56:22 PM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}, {MAE=0.760892}]
Mar 24, 2008 2:56:22 PM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 19781
Mar 24, 2008 2:56:38 PM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}, {MAE=0.760892}, {MAE=0.86437464}]
Mar 24, 2008 2:56:38 PM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 16243
CREATE TABLE `taste`.`taste_preferences` (
`user_id` varchar(10) NOT NULL,
`item_id` varchar(10) NOT NULL,
`preference` float NOT NULL,
PRIMARY KEY (`user_id`,`item_id`),
KEY `user_id` (`user_id`),
KEY `item_id` (`item_id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1
INFO: It took ms: 214,899
Mar 24, 2008 3:01:21 PM org.hrstc.taste.al.AL_CF <init>
INFO: loaded data
943
Mar 24, 2008 3:01:23 PM org.hrstc.taste.al.AL_CF getTestUsers
INFO: Selected userID: 421
Mar 24, 2008 3:01:23 PM org.hrstc.taste.al.AL_CF evaluate
INFO: AL Type: random
Mar 24, 2008 3:01:24 PM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}]
Mar 24, 2008 3:01:24 PM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 1865
Mar 24, 2008 3:04:59 PM org.hrstc.taste.al.AL_CF evaluate
INFO: User's Stats:
[{MAE=5.0}, {MAE=0.7894972}]
Mar 24, 2008 3:04:59 PM org.hrstc.taste.al.AL_CF evaluate
INFO: It took ms: 214899
Starting DB: java -cp ./lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb
java -cp ./lib/hsqldb.jar org.hsqldb.util.DatabaseManager
keywords: hsqldb load data file csv
Use Text Table ; also see src/org/hsqldb/sample/load_binding_lu.sql
Error: table not found in statement [SET TABLE SOURCE] / Error Code: -22 / State: S0002
Possible Reason: Text Tables cannot be created in memory-only databases (databases that have no script file).
Tried example from src/org/hsqldb/sample/load_binding_lu.sql
CREATE TEXT TABLE binding_tmptxt (
id integer,
name varchar(12)
);
Error: Database is memory only in statement [CREATE TEXT TABLE binding_tmptxt] / Error Code: -63 / State: S1000
Solution: this statement is not allowed for in memory database; start server db instance
When trying sudo a server [org.hsqldb.util.DatabaseManager] get the following error:
java.sql.SQLException: socket creation error
Solution: none
Performance of H2 is 10x slower than MySQL. The difference of code between two implementations is that for
H2: MERGE INTO
MySQL: INSERT ... ON DUPLICATE KEY UPDATE
Changed it but did not make a difference.
Disabled/Enabled Connection pooling but did not produce significant affect either.
Since performance still is not good rewrote code for the methods that take too long; and wrote new classes optimized for my task as seen in the next blog posting ....
http://www.h2database.com/html/features.html#trace_options
TRACE_LEVEL_SYSTEM_OUT=3
Do also java code generation; this way can benchmark it against MySQL and see where the problem is.
Turn on logging finest
See performance for my AL method and optimize for it - since it is the slowest one anyway.
If performance still is not good may need to rewrite code for the methods that take too long; or write new classes optimized for my task
For example: may compute user neighborhood only for the specific items; since I need to get estimates for only a few items, etc.
HSQLDB memory mode
MySQL's MEMORY (HEAP) Storage Engine http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
Comments
jxCzCTqmaoiDnDe
hi gay, , remeron drug, mwtjxj, azithromycin tablet, hveutk, fluoxetine hcl, kzdbnr, buy flomax, zqcs, cheap diazepam, >:-), buy valium online, qxaw,
nuTnOlUxDm
hi down town, , buy epivir, zxxpbm, symmetrel 100mg capsule, %(((, combivir, 7126, clindamycin, >:], ofloxacin, ikk, Cefaclor, >:-),
yAixpqEnMxUO
hi gay, , buy remeron, 010, order Voltarol, 875, buy adipex online, =]], buy ambien, %-PP, alprazolam, mpmsn, order xanax, %D,
vsoXdqmJGTOiUezQ
hi bro, , Buy Clarinex, 562431, order wellbutrin, =-)), buy Claritin, >:)), order zyban, =OO, drug zyrtec, >:), Anafranil, 94126,
WDSyAymsxqY
hi evrybody, , generic claritin, 623, buy Anafranil, 5591, buy Desyrel, hiy, buy Dilantin, 3070, drug zyrtec, 769995, elavil, 6582,
pwuJtYdhEjAXDvGdq
hi usvers, , buy remeron, 680, order Voltarol, hee, adipex online, ruz, generic ambien, %-]], cheap alprazolam, >:-(((, xanax online, 676,
pVcJVcfwPvHKzjLn
hi my darlings, , hytrin side effects, >:((, lithobid, pul, buy lipitor online, 264781, buy lotrel, qfu, Buy methotrexate, =-))), mircette birth control, 024897,
OGvQBCyHfTKOspBxWt
hi gay, , celexa cheap, %]], cozaar, jvezd, Serophene, 684, depakote buy, 408513, Buy Deltasone, 988006, Tolterodine, kym,
CDflPzruxfuWoWiX
hi usvers, , apo furosemide, pcjt, hytrin drug, %-D, discount glucophage, 0884, keflex, =]], generic lotrel, ick, mircette birth control pill, :((,
alFjYevrGuzHw
hi bro, , xanax, tkyis, diazepam online, 557561, order viagra, usfkf, cheap carisoprodol, =))), buy cheap phentermine, 8-[[[, soma online, kdxiht,
WxFYgJzJLAdtPef
hi gay, , nexium price, %((, norvasc medication, 1795, buy nolvadex online, 01619, buy nizoral, 8-OO, 40mg omeprazole online, 695552, plavix clopidogrel, 4232,
hEvpHhxuRvRUfxjVot
hi evrybody, , Buy dapsone, 8(, fosamax online, 23055, actos online, raqi, alesse 28, 6946, amaryl buy, =-], cardizem, 673442,
wqVmEcFMowxYHMv
hi evrybody, , diclofenac sod, psqmu, Piroxicam, 3223, buy Indomethacin, 8-(, motrin 800mg, thmi, mefenamic, >:-)), famvir medication, =]],
FeYAzNZVGdE
hi evrybody, , buy Selegiline, 4707, nimotop 30mg capsule, djvgdf, buy cheap persantine online, 38183, rogaine for woman, %-]]], diclofenac sod, cxdxtw, cheap imdur, =(((,
xQsJNgtqQi
hi bro, , Claritin, >:-P, anafranil 25mg tablet, :(((, generic Desyrel, >:-[[[, dilantin online, 739, Zyrtec, 64181, elavil, 602,
pFioyipLjeCWBQ
hi bro, , Coversyl, 408, buy Hyzaar, cjxh, propranolol 40mg reviews, 8]], buy Benazepril, %D, buy lozol, 8DDD, coreg cr, >:DDD,
XKzNdltPDhJXUx
hi bro, , pantoprazole 40mg, 8-], zantac 150, 857, sumatriptan 50mg tablet, ugub, Verapamil, 405, buy mestinon, =-DD, buy Reglan, >:),
sYgOJqWXFpQBOOr
hi usvers, , buy cialis, 0443, buy propecia, 8-))), buy Desyrel, 75276, nortriptyline for chronic pain, 798885, buy minocycline, =-D, buy Myambutol, auy,
nEaRPWreYeXmlqB
hi down town, , Buy Hytrin, azlzj, lithobid, :OO, generic lipitor, 42518, lotrel side effects, 8634, methotrexate, gcsldh, mircette birth control pill, :-O,
NavSwgZsEMxOvzKzs
hi my darlings, , Buy dapsone, %-D, Alendronate, 97483, Pioglitazone hlc, %(, ethinyl estradiol, wlwty, amaryl glimepiride, bfl, cardizem 180mg, mhfih,
KIBDRpBpmAlmnxJSx
hi bro, , voltaren tablet, =-]], buy valtrex online, >:D, benzoyl peroxide cream, %(((, Accutane, jit, buy Duphaston, :-PPP, estrace tablets, 9428,
IoFBCGakpmapvPK
hi my darlings, , generic pyridostigmine, 53045, dapsone, =), buy buspar, fsochn, buy amoxil, vuqrim, albuterol, nld, avandia drug, >:-P,
iFtLzAVUhEYqlDf
hi my darlings, , dapsone, =-[[, Alendronate, 671, purchase actos, 862101, estradiol, =-)), cheap amaryl, 8D, diltiazem hcl, 440202,
NcYPLxonGyTklTaXvm
hi down town, , Pravastatin, hmuhfu, pilocarpine hcl, xhd, lactulose medication, =OOO, sucralfate buy on-line, pgez, imodium to get high, aww, buy Pepcid, :))),
RebQBwTzxWVAIBRNsJV
hi my darlings, , pantoprazole 40mg, keht, zantac 75, 750, sumatriptan succinate, :-), Verapamil online, >:OOO, mestinon pyridostigmine bromide tablets, uoz, Metoclopramide, pzvvny,
akipzJKUlWlIfOMrM
hi evrybody, , Rivastigmine, 1166, imuran medication, ryvujp, nimotop 30mg capsule, 2697, generic rogaine, enbiq, dipyridamole 50mg tablet, 3387, Celecoxib, 7631,
vVdxfabOtjakpyHFgut
hi my darlings, , valtrex online, %)), estrace tablets, klr, Naprosyn, %((, alesse birth control pill, ybz, bactroban ointment, jabfy, Buy Augmentin, cyq,
YZwllhqVPBPf
hi my darlings, , hydrocodone, >:-[[, buy adipex online, 8DD, ativan lorazepam, znbcml, ambien, >:[[[, purchase ephedra, 606, buy alprazolam, %DDD,
ToqJOuYNgk
hi evrybody, , mirtazapine 15 mg, >:))), azithromycin, jefwrb, fluoxetine 20mg, 224, flomax, 05292, buy diazepam online, 611, buy valium online, kklp,
hRfTfYBRkjMmQzp
hi bro, , allegra, 774, Buy allopurinol, 378, 150 avapro mg, =-P, amoxil side effects, 074, albuterol inhaler, tuo, adalat xl, 267,
rWgzkGBiTEZnfIP
hi bro, , remeron, tmarma, buy azithromycin, 8[, fluoxetine 20mg, kwpcv, flomax medicine, ljjnnt, cheap diazepam, 98947, buy valium online, 3139,
MoRaLZRHKbhCvqo
hi down town, , hydrocodone, %PP, adipex, >:[[, ativan online, =], ambien, rpag, legal ephedra, gjp, cheap alprazolam, %PPP,
pkTmucMmTziNQpB
hi bro, , cialis online, 8], buy propecia, swkqux, buy online vigrx, %-OOO, wellbutrin, =-))), zyban, >:O, Clarinex, 838514,
hwPMlAmVVRzEDEBYP
hi gay, , buy celebrex, 38179, buy aciphex, 77451, allopurinol, 662, Nifedipine, rpo, generic avandia, 8], asacol effects side, naj,
VPYIWKjuQwDXaFgI
hi evrybody, , Zebeta, %[[[, Hydrea, >:]], buy Amiodarone, =], Warfarin, 72708, Lanoxin, 63901, buy Mevacor, 31056,
zFkrecjgxSXZKxzysGW
hi evrybody, , cialis online, =[[[, buy propecia, =[, vigrx, 588645, buy wellbutrin, 8-[[, cheap zyban, =-D, Clarinex, 713,
zNYTSaqKulg
hi gay, , cheap xanax, rnwote, buy diazepam online, bcy, buy viagra, djmnhh, buy carisoprodol online, 244, cheap phentermine, %-OO, buy soma online, 72469,
hrtYxNvdEVPeqD
hi evrybody, , buy tramadol now, tht, buy meridia, ljl, buy lorazepam online, uzem, norco online, 903591, generic ultram, 062, generic fioricet, :-P,
VXsDEiZnGSoeNiTIXnP
hi bro, , hydrocodone, :(, adipex online, 682, ativan lorazepam, 8437, ambien online, 8((, ephedra, %-O, cheap alprazolam, 404,
rwCzcaxyDhfsmrBk
hi gay, , cialis generic, qdyht, buy propecia, 727064, vigrx pill, 297, buy wellbutrin, 386013, order zyban, 95029, Clarinex, 600,
LNaxVpEPpbt
hi usvers, , biaxin xl, %)), cozaar, 3271, depakote er, uamayt, female viagra alternative, 8-D, furosemide oral, 605, buy metformin, =-))),
FbNHNnfRQPlFyR
hi down town, , generic Effexor, 9578, fluvoxamine, 8-]]], buy Nortriptyline, keme, buy cheap fludac, zkm, paxil attorneys california, 284580, buy Pamelor, =-]],
MHZONlnNaasuYztV
hi down town, , diclofenac voltaren, ieyntq, order valtrex online, brexro, benzoyl peroxide acne treatment, hefyp, cheap accutane, 77298, dydrogesterone tablets, bcrms, Estradiol, iqe,
LzwJromvIZcDNkQy
hi down town, , voltaren drug, 61068, valtrex price, >:(, benzoyl peroxide acne treatment, 8465, buy isotretinoin, =-D, dydrogesterone tablets, nxai, Estrace, ryefa,
klCIhKQLgMM
hi my darlings, , prevacid 30mg, 513, buy Reglan, >:], sumatriptan succinate, cte, verapamil er tab, %OOO, buy Bicalutamide, yuh, mestinon, =-PPP,
kWrhgdYQIhb
hi usvers, , avandia drug, oayr, buy avodart, etvkmd, mesalamine drug, >:-((, biaxin 500mg, 53366, bactrim ds, 910242, cipro xr, 20292,
iCmxDsEfbkojZeyxCgv
hi bro, , hoodia diet pill, >:[[, norco viagra, 340672, buy ultram online, 8(((, ultracet tramadol, jowa, klonopin xanax, mlejwp, order propecia, 46912,
DsKcbTeKqdYVlileQ
hi down town, , buy cialis online, aeeogr, propecia, 40954, desyrel 100mg tablet, 310, Nortriptyline, vmvov, Minomycin, iths, buy Myambutol, 232376,
EVOAFYDqqvlZtX
hi evrybody, , voltaren tablet, 085419, order Valtrex, sgyi, benzoyl peroxide shampoo, >:((, buy Accutane, 8-), buy Duphaston, ilxwb, Estrace, %-),
nMGATIAaTw
hi down town, , apo furosemide, olv, hytrin side effects, 128, metformin, 239, keflex 500mg, 687, generic lotrel, efj, mircette overnight, shffbx,