I published 3 tables to act as an audit trail for my application which executes a process and takes about 30 minutes to run. It is processing a few thousand CSV files and adding them to the database. There is a bug in my update algorithm and I need the audit trail to try and figure out if the tables are being updated out of sequence.

My application completes fine and creates the xAudit file in the Store AuditTrail. But when I try and create a table from that file using:

create [temporary] table "_audit_init" from Updates "Init_Audit" in store AuditTrail

it takes a long time to run this command from EDBMgr and then I will get an Out of Memory error when EDBSrvr consumes (Commit) 4.084 GB of memory.

I am using WIn7 64bit with 8GB of memory and EDB 2.13B2 C/S.

I then replaced the EdbSrvr with the 64bit version and tried again. This time EDBSrvr consumed (Commit) 18.3 GB of memory. The progress bar in EDBMgr got a lot further, to almost 80% complete before it just hung. There was no Out of Memory message this time but Windows kept displaying a dialog window saying Windows was low in memory and I should terminate EdbSrvr. If I declined, Windows would continue to limp along and the SQL command would never finish and Windows becomes unstable.

The Init_Audit file in the Stores directory is 1.3Gb in size.

1) Why is EdbSrvr consuming all of this memory? Is it trying to throw all of this data into a transaction? If yes, then is there a way to eliminate the transaction, or at least break up the transaction into 50k rows so it doesn't consume all of the memory?

2) Why can't the Publish command have a parameter called "Max_Size" where  I could use "100MB" which means it will generate serialized files "Init_Audit_001" and "Init_Audit_002" and so on as long as each file does not exceed 100MB?

3) Do I need to go out and upgrade the computer to 32GB? Then what happens next year when the files are a lot larger?

Is there a solution to my memory problems? Or should I just make a reservation for the old programmers home right now?

TIA
Barry

Barry wrote:

> it takes a long time to run this command from EDBMgr and then I will
> get an Out of Memory error when EDBSrvr consumes (Commit) 4.084 GB of
> memory.

I think I'd gather up what is needed to reproduce this, and send it to
Elevate Support. That way you can get hard info, and they can reproduce
and fix it properly.

The newsgroup is about peer support, and it sounds like there is a loop
bug there somewhere.


--

Matthew Jones

<<Barry wrote:

1) Why is EdbSrvr consuming all of this memory? Is it trying to throw all of this data into a transaction? If yes, then is there a way to eliminate the transaction, or at least break up the transaction into 50k rows so it doesn't consume all of the memory?
>>

I agree with Matthew that contact support direct.

Is there anything in the logevent table that might help ?

<<
2) Why can't the Publish command have a parameter called "Max_Size" where  I could use "100MB" which means it will generate serialized files "Init_Audit_001" and "Init_Audit_002" and so on as long as each file does not exceed 100MB?
>>

It does not at the moment but might be a good enhancement suggestion.

You can call SAVE UPDATES as often as you want though to reduce the size of any individual update file - for example every X records are processed. You would need to name them in some proper order and load individually but this does allow controlling the size today and if you do the naming right then it would be easy to load them as well using a quick code or script.

Raul

<<Barry wrote:

3) Do I need to go out and upgrade the computer to 32GB? Then what happens next year when the files are a lot larger?

Is there a solution to my memory problems? Or should I just make a reservation for the old programmers home right now?
>>

I think the issue seems to be with the size of update file and EDB in this case.

However, in the past we have used Amazon EC2 for doing some heavy processing - i just checked and for about $2/hour you could provision a server with 122GB of RAM. This is just in case you want to try loading in environment where lack of RAM will not be an issue. Getting the update file uploaded might be biggest challenge.

Raul

"Matthew Jones" wrote:

Barry wrote:

>> it takes a long time to run this command from EDBMgr and then I will
>> get an Out of Memory error when EDBSrvr consumes (Commit) 4.084 GB of
>> memory.

>I think I'd gather up what is needed to reproduce this, and send it to
>Elevate Support. That way you can get hard info, and they can reproduce
>and fix it properly.

>The newsgroup is about peer support, and it sounds like there is a loop
>bug there somewhere.

I will mention it to Tim, but I don't think it is a bug. I think EDB is trying to wrap the Create Table in a transaction which consumes all available memory. Why? I don't know.

I'm not using Replication yet. I'm just using the Update file to create an audit trail. So if anyone listening is using Replication, how large of an Update file can you have? I mean, what happens if the Update file gets to be 50gb in size? Can EDB still process it on a computer with 8gb of ram? In other words, how memory intensive is replication on the box that is processing the received 50gb Update file? (Disregarding any problems of getting the Update file to the receiving computer)

TIA

Barry

Raul wrote:

<<Barry wrote:

>>1) Why is EdbSrvr consuming all of this memory? Is it trying to throw all of this data into a transaction? If yes,
>>then is there a way to eliminate the transaction, or at least break up the transaction into 50k rows so it doesn't
>>consume all of the memory?
>>

>I agree with Matthew that contact support direct.
>Is there anything in the logevent table that might help ?

Just ErrorCode 9999 "Out of Memory"

<<
2) Why can't the Publish command have a parameter called "Max_Size" where  I could use "100MB" which means it will generate serialized files "Init_Audit_001" and "Init_Audit_002" and so on as long as each file does not exceed 100MB?
>>

>It does not at the moment but might be a good enhancement suggestion.
I will mention it to Tim

>You can call SAVE UPDATES as often as you want though to reduce the size of any individual update file - for example every X records are processed. You would need to name them in some proper order and load individually but this does allow controlling the size today and if you do the naming right then it would be easy to load them as well using a quick code or script.<

That is what I'm up to now. It actually makes more sense for me to do it this way since I am batch processing the data in groups anyways.

Barry

Barry,

<< it takes a long time to run this command from EDBMgr and then I will get
an Out of Memory error when EDBSrvr consumes (Commit) 4.084 GB of memory. >>

This is now fixed for the next 2.19 minor release.  The loading was simply
batching up the population of the table, and now it flushes it periodically
to prevent excessive memory consumption.

<< 2) Why can't the Publish command have a parameter called "Max_Size" where
I could use "100MB" which means it will generate serialized files
"Init_Audit_001" and "Init_Audit_002" and so on as long as each file does
not exceed 100MB? >>

I prefer to leave the timing of the creation of the update files to the
developer.  It's simple stuff to add a scheduled job that dumps the logged
updates every week or month to a new updates file.  That way you know
exactly how and where the update files are being created, so you know where
to go get them if you want to interrogate them for purposes of an audit
trail.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Barry,

<< it takes a long time to run this command from EDBMgr and then I will get
an Out of Memory error when EDBSrvr consumes (Commit) 4.084 GB of memory. >>

<This is now fixed for the next 2.19 minor release.  The loading was simply
batching up the population of the table, and now it flushes it periodically
to prevent excessive memory consumption.>

That's great, thanks.

<< 2) Why can't the Publish command have a parameter called "Max_Size" where
I could use "100MB" which means it will generate serialized files
"Init_Audit_001" and "Init_Audit_002" and so on as long as each file does
not exceed 100MB? >>

<I prefer to leave the timing of the creation of the update files to the
developer.  It's simple stuff to add a scheduled job that dumps the logged
updates every week or month to a new updates file.  That way you know
exactly how and where the update files are being created, so you know where
to go get them if you want to interrogate them for purposes of an audit
trail.>

Now that the memory consumption issue will be solved in the next release, this is Max_Size option is less of an issue for audit trails.  My batch operation was adding 1 million rows at a time (30 minutes) so the update files were getting quite large. But wouldn't a Max_Size option also make it easier for replication to transfer smaller files (1MB) to the other computer, rather than one large file (50MB)? I'm thinking it would reduce the demand on the EDB server as well as make it easier to recover from a connection loss because it won't have to resend the 50MB file. (Or do you have "auto-recovery from where it left off" already built into replication?)

Barry