Does anyone use jOOQ in some embedded IoT-context?

[Thorsten Schöning]

Hi all,

TL;DR:

Does anyone use jOOQ in some embedded IoT-context with ARM-based
hardware and only few MiBs of RAM at all?

Does anyone use jOOQ with e.g. Postgres/Oracle/... on some high
resources server and mostly the same codebase in somer lower resources
setup using less RAM, CPU-power and e.g. SQLite?

Does anyone use jOOQ with GraalVM and native images?

Thanks for your experiences!

Background:

I'm planning some special gateway to receive, store and forward
telegrams from various different smart meters[1]. Such a gateway
exists already and has proven to be somewhat limited, so "simply"
should be replaced with a version 2, doing conceptually the same
things, but with fewer technical restrictions.

The important thing to note is that I already have the server to which
version 1 of the gateway is sending telegrams right now. This means I'm
already able to store, parse, process etc. those telegrams in various
ways on the server and many of those things will exactly be needed the
same for version 2 of the gateway.

The important difference of course is resources: The server has
multiple CPUs and GiBs of RAM, while the new gateway is some ARM-based
thing with only 256 MiB of RAM currently. To make things even more
complicated, one version of that gateway might(!) be powerd by
battery only.

Nevertheless, reusing as much of the already available code base seems
to be abig benefit to me, to especially not need to develop the same
complex business logic multiple times. I've already put my server with
Tomcat and stuff 1:1 on some Raspi and things worked flawlessly as
expected, only the RAM-limit became a problem. And that's where
GraalVM native images and other downstripping becomes interesting.

[1]: https://en.wikipedia.org/wiki/Smart_meter

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Lukas Eder]

Hi Thorsten,

That's a very interesting setup, thanks for sharing. MBs of RAM. Reminds me of my first days with software / computers, on an 80286 with a whopping 4MB of RAM (and tens of MB of disk space!)

I can't give you any user insight on your questions, although, you might find some interesting insight and power user feedback regarding your third question about jOOQ with GraalVM here:

https://github.com/quarkusio/quarkus/issues/7440 and here: https://github.com/jOOQ/jOOQ/issues/8779

Using jOOQ on such low profile hardware is a bit of a risk. For a few reasons:

- jOOQ's DSL isn't very small in terms of byte code size. You might be able to reduce it using something like Proguard, throwing out unnecessary stuff. A future jOOQ-lite distribution might be helpful, too: https://github.com/jOOQ/jOOQ/issues/8928. But vanilla jOOQ seems to be quite heavy for this environment

- The runtime overhead of building *every* query dynamically might be too much. Our benchmarks hint at the StringBuilder effort being the biggest remaining bottleneck within jOOQ (if you ignore the much bigger work of the JDBC driver, execution planner, I/O, etc.). We have plans to migrate towards more immutability, and thus better possible caching of de-facto static SQL queries. But until then, keep in mind that currently, all jOOQ queries are constructed dynamically at runtime. You could obviously build a cache yourself, where a jOOQ query produces its SQL only once for the lifecycle of your application. Something like this: https://github.com/jOOQ/jOOQ/issues/8320

However, I think your setup is special, because you want to re-use your jOOQ-based code base between environments where jOOQ shines (your server), and where jOOQ is not yet as efficient as it could be. It would be interesting to be able to construct jOOQ queries either on this server, and then ship the query strings to your ARM-based environment (I'm aware of a customer who does this, for entirely different reasons), or use some way generate jOOQ queries at build-time, shipping the SQL statements in a file. In fact, I have thought about offering such tooling in the past. It would be really interesting to do all the SQL generation work at compile time, and then produce type safe JDBC (or R2DBC, etc.) wrapping logic, including the logic that maps query results to POJOs. Perhaps something similar to SQLDelight, but more powerful.

Would that be something like what you had in mind?

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/51732650.20201003105555%40am-soft.de.

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Sonntag, 4. Oktober 2020 um 14:16 schrieben Sie:

> https://github.com/quarkusio/quarkus/issues/7440 and here:
> https://github.com/jOOQ/jOOQ/issues/8779

These links read very interesting and are indeed what I hoped to find,
thanks!

> - jOOQ's DSL isn't very small in terms of byte code size.[...]

Which tells me it consumes a "lot of memory" on runtime even for
unused things, correct? Do you have an absolute number like 1 MiB or
10 or 100?

I really don't have any feeling about how much memory we talk here.
With my Raspi-powered PoC, I hosted Tomcat+Axis2 and stuff like that
and 128 MiB of RAM weren't enough anymore, but I didn't try e.g. 256
MiB RAM and Tomcat+Axis2 might not used as well.

> - The runtime overhead of building *every* query dynamically might be too

> much. [...] https://github.com/jOOQ/jOOQ/issues/8320

Caching would definitely make sense for some queries, especially the
ones simply storing telegrams.

> [...]It would be

> interesting to be able to construct jOOQ queries either on this server, and
> then ship the query strings to your ARM-based environment (I'm aware of a
> customer who does this, for entirely different reasons), or use some way
> generate jOOQ queries at build-time, shipping the SQL statements in a file.

If at all, the latter would better fit my use case, but it's difficult
to decide currently if some intermediate file format would be of any
benefit. Those files would need to be read as well and decided if to
keep them in memory or alike.

How would that compete with generating queries at startup time of some
device/app and caching jOOQ-instances for queries itself in memory?
The files in theory could be mapped into memory only and file system
cache would optimize things, while jOOQ- instances would always need
to be available in memory. Difficult and only worth it if really every
CPU-cycle or bit of RAM counts I guess.

> In fact, I have thought about offering such tooling in the past. It would
> be really interesting to do all the SQL generation work at compile time,
> and then produce type safe JDBC (or R2DBC, etc.) wrapping logic, including
> the logic that maps query results to POJOs. Perhaps something similar to
> SQLDelight, but more powerful.

> Would that be something like what you had in mind?

When it comes to optimization, yes. But as said, I'm not sure where I
need to optimize right now at all, e.g. CPU cycles or RAM or I/O to read
files or ... What I somewhat know is that my use case needs to INSERT
a lot and when it comes to reading, it's e.g. often about comparing
timestamps to decide if to INSERT at all or simply reading bulk
inserted binary blobs to forward them somewhere else.

So, I wouldn't benefit too much from reading into POJOs, but caching
my queries should really be possible, as they are not too dynamic at
runtime at all.

Whatever tooling you have in mind, it might be a good idea to consider
things like GraalVM native images. From my limited knowledge, that
seems to execute static code or alike during the generation and that
might be used by jOOQ as entry point to generate queries and cache
them in memory.

[Lukas Eder]

Hi Thorsten,

On Mon, Oct 5, 2020 at 1:08 PM Thorsten Schöning <tscho...@am-soft.de> wrote:

> - jOOQ's DSL isn't very small in terms of byte code size.[...]

Which tells me it consumes a "lot of memory" on runtime even for
unused things, correct?

Java's class loaders are lazy, so unused things might not consume a lot. I don't know your environment. I don't know the impact of jar file sizes there. Just wanted to point you to some things to look out for.

Anyway. Do check out Proguard and similar tools. They're designed for such cases. Also, the jOOQ lite distribution would really be useful to you, I think (Again, https://github.com/jOOQ/jOOQ/issues/8928 for reference). The lowest hanging fruit is to remove the @Support annotation from the byte code. It already produces 68kb. Then, all the Record1 - Record22 types aren't really needed. Perhaps, we keep Record1, which is useful because single-column queries are special, e.g. for IN predicate usage. But all the rest isn't needed.

If this is something you're interested in, I'm happy to discuss a sponsored project to expedite this.

 

Do you have an absolute number like 1 MiB or 10 or 100?

Would that really be meaningful to you? 1 MiB of what? Maximum memory usage? Maximum TLAB usage? Maximum GC throughput per second? Maximum class memory usage (permgen in old Java versions)? We don't have such numbers because any number would depend on so many things that you do, that we can't guess / assume...

With an environment like yours, I'm pretty sure you will need to run continuous memory consumption tests along with your integration tests and keep an eye out for outliers of anything, including your own memory usage. A rogue HashMap can kill your device if it implements a cache that you're unaware of during dev time.

 

I really don't have any feeling about how much memory we talk here.
With my Raspi-powered PoC, I hosted Tomcat+Axis2 and stuff like that
and 128 MiB of RAM weren't enough anymore, but I didn't try e.g. 256
MiB RAM and Tomcat+Axis2 might not used as well.

Tomcat does a lot of things. Do you really need a container?

 

> [...]It would be
> interesting to be able to construct jOOQ queries either on this server, and
> then ship the query strings to your ARM-based environment (I'm aware of a
> customer who does this, for entirely different reasons), or use some way
> generate jOOQ queries at build-time, shipping the SQL statements in a file.

If at all, the latter would better fit my use case, but it's difficult
to decide currently if some intermediate file format would be of any
benefit. Those files would need to be read as well and decided if to
keep them in memory or alike.

The alternative is to use SQL string constants which are kept in bytecode and would also consume memory. But again, I think you should measure things and check out specific bottlenecks. I'm sure there are lower hanging fruit than stored SQL strings.

 

How would that compete with generating queries at startup time of some
device/app and caching jOOQ-instances for queries itself in memory?

The jOOQ query instance probably consumes more memory than the SQL string itself (there's a lot of overhead with object headers alone). Also, pre-generating the SQL string optimises CPU, which you probably don't have abundantly either.

 

The files in theory could be mapped into memory only and file system
cache would optimize things, while jOOQ- instances would always need
to be available in memory. Difficult and only worth it if really every
CPU-cycle or bit of RAM counts I guess.

Hard to say. Measure! :)

 

> In fact, I have thought about offering such tooling in the past. It would
> be really interesting to do all the SQL generation work at compile time,
> and then produce type safe JDBC (or R2DBC, etc.) wrapping logic, including
> the logic that maps query results to POJOs. Perhaps something similar to
> SQLDelight, but more powerful.

> Would that be something like what you had in mind?

When it comes to optimization, yes. But as said, I'm not sure where I
need to optimize right now at all, e.g. CPU cycles or RAM or I/O to read
files or ... What I somewhat know is that my use case needs to INSERT
a lot and when it comes to reading, it's e.g. often about comparing
timestamps to decide if to INSERT at all or simply reading bulk
inserted binary blobs to forward them somewhere else.

So, I wouldn't benefit too much from reading into POJOs, but caching
my queries should really be possible, as they are not too dynamic at
runtime at all.

Whatever tooling you have in mind, it might be a good idea to consider
things like GraalVM native images. From my limited knowledge, that
seems to execute static code or alike during the generation and that
might be used by jOOQ as entry point to generate queries and cache
them in memory.

Yes, that GraalVM feature will definitely be investigated in the near future.