Firebird .Net multithreading

[Lasse Hansen]

Hi

I have a firebird 2.5.9 installation which causes some issues when under load.

I am using the .Net 6.0 provider with a connection string that looks like this.

 host=***;User=***;Password=***;Database=Vinterman;Port=3050;Dialect=3;Charset=UTF8;Pooling=true;MinPoolSize=0;MaxPoolSize=200;

I have a simple test program which start a number of threads and runs a simple query using LINQ: 

await context.IdpIdRelations.Where(x => x.IdpUniqueId == email).ToListAsync();

The IdpIdRelations table is indexed on the field IdpUniqueId.

Running this with one thread results in a execution time of 0.6 seconds.
Running 200 threads makes all threads take  2.7 seconds.
Even more threads makes the execution time of each thread bigger.

The reason I have made this program is because we are seeing serious performance problems under load.

We have a superclassic installation but have also tried with a classic installation and the same issue occurs.

Running the threads directly through isql seems like it is faster (it completes the first threads before the rest have started).

Is there anything wrong with the .Net provider? Am I using it wrong? Or any other guess as to why this issue occurs?

[Jiří Činčura]

> I am using the .Net 6.0 provider with a connection string that looks like this.

That's quite an old version. I would strongly recommend upgrading to latest 9.x.

> Running this with one thread results in a execution time of 0.6 seconds.
> Running 200 threads makes all threads take 2.7 seconds.
> Even more threads makes the execution time of each thread bigger.

Please share your code. And also explain what real-world scenario are you trying to replicate.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Lasse Hansen]

Thanks for the reply

My mistake in asking the question. What I meant was that I use the .Net provider version 9.1.1 in a .Net 6.0 project.
Here is the code (not sure if there is a better way to format it

        public async static Task LoadTestDatabase(string connectionString)
        {
            var stopwatch = new Stopwatch();
            var numberOfThreads = 5000;
            // Create an array to hold references to the tasks.
            Task[] tasks = new Task[numberOfThreads];
            // Start 1000 tasks to execute an async method.
            for (int i = 0; i < numberOfThreads; i++)
            {
                int threadNumber = i;
                tasks[i] = Task.Run(() => SimpleCall(threadNumber, connectionString, "email"));

            }    
            // Wait for all tasks to complete.
            await Task.WhenAll(tasks);

            Console.WriteLine($"All threads have completed in {stopwatch.Elapsed}");
        }

        private async static Task SimpleCall(int threadNumber, string connectionString, string email)
        {
            var stopwatch = new Stopwatch();
            using (var context = new MyContext(connectionString))
            {

                stopwatch.Start();
                Console.WriteLine($"Starting thread {threadNumber}");
                try
                {
                    var idpRelations = await context.IdpIdRelations.Where(x => x.IdpUniqueId == email).ToListAsync();                  
                    Console.WriteLine($"Finished thread {threadNumber} in {stopwatch.Elapsed}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Exception on thread {threadNumber}: {ex.Message}");
                }
            }

        }

The real world scenario is we have a microservice hosted on Kubernetes and a mobile application. The mobile application tracks the users position and sends it to be stored in the database every 2 minutes. We are expection a peak of 12000 users.

[Lasse Hansen]

The numbers are a bit off in the code provided because I have teste with anywhere from 1 - 5000 threads. I have of course also updated the MaxPoolSize of my connection string to match the thread number

[Gerdus van Zyl]

I did some tests and could not replicate your results. Using threads to process the location updates takes less time than running it serially. There are of course overheads in creating tasks/threads and transaction management.
Running a SQL statement without EF can also be faster.

If possible use a background worker to batch updates into one transaction that will process much quicker.

Some results of 10 000 location updates:

| Method             | ConnectionPooling | Mean    
| OneTransaction     | False             | 77.959 s
| OneTransactionSQL  | False             |  2.861 s
| TransactionPerUser | False             | 42.594 s
| Batch100           | False             |  7.443 s
| Batch100Sql        | False             |  5.861 s

| OneTransaction     | True              | 77.779 s
| OneTransactionSQL  | True              |  2.989 s
| TransactionPerUser | True              |  8.909 s
| Batch100           | True              |  7.394 s
| Batch100Sql        | True              |  6.162 s

Code available here: https://infireal.com/firebird-list/BenchmarkFirebirdScale.zip

[Steve Naidamast]

Using threads has never been a way to increase performance.  Instead, they are used to create the "perception" of better performance.

Threads were primarily designed to allow for parallel processes that did not rely on each other's data.  For example a long running data sort in the background, while an interface is being drawn for a user.

This is not to say that threads cannot increase performance but this requires a hardware foundation that can support the use of multiple threads.

The standard for each core that can do so is approximately 10 threads apiece.  The more threads you want to run optimally, the more cores are required.  Thus, in your example, 200 threads would require approximately 20 cores to run efficiently.  Please note that with newer developments in hardware over the years this standard may have been increased but 10 threads has been the original standard to go by.

Finally, Gerdus van Zyl's suggestion that you do not use the Entity Framework, or any ORM for that matter, is spot on.  Such frameworks were never designed, again for performance purposes, as many may have come to believe.  Instead, their primary asset is that they allow developers to refine their processes against existing databases instead of resorting to low-level development with a standard data access layer.

Any such ORM will always add inefficiency to database processes since the frameworks are fairly heavy and ignore the use of the most efficient technique for data access, the stored procedure.  With such frameworks, using stored procedures then makes little sense (and redundant) since one is using a very heavy piece of software to process a task that does not require it.

Steve Naidamast

Sr. Software Engineer

[Jiří Činčura]

> The real world scenario is we have a microservice hosted on Kubernetes
> and a mobile application. The mobile application tracks the users
> position and sends it to be stored in the database every 2 minutes. We
> are expection a peak of 12000 users.

How does this translate to having X number of threads? Given the insert into the database is going to be very fast, I assume - that's the "requirement", this is not going to grow pretty much a bit over thread pool size (unless you do some terrible blocking). BTW this translates into 100 RPS on average. Not a huge number. And your benchmark is actually testing something completely different.

Also, you probably want the response to be as fast as possible. Thus maybe a better design is going to be to put the data into some queue and then have fixed amount of workers storing into database (from the queue). This will give you blazing fast response times and also is not going to kill the database in case something goes wrong. Plus you can scale independently.