B4J Question [ABMaterial] MySQL DB location questions

[Cableguy]

Hi guys and gals,

While witing for AB to release his tuto on how to do a self-contained/self deployable RPI ABM hardware/software solution, I want to start creating my ABM based app...

To do so I plan to use a kind of "Portable" B4J version, dedicated exclusively to this project, in a 1TB HDD encosure. This should be more than enough for development and testing purposes.

My question is, Where should I put/create my DB file and how would the path to it look like, since it would not be a fixed letter drive.

My goal is to be able to do all development in this drive and then just ZIP from there and UNZIP to the definitive deployement machine (typically a RPI 3B booting from HDD)

Sorry if this seems a stupid question, and I must confess that I havent even started the seting up, but wanted to start on a solid base idea.

Thanks

 

[Cableguy]

Hummm… Ok, so I will add to the scope..


The aim is to build a "in-house webapp server" based on a Raspberry Pi 3B booting from an HDD.
The MAX number of users will be…. drumm roll… 2, maybe 4, IN-HOUSE, Local LAN, at least as a starting point. Later on, if needed be, will scale it as needed.
BD Wise, I Don't see to much need for pooling, from the starting point, but again, if needed be, I may include that from ground zero.
It will be mainly a managegem webapp, so not that many connections/sec or /min, and the DB will also contain not that many tables, maibe at most 20, and a very few BLOBs…
So, this is the scope… my goal is to be able to develop all on one machine, DB included, and then just "zip it up" to the RPi…

Can it be done? Tips, Tools, etc are welcome!

 

[Harris]

Where? SQLite (or another embedded DB) would pretty much give him a "portable" solution, where he can easily move the database to a new location

Well, there you go... My limited experience and understanding of these portable solutions distort my impression. I always choose the path of least resistance - based on my own experience and the expert advice (discussions) of others (mine excluded from expert - only suggestions).

Many ways to skin a cat... Choose your method based on knowledge, advice and adventurous nature.

 

[DonManfred]

>mysqldump.exe -uUSERNAME -pPASSWORD --result-file="B:\backup.sql" DATABASENAME

 

[OliverA]

From my own, non-scientific testing, on an i3 with a traditional HDD (Windows 10 Pro), H2 using the H2 pooling performed best (outperformed SQLite, by a narrow margin) but used more memory (430-630MB) than SQLite (130-360MB). MySQL using either 3CP0 or Hikari as the pooling option performed horrible when using traditional HDD (by about a third of the other two options). On a Celeron with a SSD (Ubuntu), SQLite, H2 with pooling and MySQL with pooling had similar results (with a SQLite/H2/MySQL performance order) with varying memory usage (SQLite - 150MB, MySQL - 200MB and H2 600MB). HSQLDB is similar to H2, it's just a tad harder to use out of the box (it uses user GRANT concept).
Note: This was a pure write test, including the writing of an up to 40KB picture.
Note2: With the requirements posted above, MySQL would still work out if you are comfortable with that, it just does not copy as easy as you would like. Also, SSD's make a huge difference with MySQL (by an order of 3+ in my tests) and adds about 40% performance to H2 and SQLite, even though (in this case) the processor was weaker (Celeron vs i3).

 

[Harris]

Also, SSD's make a huge difference with MySQL (by an order of 3+ in my tests)

I bought 2 hosted VPS last Black Friday sale. One with standard hard drive and 1 with SSD.
The SSD is many times faster than the same Solid State HD. No hard facts but SSD updated Ubuntu in (5) minutes where HD took like an hour...

I never thought there could be such a difference. Whether MySQL, or any other disk intensive operation (ie. large web app)- SSD makes a real difference in serving it up!
Me thinks I shall upgrade my second VPS from HD to SSD.