B4J Question [ABMaterial] MySQL DB location questions

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!

OliverA said:

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

Click to expand...

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.

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

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).

OliverA said:

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

Click to expand...

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.