PDA

View Full Version : Performance issues



FedeB
2014-05-06, 15:17
I'm running some tests to evaluate the viability of migrating our Project from VFP (Visual FoxPro) 9 to Lianja.
We have a large database system so performance is an issue.
We need Lianja to be as fast as VFP in data processing.

My actual system is Windows 2012 R2 Standart (x64), 16gb ram, Intel Xeon E5-2660@2.2GHz (16 virtual cores).
I'm working with a .DBI with memo. The table has 4.000.000 records (4 millon).
TABLE.DBI 1.5gb
TABLE.FPT 0.6gb
I imported the table to a TEST database in Lianja.
It took over 30 min to get the importation done (a long time i may say).
The result was:
TABLE.DBF 1.6gb
TABLE.DBT 2.34gb

The first thing i noticed is that memo file grew 4 times. Why is that?

Now for the performance issue.
I run a simple VFP script for testing purposes:

nCount = 0
USE TABLE
SCAN
ncount = ncount + 1
ENDSCAN

This code time were in Lianja 1.1.2 (working with the imported table):
1) 164 sec
2) 179 sec
3) 166 sec

The same code (working with the original .DBI files):
1) 37 sec
2) 2 sec
3) 2 sec

We can see 2 things. VFP is way faster in this test.
The other thing is thas seems that VFP took advantaje of Windows cache after the first run.

Another important thing is that in versión 1.1.0 of Lianja working in a Windows 2008 with 4gb ram, the time was:
1) 26 sec
2) 28 sec
3) 30 sec

In VFP 9 the times were:
1) 38 sec
2) 6 sec
3) 7 sec
I don't know what went wrong.

Anybody run test like this?
Is there a better way to test performance?

Thanks,
Federico.

hmischel@diligentsystems.com
2014-05-06, 16:03
Hi Federico,

I performance test differently than you do.

I use VFP (and Lianja) as front and middle tier. My data resides on SQL Server not in a DBF flat file.
So while I have hundreds of millions of rows, I only query and return those I need into my VirtualTable. Making the type of testing you are doing not very relevant for my types of apps.

Sorry.

Does your app architecture lend itself to using Lianja SQL Server, MS SQL Server, Oracle etc?


Herb

barrymavin
2014-05-06, 21:09
Hi Federico,

Nothing changed in v1.1.2 from v1.1.0 that would affect your timings so either the files are different or there is some other difference on the machine you are now using. You may want to check the debug directory and make sure there was no debugging left in v1.1.2 also.

This is not a real world test in a multiuser system which is where Lianja comes out ahead and VFP performance drops dramatically.

VFP will cache data internally so will Lianja if asked to do so but 4m records is a lot to cache. You do not mention the record size but based in the table size in single user mode VFP will cache the whole 1.6GB which is getting close to its 32 bit maximum address range.

it's obvious that there is a lot if caching going on so what you need to determine what is the difference between your v1.1.0 and v1.1.2 tests as they are clearly not the same.

the import time is purely reading the 4m records and converting them internally using native C++ so you may have a lot of disk fragmentation or be low on memory / diskspace.

also you should close VFP as it may be caching all the data internally and starving Lianja from memory which would result in a lot of paging activity.

if all your app needs to do is process large tables in a scan/endscan loop I would suggest buying an SSD.

barrymavin
2014-05-06, 21:15
Sorry I did not answer about the memo file, that looks wrong which leads me to think the original memo file is corrupted in some way.

barrymavin
2014-05-06, 21:29
See this thread also.
http://www.lianja.com/community/showthread.php?830-Initial-Testing-Speed-Indexes-and-VFP-noSQL-commands

barrymavin
2014-05-07, 00:37
On my machine I created a table of 4m records and ran your tests.

674

With no caching specified.

676

And now after optimizing the cache sizes to the optimum size (this is calculated based on the file size). You can reduce this using SET DCACHE TO <expN> to reduce memory usage if the table is very large but still see big performance improvements.

675

So if you are wanting to do a lot of data crunching you need to adjust the cache. I have made a few changes in v1.1.3 which should speed that up even more.

In v1.1.3 I have increased the DCACHEBLOCKSIZE for best performance at the expense of more memory usage when SET DCACHE is ON.

You can tune the table cache with these commands e.g..



set dcache on
set dcache to optimum
set dcache to 100
set dcacheblocksize to 4096*8


You may want to match your DCACHEBLOCKSIZE with the settings for NTFS. Here's a (oldish) link that discusses various performance tuning tips for NTFS.
http://answers.microsoft.com/en-us/windows/forum/windows_7-performance/ntfs-performance-settings/4fd613d2-490a-4521-978b-9cb86deabf90

So generally speaking, based on what it is you want to do play about with the DCACHE configuration and find the sweet spot for your App and what it is you are wanting to achieve.

Disk I/O performance is always a trade off between memory utilization, network bandwidth (if on a LAN), and disk speed (SSD recommended nowadays).

And finally, you can see what the table cache is in v1.1.3 using LIST STATUS

677

FedeB
2014-05-07, 13:38
If i use:
SET DCACHE ON
SET DCACHE TO OPTIMUM
I get lianja.exe to grow up to 1760mb of ram (out of 16gb).
679

If i use:
SET DCACHE ON
SET DCACHE TO 1000
Memory never go over 100mb (the same goes with SET DCACHE TO 10, 100, 1000 or 10000)

What does expN stands for? MB.?

barrymavin
2014-05-07, 20:05
You already posted that on a ticket and I already replied.

yes it will use a lot of memory because you asked it too. You asked it to cache the whole of a 4m record table with large records in memory. That's what it does.

To answer you question, the value you specify to SET DCACHE is the internal buffer size (disk read size) which I thought I had fully explained earlier in this thread with screenshots.

As I already mentioned in v1.1.3 (when generally available) you can use SET DCACHEBLOCKSIZE to specify the size of the cache read size.

FedeB
2014-05-08, 09:00
I wasn't complaying about memory use.
I was triyng to understand how to set DCACHE correctly.
I'll wait for 1.1.3 to try the additional commands.
I post here because i think someone else may find this topic interesting.
But i'll stick to tickets if you think it's better.
Thanks.

barrymavin
2014-05-08, 09:23
No its good to discuss. Tickets are for bug reporting. The forums are for sharing knowledge. So...

If you have a large table and you want to take advantage of the caching but keep memory usage down the best way is to do this.



set dcache on
set dcacheblocksize to 1024*64
set dcache to 100


The way the caching works is like a normal virtual memory system. The cache for each table has an array of "dcacheblocks". These are loaded with the correct disk blocks if they collide.

What this does in effect is reduce the disk I/O calls and make one big one that can be read in one go. Subsequent records are read from that cache.

With smaller tables ( not 4m records :) ) you can load them all and reduce disk I/O if you want to make several passes over the data.

FedeB
2014-05-20, 14:49
Just wondering ... did you try your barry.prg on a Windows 2012 R2 Standart (x64)?
It seems to me that maybe the OS has something to do with this.
I'll try on Windows 2008 to compare results.

barrymavin
2014-05-20, 14:57
You don't yet have v1.1.3, when you do, you can then see.