Issue Details (XML | Word | Printable)

Key: CORE-3642
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Arioch
Votes: 2
Watchers: 2

If you were logged in you would be able to see more operations.
Firebird Core

In-memory database - opening read-only database from memory rather than from file in FB Embedded

Created: 24/Oct/11 08:27 AM   Updated: 24/Oct/11 11:29 PM
Component/s: API / Client Library
Affects Version/s: 2.5.1
Fix Version/s: None

 Description  « Hide
Replacing application-internal data structures with some data warehouses has a long history.
Once it was in-memory ISAM tables with manual sorting/indexing
As CPU/memory increased, solutions like generic SQLite(having 'in-memory' mode) or language-specific NexusDB/sqlMemTable appeared.

Latter has drawback that when new Language version is rolled out they usually lag behind for quite a while.
SQLite is popular, but not as feature-rich as FB and language-specific access components might be worse for some languages (especially Delphi lineup) than for FB.

However generally converting file-based database into memory-based would be too complex goal to worth doing/asking for. But not now, i feel.

Global Temporary Tables were introduced into FB quite ago. And with 2.5.1 release GTTs can be used fully even in generally read-only database.
So all the code for in-memory tables, indexes, views etc is in place already.
Metadata can't be changed but it arguably may appear 'right thing' for the in-memory database: not using 'db generator script' but using ready-made database file with ready-made data access components bindings, PSQL, etc. Also this aligns well with Windows restriction that in-memory file should have predefined length and never grow (if implementation would use file handles API for bootstrapping rather than fully separate file class, this easy way is okay probably, since 'tis only needed to read headers once on r/o database).

What i feel is left to be implemented:
1) database file opening from memory in FB Emb
1.1) either only-r/o databases
1.2) or every database, if opened 'in-memory' should be treated effectively r/o
All the actual work is to be shared between persistent VIEWS/TRIGGERS/PROCEDURES

2) bonus: UDF support in-memory. Not via extra files but again via function pointers
Nice thing, though not necessary, and can be done anytime later.

3) bonus: EVENTs posting via common connection stream (X-NETc in-memory stream for FB-Emb), rather than separate TCP connection.
This can start as FB Emb optimization (TCP requirement for an application to talk to itself is curious artifact) and later generalized into FB-generic enhancement (as done in IB7.1sp1).
While this may made diagnostic sometimes more complex, it would made system configuration a bit easier
This change can be done anytime later as well.

4) Lock logic. IF application tries to use 2-connections scheme, or application has plugins that might open there own connection to database - should there be locks ? Or should in-memory DB be forced to single exclusive connection ? Faking filename to something like "/\memory@12345ABCDpid1234/\" would probably warrant any currently possible filename still usable and Lock id unique enough to allow non-exclusive connections.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 24/Oct/11 10:27 PM
You have mixed several separate topics (in-memory, UDF, EVENTs and Lock logic) into a single case -- very bad form. Separate case should be opened for each topic.

As for the general notion expressed in this feature request. Personally, support for in-memory databases will provide little value-add for my applications.
1 - All my database would have to have on-disk brothers, I can't have the database "disappear" if the server is restarted.
2 - The time to load the database into memory would minimize the benefit of having an in-memory database.
3 - Most of my databases are much larger than the RAM most servers can support today without expensive 16GB DIMMs.

There are more crucial performance features (which would benefit all platforms) which would be necessary in order for a true in-memory solution to be maximized:
1 - Smarter GROUP BY handling
2 - multi-threaded SORT
3 - multi-threaded NATURAL SCAN

Arioch added a comment - 24/Oct/11 10:46 PM
If the idea would be accepted, then the topic might be turned into umbrella, with sub-tickets for each issue.
But beforehand i think it's worth to discuss the topic in general.

That means your applications are of "lots of data, needs very fast access" kind.
Those that sometimes get noSQL approach like BigTable maybe, TeraData, etc.
Those that definitely are not covered with SQLite.
And the only reason to have database fully in memory would be for extra-fast access. Faster than caching can provide.

But my topic is more about SQLite sweet spot - replacing variables/structures for relatively small amount of data, given comfort of declarative SQL assignments, declarative queries and built-in constraints checking. All that otherwise would be coded in language against arrays/lists/hashmaps/etc.
Speed of execution is not the goal there obviously, since we go away from specific data structures with direct access.
Comfort (declarative UPDATEs instead of loops) and reliability (COSTRAINTS and FKs) of development is.
And ability to work without temporary files, that tends to be orphaned and clutter the disk ;-)

Adriano dos Santos Fernandes added a comment - 24/Oct/11 11:16 PM
Put your database in ramdisk, put your temp files in ramdisk.

I see no value in this feature request.

Arioch added a comment - 24/Oct/11 11:22 PM - edited
Desktop application asking Average Joe to install RAM Disk and configure itself where to find it?

Do you think Firebird is only to be used on big irons with gigabytes of data ?
Do you think Firebird should not fit where SQLite does ?

Adriano dos Santos Fernandes added a comment - 24/Oct/11 11:29 PM
Have you considered implement this yourself, in the right way, modularizying things and make, say, plugable disk storage? My +1 for it if it's your intention.

After all, this is opensource, like sqlite. Anyway, tracker is not the better place to discuss raw ideas. For that there is firebird-devel and firebird-architect lists.