I noticed that I didn't renew my support/upgrade while visiting your site so I took care of that.
I recently moved my Coldfusion server from windows 2003 32 bits to windows 2008 r2 64 bits. I'm using several database like mysql, access and hxtt dbf. Since the migration i've been having an issue with very slow INSERTs in my HXTT database but my other databses work perfectly well.
I copied the settings from the old server and I used the same JAR file. I kept the Coldfusion server in 32 bits mode.
Every INSERT takes from 600ms to 900ms and normaly it is 1ms to 3ms per INSERT. I suspected that the anti-virus could be messing up with the files as they were updated but HXTT acts the same with the anti-virus disabled. I also upgraded to the latest release of HXTT with no differences. The READs seem to be ok, only the INSERTs are affected.
Do you have any ideas about what could be causing that behavior?
Thanks in advance.
JF.
|
>I noticed that I didn't renew my support/upgrade while visiting your site so
>I took care of that.
Thanks. Please download the latest package, although I don't think that' the key for your issue.
>Every INSERT takes from 600ms to 900ms and normaly it is 1ms to 3ms per INSERT.
What's your JDBC url setting? Whether you have lockType connection property?
|
I copied the settings I had before. I'm using this:
jdbc:dbf:///d:/admino/c001/?lockType=Alaska;delayedClose=3;emptyDecimalAsZero=true;ODBCTrimBehavior=true;COMPACTEDINDEX=true
I still have the old server running and it is the exact same URL. The only difference is the OS which is 2008 R2 64 bits and it was 2003 32 bits before. This is really strange, I have never encoutered anything like that in my test setups with your software. I'm just asking if you have any idea what could be slowing down the INSERTs that much.
Here's an example of ONE insert on the new server with a time of 446ms:
InsertXIN (Datasource=adminolocal_jdbc, Time=446ms, Records=1) in C:\xampp\htdocs\dmanic\COM_fermtrans_2.cfm @ 20:28:53.053
INSERT INTO XIN(CAT,CNO,DES,ENT,ESI,EXT,INV,ITM,PDS,PJN,PRX,QTE,QTY,SER,TXI,VOL,DIV,KNI)
VALUES ('86L','10430','LABRADOR 18 LITRES',1,0.00,1,20012842,'L180',48.00,1,6.000,0.00,1.00,'',0,1.00,{ts '2011-09-20 00:00:00.0'},0)
Here's another insert on the OLD server (soon to be formatted) with a time of 15ms:
InsertXIN (Datasource=adminolocal_jdbc, Time=15ms, Records=1) in C:\xampp\htdocs\dmanic\COM_fermtrans_2.cfm @ 19:42:33.033
INSERT INTO XIN(CAT,CNO,DES,ENT,ESI,EXT,INV,ITM,PDS,PJN,PRX,QTE,QTY,SER,TXI,VOL,DIV,KNI)
VALUES ('D5L','10430','--dp cruches Labrador',1,0.00,1,20012772,'LZZZ',0.00,0,10.000,0.00,1.00,'',0,0.00,{ts '2011-09-20 00:00:00.0'},0)
|
I can confirm that my problem is still there with the latest package.
As you (and I) suspected, it is not related to the release.
Thanks in advance.
JF.
|
>lockType=Alaska;
I don't think your old Alaska software can work still on Windows 2008 R2 64 bits. If there's only Java software to use that database on Windows 2008, you can remove that lockType connection property.
>Here's another insert on the OLD server (soon to be formatted) with a time of 15ms:
>Here's an example of ONE insert on the new server with a time of 446ms:
Please update your old server with the latest package too. If the time is still 15ms, then we should focus on why the Java will be so slow on Windows 2008.
|
The 2008 R2 is a file server, the software that use the Alaska locks is on the clients computers. Only the database reside on the 2008 R2 server. Coldfusion is acessing these DBF files thru the HXTT driver on that same server.
I understand what you're saying about the Java but I'm wondering why the SELECTs and the UPDATEs are ok but only the INSERTs are affected bu the delay.
|
>I understand what you're saying about the Java but I'm wondering why the SELECTs
> and the UPDATEs are ok but only the INSERTs are affected bu the delay.
Yeah. Please check whether your old server can insert quick with the latest package.
|
I made the test and the latest package does fast inserts exactly like the old package used to do. Only the Windows 2008 R2 64bits server inserts slowly either with the old or the latest package.
Thanks.
JF.
|
Any ideas based on our discussion as to why the could be happening? Should I install a different version of java?
Thanks.
JF.
|
|
Whether you're using JDK1.7?
|
It is my understanding that Coldfusion already works on top of their own java runtime called JRUN and that your driver is executed within that environment even if there's no JDK installed.
It is the exact same version of Coldfusion and I even un-installed it and re-installed it just to see. With the same result. I'm not sure it is related to java but it could be something with Windows 2008 R2 that slows down frequent file access?
|
>I'm not sure it is related to java but it could be something with Windows 2008 R2
>that slows down frequent file access?
Try http://www.petri.co.il/how-to-disable-smb-2-on-windows-vista-or-server-2008.htm
In order to disable SMB 2.0 on the server-side computer, follow these steps:
Warning!
Run "regedit" on Windows Server 2008 based computer.
Expand and locate the sub tree as follows.
HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters
Add a new REG_DWORD key with the name of "Smb2" (without quotation mark)
Value name: Smb2
Value type: REG_DWORD
0 = disabled
1 = enabled
Set the value to 0 to disable SMB 2.0, or set it to 1 to re-enable SMB 2.0.
Reboot the server.
|
|
I will do this test today. But the DBF files are local to the HXTT driver. However, they are part of a network share to be accessed by other applications (using Alaska software technology). I'll get back to you later.
|
|
Disabling smb2 on the server didn't have any effect on the slowliness of the inserts.
|
I also tried a remote/server setup on the same machine but the inherent performance degradation of such a setup affect the reads as well as the inserts.
The direct setup problem only affects the inserts, the reads and updates are perfect.
|
|
Please try to remove temporarily lockType=Alaska to see whether the insert speed restore normal.
|
Thanks for the information.
As it turns out, I found the problem. As I suspected it is not related to your driver or Java.
I installed everything on a brand new Dell R310 server and it seems that the Perc H200 raid controler turns off write caching. There's no documented way to turn it back on and the forums are filled with complains about slow write performance with this particular controler.
I made a test and put my data on an externat USB HD and the performances where 3 times better than the internal drive.
For now I will focus on this issue with Dell. I'm still using the June 2010 version of your driver as the newest version gives me different, incorect results but I will get back to you with a separate ticket on that issue.
Thanks again for your help.
|
Just for clarification:
Since DBF and index files are all individual files, the absence of caching will slow performance down with a direct access driver like HXTT as opposed to a sql server like mysql where I don't see any significant performance problem or a MDB file where everything is in the same file.
It is not your fault, it is Dell's fault for selling a raid controller without cache.
When disk caching is present; fast individual file write are is possible because of the buffer it creates between HXTT and the physical disk.
Thanks again.
|
>I'm still using the June 2010 version of your driver as the newest version gives
>me different, incorect results but I will get back to you with a separate ticket
>on that issue.
Thanks. Please let let us know more, then we will recur and fix it.
>When disk caching is present; fast individual file write are is possible because
> of the buffer it creates between HXTT and the physical disk.
HXTT use cache too, but for Xbase compatible mode, it will diccard or disable cache to avoid the obsoleted modified data.
|
Main
webmaster@hxtt.com