Database caching

[scott-materials]

I've been reading about memory caching. Redis looks complicated but memcached should be easy to set up and is invisible to a general user. It is naturally multithreaded. This might be worth thinking about when we get back to evolutionary algorithms and we are concerned about throughput on fingerprints.

[jacksund]

I'm in the middle of learning a bunch about this! Looks like in the world of cheminformatics, companies take one of two strategies:

1. build in-memory databases that are super fast (so Redis or something similar like you suggest)
2. register custom functions to the database engine (postgres)

The first is close to what you're suggesting and I think would be a great short-term solution -- although more expensive and error prone (bc of another running service). I agree it's worth trying out and reading into though.

The second is something I need to learn more about, but I think will end up being the best long-term solution for materials fingerprints. Right now in Simmate, the bottleneck is transferring fingerprint data to each worker process. Meanwhile, comparing fingerprints (cosine/euclidean distances) is super cheap and fast. So for molecular systems where the scale is 10's of millions of molecules and fingerprints, they do the following:

1. store the fingerprints in the database
2. add a database index so data is quickly accessed/iterated
3. register a comparison function (written in C++) that looks like get_distance(fp1,fp2) that returns a distance value
4. fit that function to an operator in the database with (e.g. @) with some default distance tolerance
5. run queries in the database like SELECT id FROM my_fp_table WHERE m @< my_fp_123 (returns the ids of all structures that have a fingerprint distance less than some value)

This eliminates the transferring of fingerprints to other computers -- and instead just has the database run fingerprint comparisons itself. This takes ~100ms for >30mil fingerprints, so it would work great for our application.

The challenge here is just learning steps 3 and 4! It'd be a super short script (<200 lines) but would probably be really challenging for us because we don't know C++! If you ever run into a potential collaborator that codes in that language, it might be worth looping them in

[scott-materials]

Very interesting. I hadn't contemplated running it on the cloud server itself. It makes sense that this would be robust and fast.

Can you envision other calculation tasks (beyond fingerprint comparison) where something like redis would be preferred? I guess the primarily disadvantages of a registered function are:
(1) initial programming,
(2) possible incompatibility with sqlite(?) Although no one would have used sqlite for an evolutionary search.
(3) transient spikes in database server utilization (cpu mostly),
(4) difficulty in bundling with simmate(?)
(5) tasks that are more demanding than get_distance should be shipped off to the worker, in which case redis might be preferred if the same data is being repeatedly shipped off.

None of those are terribly significant, and redis wouldn't address most of these issues. I could certainly look into hiring a c++ programmer if this approach looks best. It does sound promising.

[jacksund]

1. This is my biggest roadblock haha. But since we just need simple comparison functions, hiring a freelancer is a good idea!
2. It looks like postgres has built-in methods for adding functions in SQL or C (here), while sqlite doesn't (here). I don't have a full grasp for how these would be different, but it'd certainly be a separate SQL call or method for each backend. Requiring evo-search users to use postgres is already something I do, so it's fair to say we only implement functions for postgres.
3. I think the CPU spikes will still be much better than they are now, but yeah, eventually CPU use on the database will be too much.
4. I'm not 100% sure on this, but I think it will be pretty simple. It looks like you can register the function with a single postgres call, so we could make this call when we first build the database tables.
5. are there other functions you're thinking of here? I don't think we'll have anything besides get_cos_dist(), get_euc_dist(), etc.

[scott-materials]

5. No tasks that I have in mind yet. But who knows where things will go?

[scott-materials]

@jacksund Are you familiar navigating the process of finding a developer and proposing the project to them? If you want to navigate this, I’ll pay for the developer.

[jacksund]

I've never had to hire someone for something like this, so I'm not sure...

Since this is a small feature, my initial guess is that either an "open-source bounty" or freelancer is the way to go. But bounty's seem like a shot-in-the-dark and could go unnoticed for months. For freelancers, I'm guessing the challenge will be with finding the right person.

Another (probably more promising) idea to reach out to someone that's done this stuff before. Even if they don't typically freelance, I'm sure they'd be happy to help in exchange for pay. Rather than mentioning those developers in this thread, I'll email you some suggestions, and you could contact them directly.

[scott-materials]

@jacksund see my reply on email.