Is it RAID or something else? 2

[IRABYY]

Colleagues:

We recently started to experience quite bazzar behavior of a simple INSERT (SQL) command, i.e.

INSERT INTO (DBF("TABLE_ALIAS")) FROM MEMVAR

If you issue after that

GO BOTTOM IN TABLE_ALIAS
IF TABLE_ALIAS.FieldN # m.FieldN
   && Some code
ENDIF

the IF condition above is almost always True, and program enters IF...ENDIF code block.

This started when we and our customers switched from WinNT 4 to WinNT 5 (and WinNT 2003) servers with RAID HDD system. Considering that this system has what I call "heavy buffering", I've come to suspect that INSERT does not actually write the contents of mem. vars onto file on disk but just keeps it there, and with the next INSERT (I do it in cycle) the contents of the latest mem.vars gets lost. Therefore, I modified the above part like this:

INSERT INTO (DBF("TABLE_ALIAS")) FROM MEMVAR
FLUSH

Still, sometimes FLUSH after INSERT and/or REPLACE commands does not do the job. So, out of sheer desperation, I put FLUSH in cycle as well, i.e.

INSERT INTO (DBF("TABLE_ALIAS")) FROM MEMVAR
FOR I=1 TO 5
   FLUSH
NEXT I

and

REPLACE TABLE_ALIAS.FieldN WITH m.FieldN IN TABLE_ALIAS
FOR I=1 TO 5
   FLUSH
NEXT I

This worked 99% of the times, but still this remaining 1% of uncertainty bothers me a lot. By Murphy's law, this 1% is bound to happen at the most "juicy" customer's system.

Is there any way to ensure 100% that the INSERT and/or REPLACE commands are actually writing onto disk, not into memory buffers without FLUSHing buffers 5 times in a row (thus slowing the performance)? OR - are there any other commands or settings that are designed specifically to bypass the memory buffers and write data directly onto HDD?

AHWBGA.



Regards,

Ilya

 

[craigsboyd]

Am I to understand that INSERT FROM MEMVAR in VFP 7 will cause the indexes to be corrupted??? If so can this behavior be reproduced consistently?

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[IRABYY]

slighthaze (IS/IT--Manageme):
Am I to understand that INSERT FROM MEMVAR in VFP 7 will cause the indexes to be corrupted??? If so can this behavior be reproduced consistently?

That's what we are getting lately. Not very much consistently, though.

I am yet to get the results of the testing of my corrected program, and it won't happen until tomorrow morning.

The corrections I've made are the following:

SELECT <TABLE>
ZAP
REINDEX  && Added line of code

After all the records have been INSERTed

SELECT <TABLE>
REINDEX  && Added line of code

Yes, it's that "dreaded" REINDEX command, but the table is not very big (2...4 dozen records at most). But it's that very table whose CDX was corrupted twice in a row lately.

I will know if this helps tomorrow.


Regards,

Ilya

 

[SqueakinSweep]

As I dig deeper into my recollection of this problem, I seem to recall that the problem only occurred on W2K clients, but I couldnt be 100% certain on this.

In VFP6 without SP5, this behaviour was consistent, which is different to what IRABY is reporting. It is just that it sounded so familiar, that I decided to take a hopeful punt at the possible problem.

If the table indeed has so few records, why not simply append the records in to a table with no indexes, and then simply recreate the indexes at the end of the Inserts.

Glad to know you're making progress



WTrueman
...if it works dont mess with it

 

[IRABYY]

wtrueman (Programmer):
I seem to recall that the problem only occurred on W2K clients

The case at hands is WinNT2003 Server. New data is on CD or on disk C:, target dir is on RAID disk, both in the same box.

If the table indeed has so few records, why not simply append the records in to a table with no indexes, and then simply recreate the indexes at the end of the Inserts.

Different discrepancies, True Man: the one with corrupt CDX is small, while the one that reports partial merge because CDX's not updated is up to 2M recs (and size doesn't matter, it happens even after dozen records sometimes).

Glad to know you're making progress

I wish! Still haven't got report from Operations dept. Chickens haven't hatched yet...



Regards,

Ilya

 

[IRABYY]

Everybody:

Chickens have hatched! Operations manager just reported to me that the data merge operation last night was successfull, no glitches from my program's part. So, that was lagging CDX update all along.

True Man, Sir, you're getting the Star of Honor from me for your tip! (You are welcome.)

Still another mystery remains unsolved: why CDX - or DBF's header, for that matter - got corrupted? The table was hardly 30 records long...


Regards,

Ilya

 

[SqueakinSweep]

Irabyy

Thanks for the kind words and star.
Glad to be of help.

Wonders if the same problem exists in VFP8 too????


WTrueman
...if it works dont mess with it

 

[ramani]

I would suggest a TRANSACTION wrap around the update.
i.e.

BEGIN TRANSACTION
INSERT INTO (DBF("TABLE_ALIAS&quot) FROM MEMVAR
ENDTRANSACTION

If the transaction is the first or only transaction (that is, the transaction isn't nested), the changes are written to disk.

If a transaction is nested, END TRANSACTION causes all cached updates to be folded into the next higher transaction level. Nesting transactions has the potential to overwrite changes made to data at a higher transaction level.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[craigsboyd]

Ok, I am really trying to understand this so please bear with me. Are you guys saying that if you use INSERT FROM MEMVAR in VFP 7 that there is going to be index corruption resulting from that?? If so, is the problem reproduceable? I mean could you provide me a way to reproduce the problem in VFP? Thank you for your time...I really want to make sure I am not misunderstanding this thread.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[SqueakinSweep]

slighthaze

It certainly does happen in VFP6 without SP5 installed. I never drilled down too deeply to identify under which circumstances it happened. I was looking after a legacy system at the time. In this instance the index went out of sync on every INSERT, which seems different to what IRABYY is reporting.

The only other thing I remember is that the table in question used concatenated Index keys, which is something I try to avoid at all cost, when developing.

IRABYY seems to have hit the same problem, though intermittantly, and Im wondering if all his Clients have V7.SP1. I discovered the problem where 2 were using the same application, one it worked for fine, and the other caused the index to not update. As soon as I applied SP5, to the client corrupting the index the problem was resolved.

There certainly seems to be a set of circumstances, be it OS or Service Pack, that allows the same problem to raise its head in VFP7.

WTrueman
...if it works dont mess with it

 

[IRABYY]

Would be my pleasure, Haze!

Here's how it goes:

You have to merge records from a table with new data (CURRENT) to the existing table (MERGE), all the while keeping an eye on the MERGE table's files' size (in my case it's FPT, but may be DBF and/or CDX as well). After making sure that that a) MERGE files are not Read-Only (another big topic, BTW), and b) the resulting file size after merging is less than 1.85MB (~2,000,000,000 bytes):

lcPrevSelect = IIF(!EMPTY(ALIAS()), "SELECT " + ALIAS(), "")
lcAppendFrom = DBF("CURRENT")
lcMerge = DBF("MERGE")
SELECT CURRENT
lnMax = RECCOUNT("CURRENT")
DO CASE
CASE lnMax <= 10*10^3
   lnMode = 500
CASE BETWEEN(lnMax, 10*10^3 + 1, 500*10^3)
   lnMode = 1000
CASE BETWEEN(lnMax, 500*10^3 + 1, 10^6)
   lnMode = 5000
OTHERWISE
   lnMode = 10000
ENDCASE
FOR I = 1 TO lnMax
   GO I IN CURRENT
   SCATTER MEMO MEMVAR
   INSERT INTO (lcMerge) FROM MEMVAR
   FLUSH
   IF MOD(I, lnMode) = 0
      WAIT [ Appending ] + ALLTRIM(STR(lnMax, 12, 0)) + [ records from ] + lcAppendFrom + [ ] + CHR(13) + [ to ] + lcMerge + ;
           [, ] + ALLTRIM(STR(I, 12, 0)) + [ records appended...] WINDOW NOWAIT NOCLEAR
      FLUSH
   ENDIF
NEXT I

&& Just to make it sure:
FOR I = 1 TO 5
   FLUSH
NEXT I

WAIT CLEAR
*SET ORDER TO (JUSTSTEM(lcMerge) + &quot;0&quot;) IN MERGE && Commented out by True Man suggestion
GO BOTTOM IN MERGE
*SET ORDER TO && Commented out by True Man suggestion
IF MERGE.Key != &tcAppID..EndKey && Oops!
   lcErrMsg = [Merge of data from table file &quot;] + DBF(&quot;CURRENT&quot;) + [&quot; into table file &quot;] + DBF(&quot;MERGE&quot;) + ;
              [&quot; for the report &quot;] + ALLTRIM(tcReptName) + [&quot;]
   IF AERROR(laErr) > 0 && Some system error - lay the responsibility on the customer's MIS Dept.
      lcErrMsg = lcErrMsg + [ failed due to system error!] + CHR(13) + [Contact your MIS Department ] + ;
      [with the reference to the following System Error Description:] + CHR(13) + [&quot;] + laErr[2] + [&quot;]
   ELSE                 && Something weird!
      lcErrMsg = lcErrMsg + [ incomplete.] + CHR(13) + [Not all the pages were appended (&quot;partial merge&quot;).] + CHR(13) + ;
                 [Please re-run this Data Delivery Unit.] + CR + [Certain files could not be updated.] + CR + ;
                 [(Note: Current Data Delivery Unit won't be duplicated)] + CR + [If this message persists contact your vendor.]
   ENDIF (AERROR(laErr) > 0 && Some system error - lay the responsibility on the customer's MIS Dept.)
   =MESSAGEBOX(lcErrMsg, 48, MsgBoxHeader(PROGRAM(), LINENO()))
   &lcPrevSelect
   glNormal = .F.
   RETURN .F.
ELSE
&& If we came to this point - everything's kosher.
   &lcPrevSelect
   RETURN .T.
ENDIF (MERGE.Key != &tcAppID..EndKey && Oops!)

Where:
tcReptName - parameter
tcAppID - parameter, report identifier; in the case above - also the name of the cursor containing starting and ending report's page key (coded page #)
MsgBoxHeader() - UDF, adds some info into the msg. box header for identifying the routine displaying that message box.

HTH.


Regards,

Ilya

 

[IRABYY]

ramani (Programmer):
I would suggest a TRANSACTION wrap around the update.

Sorry, bro, can't use TRANSACTION, VFP 7's Help prohibits it for free tables. Can't make it into DBC either: with 500+ accounts, we would have to make 500+ custom DBCs, which means MAINTENANCE (that's REALLY DREADFUL, Trueman, REINDEX is dinky toys in comparison). Add to this that we have only 5 programmers + 1 tech. support rep., and our customers are everywhere from WA to TX to MN to NY to UK with FL and Canada on the trail... IHYU.

Thanks for the try, though!


Regards,

Ilya

 

[badukist]

Still another mystery remains unsolved: why CDX - or DBF's header, for that matter - got corrupted? The table was hardly 30 records long...

Two main causes for this, from what I've read so far, if no abnormal app exit exists.
1. Oportunistic locking at server side.
This mean that a file is exclusively locked for the first client that open a file shared and read,write cache is used intensively for the file , untill another open shared request. If another app tries to open the file, then the Oportunistic locking level II is used (whatever this mean, but doesn't look good)
2.Read caching.
Network data is cached locally, and is refreshed from time to time, when the app flushes its buffers.
I suspect this setting for many issues (the one is that the second parameters of SET REFRESH doesn't cause the reread of REAL network data, but data from local cached buffers, so you will not see ACTUAL data changed by another workstation)
I see this setting as an important one regarding table corruption (table header record count update can be based on old, already changed, value).

There is another setting that can cause headaches, specially for COM objects or server side apps that tries to open exclusively a table. This is RFCB (File Handles Caching)
"...close requests are acknowledged by the server, but are buffered from the file system. This is intended to optimize response time to repeated open/close operations performed by clients. In regards to Opportunistic Locking (oplock), this optimization is a logical extension of the way a client caches its own file close request and relies on the server to arbitrate future requests for file access by other clients. " I can understand from this that if all workstations have closed the table, then one COM object/app that run locally cannot open it exclusive?
Check this link:

http://support.microsoft.com/defaul...port/kb/articles/Q126/0/26.ASP&NoWebContent=1

From a thread on UT I've read that disabling both read and write cache (disk/network) and oportunistick locking has solved corruption issues but the performance was going down by 40%.

 

[IRABYY]

badukist (Programmer):
From a thread on UT I've read that disabling both read and write cache (disk/network) and oportunistic locking has solved corruption issues but the performance was going down by 40%.

Yes, I read that also. But I'm afraid I cannot enforce this change of environment settings even on our in-house servers, to say nothing about those at customers'.

This is never-ending battle between Development and Production for you: quality vs. quantity, with Production caring for quantity first and foremost to assure income, while Development cares mostly for quality in order to minimize maintenance.

Looks like the only choice for me is to bite the bullet and FLUSH 10 times. That is unless there are means in VFP itself to turn buffering off on server itself. So far, I could not find anything. Guess I have to look into WinAPI functions for that. Any ideas?


Regards,

Ilya

 

[CoreMemory]

Just out of curiosity, have you tried using INSERT with an explicit list of destination fields and a VALUES clause for the source fields.

I know its more coding, but then the memvars are avoided altogether and I believe there is less setup work as VFP does not have to convert all the source field values into memoray variables and can just do a series of internal move operations from the buffered source table row to the row in the destination table that is being inserted.

 

[IRABYY]

CoreMemory (IS/IT--Manageme)
Just out of curiosity, have you tried using INSERT with an explicit list of destination fields and a VALUES clause for the source fields.

Would have worked... if I knew those values. In fact, I never do. For that matter, I don't even know the structure of the table I am currently working with. I am just a Generic Program, like a postman: does postman know what's in the letters he delivers?

Nevertheless, thanks for the advise, CoreMemory.

In fact, I even programmed the merge mode with APPEND FROM, but with customers' Sys. Admins blocking access to everything even for themselves, I have to ensure target files' writeability on every other step. (No offense meant.) Therefore, I made APPEND FROM available only for data merge on our in-house servers, where we control the environment ourselves. At customers', only record-by-record merge mode is implemented. Just imagine some system's TSR utility makes target table files Read-Only in the middle of the APPEND process... And I've seen that happen! The results were devastating, from partial merge to the CDX and FPT corruption to DBF ending up empty due to file's header corruption. In a word - loss of data, and restoration is always long and difficult process + customer's frustration.



Regards,

Ilya

 

[IRABYY]

Back to our muttons, colleagues:

I do not remember whether one of you told me so, or I read it in one of the articles on the subject, but there was a tip somewhere that with exclusive use file is locked right away (no Opp. Locks implemented). I guessed that, then, the writing should go onto disk bypassing cache. So, I made sure the merge target table is used exclusively (+ FLUSH after each INSERT FROM MEMVAR). I made this a new release for in-house and distribution. Guess what? Users brought up complaint that it is now 4 times slower than the previous release was!

I removed that FLUSH after INSERT, recompiled the program and gave it for Alpha testing to our Operations Dept. Once they get back to me with results - I will get back to you for report.

But I wonder what might have caused this slowdown of the performance?... Could it be that file is now opened in exclusive mode?


Regards,

Ilya

 

[DSummZZZ]

As far as I know, unless you explicitly turn off caching, it is going to get used by default. Even with exclusive use of the file. In fact, it seems possible to me that since the OS now knows it has exclusive use of the file and noone else is going to be updating it, it would cache even more data than before.

But I have never heard of a table opened exclusivly being slower than one opened shared. It's usually exponentially faster to do any processing on a table used exclusive.



-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[IRABYY]

DSummZZZ (Programmer):
I have never heard of a table opened exclusivly being slower than one opened shared. It's usually exponentially faster to do any processing on a table used exclusive.

In general - I agree with you, Dave. Although, there are such factors like processing on local-to-local disk, or local-to-netwirk, or network-to-network. When it's local-to-local the difference should be insignificant. But when it's network... it all depends on the network configuration, policies, traffic load, etc.

Still, I think it was FLUSH after every INSERT that slowed down the performance so drastically. Ops Manager just reported it's going faster without that FLUSH (I still FLUSH buffers every so many hundreds of records INSERTed), but how much faster they could not say, precessing is not done yet. When it's complete - I'LL BE BAAACK! (Gov.-elect Ah'nold as T-100)


Regards,

Ilya