Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

In-memory database - opening read-only database from memory rather than from file in FB Embedded [CORE3642] #3993

Open
firebird-automations opened this issue Oct 24, 2011 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Arioch (arioch)

Votes: 2

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Arioch (arioch)

description: Replacing application-internal data stractures 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 to complex goal to worth doing/asking for. But not now, i feel.

General 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.
Metadata can't be changed but it arguably may apeear 'right thing' for the in-memory database: not using 'db genearator script' but using readymade database file with ready-made data access components bindings. Also this aligns well with Windows restriction that in-memory file should have pre-defined length and never grow (if implementation would use file handles API for bootstrapping, 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 pened '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 artefact) 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.

=>

Replacing application-internal data stractures 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 to complex goal to worth doing/asking for. But not now, i feel.

General 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.
Metadata can't be changed but it arguably may apeear 'right thing' for the in-memory database: not using 'db genearator script' but using readymade database file with ready-made data access components bindings. Also this aligns well with Windows restriction that in-memory file should have pre-defined length and never grow (if implementation would use file handles API for bootstrapping, 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 pened '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 artefact) 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 warant any currently possible filename still usable and Lock id unique enough to allow non-exclusive connections.

@firebird-automations
Copy link
Collaborator Author

Modified by: Arioch (arioch)

description: Replacing application-internal data stractures 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 to complex goal to worth doing/asking for. But not now, i feel.

General 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.
Metadata can't be changed but it arguably may apeear 'right thing' for the in-memory database: not using 'db genearator script' but using readymade database file with ready-made data access components bindings. Also this aligns well with Windows restriction that in-memory file should have pre-defined length and never grow (if implementation would use file handles API for bootstrapping, 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 pened '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 artefact) 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 warant any currently possible filename still usable and Lock id unique enough to allow non-exclusive connections.

=>

Replacing application-internal data stractures 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Arioch (arioch)

description: Replacing application-internal data stractures 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.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Put your database in ramdisk, put your temp files in ramdisk.

I see no value in this feature request.

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant