Hi guys

I don´t know if I understand what is happening but I have almost the same
situation in the past and after I analize what is really happened, using
Performance Monitor, I have realized the problem was disk access (to heavy
traffic). I did two basic things. It was running on Win 64 and 4 Gb RAM and
it was upgraded to 8Gb RAM (windows 2008 runs much much better with 8 Gb)
and replace the hard disk with a 15K rpm. It pratically solved the problems.

Just remember, actually the problems with performance, at least for me, are
in the sequence:
disk, memory, processor
and almost all the times the problem is write disk bottleneck.

Eduardo

Tony,

<< I have tried multiple clients talking to a single edbsrvr, again this
single edbsrvr runs at most 100%, so no improvement over using a single
client. However the fact that this runs at 100% suggest that the processing
is not limited by waiting for IO (i.e disk, network access etc). Resource
Monitor tells me that running like this, edbsrvr has 13-14 threads >>

I'm not sure what is going on, but if you'd like to send me your setup (or
an approximation of it) for running the multiple clients against the same
EDB Server, I'll be happy to take a look here and see what I can find out.
I'm not sure if there's anything I can do to affect this issue, though, if
the OS just decides for whatever reason to not schedule threads on free
cores/processors.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tony,

I was trying out the new EDB build and decided to try a simple app that just loads CSV file into EDB database and in the process try to max out the cpu.
Setup : 1.3million line CSV , app just parses it and inserts records into EDB tables. Each app instance inserts into own own table, running on Win7, 4-core i7 CPU with hyperthreading (8 cores visible to windows)
If app maxes out a core then it should have approx 12-13% CPU utilization in the task manager (max is 12.5% but task manager rounds up it seems). Finally the csv and edb catalog+ db files were stored on SSD so disk I/O issues should be minimal

Anyways, when i ran 4-6 copies of the app against a EDBServer i saw EDBServer utilization in the 38-40% range : hence at least on my system the EDBServer definitely gets scheduled on multiple CPUs and will take advantage of those (40% could be read as 3+CPU cores maxed out though in reality it was distributed on most of the cores).

Apps themselves ran in the 6-7% CPU each + EDBManager around 40% so overall CPU utilization was hovering around 80% so this simple test did max out the system reasonably well.

I also ran apps both in local file as well as remote (127.0.0.1 edbserver) and results in my case were as follows:
- local mode exclusive table access : 280000 record per sec on average
- local mode non-exclusive access : 11600 record per sec
- remote exclusive : 5650 records per sec
- remote non-exclusive : 4280 records per sec

I also tried 64bit versions and there was no difference between 64 and 32bit apps with local file access.
64bit edbserver did not make any difference either (nor did i expect it to).

However the 64bit client app never did finish running against the edbserver (64bit server) - one or more instances always ended up with the 'ElevateDB Error #1100 A connection to the server at '127.0.0.1' cannot be established ('The stream signature was not received')" at in the process )varies so not consistent). 32bit client completed every time (against 64bit server) so there seems to be something different about 64bit client or most likely embarcadero needs to work on the 64bit compiler some more

Raul




<<
I'm not sure what is going on, but if you'd like to send me your setup (or
an approximation of it) for running the multiple clients against the same
EDB Server, I'll be happy to take a look here and see what I can find out.
I'm not sure if there's anything I can do to affect this issue, though, if
the OS just decides for whatever reason to not schedule threads on free
cores/processors.

>>

Tim

I'm in the process of getting an SSD drive installed into our server, which hopefully will help. I'm also trying to rejig the migration process so as to remove the need for so much data I/O. That is, at present we :

   create unconstrained database,
   load dirty legacy data,
   clean data,
   create constrained database,
   copy data from unconstrained to CSV
   copy data from CSV to constrained database.

I'm trying to replace that procedure with :

   create unconstrained database,
   load dirty legacy data,
   clean data,
   apply constraints

Originally (before my time, a few years ago), the first method was considered faster, but i'm hoping that with the newer versions of ElevateDB the latter will now be much faster for large data sets.

Anyway, having said all that, I'll hold off sending you my code and data for now while we see if the SSD and rejig helps.

Thanks very much
Tony Wood


"Tim Young [Elevate Software]" wrote:

Tony,

<< I have tried multiple clients talking to a single edbsrvr, again this
single edbsrvr runs at most 100%, so no improvement over using a single
client. However the fact that this runs at 100% suggest that the processing
is not limited by waiting for IO (i.e disk, network access etc). Resource
Monitor tells me that running like this, edbsrvr has 13-14 threads >>

I'm not sure what is going on, but if you'd like to send me your setup (or
an approximation of it) for running the multiple clients against the same
EDB Server, I'll be happy to take a look here and see what I can find out.
I'm not sure if there's anything I can do to affect this issue, though, if
the OS just decides for whatever reason to not schedule threads on free
cores/processors.

--
Tim Young
Elevate Software
www.elevatesoft.com

Raul

Thanks for the considerable effort you have put in to try and help !

As i mentioned above, I'm getting an SSD installed into the migration server. It seems as though you had much better results than me, so hopefully the SSD will make the difference (I picked up a 120Gb Kingston HyperX SATA3 and am waiting for the IT guys to fit it ....). In particular your 'local' results are dramatically better than "remote", whereas I found no perceptible improvement dispensing with edbsrvr.

A few questions on your method though (please understand I'm a ElevateDB newb and don't know Delphi) :

Exclusive / non-exclusive access - do you configure this somehow (if so how?), or do you mean that the tables have no FK relationships and so may be accessed / loaded simultaneously.

>>> Apps themselves ran in the 6-7% CPU each + EDBManager...
Presumably this means your Apps somehow depend on EDBManager - can this only be done from Delphi ? The only way I know to use EDBManager is to manually load and run SQL scripts so would be interested to know how to automate running 'Apps'

>>>  i saw EDBServer utilization in the 38-40% range
This suggests I must be doing something wrong as my edbsrvr(s) stubbornly refuses to go above 6 or 13% (depending on the number of CPU cores). The best i've seen is 1 edbsrvr at 13% and one other at half that.

One final generic question : If I was able to create a local DB, could this then be somehow converted to be a remote DB (which is a what our main application expects to see after the migration process has finished) ?

Thanks again for you help, I'll post my results once  I get the SSD fitted.
Tony Wood (Melbourne, Aus)


Raul wrote:

Tony,

I was trying out the new EDB build and decided to try a simple app that just loads CSV file into EDB database and in the process try to max out the cpu.
Setup : 1.3million line CSV , app just parses it and inserts records into EDB tables. Each app instance inserts into own own table, running on Win7, 4-core i7 CPU with hyperthreading (8 cores visible to windows)
If app maxes out a core then it should have approx 12-13% CPU utilization in the task manager (max is 12.5% but task manager rounds up it seems). Finally the csv and edb catalog+ db files were stored on SSD so disk I/O issues should be minimal

Anyways, when i ran 4-6 copies of the app against a EDBServer i saw EDBServer utilization in the 38-40% range : hence at least on my system the EDBServer definitely gets scheduled on multiple CPUs and will take advantage of those (40% could be read as 3+CPU cores maxed out though in reality it was distributed on most of the cores).

Apps themselves ran in the 6-7% CPU each + EDBManager around 40% so overall CPU utilization was hovering around 80% so this simple test did max out the system reasonably well.

I also ran apps both in local file as well as remote (127.0.0.1 edbserver) and results in my case were as follows:
- local mode exclusive table access : 280000 record per sec on average
- local mode non-exclusive access : 11600 record per sec
- remote exclusive : 5650 records per sec
- remote non-exclusive : 4280 records per sec

I also tried 64bit versions and there was no difference between 64 and 32bit apps with local file access.
64bit edbserver did not make any difference either (nor did i expect it to).

However the 64bit client app never did finish running against the edbserver (64bit server) - one or more instances always ended up with the 'ElevateDB Error #1100 A connection to the server at '127.0.0.1' cannot be established ('The stream signature was not received')" at in the process )varies so not consistent). 32bit client completed every time (against 64bit server) so there seems to be something different about 64bit client or most likely embarcadero needs to work on the 64bit compiler some more

Raul




<<
I'm not sure what is going on, but if you'd like to send me your setup (or
an approximation of it) for running the multiple clients against the same
EDB Server, I'll be happy to take a look here and see what I can find out.
I'm not sure if there's anything I can do to affect this issue, though, if
the OS just decides for whatever reason to not schedule threads on free
cores/processors.

>>

Raul :

>> First, are you using the ebsrvr as shipped by Elevate?
Yes, except when running multiple instances of edbsrvr, i change the name to edsrvr0, edbsrvr1 etc...


>> If you check it in task manager and use "set affinity" option does it show that all CPUs are selected? This is >> just to ensure its not limited by CPUs it can access.
Yes


>>Do you need to use edbsrrvr? It is afterall just another edb application (though written by the author of edb) so >>running with direct file access mode would allow you to basically cut the overhead of IP communication and
>>theoretically get max performance. You mentioned you can load individual tables independently so doign >>exclusive access on a local DB file directly might result in this being faster. It might be worthwhile to switch to >>local session in your loader app and see what kind of speed you can achieve

Great idea, got me all excited, but unfortunately didn't seem to improve matters, once i figured out how to edit my connection string to connect locally through ODBC



Raul wrote:

Tony Wood,

Tim is likely only one able to properly answer this question as this requires fairly detailed edbsrvr and edb knowledge.

However i will speculate  - beware that what follows is likely wrong:

First, are you using the ebsrvr as shipped by Elevate?
If you check it in task manager and use "set affinity" option does it show that all CPUs are selected? This is just to ensure its not limited by CPUs it can access.

It is quite possible that your scenario is running into a bottleneck either in edbsrvr or edb engine itself. As has been mentioned dbsrvr is multi-threaded but i don't know for example if all those threads have individual access to actually write data or whether that is handled by one or few main engine threads. Hence your scenario where you are basically acting as data-pump might result in database writes queuing up. Alternatively there will be some lock management and other control going on inside the edbsrvr/engine so again you might have hit the max thruput.

Running individual apps against their own edbsrvr does seem to indicate that there is bottleneck somewhere (since disk and network should have limited these similarly to single edbsrvr - and you're not seeing that)

Do you need to use edbsrrvr? It is afterall just another edb application (though written by the author of edb) so running with direct file access mode would allow you to basically cut the overhead of IP communication and theoretically get max performance. You mentioned you can load individual tables independently so doign exclusive access on a local DB file directly might result in this being faster. It might be worthwhile to switch to local session in your loader app and see what kind of speed you can achieve. Possibly a hybrid solution might work depending on your data - run the db locally and smaller (if there is such thing) remotely back to central edbsrvr

Raul

TonyWood

>Exclusive / non-exclusive access - do you configure this somehow (if so how?), or do you mean that the tables have no FK relationships and so may be accessed / loaded simultaneously.

Its a switch on the engine component - defaults to False and if set grabs the files for exclusive Windows access. Because of the way Windows works (I assume its something to do with locking & buffering) exclusive access is faster than shared access. If you search you'll find posts about the second user really slowing things down.

>>>> Apps themselves ran in the 6-7% CPU each + EDBManager...
>Presumably this means your Apps somehow depend on EDBManager - can this only be done from Delphi ? The only way I know to use EDBManager is to manually load and run SQL scripts so would be interested to know how to automate running 'Apps'

I'll wait for Raul but I'd guess its a typo and should say server

>>>> i saw EDBServer utilization in the 38-40% range
>This suggests I must be doing something wrong as my edbsrvr(s) stubbornly refuses to go above 6 or 13% (depending on the number of CPU cores). The best i've seen is 1 edbsrvr at 13% and one other at half that.
>
>One final generic question : If I was able to create a local DB, could this then be somehow converted to be a remote DB (which is a what our main application expects to see after the migration process has finished) ?

Easy Its basically a matter of moving the files and an ALTER DATABASE command.

Roy Lambert [Team Elevate]

Tony,

No problem - i was trying out the new EDB release and this was as good of a test program as any. I find these NGs have helped me out a lot in the past so happy if this might be of use to others.

<<
It seems as though you had much better results than me
>>
Mine is a very simple app  (load from CSV) that loads simple tables (no FKs, etc) so i would expect a real-world app to be slower.

<< In particular your 'local' results are dramatically better than "remote", whereas I found no perceptible improvement dispensing with edbsrvr.
>>
I have generally found local to be always faster as long as bottleneck is the network/server - meaning client app can feed it faster than it can process. If your local and remote are same speed then I'm back to thinking the slowdown is not in edb side but in loading app possibly.

I had made a typo in local results (extra 0) so here are revised numbers:

Remote - 4280  (100%)
Remote Exclusive - 5650 (132%)
Local - 11600 (271%)
Local Exclusive - 28000 (654%)

Local in my case is some 3 times faster and local exclusive mode being fastest as expected. Note that in both cases data was stored on SSD. So I just ran the test on my regular drive (7200rpm drives in raid1) :
- remote - 4100  (100%)
- remote exclusive - 5700 (139%)
- local - 7300 (178%)
- local exclusive - 17500 (426%)

Speed differences remained but it's clear the remote version is not constrained by disk only (which makes sense considering data travels thru a "relatively slower" IP stack) while local versions had quite a speed drop since disk could not keep up with ssd. Note that overall differences are still consistent in that local exclusive is fastest and remote non-exclusive is slowest.

<<
Exclusive / non-exclusive access - do you configure this somehow (if so how?), or do you mean that the tables have no FK relationships and so may be accessed / loaded simultaneously.
>>
Both actually - the tables are independent and can be loaded simultaneously and in this case each app instance opens its table exclusively to its own use. I'm using EDBTable component so i'm just setting the Exclusive property to true (e.g. EDBTable1.Exclusive :=  truewhen opening it.
Manual for more details:
http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=rsdelphiwin32uni&version=XE2&comp=TEDBTable&prop=Exclusive

<<
>>> Apps themselves ran in the 6-7% CPU each + EDBManager...
Presumably this means your Apps somehow depend on EDBManager - can this only be done from Delphi ?
>>
Sorry - a typo. I meant apps take 6-7% and EDBServer instance they connect to takes up to 40%. EDBManager is just another delphi app so you can easily duplicate its functionality (like running scripts) in your own delphi app.

<<
This suggests I must be doing something wrong as my edbsrvr(s) stubbornly refuses to go above 6 or 13% (depending on the number of CPU cores). The best i've seen is 1 edbsrvr at 13% and one other at half that.
>>
Assuming you're seeing 6% in 16-core and 13% on 8-core then yes your edbserver is somehow single CPU bound.


<<
One final generic question : If I was able to create a local DB, could this then be somehow converted to be a remote DB (which is a what our main application expects to see after the migration process has finished) ?
>>
Roy i think answered this already if you need to remove it to another system.

In my case it's all on the same computer so they are the same thing - i just configured the edbserver and my app to reference same config folder (my app simply uses local session to connect direct). App can either use remote session or local session but in both cases is the same database so i don't need to make any changes.

Raul

Tony,

<< Originally (before my time, a few years ago), the first method was
considered faster, but i'm hoping that with the newer versions of ElevateDB
the latter will now be much faster for large data sets. >>

Seeing that it eliminates an entire copy step, you should definitely see an
improvement.

<< Anyway, having said all that, I'll hold off sending you my code and data
for now while we see if the SSD and rejig helps. >>

Sounds good.  Feel free to post an update when you get everything working.
You could very well be getting disk-bound, which would explain why the CPU
usage is so low and why the OS never schedules any of the threads on a
different CPU.

--
Tim Young
Elevate Software
www.elevatesoft.com