Author | Topic: Degrading client performance as database files are opened locally on the server | |
---|---|---|
Tim Callahan | Degrading client performance as database files are opened locally on the server on Fri, 11 Aug 2017 20:45:44 +0200 I have a project converting a large clipper 5.2 system to xBase++. The system uses DBF/FPT/CDX files with the FOXCDX dbe. In operational testing I found a strange performance issue which is that client performance degrades significantly as database files are opened locally on the server. Details - perhaps too many - are below. Thanks, Tim Test Setup -------------- • Three client platforms and two server platforms, all under controlled conditions. • Clients: Win7 10/100, Win7 gb, Win Server 2008 R2 via XenApp • Servers: Win Server 2012 R2, Win Server 2008 R2 (VMware virtual machine) • xBase++: 2.00.785 • Dbe: FOXCDX (see dbesys.prg example) • Client test function: representative function that creates a DBF/CDX file on the client C: drive from data assembled from multiple open data files residing on the server. Test Procedure ------------------ 1. Reboot the server 2. On a client map a drive to the server database share 3. Run the app from the client c: drive, starting it in the mapped server folder – performance is good for multiple simultaneous clients 4. Run an app locally on the server and open files in the same database folder as the client 5. As the number of files opened locally on the server increases the client performance degrades significantly for all clients, even new instances on different machines (see graph) 6. Close the locally opened files on the server 7. Client performance remains degraded Notes ------- • The performance is poor for all subsequent clients once a database folder has files opened locally on the server • The performance degradation is limited to the specific folder containing the database. That is, if you run the tests on a database in a different server folder the performance is good until files begin to be opened on the server in the this new folder. • A server reboot is required to fully clear the database folder “contamination” • The results in the graph are representative, many more combinations were tried with the same results • The results are the same for all client and server combinations Performance graph.JPG dbesys.txt | |
Jim Lee | Re: Degrading client performance as database files are opened locally on theserver on Sat, 12 Aug 2017 00:06:06 +0200 welcome to Xbase++ World you mix .T. with FOXDBE_LOCKMODE_VISUAL which is wrong ... NEED .F. DbeInfo(COMPONENT_DATA, FOXDBE_CREATE_2X, .T. ) DbeInfo(COMPONENT_DATA, FOXDBE_LOCKMODE, FOXDBE_LOCKMODE_VISUAL) but while you say you want to use COMIX it must be .T. with FOXDFBE_LOCKMODE_CLIPPER DbeInfo( COMPONENT_DATA, FOXDBE_CREATE_2X, .T. ) DbeInfo( COMPONENT_DATA, FOXDBE_LOCKMODE , FOXDFBE_LOCKMODE_CLIPPER ) --- while DBF Database are File based it need SMB to inform other Client on Network. when 1st User open DBF shared on Server it is SMB "Exclusive" (NOT Xbase++ EXCLUSIVE) when next User want to open same DBF all Client have to be inform to switch to "Ops Level 2" (Ops-locking) https://msdn.microsoft.com/en-us/library/windows/desktop/aa365433(v=vs.85).aspx at this Point you may have Problem while 2nd User need long time to open DBF -> "SharingViolationDelay" http://support.microsoft.com/kb/150384 --- since Vista we got SMB2 as default. while SMB1 is still alive you may have Problem when using Lanmanager/Net-Drive ( NET USE X: ... ) ! Note : SMB1 will default be disable in next Windows 10 1709 (RS3) Release https://blogs.technet.microsoft.com/filecab/2016/09/16/stop-using-smb1/ when using SMB2 you may need Alaska SMB2-Patch to disable on Workstation http://technet.microsoft.com/en-us/library/ff686200%28WS.10%29.aspx HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Lanmanworkstation\Parameters DirectoryCacheLifetime FileNotFoundCacheLifetime FileInfoCacheLifetime --- you say your Source is Cl*pper ... did you use : 3PP Lib ASM Code DBF > 1 GB SET FILTER INDEX ON ... NEXT, REST, EVAL OrdKeyCount() | |
Tim Callahan | Re: Degrading client performance as database files are opened locally on theserver on Mon, 27 Nov 2017 13:54:44 -0800 Thanks and sorry for the late reply. This makes sense and I verified with Alsaka support this is exactly what is happening I have switched to using ADS which is a better solution anyway Tim On Sat, 12 Aug 2017 00:06:06 +0200, Jim Lee wrote: >welcome to Xbase++ World > >you mix .T. with FOXDBE_LOCKMODE_VISUAL which is wrong ... NEED .F. > > DbeInfo(COMPONENT_DATA, FOXDBE_CREATE_2X, .T. ) > DbeInfo(COMPONENT_DATA, FOXDBE_LOCKMODE, FOXDBE_LOCKMODE_VISUAL) > >but while you say you want to use COMIX it must be .T. with >FOXDFBE_LOCKMODE_CLIPPER > > DbeInfo( COMPONENT_DATA, FOXDBE_CREATE_2X, .T. ) > DbeInfo( COMPONENT_DATA, FOXDBE_LOCKMODE , FOXDFBE_LOCKMODE_CLIPPER ) > >--- > >while DBF Database are File based it need SMB to inform other Client on >Network. > >when 1st User open DBF shared on Server it is SMB "Exclusive" (NOT Xbase++ >EXCLUSIVE) >when next User want to open same DBF all Client have to be inform to switch >to "Ops Level 2" (Ops-locking) >https://msdn.microsoft.com/en-us/library/windows/desktop/aa365433(v=vs.85).aspx > >at this Point you may have Problem while 2nd User need long time to open >DBF -> "SharingViolationDelay" >http://support.microsoft.com/kb/150384 > >--- > >since Vista we got SMB2 as default. >while SMB1 is still alive you may have Problem when using >Lanmanager/Net-Drive ( NET USE X: ... ) > >! Note : SMB1 will default be disable in next Windows 10 1709 (RS3) Release >https://blogs.technet.microsoft.com/filecab/2016/09/16/stop-using-smb1/ > >when using SMB2 you may need Alaska SMB2-Patch to disable on Workstation >http://technet.microsoft.com/en-us/library/ff686200%28WS.10%29.aspx >HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Lanmanworkstation\Parameters >DirectoryCacheLifetime >FileNotFoundCacheLifetime >FileInfoCacheLifetime > >--- > >you say your Source is Cl*pper ... did you use : > >3PP Lib >ASM Code >DBF > 1 GB >SET FILTER >INDEX ON ... NEXT, REST, EVAL >OrdKeyCount() > > > > > > | |
Andreas Gehrs-Pahl | Re: Degrading client performance as database files are opened locally on the server on Sat, 12 Aug 2017 04:20:04 -0400 Tim, In your DbeSys() program, move the line: "DbeSetDefault("FOXCDX")" right after "DbeBuild(...)" and before the first call to DbeInfo(). Or just leave it out completely, as calling DbeBuild() automatically includes DbeSetDefault(). You also have an error in your code: "DbeInfo(COMPONENT_DATA, CDXDBE_LOCKRETRY, 1000000)" will actually change the Memo Block size of the FPT Memo files, rather than change the CDX Index Lock Retry. To change the Lock Retry, you need to use "COMPONENT_ORDER" and not "COMPONENT_DATA" in that function. So, change it to: "DbeInfo(COMPONENT_ORDER, CDXDBE_LOCKRETRY, 1000000)" Your code is actually equivalent to: "DbeInfo(1, 1002, 1000000)" in the *.ppo file, which is the same as: "DbeInfo(COMPONENT_DATA, FOXDBE_MEMOBLOCKSIZE, 1000000)" while it should actually be: "DbeInfo(2, 1002, 1000000)" You could also add the following lines, if you like: DbeInfo(COMPONENT_DATA, FOXDBE_LOCKRETRY, 1000000) DbeInfo(COMPONENT_DATA, FOXDBE_LOCKDELAY, 10) DbeInfo(COMPONENT_ORDER, CDXDBE_LOCKDELAY, 10) Also, as you use: "DbeInfo(COMPONENT_DATA, FOXDBE_CREATE_2X, .T.)" you should probably also add: DbeInfo(COMPONENT_ORDER, CDXDBE_MODE, CDXDBE_FOXPRO2X) And if you use: "cmxfox52.obj to force Fox 2.x locking compatability", you probably should use: DbeInfo(COMPONENT_DATA, FOXDBE_LOCKMODE, FOXDBE_LOCKMODE_2X) rather than "FOXDBE_LOCKMODE_VISUAL", which is VFP 5.x-9.x compatible. Neither of those code issues will probably fix (or affect) the slow-down problem you encountered, which is probably caused by the SMB configuration that Jim already mentioned. Hope this helps anyway, Andreas Andreas Gehrs-Pahl Absolute Software, LLC phone: (989) 723-9927 email: Andreas@AbsoluteSoftwareLLC.com web: http://www.AbsoluteSoftwareLLC.com [F]: https://www.facebook.com/AbsoluteSoftwareLLC | |
Tim Callahan | Re: Degrading client performance as database files are opened locally on the server on Mon, 27 Nov 2017 13:52:44 -0800 Sorry for the late thank you. That helped but we still saw poor performance when several clients accessed the database. I tried tweaking oplocks, SMB1/2, etc. to no avail. After working with Alaska support this is expected behavior in Window Server environments > 2003. I have switched to using ADS which provides the needed performance. Tim On Sat, 12 Aug 2017 04:20:04 -0400, Andreas Gehrs-Pahl wrote: >Tim, > >In your DbeSys() program, move the line: "DbeSetDefault("FOXCDX")" right >after "DbeBuild(...)" and before the first call to DbeInfo(). Or just >leave it out completely, as calling DbeBuild() automatically includes >DbeSetDefault(). > >You also have an error in your code: > >"DbeInfo(COMPONENT_DATA, CDXDBE_LOCKRETRY, 1000000)" will actually change >the Memo Block size of the FPT Memo files, rather than change the CDX Index >Lock Retry. To change the Lock Retry, you need to use "COMPONENT_ORDER" and >not "COMPONENT_DATA" in that function. So, change it to: > >"DbeInfo(COMPONENT_ORDER, CDXDBE_LOCKRETRY, 1000000)" > >Your code is actually equivalent to: > >"DbeInfo(1, 1002, 1000000)" > >in the *.ppo file, which is the same as: > >"DbeInfo(COMPONENT_DATA, FOXDBE_MEMOBLOCKSIZE, 1000000)" > >while it should actually be: > >"DbeInfo(2, 1002, 1000000)" > >You could also add the following lines, if you like: > >DbeInfo(COMPONENT_DATA, FOXDBE_LOCKRETRY, 1000000) >DbeInfo(COMPONENT_DATA, FOXDBE_LOCKDELAY, 10) >DbeInfo(COMPONENT_ORDER, CDXDBE_LOCKDELAY, 10) > >Also, as you use: "DbeInfo(COMPONENT_DATA, FOXDBE_CREATE_2X, .T.)" >you should probably also add: > >DbeInfo(COMPONENT_ORDER, CDXDBE_MODE, CDXDBE_FOXPRO2X) > >And if you use: "cmxfox52.obj to force Fox 2.x locking compatability", you >probably should use: > >DbeInfo(COMPONENT_DATA, FOXDBE_LOCKMODE, FOXDBE_LOCKMODE_2X) > >rather than "FOXDBE_LOCKMODE_VISUAL", which is VFP 5.x-9.x compatible. > >Neither of those code issues will probably fix (or affect) the slow-down >problem you encountered, which is probably caused by the SMB configuration >that Jim already mentioned. > >Hope this helps anyway, > >Andreas |