SQLA 17, in-memory crash

[former_SQLA_member1694932]

Hi,

I'm testing idea to have im-memory instance with memory limitation. the command line is:
dbsrv17 -im nw -c 1m -ch 1m -n dm dm.db

I expected to get error message like "out of space/memory" but got crash while filling database with a test data.

==================
...
Press 'q' to shut down the database server
Cache size adjusted to 10424K
....
Cache size adjusted to 63916K
Cache size adjusted to 64176K
Cache size adjusted to 64440K
Cache size adjusted to 64704K
Cache size adjusted to 64840K
Dumping cache info to 'SA_cache_6098.0.dmp' ...
Completed dumping cache info
Dumping cache info to 'SA_cache_6098.1.dmp' ...
Completed dumping cache info
Fatal error: cache page allocation
(&=��
terminate called after throwing an instance of 'DBException'
what(): Fatal error: cache page allocation
SQL Anywhere Support Utility Version 17.0.10.6127
Aborted (core dumped)
========================

It looks like some kind of bug on cache page allocation.

Sunce I can't test my idea on server and I can't find the answers in the documentation, it might be a good idea to ask those questions here.

• How does in-memory model store data. Is it stored in cache? Or is it some "internal in-memory db" + cache?
• Is there any way to limit a memory for in-memory model? In both term, data and cache.

[Breck_Carter]

What kind of device are you trying to support, with such extreme options as -c 1m -ch 1m?

Note that -ch 1m is automatically changed to -ch 64m as you can see from the "Cache size adjusted" messages. You might consider -chx instead.

Note that you have specified "never write" so presumably all your modifications are written to the cache.

I don't have any experience with -im nw databases, but perhaps your applicaton code could keep an eye on PROPERTY ( 'CacheFree' ), and/or PROPERTY ( 'CacheFileDirty' ).

[former_SQLA_member1694932]

Actually, it was idea to create a minimal allowed cache. I noted it was changed to:

Minimum cache size: 8360K, maximum cache size: 65536K

Well, it is great to have properties to get info about cache, however more correct and safe way to prevent critical failure on server side.

From other hand, having no info about architecture it is hard to assess how "correct" that properties are. I suspect in-memory mode is differ from standard model. In standard model the cache is used (in short) to bring data in memory and reduce I/O operations. For in-memory model (from documentation)

[This feature is most useful on systems with a large amount of available memory, typically enough to hold all the database files within the cache.]

idea is to keep database in memory. In this case having "standard" cache mechanism over database perhaps is redundant and affects performance.

Correct me if I'm wrong.

[Breck_Carter]

For a disk-based database, the cache is used for many purposes beyond saving copies of table and index pages for optimization purposes. Storage of temporary data is one purpose, to avoid disk I/O to the temporary file. The docs hint at other purposes for the cache but they are not thoroughly documented. Queries-From-Hell often use vast amounts of temporary space, filling up the cache and writing masses of data to the temporary file... such queries would certainly be bad news for an in-memory database since there is no temporary file and "filling up the cache" is fatal.

I repeat my question, "what kind of device are you trying to support?" since most devices have much more than 64M of RAM.

Also, what behavior are you looking for, from dbsrv17.exe, when -im nw cache is exhausted? A gentle warning when it is 90% full, for example? It may not be possible for the engine to do ANYTHING after the cache reaches 100%, since it uses the cache for all sorts of internal processing purposes, and with -im nw there ain't no temporary file to use in order to create free space in the cache... but I'm guessing.

If you have a suggestion for a product enhancement, post it here... and provide a business case.

[former_SQLA_member1694932]

Well, I repeat my answer 🙂 The cache size was setup to minimum for test purpose only. More cache - more work to fill it in. If it is so important for answer, if it is used it will be x64 server with 64-128Gb RAM.

As you wrote, in-memory database has own specific. Sophisticated queries to calculate universe movement definitely are not applicable for such servers. A heavy complex query can be aborted when some percentage of resources are used. But it needs to be done without fatal error and server self destruction. I guess people quite adequate understand purpose of in-memory database to run reasonable requests.

Actually, I already wrote some kind of suggestion. Reservation memory on server, lets say 10% ( I don't know internals, perhaps it is less or more). When 90% is filled in, switch server in mode when SELECT and DELETE only are allowed. If user deleted records lowering memory usage less then 90%, open server for all kind queries.

As for me it looks very natural.

Btw, perhaps this logic already implemented but due to bug(?) I can't check it.

[VolkerBarth]

You have repeatedly told why you think the -im nw mode (as currently implemented) does not fulfill your requirements.

Could you please also tell what goal you are trying to achieve or what kind of application you want to support? And why the default mode does not fit your needs?

[johnsmirnios]

Hi, crcr. Your point that we could handle the out-of-memory condition more gracefully is well taken. A constrained memory environment was not the designed use case for in-memory mode but we could have been more graceful about it.

SQLAnywhere does deal with complex, high-memory-use queries by apportioning memory between connection, having back-off query plans that use less memory, dynamic run-time cache resizing, etc. SQLAnywhere even does its own "memory management" that allows some in-memory data structures such as cursors and parsed statements to be swapped out to the temp file then reloaded & relocated when needed again later (these are called "relocatable heaps"). But in-memory mode is a very different problem when modified pages start to collect in the cache.

The server is given a fixed amount of RAM to play with (specified by -ch). Beyond that (except for a few other special purposes) it cannot allocate memory. We use the memory to provide caching for database pages as well as for the allocation of in-memory data structures that cannot be relocated such as table definitions, user definitions, connections, etc. Normally, all the pages that are for database caching can be evicted (after writing them out if they have been modified) and replaced. It's very much like virtual memory or a filesystem cache. With "in-memory mode" a modified page cannot be written out to disk so it continues to occupy memory forever unless some operation (such as a delete or drop) happens to free the page. The more pages you add or modify, the smaller the remaining memory becomes to cache pages... until you have none. Even without in-memory mode, a huge amount of non-relocatable allocations can cause the server to run out of memory but that's much harder to do than with in-memory mode.

[former_SQLA_member1694932]

From your explanation I got idea in-memory model loads database file in memory having a simplified "standard model" cache mechanism over. Is it correct?

Well, I still can't understand, event taking in consideration all your explanations, why it is impossible to control/reserve 10% of the allocated memory in -ch for a request processing or have it calculated.

Next, it is simple magic, decline insert/update when it doesn't fit available memory.

Ok, lets come to another entrance.

Resource governor for '%1' exceeded

Error constant SQLE_RESOURCE_GOVERNOR_EXCEEDED
ODBC 2 state ERROR
ODBC 3 state ERROR
Parameter 1 The resource whose limit has been exceeded.
Severity 19
SQLCODE -685
SQLSTATE WP009
Sybase error code 13715


lets follow your idea, the server should die in this case, for sure, high art - put kernel in panic 🙂

[former_SQLA_member1694932]

Well, I really don't know what to answer. Have you noticed the server was crashed? It is definitely what I should expect. Btw, people from SQLA development think it is abnormal, start dbsupport to submit report, write "the server encountered a serious error" .. and only you think it is normal behavior and should satisfy my requirements.

I wrote already... I was investigating in-memory model to cache data for another application.

What do you think, what is advantage in-memory model over default model?

[johnsmirnios]

Hi, crcr. At its core, in-memory never-write mode is just a standard database server that cannot evict dirty (modified) pages. It does not load the whole file into memory. It loads pages on demand and is free to evict clean pages because they can be re-read from disk if necessary.

Your suggestion to gracefully limit the number of dirtied pages is reasonable but does not exist in the product today. Although the concept is simple, it would actually be a very difficult feature to implement in SQLAnywhere though, as a customer, that's not your concern.

The governors are, in fact, a defensive mechanism much like you are suggesting for in-memory mode. Many client applications forgot to close or drop cursors & prepared statements. Without the governors, thousands or even millions of cursors & statements were left allocated, the server would run out of memory, and the server would abort just like you are seeing in your in-memory tests.

Is there anything that I or the people on this forum can do to guide you in the use of the product as it exists today?

[former_SQLA_member1694932]

I guess no. Thank you for information.

The case is closed 🙂

[johnsmirnios · Accepted solution]

That's pretty much behaving as expected. In-memory mode works by saving copies of what would be written to disk in-memory. When you run out of memory, the server will stop processing any further requests (ie, a fatal error). See the "Caution" section at the bottom of this page: http://dcx.sap.com/index.html#sqla170/en/html/3bc822196c5f1014a8aeb73da61cc3d8.html

[former_SQLA_member1694932 · Accepted solution]

I've read that "Cautions" before the test. But if "When this happens, the database server issues an error and stops processing requests." means fatal error and crash with core dump as "stops processing requests" it is definitely bad architecture.

Something is wrong with the implementation of this model, what puts it in the category "do not use".

Personally, I expected the server stops to process INSERT and UPDATE requests if there is not enough memory, but it processes DELETE and SELECT, all requests when memory is released after DELETE. I can't see why it can't be implemented.

[VolkerBarth · Accepted solution]

So what is your use case? A demo/kiosk system so users can "use" an application, and all action is automatically undone finally?

[VolkerBarth · Accepted solution]

So you would expect the server to just ignore/reject some requests while allowing others? Note that even read-only requests like SELECT may need more cache because of access to data not yet cached, complex queries that require work tables and the like. In my understanding exhausted memory is a fatal error.

And obviously the solution is simple: Just provide enough memory, as the caution paragraph tells.

[former_SQLA_member1694932 · Accepted solution]

Well, if it is demo/kiosk, then SQLA doesn't fit? Only for people who can afford pants? I investigated SQLA in-memory like cache keeper.

It is only your understanding when full of data database should die due to lack of memory. And it is absolutely not obvious solution "just provide enough memory". What is "enough"? If data can be calculated with some approximation, what about processes running in parallel with memory allocation? Of coz, you can teach users SQLA dao in meaning "enough". 10TB memory, single process in system or kernel mode.

The solution is very simple, it is called memory management. Reserve, for instance, 10% of the total process memory to allow server running without fatal error and crash.

I can't see any contradictions to allow one request and disable other. What about read-only mode? Are you against it? It allows select but rejects update and insert.

[VolkerBarth · Accepted solution]

Well, if it is demo/kiosk, then SQLA doesn't fit? Only for people who can afford pants?

That is not what I said nor suggested. I just was curious about the use case in order to unterstand the requirements. Well, I'm no more.

[johnsmirnios · Accepted solution]

Maybe, then, you are looking for read-only mode? http://dcx.sap.com/index.html#sqla170/en/html/3bcd7eb06c5f101480aeda759d0119f2.html*loio3bcd7eb06c5f...

In-memory mode has a specific goal of allowing as many modifications as can fit in the memory you configure the server to use and also allows the database to be on read-only media. Read-only mode also allows the database on read-only media (IIRC) but cannot deal with, for example, a database that requires recovery to start up.

Even if we reserved a certain percentage of the memory to hold updated pages your server would hit a wall and then there'd be no way to allow further updates without restarting the server. Problems would happen sooner since not all memory is available for holding updates. Similar to your question about "how do I know how much memory is enough?", one could ask of your proposed solution "how much memory do I need so that 10% is enough?".

As Volker said, if we knew more about what problem you were trying to solve we could guide you to the appropriate solution.

[former_SQLA_member1694932 · Accepted solution]

I think Read-only mode with editable temp files could be a trick but with in-memory model it will use common cache.

The server is normally running and on some transaction the server hit a wall. Ok, last transaction is aborted. I can't see any obstacles to continue to work, the data is still valid. Thus, SELECT is still possible. Correct? Next, during server work if some data deleted, the pages should be returned to "free pool" and available cache size will be increased. So DELETE is possible as well. Correct? It is normal work. Why this schema can't be valid when server hits memory limit?

It is so natural that I would rather say there is bug in server than assume SQLA didn't implement it.

[johnsmirnios · Accepted solution]

You are right: it is natural. Unfortunately, as mentioned in another post, the implementation within SQLAnywhere is deceptively complex. Addressing that complexity was not part of the designed use case and so it was not done.

[Breck_Carter · Accepted solution]

It may not be safe to assume deleted rows would immediately add free space to the cache... what if the application does a ROLLBACK?

[VolkerBarth · Accepted solution]

The case is closed 🙂

In my very humble understanding, there is no interest in further hints by us forum members, as the question has been basically ROLLBACKed... - But of course, that could be another misunderstanding or misjudgement on my part.

Otherwise, one could also mention that SELECTing might also require to load more pages into the cache and will fail when the server is not allowed to evict already loaded (dirty) pages and there are no more clean pages to evict...

[Breck_Carter · Accepted solution]

If dbsrv17 -im nw was a Superhero it would be Batman 🙂

[former_SQLA_member1694932 · Accepted solution]

No free space - no insert/update. It doesn't ruin paradigm.

[former_SQLA_member1694932 · Accepted solution]

John gave the comprehensive answer for this case. He perhaps even implements solution unless you convince him fatal error is bless.

Volker, relax. Party is over. All guests are in the garden. You are alone on this stage.

[Breck_Carter · Accepted solution]

> Volker, relax.

If you want to get the last word on this forum, don't try attacking Volker Barth, he's one of the good guys 🙂