To All (sent via email also):

Based upon this incident report with DBISAM this week:

http://www.elevatesoft.com/incident?action=viewrep&category=dbisam&release=4.34&incident=3710

I did a code review/testing of EDB to see if it suffered from the same
issue.  It does, but not nearly as much, so the effect is less noticeable.

However, there is one thing that did come up that is not "fixable" in a
concrete way, and dovetails into what Barry was asking about here:

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&msg=15309&page=1

This question has also been asked by other customers, in some cases just in
the form of "I need to share temporary tables among multiple sessions".

The issue is that in-memory database catalogs are created on the EDB Server
on a per-process basis of isolation.  The creation/usage part is fine, but
the problem occurs when the EDB Server has to figure out if it should delete
the database catalog.  It can't do so on a session close, because the
isolation level is per-process, not per-session.  However, there is no way
for the EDB Server to accurately know when a client process has terminated,
so these database catalogs are left alone, thus causing a memory leak for
any that are no longer needed.

The possible breaking change would be to get rid of the per-process
isolation for the in-memory databases on the EDB Server, meaning that
in-memory database contents would be visible to all sessions at all times.
If your application relies on the per-process isolation of in-memory
databases, then this change could pose a problem.

Thoughts or suggestions ?

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Thoughts or suggestions ?

I have one thought and one comment.

The thought first:

I was thinking about this as I was walking the dogs. I'd suggest an even more radical question - should ElevateDB support memory databases?

I don't know how many out there use them or what for. My use is of in-memory transient tables rather than databases, those are just means to the former.

Over the years starting with DBISAM (can't remember what I did before I bought it and the big iron stuff isn't germane) I've used in-memory tables with DBISAM, memory databases with ElevateDB, TMS AdvStringGrid with ElevateDB to replace most of the memory based tables and now my own in-memory string table.

In all cases its been for relatively small tables (few hundred rows tops) for stuff that I want to use and throw away. Because I started with DBISAM I've had a habit of using program generated at the time unique table names (generally prefix  + gettickcount) so I haven't bothered with the fact I can have multiple in-memory databases, and this change wouldn't really impact me.

From another thread Tim suggested (and I tend to agree) that there's little speed difference between in-memory and disk based tables. This is probably getting even truer as disk speeds increase, caching improves and more people are using SSDs.

Do we need the in-memory database? Would there be any benefits to removing the capability?

The comment second:

This would be the start of the real separation between c/s and f/s unless there's some way to share memory on the file server PC amongst its clients. I'm pretty sure I've seen posts from some people out there using both c/s and f/s to access a database so this is potentially awkward for them if they use memory databases.

Roy

Tim,

<<
This question has also been asked by other customers, in some cases just in
the form of "I need to share temporary tables among multiple sessions".

Thoughts or suggestions ?
>>

I was about to post in thread Barry started that in my view the current EDB in-memory db/tables are too limiting - i would use temporary if i really needed per session isolation and would like ability for multiple sessions to share the in-memory tables (whether part of same app and in multiple threads or for c/s even for multiple processes/apps).

We do have some use cases (not a lot but few) where multiple threads updating the same in-memory database would make life easier (currently using physical tables by in-mem would be better).

My vote would be to support the shared in-memory db/tables.


Raul

<<I was thinking about this as I was walking the dogs. I'd suggest an even more radical question - should ElevateDB support memory databases?
>>
There are scenarios where i believe it to be useful - for example mixing a remote C/S session and local session (that would be all in memory so the app sets up in-mem config + db). Not a everyday occurrence but in our case we have lot of scenarios where out app polls or gets pushed data from remote system and has to both cache it and do some consolidation and matching and then send to our main server (c/s session). This would be extra useful with shared in mem db/tables as i usually have 3-4 threads managing this and current use of physical disk table is not ideal as we need to do lot of cleanup and get hit with AV locking and other usual issues.


<<
Do we need the in-memory database? Would there be any benefits to removing the capability?
>>

No - i do have some real cases where i see value (assuming shared in mem db)

Raul

"Tim Young [Elevate Software]" wrote:

>
The possible breaking change would be to get rid of the per-process
isolation for the in-memory databases on the EDB Server, meaning that
in-memory database contents would be visible to all sessions at all times.
If your application relies on the per-process isolation of in-memory
databases, then this change could pose a problem.<

I definitely like this approach.

I had (incorrectly) assumed if memory tables were created with a Create statement that did not use "Temporary" they were shareable. And if memory tables were created with the "Temporary" keyword then they would be created on the client's machine like any temporary table. This would be consistent with how EDB handles disk based tables. So if a developer wants isolated memory tables, he would use "Create Temporary MemTable ..." and if he wanted a shareable memory table he would use "Create MemTable ...".

MySQL can share Memory tables on the server, I can't see why EDB can't either. I thought this was the whole purpose of using memory tables on a server. Otherwise, as many have pointed out, you might as well be using EDB temporary tables instead of memory tables because there would be little difference between the two because they are non-shareable.

Just my 5 cents.

Barry

Another 5 cents:

I'm heavily using in-memory-tables for all kinds of stuff, sometimes even in cases where a stringlist would do (because those don't have these nice SQL features).

Reason why I'm not using disk-based temporary tables is mainly the belief, that memory is faster than disk, that "small" temporary stuff should happen in RAM and not on disk (may it only be to prolong the lifetime of the disk, matters more with SSDs), and that, when you restart the machine, all temporary stuff that wasn't cleaned up properly should be gone.

I'm not relying on strictly isolated memory databases per process, but I need a way to keep things as they are. Correct me if I'm wrong: making in-memory database contents visible to all sessions at all times, would only mean to me to create a memory db with a unique name for each session, and simply accessing only that one in that session. I could live with that.

Would be even a feature to have the possibility to use shared in-memory-tables. At the moment, I can't think of a situation where one needs a strict isolation.

Heiko

Umm...some more thoughts:

>>However, there is no way for the EDB Server to accurately know when a client process has terminated<<

But at some point the server process is terminated, right? Why not drop the catalogs then?

And...what would happen if we had a shared memory database, a client crashes and doesn't clean up "his" temporary tables? There would be an even greater memory leak, wouldn't it?

Heiko

Raul


><<I was thinking about this as I was walking the dogs. I'd suggest an even more radical question - should ElevateDB support memory databases?
>>>
>There are scenarios where i believe it to be useful - for example mixing a remote C/S session and local session (that would be all in memory so the app sets up in-mem config + db). Not a everyday occurrence but in our case we have lot of scenarios where out app polls or gets pushed data from remote system and has to both cache it and do some consolidation and matching and then send to our main server (c/s session). This would be extra useful with shared in mem db/tables as i usually have 3-4 threads managing this and current use of physical disk table is not ideal as we need to do lot of cleanup and get hit with AV locking and other usual issues.

The in memory databases as they currently exist can already be be accessed by multiple threads from within the same app (its the definition of process that gets confusing)

The way I read Tim's post is that the idea of shareable in-memory databases would ONLY be implemented for c/s. I'm not sure what would happen for f/s (ie local) but I don't see any compatible with current operations to achieve sharing away from the local PC and I'm not even sure how easy it would be between separate instances of an app on a PC.

Don't forget that when Tim talks about the server he's talking about one app and controlling the memory it has. For f/s you'd need to set up an area of shared memory somewhere and point at it. Doable, but I don't think its what Tim's proposing.

Roy

ps I never realised that AV software would ignore in-memory tables. That's a useful piece of info to store.

Heiko

>And...what would happen if we had a shared memory database, a client crashes and doesn't clean up "his" temporary tables? There would be an even greater memory leak, wouldn't it?

Interesting question. I think the answer is much the same as with disk based tables and the server processes. There would need to be some sort of deas session timeout cleanup.

It started me thinking about the whole can of worms that could be. Maybe Tim will need to introduce (as Barry thought existed) TEMPORARY tables for the in-memory database as well.

The only thing I think can be certain is that when the server is closed the in-memory databases and tables will go, and if that doesn't work switching off the PC should

Roy

Heiko

>I'm heavily using in-memory-tables for all kinds of stuff, sometimes even in cases where a stringlist would do (because those don't have these nice SQL features).

Strangely over the years where I've used in-memory tables I haven't used SQL on them. That's why when I built my own string table system I've built in simple sorting and filtering but that's all. A number of them do get populated using SQL though.

>Reason why I'm not using disk-based temporary tables is mainly the belief, that memory is faster than disk, that "small" temporary stuff should happen in RAM and not on disk (may it only be to prolong the lifetime of the disk, matters more with SSDs), and that, when you restart the machine, all temporary stuff that wasn't cleaned up properly should be gone.

For the smaller tables (which will be the vast majority of mine) quite often they'll all be in RAM anyway due to Windows/ElevateDB caching which probably explains the fact that I'm with Tim on little speed difference. Lifespan of SSDs, however, could be a good justification until we reach the point of SSDs either getting a longer lifespan or becoming dirt cheap.

>I'm not relying on strictly isolated memory databases per process, but I need a way to keep things as they are. Correct me if I'm wrong: making in-memory database contents visible to all sessions at all times, would only mean to me to create a memory db with a unique name for each session, and simply accessing only that one in that session. I could live with that.

Or as I do and create unique throwaway table names. Either approach will work

Roy