TMyDump restore database performance

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
vindac
Posts: 20
Joined: Tue 18 Mar 2014 16:44

TMyDump restore database performance

Post by vindac » Wed 27 Jun 2018 14:53

Hello,

I need to restore a lot (150000) of database backups, lookup a value and delete the database again. I'm using TMyDump.RestoreFromFile for this in multiple threads (each thread always having its own connection and TMyDump).

With 1 thread performing the restores, I can process 0.57 fps (files per second)
With 10 threads, I get 1.07 fps.

The cpu does not seem to be the bottleneck, neither the disk (SSD) or the network.

Then I tried using 5 different mysql servers (fysical different servers in our LAN) for 10 threads. That means each mysql server gets used by 2 different threads (connections) : I only get 0.94 fps.


I don't understand why I can't get the speed up this way. The processing (restore) now is getting done on 5 different servers, so why do I still get almost the same speed? It seems like there is a local bottleneck somewhere?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: TMyDump restore database performance

Post by ViktorV » Mon 02 Jul 2018 08:16

Most likely, this behavior does not depend on our components.
In this case, it turns out that execution of all threads occurs on one processor core, that negatively affects the performance.
This behavior can also be reproduced on standard Delphi components (TClientDataSet). We included such a sample: https://www.devart.com/pub/dac/ThreadsTest.zip

Also, in the sample, we demonstrated two other situations:
- a thread performs a simple cycle: threads are executed on different processor cores;
- string concatenation is used in the thread: threads are executed on one processor core.

You can ask about the operation specificity of multi-threaded applications at specialized resources, for example, the Embarcadero forum.

vindac
Posts: 20
Joined: Tue 18 Mar 2014 16:44

Re: TMyDump restore database performance

Post by vindac » Thu 05 Jul 2018 08:21

Thank you ViktorV for the help and the code sample! It gives me the means to look further!

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: TMyDump restore database performance

Post by ViktorV » Thu 05 Jul 2018 10:00

Thank you for the interest in our products.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.

Post Reply